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 can 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 to 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 enables you to get user input by using report parameters.
Using report parameters
Business Need The accounting department is 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. And, 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 can modify the Invoice-Batch Number Report to include these features. We can add a WHERE clause to the SELECT statement that creates the dataset. Then we can send the user’s selections for city, start date, and end date to the WHERE clause using report parameters.
Reopen the Chapter04 Project, Open the Invoice-Batch Number Report, and Add Parameters to the Query in the Original Dataset
Create a Second Dataset Containing a List of Cities
Customize the Report Parameters
If you closed the Chapter04 project, reopen it. (If you need assistance with this, see Task 1 of the Customer-Invoice Report.)
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.
Click the Data tab. You see the Query Designer screen with the query built for this report while running the Report Wizard.
You can change the size of the Solution Explorer window, the Datasets window, and the other windows around the outside of the Visual Studio window to make more room in the center to create your report. Just click the separator between the windows and drag in the desired direction.
Right-click in the diagram pane and select Add Table from the Context menu.
The accounting department wants to specify a date range based on the date each batch was run. This date is stored in the InvoiceBatch table. We need to join this table with the InvoiceHeader table. Double-click InvoiceBatch (dbo) in the list of tables. The Graphical Query Designer automatically creates the JOIN for us.
Click Close to exit the Add Table dialog box.
In the InvoiceBatch table, click the check box next to the RunDate field. This adds RunDate to the criteria pane.
Now we can create the portion of the WHERE clause involving the billing city. In the criteria pane, click the cell across from BillingCity and under Filter. The cursor moves to that cell. Type =@City and press ENTER. The Graphical Query Designer appears as shown in the following illustration. Notice the SQL statement in the SQL pane now includes a WHERE clause.
Next, we create the portion of the WHERE clause involving the RunDate. Scroll down in the criteria pane until RunDate is visible. Click the cell across from RunDate and under Filter. Type >= @StartDate AND < @EndDate +1 and press ENTER. The Query Designer portion of the screen appears as shown in the following illustration. Notice the addition to the WHERE clause in the SQL pane. We discuss why we are using @EndDate +1 in the task notes.
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.
Right-click in the SQL pane and select Execute SQL from the Context menu.
The Query Designer requires values for the three parameters you just created to run the query. You see the Query Parameters dialog box. Enter Axelburg for @City, 12/01/2005 for @StartDate, and 12/31/2005 for @EndDate. Click OK.
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 Designer 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/2005. 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 the batch was run. For instance, batch 447 was run on 12/31/2005 at 7:54:49 P.M. It has a value of 12/31/2005 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/2005 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 it uses is 00:00:00 AM or midnight. Remember, midnight is the start of the new day. This means when you’re comparing datetime values, midnight is less than any other time occurring on the same day.
Let’s think about the comparison 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/2005 for the end date, they expect the result set to include batches run on 12/31/2005. However, when SQL Server compares the value of RunDate (12/31/2005 7:54:49 PM) with the value of @EndDate (12/31/2005 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.
To include batches that occur on the day specified by @EndDate, you need to use RunDate < @EndDate +1. What this expression does is add one day to the value of @EndDate and check 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/2005 7:54:49 PM) with the calculated value (12/31/2005 00:00:00 AM+1 day=1/1/2006 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.
The accounting department wants to be able to select a value for the @City parameter from a list of billing cities. You need to create a second dataset in the report that provides 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 appears.
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.
Make sure you type BillingCities without a space between the two words. Spaces are not allowed in dataset names.
Based on what you learned in Chapter 3, we’ll compose the query for this dataset without the Query Designer. 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
Click OK. You see the Generic Query Designer with the new BillingCities dataset loaded.
Task Notes Remember, the word DISTINCT means we want SQL Server to remove duplicates for us. To do this, SQL Server automatically sorts the result set. For this reason, you don’t need to specify an ORDER BY clause for the SELECT statement.
Click the Layout tab. From the menu, select Report|Report Parameters. The Report Parameters dialog box appears.
Make sure City is selected in the Parameters list, then type Select a City in the Prompt field. This is the prompt the user sees when running the report.
For Available Values, select the From Query radio button. This lets you use the BillingCities dataset to create a drop-down list.
From the Dataset drop-down list, select BillingCities.
From the Value Field drop-down list, select BillingCity. From the Label Field drop-down list, select BillingCity. The Value Field determines what value is assigned to the parameter. The Label Field determines what the user sees in the drop-down list when selecting a value. In this case, they are one and the same thing.
For Default Values, select the Non-Queried radio button.
Type Axelburg in the text box in the Default Values section. This serves as the default value for the City parameter. The Report Parameters dialog box should now look like this:
Click StartDate in the Parameters list.
Select DateTime from the Data Type drop-down list.
Type Enter a Start Date in the Prompt field.
Click EndDate in the Parameters list.
Select DateTime from the Data Type drop-down list.
Type Enter an End Date in the Prompt field.
Click OK. You will return to the Report Designer.
Click the Preview tab.
The prompts for the three report parameters appear at the top of the preview area. No report is displayed until a value is entered for each parameter.
Axelburg is selected from the Select a City drop-down list because you made this the default. Type or use the date picker to select 12/01/2005 for Enter a Start Date. Type or use the data picker to select 12/31/2005 for Enter an End Date.
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.
Click the Save All button in the toolbar.
Task Notes Each time you added a parameter to the query in the dataset, the Report Designer created a corresponding report parameter for you. When the report is viewed, the values 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 enables you to control the user’s interaction with the report parameters. You can change the prompts 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 capability 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 capability to enable the user to select valid values from a list makes the reports much more user-friendly.