Bring Cube Data into Focus

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.

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.

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.

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.