Pushing the Parameters

As a DBA and data analyst, no matter how hard I try, I can't always avoid writing code to solve complex problems or take advantage of the wealth of management tools that SQL Server provides. Over the years, I've scripted solutions for all kinds of SQL Server-related projects, from creating Data Transformation Services (DTS) packages to writing Active Server Pages (ASP) code, celebrating each time I manage to crawl past the syntax errors and trudge through the logic of nested loops. However, when Microsoft released SQL Server 2000 Reporting Services, which requires Visual Studio .NET 2003 for designing reports, I was worried that I would now have to cross the professional divide and become a full-fledged programmer. But you don't have to be a programmer to develop a Reporting Services report. Reporting Services provides two powerful report features, parameters and expressions, that when combined let report designers add advanced functionality to their reports without writing lengthy code.

Parameters are assigned values, typically based on user input, that you can use with expressions to manipulate data, perform calculations, or provide custom formatting. For example, expressions, which are compact, often single-line functions of standard Visual Basic .NET code, let you control the values for report item properties based on certain conditions or criteria. For example, you might use an expression to control a formatting property such as the text color of a field in a report. If the field contained a dollar amount, you could set the text color for that field to show red when the dollar amount went below a specified value. In Reporting Services, you can immediately incorporate expressions and parameters to address almost any report request. I've worked with Reporting Services since the first beta release, rewriting approximately 100 reports that were originally written in other reporting platforms. I've liberally used parameters and expressions in almost every report, and I'd like to share a few of the uses I've found for this dynamic duo, including expanding and collapsing multiple groups, dynamically grouping reports, and parameterizing dates. In this article's examples, I use the most recent Microsoft sample database, AdventureWorks2000. Microsoft designed the sample reports in Reporting Services with the AdventureWorks2000 database, and I refer specifically to the Territory Sales Drilldown report in that database.

Expand and Collapse Multiple Groupings

When I first started working with Reporting Services, I had many questions about how to get the functionality I wanted. My first question came up as I began to add interactivity to Reporting Services reports. Customers often ask me for reports that show summary data but let them expand—or drill down—to see details without having to run additional reports. In Reporting Services, you can accomplish this level of interactivity by using a standard property value called Visibility that hides or shows record details or entire report sections. Users control the visibility when the report is rendered. However, I had a problem with one aspect of creating this type of interactive report: Users couldn't expand or collapse more than one rolled-up (i.e., grouped) section at a time. For example, if a report was grouped by geographical region and contained several regions such as Northeast and Southwest, users would have to expand or collapse each region one at a time to show or hide the detailed records beneath, which could be time-consuming. The problem was exacerbated when users wanted to print a fully expanded report; they would have to expand all the regions one at a time before they could print the complete report. To solve this problem, I used a parameter called Expand_All that automatically expands or collapses every grouping.

To demonstrate this functionality, let's look at a sample Reporting Services drilldown report, Territory Sales Drilldown, which is in Reporting Services' base installation directory (: Program FilesMicrosoft SQL ServerMSSQLReporting ServicesSamplesReports). Open a copy of the report in Visual Studio .NET and save it as a new report called Territory Sales Drilldown Parameters. This particular report includes no parameters by default. To add the Expand_All parameter to the report, from the Report menu, select Report Parameters, and fill in the information that the resulting dialog box requires. For the parameter name, type Expand _All (parameter names can't contain spaces). For the prompt, type Expand All. Make the data type Boolean with a non-queried default value of False so that when rendered, the report items are configured so that the Expand_All parameter controls whether they're initially collapsed or hidden.

Now that you've defined the parameter, you can combine it with an expression in the report. Reporting Services' design environment gives you many opportunities to use expressions. For example, for any report properties that support them, you can use expressions with parameter values to set those properties. In the case of the Expand_All parameter, you can use the parameter's runtime value (True or False) in an expression to expand or collapse all the report groupings at once. I use the following simple expression to control the visibility of the groupings in the Territory Sales Drilldown Parameters report:

=IIF(Parameters!Expand_All.Value = False,True,False)

When you apply this expression to the visibility properties of the Detail and Sales Person rows, users can toggle the visibility of the report when the parameter value changes. The expression's IIF function evaluates the Expand_All parameter's default value; if the value is False, the hidden property will be True and the report will render in its collapsed format when users open it.

One last detail remains for you to set up: You need to make sure the toggle image correctly displays as a plus sign (+) when the report details are collapsed or a minus sign (­) when the report details are expanded. You can use the same expression you used to control grouping visibility to control the initial view of the toggle image. The toggle-image property exists on the field it refers to; in this case, the SalesPerson and OrderNumber fields contain the property. To set the InitialToggle-State property, right-click the SalesPerson field, select Properties, then click Advanced to open the Advanced Properties window. Next, select the Visibility tab that Figure 1 shows to use an expression to make sure the toggle image matches the user selection. Notice that initially, the visibility section of the tab is set to Visible. This is because I previously set the entire row—not just the SalesPerson and OrderNumber fields—to be hidden. I'm using the individual field's Visibility property setting to control only the initial appearance of the toggle image.

After selecting the proper settings on the Visibility tab, you can render the report in preview mode. As Figure 2 shows, users now have full control over the expandability of the report items, and the toggle image shows as a ­ character, indicating that the report details for these items are expanded.

Dynamically Group Report Data

My next question came while I was researching Reporting Services' ability to dynamically alter the content or layout of a report based on user-selected values from parameters. What I really needed was the ability to dynamically control how report data is grouped. For example, in the reports I was rewriting, I had approximately five data groupings that users frequently needed to see. I didn't want to manually configure every report to include all five groupings on every report because such a task would be tedious and time-consuming. The answer was to use parameter values that I could associate with the names of the fields the users would need to group on. Users could then select parameter values such as Territory or Region, and Reporting Services would dynamically regroup the report based on their selections.

To see how to dynamically regroup a report by using a parameter value, go back to the Territory Sales Drilldown report. The sample query for the unmodified report contains one grouping, Name, from the SalesTerritory table. The report doesn't include the Group field, which is a higher-level regional breakdown that includes such values as Pacific and North America. On the Data tab for this report in the Visual Studio .NET report designer, select the Group field from the SalesTerritory table to add it as an available field within the report. When you click Refresh Fields on the Data tab toolbar, the new field will be available to the report. Next, open the Report Parameters property window by selecting Report Parameters from the Report drop-down menu. Add a report parameter called Grouping, as Figure 3 shows, and set the parameter's data type to be String. Next, set the available values of the fields to non-queried, and assign the Label and Value of each available selection. In this case, set the Labels, which is what users will see in the parameter drop-down list, to be Territory and Region. Next, set the Values to be Name and Group, which is what the parameter will use in the background to match the field name. Finally, set the default non-queried value to Name; this setting is important because it ensures that the report will automatically render when users execute it. If you don't set the default value, users will see a blank page until they choose a parameter value and click View Report. Non-queried values are those that you set up manually; you don't rely on a database query to populate their values.

Now that you've defined the report parameter, you can modify the report to add the dynamic-grouping functionality. You need to modify four locations on the report: the Territory text box, the Territory label, the Sales_Territory grouping, and the report title text box. To modify the Territory text box, select the Layout tab, and replace the current expression

=Fields!Name.Value

with an expression that will evaluate to the field name the parameter provides, such as

=Fields(Parameters!Grouping.Value).Value

Do the same for the Sales_Territory group in the Grouping and Sorting property window. You can access the grouping and sorting properties in several ways, but the easiest way is to select the entire table by right-clicking the left uppermost corner of the table and selecting Properties. From the Table Properties window, click the Groups tab, highlight the Sales_Territory group, and select Edit.

To make the Territory label and report title dynamically change when the parameter changes, from the Layout tab, select the TerritoryLabel cell and replace the label Territory with the expression =Parameters!Grouping.Label. Next, replace the report title Territory Sales with =Parameters!Grouping.Label & " "& "Sales". Because parameters contain two values, one that's displayed to the user for input and another that will be the data value, associating the report labels with the label values will maintain a uniform appearance between the parameter and the new, dynamically regrouped report, as Figure 4 shows.

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 < FilterValue>:

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

=

< 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.