Report Parameters

From the users’ standpoint, all our sample reports up to this point have been “what you see is what you get.” These reports each ran a predetermined query to create the dataset. No user input was requested.

In the real world, this is not the way things work. Most reports require the user to specify some criteria that will help determine what information is ultimately in the report. The user may need to enter a start and an end date, or they may need to select the department or sales region that will be included in the report. Users like to have control over their reports so they receive exactly the information they are looking for. Our next report demonstrates how Reporting Services allows you to get user input by using report parameters.

The Parameterized Invoice-Batch Number Report

Features Highlighted

• Using report parameters

Business Need The accounting department is very pleased with the Invoice-Batch Number report. Like most users, when they are happy with something, they want to change it. No software or report is ever really completed. It only reaches a resting point until users think of another enhancement.

The accounting department would like to be able to view the Invoice-Batch Number report for one city at a time. They would like to pick the city from a list of all the cities where they have customers. They would also like to specify a start date and an end date and only view batches that were run between those dates.

We will modify the Invoice-Batch Number report to include these features. We will add a WHERE clause to the SELECT statement that creates the dataset. Then we will send the user’s selections for city, start date, and end date to the WHERE clause using report parameters.

Task Overview

1. Reopen the Chapter04 Project, Open the Invoice-Batch Number Report, and Add Parameters to the Query in the Original Dataset

2. Create a Second Dataset Containing a List of Cities

3. Customize the Report Parameters

Parameterized Invoice-Batch Number Report, Task 1: Reopen the Chapter04 Project, Open the Invoice-Batch Number Report and Add Parameters to the Query in the Original Dataset

1. If you have closed the Chapter04 project, reopen it. (If you need assistance with this, see Task 1 of the Customer-Invoice report.)

2. If the Invoice-Batch Number report is open, you are ready to go. If it is not open, double-click the entry for the Invoice-Batch Number report in the Solution Explorer on the right side of the screen.

3. Click the Data tab. You will see the Query Builder screen with the query that was built for this report while running the Report Wizard.

   Note

   You can change the size of the Solution Explorer window, the Fields window and the other windows around the outside of the Visual Studio window in order to make more room in the center to create your report. Just click on the separator between the windows and drag in the desired direction.

4. Right-click in the diagram pane and select Add Table from the context menu.

5. The accounting department wants to specify a date range based on the date that each batch was run. This date is stored in the InvoiceBatch table. We will need to join this table with the InvoiceHeader table. Double-click “InvoiceBatch (dbo)” in the list of tables. The Query Builder automatically creates the JOIN for us.

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

7. In the InvoiceBatch table, click the check box next to the RunDate field. This will add RunDate to the grid pane.

8. Now we will create the portion of the WHERE clause involving the billing city. In the grid pane, click the cell across from BillingCity and under Criteria. The cursor will move to that cell. Type =@City and press ENTER. The Query Builder portion of the screen will appear, as shown in the following illustration. Notice that the SQL statement in the SQL pane now includes a WHERE clause.

   

   Note

   Your screen may not look exactly like the illustration. Some of the Visual Studio windows have been closed in the screen in order to make room for the Query Builder.

9. Next, we will create the portion of the WHERE clause involving the RunDate. Scroll down in the grid pane until RunDate is visible. Click the cell across from RunDate and under Criteria. Type >= @StartDate AND < @EndDate + 1 and press ENTER. The Query Builder portion of the screen will appear as shown in the following illustration. Notice the addition to the WHERE clause in the SQL pane. We will discuss why we are using “@EndDate + 1” in the task notes.

   

10. We needed to include RunDate in the WHERE clause, but we do not need to include it in the FIELD LIST of the SELECT statement. Click in the cell across from RunDate and under Output to remove the check mark. The RunDate field is no longer in the FIELD LIST for the SELECT statement in the SQL pane.

11. Right-click in the SQL pane and select Run from the context menu.

12. The Query Builder requires values for the three parameters you just created in order to run the query. You will see the Define Query Parameters dialog box. Type Axelburg for @City, 12/01/2003 for @StartDate, and 12/31/2003 for @EndDate. Click OK.

13. After viewing the result set, right-click in the results pane and select Clear Results from the context menu.

Task Notes You have now added three parameters to the WHERE clause of the SELECT statement. Only rows where the City column has a value equal to the value of @City will be displayed in the result set. When you ran the query in the Query Builder just now, you gave the @City parameter a value of “Axelburg.” Therefore, only rows with Axelburg in the City column were included in the result set.

One of the trickiest things about working with dates in SQL Server is remembering that they consist of both a date and a time. SQL Server does not have columns that are just a date, as in 12/31/2003. SQL Server only has a datetime data type, which consists of a date and a time together.

When the invoice batches are run at GDS, the invoicing program assigns both the date and the time that the batch was run. For instance, batch 447 was run on 12/31/2003 at 7:54:49 P.M. It has a value of “12/31/2003 7:54:49 PM” stored in its RunDate column by the invoicing program.

When a user is asked to enter a date, most of the time they enter the date without a time. When you were asked for a value for @EndDate, you entered “12/31/2003” without any time specified. Because SQL Server only deals with dates and times together, it adds on a time value for you. The default value that it uses is “00:00:00 AM” or midnight. Keep in mind that midnight is the start of the new day. This means that when you’re comparing datetime values, midnight is less than any other time occurring on the same day.

Let’s think about the comparison we created in the WHERE clause involving @EndDate. Assume for a moment that instead of using “RunDate < @EndDate + 1,” we used the more obvious “RunDate <= @EndDate.” When the user enters “12/31/2003” for the end date, they expect the result set will include batches run on 12/31/2003. However, when SQL Server compares the value of RunDate (12/31/2003 7:54:49 PM) with the value of @EndDate (12/31/2003 00:00:00 AM), it finds that RunDate is not less than or equal to @EndDate. This is because 7:54:49 PM, the time portion of RunDate, is greater than 00:00:00 AM, the time portion of @EndDate. Batch 447 would not be included in this result set.

In order to include batches that occur on the day specified by @EndDate, we need to use “RunDate < @EndDate + 1.” What this expression does is add one day to the value of @EndDate and checks to see if RunDate is less than this calculated value. Let’s look at our example with batch 447. This time, SQL Server compares the value of RunDate (12/31/2003 7:54:49 PM) with the calculated value (12/31/2003 00:00:00 AM + 1 day = 1/1/2004 00:00:00 AM). Now it is true that RunDate is less than our calculated value, so batch 447 is included in the result set.

Parameterized Invoice-Batch Number Report, Task 2: Create a Second Dataset Containing a List of Cities

1. The accounting department wants to be able to select a value for the @City parameter from a list of billing cities. We need to create a second dataset in their report that will provide that list for the users. Start by selecting “<New Dataset…>” from the Dataset drop-down list, as shown in the following illustration. The Dataset dialog box will appear.

   

2. Type BillingCities for the name. The Galactic data source is already selected for you in the Data Source drop-down list, so this does not need to be changed.

   Note

   Make sure you type “BillingCities” without a space between the two words. Spaces are not allowed in dataset names.

3. Based on what you learned in Chapter 3, we’ll compose the query for this dataset without the Query Builder. We want a list of all the billing cities for GDS customers. It also makes sense that each city name should only show up once in the list. Click in the Query String text box and enter the following SQL statement:

   SELECT DISTINCT BillingCity FROM Customer

4. Click OK. You will see the Generic Query Designer with the new BillingCities dataset loaded. The Generic Query Designer is a simplified version of the Query Builder. The Generic Query Designer does not include all of the helpful features that are found in the Query Builder. Queries created in the Report Wizard use the Query Builder by default. Queries created outside of the Report Wizard use the Generic Query Designer. In Chapter 5, we will learn how to switch back and forth between the Generic Query Designer and the Query Builder.

Task Notes Remember that the word DISTINCT means that we want SQL Server to remove duplicates for us. In order to do this, SQL Server will automatically sort the result set. For this reason, we don’t need to specify an ORDER BY clause for the SELECT statement.

Parameterized Invoice-Batch Number Report, Task 3: Customize the Report Parameters

1. From the menu, select Report | Report Parameters. The Report Parameters dialog box will appear.

2. Type Select a City in the Prompt field. This is the prompt the user will see when running the report.

3. Make sure the Allow Null Value check box is unchecked. The user must select a city; it cannot be left empty (or “null” in database-speak).

4. For Available Values, select the From query radio button. This will allow us to use our BillingCities dataset to create a drop-down list.

5. From the Dataset drop-down list, select BillingCities. We will discuss the Value Field and the Label Field drop-down lists later in this book.

6. For Default Values, select the Non-queried radio button.

7. Type Axelburg in the text box below Default Values. This will serve as the default value for the City parameter. The Report Parameters dialog box should now look like this:

   

8. Click StartDate in the Parameters list.

9. Type Enter a Start Date in the Prompt field.

10. Select DateTime from the Data Type drop-down list.

11. Make sure the Allow Null Value check box is unchecked.

12. Click EndDate in the Parameters list.

13. Type Enter an End Date in the Prompt field.

14. Select DateTime from the Data Type drop-down list.

15. Make sure the Allow Null Value check box is unchecked.

16. Click OK. You will return to the Generic Query Designer.

17. Click the Preview tab.

18. The prompts for the three report parameters appear at the top of the preview area. No report will be displayed until a value is entered for each parameter.

19. Select Axelburg from the Select a City drop-down list. Type 12/01/2003 for Enter a Start Date. Type 12/31/2003 for Enter an End Date.

20. Click View Report. The report, based on the parameter values you entered, now appears. The report, with all the rows and columns expanded, is shown here.

    

21. Click the Save All button in the toolbar.

Task Notes Each time you added a parameter to the query in the dataset, Visual Studio created a corresponding report parameter for you. When the report is viewed, the values that are entered for the report parameters are automatically passed on to the query parameters before the query is executed. In this way, the user can enter information and have it used in the WHERE clause of the SELECT statement to affect the contents of the report.

The Report Parameters dialog box allows you to control the user’s interaction with the report parameters. You can change the prompts that the user sees. You can specify the data type of a parameter. You can even determine the default value for a parameter.

One of the most powerful features of the Report Parameters dialog box is the ability to create a drop-down list from which the user can select a value for a parameter. In many cases, the user will not know values such as department codes, part numbers, and so forth, without looking them up. This ability to allow the user to select valid values from a list makes the reports much more user friendly.