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.
End of Article
