Manage Learn to apply best practices and optimize your operations.

Transport List Report Task 1: Create the Project

This excerpt from Chapter 5 of the book Microsoft SQL Server 2005 Reporting Services 2005 explains how to create reports to satisfy business needs using Transport List.

The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book Microsoft SQL Server 2005 Reporting Services 2005 written by Brian Larson. Click here to purchase the book.



Introduction

In Chapter 4, you built your first reports using the Report Wizard. This is like learning to ride your first two-wheeler with the training wheels on. Now it is time for the training wheels to come off, so you can see what this baby can really do! We are going to begin building reports from scratch. We hope these next few chapters provide the handholding you need, and then you can learn to ride sans training wheels without getting skinned knees.

First, we work with the two types of reports you were introduced to in Chapter 4. We begin by building a table report without the use of the Report Wizard. From there, we do the same with a matrix report. After that, we look at two new report types—the chart report and the list report. We end the chapter by working with some of the basic report items that make up each report—namely, the line control, the text box control, and the rectangle control. Along the way, you learn more about the Report Designer that serves as our development platform.

So, the training wheels are off and the wrenches have been put away. Don your helmets; it's time to ride!

Riding Down Familiar Roads

We cover some familiar territory as we begin building reports without the Report Wizard. In Chapter 4, you used the Report Wizard to create table reports (the Customer List Report and the Customer-Invoice Report) and matrix reports (the Invoice-Batch Number Report). We create these types of reports once more but, this time, without the aid of the wizard.

Again, we look at the business needs of Galactic Delivery Services (GDS) and create reports to satisfy those business needs.

The Transport List Report

Features Highlighted

  • Building a GROUP BY clause using the Graphical Query Designer
  • Creating a table report from scratch

Business Need The transport maintenance department at Galactic Delivery Services needs a list of all the transports currently in service. They want this list to be grouped by transport type. The list includes the serial number, the purchase date, and the date the transport was last in for repairs. The list also includes the cargo capacity and range of each transport type.

Task Overview

  1. Create the Chapter05 Project, Create a Shared Data Source, and Create a New Report in the Chapter05 Project
  2. Create a Dataset
  3. Place a Table Item on the Report and Populate It
  4. Add Table Grouping and Other Report Formatting

Transport List Report, Task 1: Create the Chapter05 Project, Create a Shared Data Source, and Create a New Report in the Chapter05 Project

  1. Run the Business Intelligence Development Studio or Visual Studio 2005. The Start page is displayed (or select File | Close Project from the menu if a solution is already open).
  2. Click New Project to create a new project. This displays the New Project dialog box. (Remember, you can create a new project in three different ways: Select File | New | Project from the Main menu, click the New Project toolbar button, or click the Create Project link on the Start page. All these actions achieve the same result.)
  3. Click the Report Server Project icon in the Templates area of the New Project dialog box. (Be sure to click the Report Server Project icon and not the Report Server Project Wizard icon.)
  4. Type Chapter05 for the project name. This project will contain all the reports you create in this chapter.
  5. Click Browse to open the Project Location dialog box.
  6. Click My Projects to go to the Visual Studio Projects folder.
  7. In the list of folders, double-click the MSSQLRS folder.
  8. Click Open in the lower-right corner of the Project Location dialog box. The New Project dialog box should now look like this:

  9. Figure 1

  10. Click OK in the New Project dialog box. A new project is created.
  11. In the Solution Explorer on the right side of the screen, right-click the Shared Data Sources folder. Select Add New Data Source from the Context menu, as shown here.

  12. Figure 2

  13. Type Galactic for Name. Click Edit. The Connection Properties dialog appears.
  14. Type the name of the Microsoft SQL Server database server hosting the Galactic database or select it from the drop-down list. If the Galactic database is hosted by the computer you are currently working on, you may type (local) for the server name.
  15. Click the Use SQL Server Authentication radio button.
  16. Type GalacticReporting for the user name.
  17. Type gds for the password.
  18. Click the Save My Password check box.
  19. Select Galactic from the Select the Database on the Server drop-down list.
  20. Click Test Connection. If the message Test Connection Succeeded appears, click OK. If an error message appears, make sure the name of your database server, the user name, the password, and the database are entered properly. If your test connection still does not succeed, make sure you have correctly installed the Galactic database.
  21. Click OK to exit the Connection Properties dialog box. Click OK again to exit the Shared Data Source dialog box. A new shared data source called Galactic.rds is created in the Chapter05 project.
  22. In the Solution Explorer, right-click the Reports folder.
  23. Put your mouse pointer over Add in the Context menu and wait for the submenu to appear. Select the New Item command from the Context menu, as shown here.

  24. Figure 3

  25. The Add New Item - Chapter05 dialog box appears. Make sure the Report icon is selected in the Templates area. Enter TransportList for the name.

  26. Figure 4

  27. Click Add. A new report called TransportList.rdl is created in the Chapter05 project. You are taken to the Data tab of this new report.
  28. Select from the Dataset drop-down list. The Dataset dialog box appears.
  29. Enter TransportList for the dataset's name in the Dataset dialog box.

  30. NOTE
    The dataset name must not contain any spaces.


  31. Galactic (shared) is selected as the data source by default. Click OK. You return to the Data tab, which now displays the Generic Query Designer. We use the Generic Query Designer in Chapter 6. For now, we switch to the Graphical Query Designer and all the helpful tools it pro
  32. vides.

    Task Notes Because we are creating several reports in the Chapter05 project, all of which select data from the Galactic database, we began by creating a shared data source. This saves us time as we create each of the reports. We continue this practice throughout the remaining chapters.

    In Steps 20 and 21, we are adding a report to the project. In Chapter 4, you saw that selecting Add New Report from the Context menu causes the new report to be created with the Report Wizard. In this chapter, we are looking to build our reports from scratch, which is why we used Add New Item in Step 21.

  33. Click the Generic Query Designer button in the Data tab toolbar as shown in the following illustration. This unselects the Generic Query Designer and switches to the Graphical Query Designer.


Figure 5

Task Notes Because we are creating several reports in the Chapter05 project, all of which select data from the Galactic database, we began by creating a shared data source. This saves us time as we create each of the reports. We continue this practice throughout the remaining chapters.

In Steps 20 and 21, we are adding a report to the project. In Chapter 4, you saw that selecting Add New Report from the Context menu causes the new report to be created with the Report Wizard. In this chapter, we are looking to build our reports from scratch, which is why we used Add New Item in Step 21.

Click for the next excerpt in this series: Transport List Report Task 2: Create a Dataset

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close