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 (<Drive>: Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports). 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.
Prev. page
[1]
2
3
next page 
