Your First Report

You are now ready to build your first Reporting Services report. Of course, few people build reports just for the fun of it. Usually there is some business reason for this endeavor. In this book, as was stated in the previous chapter, we will use the business needs of Galactic Delivery Services (GDS) as the basis for our sample reports.

Each of the sample reports used in this book will be presented in a manner similar to what you see in this section. The report will be introduced with a list of the Reporting Services features which it highlights. This will be followed by the business need of our sample company, Galactic Delivery Services, that this report is meant to fill. Next is an overview of the tasks that must be accomplished to create the report.

Finally, we will walk through each task, step by step. In addition to the step-by-step description, each task will include a few notes to provide additional information on the steps you just completed. Follow the step-by-step instructions to complete the task, then read through the task notes to gain additional understanding of the process you have just completed.

The Customer List Report

Here is our first attempt at creating a report, the Customer List Report.

Features Highlighted

• Creating a data source

• Using the Query Builder to create a dataset

• Using the Report Wizard to create a table report

Business Need The accounting department at Galactic Delivery Services would like an e-mail directory containing all the billing contacts for its customers. The directory should be an alphabetical list of all the GDS customers. It must include the customer name along with a billing contact and a billing e-mail address for each one.

Task Overview

1. Begin a New Project in Visual Studio

2. Create a Data Source

3. Create a Dataset

4. Choose the Report Layout

Customer List Report, Task 1: Begin a New Project in Visual Studio

1. Run Visual Studio .NET 2003. Visual Studio’s Start page will appear, as shown here.

   

   Note

   The first illustration shows the default configuration of Visual Studio. Your screen may vary if this configuration has been changed.

2. Click the New Project button to create a new project. This will display the New Project dialog box, as shown in the following illustration. (You can create a new project three different ways: Select File | New | Project from the main menu, click the New Project toolbar button, or click New Project on the Start page.)

   

3. Type Chapter04 for the project name. This project will contain all the reports you create in this chapter.

4. Click Browse to open the Project Location dialog box.

5. Click My Projects to go to the Visual Studio Projects folder.

6. Click the Create New Folder button in the toolbar at the top of the Project Location dialog box. If you have trouble finding the Create New Folder button, look along the top of the dialog box for a picture of a folder with a yellow sparkle.

7. Enter MSSQLRS for the name of the new folder. This folder will contain all the projects you create for this book.

8. Click OK in the New Folder dialog box.

9. Click Open in the lower-right corner of the Project Location dialog box. The New Project dialog box should now look like the second illustration.

Task Notes We have now established a name and location for this project. This must be done for every project you create. Because Visual Studio uses the project name to create a folder for all the project files, the project name must be a valid Windows folder name. You can use the Browse button to browse to the appropriate location, as we did here, or you can type the path in the Location text box.

Note

Valid folder names can contain any character except for the following:

/ ? : & \ * “ < > | # %

In addition, a folder cannot be named “.” or “..”.

The project name is appended to the end of the location path to create the full path for the folder that will contain the new project. The full path is shown just above the OK and Cancel buttons near the bottom of the dialog box in the second illustration. In our example, Visual Studio will create a folder called Chapter04 inside the folder MSSQLRS. All the files created as part of the Chapter04 project will be placed in this folder.

Customer List Report, Task 2: Create a Data Source

1. Click OK in the New Project dialog box to start the Report Wizard. The Welcome to the Report Wizard page will appear, as shown here.

   

2. Click Next. The Select the Data Source page will appear.

3. Type Galactic for the data source name.

4. Select Microsoft SQL Server from the Type drop-down list, if it is not already selected.

5. Click Edit. The Data Link Properties dialog box will appear.

6. Type the name of the Microsoft SQL Server database server that is hosting the Galactic database. If the Galactic database is hosted by the computer you are currently working on, you may type (local) for the server name.

7. Click the Use a Specific User Name and Password radio button.

8. Type GalacticReporting for the user name.

9. Type gds for the password.

10. Click the Allow Saving Password check box.

11. Select Galactic from the Select the Database on the Server drop-down list. The Data Link Properties dialog box should now look like this:

    

12. Click the Test Connection button. If the message “Test connection succeeded.” appears, click OK. If an error message appears, make sure that the name of your database server, the user name, the password, and the name of your database have been entered properly. If your test connection still does not succeed, make sure you have correctly installed the Galactic database.

13. Click OK to return to the Select the Data Source page of the Report Wizard.

14. Click the Make This a Shared Data Source check box so that it is checked. This page should now look like this:

    

Task Notes As we discussed in Chapter 1, the data source is actually a set of instructions for connecting to the database server or data file that will provide the information for your report. This set of instructions is also known as a connection string. In this sample report, we used the Data Link Properties dialog box to build the connection string. Those of you who memorize connection strings can type the appropriate string on the Select the Data Source page without using the Data Link Properties dialog box at all. The rest of us will continue to use the Data Link Properties dialog box when building future reports to have the connection string created for us.

Caution

If you do type in your own connection string, do not include the login and password information. The connection string is stored as plain text in the report definition file, so a password stored as part of the connection string is easy to discover. Instead, use the Credentials button on the Select the Data Source page to enter the login and password so they will be stored in a more secure fashion.

Reporting Services can utilize data from a number of different databases and data files; however, you need to tell the wizard what type of database or data file the report will be using. You did this using the Type drop-down list in step 4 of the previous task. This selection tells Reporting Services which data provider to use when accessing the database or data file. When you select Microsoft SQL Server, Reporting Services will use the Microsoft OLE DB Provider for SQL Server. This data provider knows how to retrieve information from a SQL Server database.

The Type drop-down list on the Select the Data Source page includes only a few of the possible types of data sources. If you are using data from a Microsoft SQL Server database, an Oracle database, or ODBC, you can select the appropriate data source type on the Select the Data Source page. If you are using any other type of data source, you will need to choose the appropriate data provider on the Provider tab of the Data Link Properties dialog box. This is shown in the following illustration.

If you are using a data source type other than SQL Server, Oracle, or ODBC, you’ll need to insert the following steps into the preceding task:

1. Click the Provider tab of the Data Link Properties dialog box.

2. Select the appropriate data provider.

3. Click Next.

Each data provider requires slightly different bits of information in order to create the connection string. The Connection tab of the Data Link Properties dialog box will change to suit the selected data provider. This means that steps 6 through 11 will vary when you use a data source type other than Microsoft SQL Server. Simply provide the information that is requested on the Connection tab. Be sure to use Test Connection to make sure everything has been entered properly before leaving the Data Link Properties dialog box.

Checking the Allow Saving Password check box on the Select the Data Source page allows Visual Studio to save the data source credentials with the data source definition. The data source credentials are the user name and password information required to access that data source. The login credentials are encrypted before they are saved to help protect them. If you are not comfortable having the credentials stored in this manner, leave both the user name and password blank. Visual Studio will prompt you for the credentials every time you execute the report or modify the dataset.

Note

If you leave the data source credentials blank and your selected data source requires a login, you will be prompted for database credentials when you click Next on the Select the Data Source page. The credentials you enter here are used to create a connection to the data source for the Design the Query page and for the Query Builder. These credentials are not stored with the data source.

A data source can be used by a single report, or it can be shared by several reports in the same project. Checking the Make This a Shared Data Source check box allows this data source to be used by many reports. Shared data sources are stored separately from the reports that use them. Non-shared data sources are stored right in the report definition. If you have a number of reports in the same project that utilize data from the same database or the same data files, you will save time by using a shared data source.

Caution

Even though the data source credentials are encrypted, it is never a good idea to use the system administrator account or any other database login with system administrator privileges to access data for reporting. Always create a database login that has only the privileges required for reporting operations and use this login as the reporting credentials.

Some companies require that reports use data from a development database server while they are being developed and a production database server when the reports are completed. Using a shared data source in this type of an environment makes it easier to switch a number of reports from the development database server to the production database server. The change is made once to the shared data source, and all the reports are ready to go.

Customer List Report, Task 3: Create a Dataset

1. Click Next. The Design the Query page of the Report Wizard will appear.

2. Click Edit. The Query Builder will appear.

   

   

3. The Query Builder is divided into four horizontal sections. The top section is called the diagram pane. Right-click in the diagram pane. You will see the context menu as shown above.

4. Select the Add Table command from the context menu. This will display the Add Table dialog box shown to the right. This dialog box contains a list of all the tables in the data source.

5. Double-click “Customer (dbo)” in the list of tables. The Customer table is added to the query.

6. Click Close to exit the Add Table dialog box.

7. A list of the fields in the Customer table is displayed. Click the check box next to the Name field.

8. Scroll down the list of fields and check the BillingContact and BillingEmail fields as well.

9. The section of the Query Builder directly below the diagram pane is called the grid pane. In the grid pane, type 1 in the Sort Order column across from the Name field. Alternatively, you can click in the Sort Order column across from the Name field and select “1” from the drop-down list.

10. The section of the Query Builder directly below the grid pane is the SQL pane. Right-click in the SQL pane. You will see the context menu shown here.

    

11. Select the Run command from the context menu. This will run the query and display the results in the bottom section of the Query Builder. This bottom section is called the results pane. The Query Builder should now look like this:

    

12. Right-click in the Results Pane. Select “Clear Results” from the context menu.

13. Click OK to return to the Design the Query page of the Report Wizard. This page should now look like the illustration here.

    

Task Notes The dataset represents the information that will be retrieved from the data source and used in your report. The dataset actually consists of two parts.

The first part is the database command used to retrieve data. This is the SELECT statement you created using the Query Builder. This database command is called the query string.

The second part is the list of the columns in the result set created by executing the query string. This list of columns is called the structure or schema of the result set. Visual Studio determines the field list by executing the query string in a special manner so that it returns the structure of the result set, but does not return any rows in the result set.

Those of you who are familiar with your data source and are also familiar with the SELECT statement can type your SELECT statement in the Query String text box on the Design the Query page. This is especially appropriate when you are executing a stored procedure to retrieve data rather than using a SELECT statement. A stored procedure is a program saved inside the database itself that can be used to modify or retrieve data. We will talk more about using stored procedures in a query string in Chapter 7.

It is a good idea to run the query yourself before exiting the Query Builder. We did this in steps 10 and 11 of this task. This ensures that there are no errors in the SQL statement the Query Builder created for you. It also allows you to look at the result set in the results pane and make sure you are getting the information you expected.

In order to display the result set in the results pane, Visual Studio must first load the result set in memory. Keeping result sets in memory can take up valuable resources that might be needed by other programs. This memory is not automatically cleared when you exit the Query Builder. For this reason, you should always clear the result set yourself, as we did in step 12 of this task. If you do not clear the result set yourself, Visual Studio will eventually display a dialog box reminding you that you have a result set open and asking whether you plan on making any further use of it. If this happens, click No to tell Visual Studio that you do not need the result set any longer.

Customer List Report, Task 4: Choose the Report Layout

1. Click Next. The Select the Report Type page of the Report Wizard will appear.

   

2. Make sure that the Tabular radio button is selected and click Next. The Design the Table page of the Report Wizard will appear.

3. With the Name field highlighted in the Available Fields list, click Details. The Name field will be moved to the Displayed Fields list.

4. Do the same thing with the BillingContact and BillingEmail fields. The Design the Table page should now look like the illustration here.

   

5. Click Next. The Choose the Table Style page of the Report Wizard will appear, as shown here.

   

6. Make sure that Bold is selected in the style list and click Next. The Choose the Deployment Location page of the Report Wizard will appear.

   

7. Click Next. The Completing the Report Wizard page will appear.

   

8. Type Customer List for the report name.

9. Click Finish. The Visual Studio window will appear.

   

10. Click the Preview tab located near the middle of the screen just above the report layout. A preview of your report will appear.

    

11. Click the Layout tab.

12. The Report Wizard created columns in our report that seem a bit too narrow. We will improve the report by widening the columns. Click the Name heading (“Name” in bold).

13. Place your mouse pointer on the line separating the gray box above the Name heading and the gray box above the Billing Contact heading. Your mouse pointer will change to a double-headed arrow, as shown here.

    

14. Hold down the left mouse button and move the mouse pointer to the right. This will make the Name column wider.

15. Follow the technique described in step 14 of this task to widen the Billing Contact and Billing Email columns as well.

16. Click the Preview tab. Your report should appear as shown here.

    

17. Repeat steps 11 through 16 until you are satisfied with the appearance of the report.

18. When you are satisfied with the report, click the Save All button in the toolbar. This will save the project, the shared data source, and the report files. The Save All button is highlighted in the following illustration.

    

Task Notes As you may have noticed, the Choose the Table Style page offers several table style choices (refer to the illustration in step 5). You can try these different table styles as you complete the other sample reports in this chapter and as you create your own reports using the Report Wizard. For ease of comparison between sample reports, the figures in this book will continue to use the Bold style.

The report server and deployment folder items on the Choose the Deployment Location page (refer to the illustration in step 6) are used when the report is moved from the development environment to a report server. These items are saved with the project, not with an individual report. For this reason, the Deployment Location page is only displayed by the Report Wizard for the first report created in a project. We will discuss report deployment in Chapter 10.

You probably had to repeat steps 11 through 16 of this task several times to get the report just the way you wanted it to look. This is not a problem. Most reports that you create will require multiple trips between the Layout and the Preview tabs before everything is laid out as it should be. The fact that you can move between layout and preview with such ease is a real plus for the report-authoring environment provided by Visual Studio.

Congratulations! You have now completed your first report.