SQL Server Magazine

Effectively Parameterize Dates
Date and time fields can have various formats; a datetime data type could represent a year, a quarter, a month, a week—even a millisecond. Because of this varied formatting, datetime data types sometimes require special treatment. Often, using built-in SQL Server functions such as DATEPART( ) and DATENAME( ) is the best way to format datetime fields. And for Reporting Services, you can incorporate these built-in functions into the source query for a report or an expression so that the dates will be pre-formatted. In one of the reports I was rewriting, I needed to be able to use a datetime field, the SalesOrderHeader table's DueDate field, as both a field returned in the original report data set and as a parameter to narrow the scope of the report to a user-selected date range. The first step to parameterize the DueDate field is to add the field to the original data set. Web Listing 1 shows the modified query for the Territory Sales Drilldown Parameters report that we've been working with, which now includes both the Group field from the previous example and the new formatted DueDate field. Next, to limit the data on the report to the value you selected in the parameter drop-down list, you need to combine the DueDate parameter with a filter expression. I encountered a couple of challenges as I added the logic to populate the DueDate parameter drop-down menu and apply the filter. First, when a parameter uses a data set to populate a drop-down menu, even though null values might be allowed, Reporting Services doesn't automatically generate them. Second, filter expressions can compare values only of the same data type, so the data type of both sources must match. I explain why these factors are important in a moment.

To parameterize the DueDate field, go to the Data tab for the Territory Sales Drilldown Parameters report, and create another data set called DueDate_DropDown. This data set will populate the DueDate parameter drop-down list. Web Listing 2 shows the code to generate the DueDate_DropDown data set. Note that Visual Studio .NET contains two query design tools for Reporting Services: the Generic Query Designer and the Graphical Query Designer. The main difference between the two designers is that the latter, which I recommend using, lets you graphically build an SQL query by selecting the tables and fields you want to include. Both of these query designers have an SQL pane in which users can directly enter or paste the query.

As Web Listing 2 shows, I used a combination of CAST() and DATEPART() functions to concatenate the year and the quarter of the DueDate field (e.g., to display third quarter 2002 as 2002­3). Using the UNION clause to select a null value for DueDate is important if you need to return a null value to the parameter list (e.g., if users didn't select a specific time period). Without the UNION clause, users would have to select one specific time frame such as 2002­3 instead of being able to see records for all due dates.

Next, you need to create the DueDate parameter with a data type of String and set its available values to come from the DueDate_DropDown data set that you just created, selecting DueDate as both the Value and the Label field. As in the previous example, you create the parameter in the Report Parameters property window. Next, select the Allow Null value box, and set the default non-queried value to be =Nothing, which is equivalent to null and will force the rendered report to display records for all due dates.

Figure 5 shows the Due Date drop-down menu. However, you haven't added the filter expression yet, so choosing a due date won't affect the report content. In Reporting Services, you can apply a filter to any data region that's based on an expression. For example, you can apply a filter expression to the properties of the only table in the report, SalesTable, which is the next step. You access the table properties again by right-clicking the left uppermost section of the table and selecting Properties. Next, select the Filter tab and enter the following filter expression in the format <Filter Expression> <Operator>< FilterValue>:

<Filter Expression>

=IIF(Parameters!DueDate.Value is 
  Nothing, "All", 
  Parameters!DueDate.Value)

<Operator>

=

< FilterValue>

=IIF(Parameters!DueDate.Value is 
  Nothing, "All", 
  Fields!DueDate.Value)

In these expressions, when the value of the FilterExpression equals the value of the FilterValue, Reporting Services will apply the filter to the report and exclude the data that doesn't match the filter evaluation. In the DueDate example, when the DueDate parameter is NULL, the report will display all records. If the DueDate parameter has a value such as 2002­3, the user will see only the records in the report that have 2002­3 as the DueDate field value. Note that you need to make sure the DueDate_DropDown data set contains a String data type value for the DueDate field so that its DueDate field matches the data type of the DueDate field that you added to the original TerritorySales data set. Because you used a String data type for the DueDate fields, you can add the literal string "All" in the evaluation of the filter statement to match all records. Logically, the filter would evaluate to All = All, and every record would display in the report. Using "All" for the literal string is an arbitrary choice; any literal string would work, such as "Everything" or "AllData"—or "Banana" for that matter.

To show users what time frame they're viewing, you can add in the report-title section a text box called Date Order Due that has the following expression:

=IIF(Parameters!DueDate.Value is 
  Nothing, "All Dates", 
  Fields!DueDate.Value)

If NULL is selected, as it is by default, users will see All Dates as the report title; otherwise, they'll see the date they selected (e.g., 2002­3), as Figure 5 shows.

Lines are blurring between the roles of DBAs and developers. Even management tools are becoming more developer-centric. Thankfully, Microsoft is helping us make this progression gradually. In Reporting Services, you use parameters and expressions to develop relatively complex logic within the confines of the report environment—and without having to write complex code. Parameters and expressions are powerful features, and with them, you can let your imagination run wild as you design valuable functionality into your reports. And if you find that you've taken parameters and expressions to the edge of their abilities, you can step up to the true power of .NET coding because Reporting Services provides ample support for custom assemblies. I've already started down that path.

End of Article

Prev. page     1 2 [3]     next page -->