Downloads
92723.zip

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.

Figure 1: Report design in Reporting Services matrix control

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.

Adding Parameters

As I explained in “Delivering Analysis Services Data with Reporting Services,”one of the challenges in parameterizing MDX statements is creating the query for the parameter. The values to fill the query can come from a relational query of the data in the star schema or you can retrieve them by using MDX to query the Analysis Services data. The data for a parameter is often a single column, something MDX typically does not handle well because it expects multiple columns to be returned. However, by using a calculated member, you can get the cellset to return a column of Null values, which Reporting Services flattens so that it can be used with parameters. (Note that in Reporting Services 2005, MDX can support parameterized queries, so the approach with that release can be different. The approach we’re using for Reporting Services 2000 still works in Reporting Services 2005.)

On the Data tab of the basic report, you can create a new dataset. Listing 5 shows an MDX query that returns a list of all product categories as well as the values from the levels above the Product category. You add the parameter by clicking the Report menu and choosing Report Parameters. In the Report Parameters dialog box that Figure 2 shows, you can add a new parameter that consumes the new dataset. Assuming the dataset is named CategoryList, you can set both the Value and Label fields to Product_Product_Category.

Figure 2: Creating a parameter from an MDX statement

If this statement were standard MDX with measures on the Columns axis, you could tie the parameter to the matrix as a filter. Using a parameter as a filter on the matrix control works well when the filter is applied to a field that’s displayed on the rows or columns of the report, which means it’s a dimension specified in the Rows or Pages axes of the MDX query. Unfortunately, this approach doesn’t work well when the goal is to parameterize something that isn’t shown on the report. Instead of tying the parameter to the matrix as a filter, you’ll need to add the parameter to the WHERE clause in the MDX statement or substitute a value for the item listed in the Columns axis of the MDX expression.

In the Measures on Rows report that you’re building, the MDX query creates a calculated member called \[Product\].\[Prod\] that contains a hard-coded value. To replace that value with a user-selected value, you must parameterize the product category. MDX in Analysis Services 2000 doesn’t support parameterized queries, but the query we’re using in Reporting Services is just an expression. Thus, you can convert the entire query into a string and use concatenation to insert the parameters into the query.

To set up the report as one that will accept parameters, you must turn the query into a string so that Reporting Services can concatenate that parameter into the string. Note that the string can’t contain any line breaks; you must type it as a single line, and the query editor will wrap it where necessary. The query now looks like this:

=”WITH MEMBER \[Product\].\[Prod\] AS
‘\[Product\].\[Product Category\].\[“ + Parameters!ProductCategory
  .Value + “\]’ SELECT \{ \[Measures\].\[Store Sales\], \[Measures\].\[Store
  Cost\] \} ON ROWS , \{ \[Product\].\[Prod\] \} ON COLUMNS, NON EMPTY \{ \[Time\].\[Month\].Members
  \} ON PAGES FROM \[Sales\]”

Once the string is built, the \[Product\].\[Prod\] calculated member name will remain the same regardless of the value chosen for the parameter, so the report doesn’t have to be modified. Each time a user selects a new product category from the parameter drop-down list, Reporting Services inserts the category into the string and builds a valid MDX expression. Note that the major disadvantage of this approach is that the string can’t be executed in the design environment; the Run command is grayed out. The rule of thumb is to start by creating the query as you did earlier in this article and hard-code a value for testing. Once the report author is sure that the query returns the correct values, the query can be converted into a string with concatenation for the parameters.

Note that when you use concatenation to put a parameter into the MDX statement, you don’t need a filter on the matrix control. When you use a filter, all items are returned in the query and the matrix takes on the job of filtering data. In this example, only the data for the selected product is retrieved from Analysis Services. Figure 3 shows how the report looks when it’s executed for just the Drinks product category.

Figure 3: A paremterized report in which the measures appear on the rows

Formatting Measures

The report in Figure 3 contains two measures, both of which are in US dollars. You can accomplish this formatting by opening the Properties window and setting the Format property of the data textbox to c. However, setting this property doesn’t work if a non-currency measure such as Unit Sales is added to the query. You can easily add Unit Sales to the query string, as Listing 6 shows, and the Unit Sales value will automatically display in the matrix at runtime, but the value will be formatted as currency instead of as an integer. To get the value to display correctly, you must apply conditional formatting. Fortunately, the Format property can handle an expression, and you can use the IIF function to determine the name of the measure, then return an appropriately formatted value. For example, the following expression checks for the name of the measure:

=IIF(Fields!Measures_Measures
Level.Value =”Store Sales”,”c”,
IIF(Fields!Measures_MeasuresLevel
.Value =”Store Cost”,”c”,
IIF(Fields!Measures_MeasuresLevel
.Value =”Unit Sales”,
“n0”,”n”)))

If the measure name is Store Cost or Store Sales, the expression returns a c. If the value is Unit Sales, the expression returns an n0. For all other measures, the expression returns an n.

Running the report with this expression in the Format property displays the values correctly, as Figure 4 shows. For each new measure that a user adds to the query, you can add an IIF statement to the expression to ensure proper formatting.

Figure 4: Multiple measures on rows and conditional formatting applied to each measure

Formatting doesn’t have to stop here. Notice that the names of the measures that are displayed in the row headers are the same as the name of the measure in the cube. If the business wanted the names changed, you could create an expression in the Value property of the row header textbox—again using the IIF statement—and the expression would return a different name based on the current measure.

This article has explored some of the additional power of Reporting Services 2000 when working with Analysis Services. It points out the fact that MDX queries can be changed if needed and measures can be shown on the rows. In addition, it shows how the actual values can be put into the Product dimension, which is then parameterized, making the report far more flexible. Feel free to try this and other variations on this MDX theme once you learn to add parameters to queries and move measures to rows if necessary. By using these techniques, you can provide greater flexibility in your reports so that users can easily change the ways they view BI data.