| Downloads |
|---|
| 94827.zip |
SQL Server 2005 Reporting Services does the yeoman's work for reports that extract data from SQL Server 2005 Analysis Services. Reporting Services is good at providing the framework for report development, parameter selection, report deployment, and controlling report access. However, sometimes Reporting Services behaves differently than expected or desired when dealing with Analysis Services data and you need to find imaginative solutions to work around Reporting Services' limitations. Unfortunately, sometimes the imaginative solutions result in data-model or security changes. Isolating reporting requirements to the presentation layer as much as possible is preferable to making changes to data models or security simply to facilitate reporting. This article addresses a common situation in which particular report requirements aren't intuitively available in Reporting Services. The three-part solution to this problem uses features available in Reporting Services and Analysis Services to create a solution that resides entirely in the report presentation layer.
The sample Reporting Services project, which uses the AdventureWorks sample database, is available for download at http:// www.sqlmag.com, InstantDoc ID 94827. The project has two .rdl files: AW_Sample_ Problem.rdl: shows the problem report, and AW_Sample.rdl: shows the solution. I'd like to thank Al Ludlow, a data warehouse developer at CIBER, for creating the bulk of this solution.
Situation and Problem
A key business report for our sample business is a sales report in which the selection of an Analysis Services dimension member from the Geography hierarchy is a report parameter. A row group exists on the sales report for each level in the Geography hierarchy. Business requirements dictate that members higher in the dimension hierarchy (ancestors) of the userselected member should not be displayed in the report. But Reporting Services doesn't understand this requirement because there isn't a facility within Reporting Services to suppress higher-level members within a hierarchy, so the report we get displays unwanted ancestors. For example, when a user selects Utah as the parameter for the report, the report displays the Country level along with Utah and its descendants. The business requirement is violated when the ancestor of Utah, United States, appears on the report, as Figure 1 shows. The desired display doesn't show ancestors of Utah, as you can see in Figure 2.
Solution
The solution we create needs to hide the undesired ancestors without affecting the data model. We can accomplish our goal by using a mix of features in Reporting Services and Analysis Services to hide the undesired ancestors when the report is rendered. The technique for hiding the ancestors of the user-selected member uses three distinct parts, all referenced from within the report definition. The three parts are:
- Creating and referencing a data set that uses MDX to provide the level-number property of the selected report parameter.
- Creating a Visual Basic (VB) code block that compares the member level for display to the member level of the userselected parameter.
- Applying the Visibility property for row groups to exercise the VB code block.
Create dataset for parameter-level number. We can use "raw" MDX to define a new dataset in Reporting Services. We can enter the MDX by using generic query editor mode, which not only gives us display values (CAPTION and UNIQUENAME) but also gives us the LEVEL.ORDINAL property, which our VB code block references later. The generic query editor is available in a new data set by clicking the Design Mode button at the top of the Data tab in the RDL definition. The Design Mode button toggles the data set mode. We enter the MDX (MDX is typed directly into the data query pane of the Data tab in the RDL definition), which provides the LEVEL .ORDINAL of the user-selected parameter. The dataset, named DS Properties, is created by the MDX code that Listing 1 shows and referenced in the complete Reporting Services project.
Insert VB code block comparing display member level to the member level of the user-selected parameter member level. The VB code block that we're going to insert into the report definition is a function that compares the user-selected parameter hierarchy level to the current row level in the report. The report row level corresponds to the level in the hierarchy that limits our solution to regular hierarchies. The algorithm, which you can see in Listing 2, compares the user parameter-hierarchy level (intLevel) to the row level (intRowLevel) and returns "false" when the parameter level is less than the row level; otherwise, it returns "true." To enter custom code into a report definition, select Report, Report Properties from the menu in the Reporting Services development environment (the Business Intelligence Development Studio—BIDS). Click the Code tab and enter the VB code as Figure 3 shows.
Use VB code block in the Visibility property of row groups. Listing 2's VB code block is applied from the Visibility.Hidden property of the Row group. To access the expression and work with the expression editor, you highlight the row group in the Layout tab of the RDL definition, right-click, and select Properties. Click the drop-down box next to the Hidden property and select <expression…> to display the expression dialog box that Figure 4, shows.
The expression that we're editing:
=Code.ShowRow(Fields!Country. Value, First (Fields!ParameterLevel. Value, "DSProperties"), 1)
references the elements needed to return true or false based on the level of the hierarchy referenced in the report and the level of the user-selected parameter. In the preceding line of code, the function call to ShowRow() references the function defined in our VB code block. The first parameter contains the level number of the userselected parameter, First(Fields!ParameterL evel.Value, "DSProperties"). DSProperties is the name of the dataset we defined, which retrieves the level value of the selected parameter.
The second parameter (1) references the level of the hierarchy that shows up on the row of the report. The following line of code shows the call to ShowRow() for the second row group of the report.
=Code.ShowRow(Fields!Country. Value, First (Fields!ParameterLevel. Value, "DSProperties"), 2)
The value 2 in the second parameter corresponds to the level of the hierarchy that's on the report row group.
The solution in this article is a good example of how to isolate reporting requirements to the presentation layer rather than implementing model or security changes. Your challenge is to try implementing this solution within your own reporting environment and perhaps adapting the solution to a more complex requirement than simply suppressing ancestor display.
