SQL Server Reporting Services gives you an inexpensive way to deliver Analysis
Services data to business users. The powerful features in Reporting Services
can let you customize your report delivery to give users drilldown capability
and let them flexibly change data views to get the information they need in
the format they can use best. Because almost every Reporting Services property
can be an expression, you can modify reports in a host of ways, such as changing
the displayed names of measures, the format of individual measures in the grid,
and so on. These modifications greatly increase the number and types of reports
that you can use to bring value to your business.
In “Delivering Analysis Services Data with Reporting Services”
(July 2006, InstantDoc ID 50204), we walked through an example that shows how
you can use SQL Server 2000 Reporting Services with Analysis Services 2000 to
deliver data from a warehouse to an entire organization. You can also apply
the examples in that article and in this one to SQL Server 2005 and Reporting
Services 2005. The example report in the July article uses an MDX statement
that pulls data from Analysis Services, then displays those results in a matrix
control in a report. We added interactivity to the report, giving users the
ability to expand and collapse regions so that they can drill down to see different
levels of data. And we parameterized the reports by using a filter on the matrix
control.
In this article, we take the abilities of Reporting Services 2000 a step further
by modifying our report so that it displays the measures on rows instead of
columns, giving users a way to change their view of the data. This capability
can be important when reports need to show lots of measures and use dimensions
to act as slicers of the data. For example, a report that shows several measures
for a single employee or single product doesn’t need to have the employee
or product on either the rows or columns. Therefore, we’ll see how to
put the measures on the rows and another dimension, time, on the columns, while
slicing by another dimension. We also explore how to parameterize the MDX query
and apply formatting to the values.
Another Dimension of Reporting Service
First, let’s review several important aspects of how Reporting Services
works with multidimensional data. First, Reporting Services 2000 doesn’t
include a graphical query tool for working with Analysis Services data. Therefore,
you have to manually code the MDX statement that delivers Analysis Services
data to your report. You can preview the results in the grid on the Data tab
of a report.
Second, Reporting Services doesn’t deal well with the results of an
MDX query, which is a multi-dimensional data object called a cellset. Instead,
Reporting Services must flatten the cellset to a two-dimensional recordset,
putting all the values on rows and columns. Flattening the recordset causes
an interesting side effect: It creates fields for values at all levels higher
than the level requested in the query. So if the query calls for individual
days, then the month, quarter, and year values are specified as well—providing
that’s how the Time dimension hierarchy is structured.
Finally, the MDX statements that Reporting Services uses are different from
the MDX used by most other tools. Because the cellset is flattened, the measure
data is typically put in whatever dimension and member is specified on the Columns
axis of the query. Other dimensions are put into the Rows and Pages axes, as
the code in Listing 1 shows. An alternative
but equivalent syntax, which Listing 2 shows,
uses a Crossjoin function to put the dimensions from the Rows and Pages axes
on the Rows axis.
Building a Report with Measures on the Rows
In most Reporting Services reports that use Analysis Services as a data source,
the MDX query puts the measures on the Columns axis in the MDX query, and in
the Reporting Services matrix control, the Measures field appears in the Data
textbox. However, sometimes business requirements call for the measures to be
listed on the rows—for example, when a company seeks to measure the results
of an individual against a host of performance metrics. How do you make this
change to your report? The answer seems simple enough: Change the query so that
measures are on the Rows axis. However, you must remember that the value on
the report is written in the Columns axis in the MDX expression that will be
consumed by Reporting Services. So what do you put into the Columns axis of
the query if measures are moved to the Rows axis?
In such a case, you usually put a single value in the Columns axis. This value
might be the All level for products, a single category (e.g., Drinks), or a
single product (e.g., Beer). Any other dimension, typically Time, can be used
on the Pages axis.
For this example, let’s create a new report named Measures on Rows in
Visual Studio. To see the finished example’s files, you can download
the .zip file. We won’t use the New Report Wizard because of a
lack of control in setting up the report. We’ll use the Foodmart 2000
cube as the data source and assume that the business wants to examine the Store
Sales and Store Cost measures for the Beer and Wine product category by month.
Listing 3 shows the MDX statement that will
return the data we need.
When you’re running Listing 3’s MDX statement on the Data tab,
you get the correct data, and a report designed with a matrix control will work
correctly. Unfortunately, this query isn’t particularly flexible because
it’s always based on the category Beer and Wine. When you click the Layout
tab and look at the Fields window, you see a field called Product_All_Products_Drink_Alcoholic_Beverages_Beer_and_Wine. In addition to being unwieldy, the field
name now contains the product category specified in the query. You can add this
field to the Data textbox on a matrix and it will work, but if the report author
modifies the query to look at a different category, the field name changes to
that of the new category. The report, however, is still looking for the old
name. So every time you change the category in the query, you break the report.
Clearly, changing the value in the query
and modifying the report in Layout every
time you want to look at a different category
is cumbersome. Let’s see how to get a field
name that doesn’t change, even if the report
author wants to change the category or
choose a brand, product, or any other level
in the Product dimension. We’ll learn how
to parameterize this value later in this article.
To establish a fixed name for the field in the report, you need to create an
MDX calculated member. In MDX, you create a calculated member by using the WITH
MEMBER clause of a query. Listing 4 shows
how you’d modify Listing 3’s
query to use a calculated member. When you create the calculated member [Product].[Prod],
the report will now always see a member with that name regardless of the value
typed into the AS clause of the expression. This means that the report author
can change the value of the report any time without having to modify the report
in the Layout tab. Adding the calculated member also paves the way for adding
a parameter to the query, which we’ll demonstrate in a moment.
In addition to dealing with the Product dimension, the query specifies two
measures: Store Sales and Store Cost. Any number of measures could be listed,
and regardless of how many are added, the Fields list on the layout tab shows
only a single value: Measures_MeasuresLevel. Regardless of how many measures
are returned, the Fields window shows only this single field, and the matrix
will automatically grow to show all the measures that the MDX query returns,
displaying one measure per row.
To design this example report, you use a matrix control in Reporting Services,
as Figure 1 shows. You put the Time values
in the Columns textbox, and you can add multiple levels of time and set the
group to let users expand and collapse data. Then, you add Measures_MeasuresLevel
field to the Rows textbox. Finally, you add the Product_Prod field to the Data
textbox. Note that you need to change the formula in the Data textbox from its
default so that instead of starting with the First function, the formula starts
with the Sum function. The First function in the Rows text-box is acceptable
because it’s simply returning the name of each measure, not its value.
If it seems strange to put the [Product].[Prod] calculated member in the Data
textbox, remember that Reporting Services flattens the cellset into a rowset
and the numerical values get put into whatever the MDX statement shows is on
the Columns axis. Therefore, placing the Product_Prod field in the Data textbox
of the matrix reveals the correct numbers, and the names of the measures fill
the Rows textbox.
Prev. page
[1]
2
next page 
