Overcoming Software Limitations in Multidimensional Reports

The second limitation is simpler to understand than the first limitation. Quite simply, the Reporting Services feature set provides dataset "filtering" for use with two-dimensional datasets but does not provide post-retrieval filtering for multidimensional datasets. This would require re-sorting, re-ordering and hiding specific rows or columns denoted within the filter language. However, column filtering is not designed to work on a multidimensional dataset; the Reporting Services filtering method only works on two-dimensional data. This means that any filtering of OLAP data must be made during the data retrieval step, and not after Reporting Services has rendered the report.

Reporting Services offers a Report Parameter feature set that provides the ability to pass a "filtering" value to the dataset query, which occurs before the report is rendered. This causes problems because the language used to query a multidimensional database or OLAP cube does not provide a simple to use filtering element, such as a "where" clause in ANSI97 SQL standards. In some circumstances, it is not feasible to use the MDX "where" filtering clause, due to the nature of multidimensional queries.

The solutions offered in this paper exceed these limitations and provide the Reporting Services programmer with a more complete set of tools to author realistic reports.

Solution 1: Parameterization of Multidimensional Sourced Reports

Before we begin to add report parameters to our OLAP query, let's look at a common OLAP query. Understanding MDX and OLAP queries is beyond the scope of this article; please seek this information elsewhere.

SELECT {
{ [13 Weeks] } *
{ [Measures].[Sell To Units], [Measures].[Sell To Units Last Year] }} ON COLUMNS,

NON EMPTY {
{ { [Sales Type].[All Sales Types].CHILDREN }*
{ DESCENDANTS( [Product].[All Product], [Product].[Product Line] ) }} } ON ROWS

FROM [Financial Cube] WHERE [Account].[Account Family Id].[SampleAccount]

In this example, "13 Weeks" is a named set defined on the OLAP cube that returns a subset of the Time dimension containing the last 13 weeks from the current date, reversed. We're returning two calculated measures "Sell to Units" and "Sell to Units Last Year" across the X-axis and grouping all our products by "Product Line" and "Sales Type." Our only filter is located in the WHERE clause, which denotes returning the data that intersects with the "SampleAccount."

This MDX query is quite easy to parameterize. We would simply create a Report Parameter named "Account" and fill it with the list of accounts retrieved from a separate dataset. This dataset could be simply defined as

SELECT { [13 Weeks] } ON COLUMNS , NON EMPTY { [Account].[All Account].CHILDREN } ON ROWS FROM [Financial Cube]

In our main dataset, we would replace the existing WHERE clause with the following:

WHERE [Account].[Account Family Id].[" + Parameters!Account.Value + "]"

And add =" to the beginning of the entire dataset, so we can trick Reporting Services into treating our entire query as a string to execute, hence the query is wrapped inside a pair of quotes: ="<query>". By adding quotes and the + concatenate around our parameter lookup (Parameters!Account.Value), we're escaping from the string, substituting the report parameter value and un-escaping.

The only drawback to this method is that the query dataset designer will not properly evaluate the query, so you won't be able to preview the rows. This requires you to confirm that you have a decent, working MDX query before you start adding in parameters.

Adding a Report Parameter to a multidimensional report doesn't appear to be much different from a standard two-dimensional query string with report parameters, until we require additional report parameters NOT filterable from the WHERE clause.

Consider the following MDX:

SELECT NON EMPTY
          { { {[Measures].[Sell To Units], [Measures].[Inventory Units]}     *
                    { DESCENDANTS( [13 Weeks], [Time].[Week].[Days] )}}
          } ON COLUMNS,

          NON EMPTY
          { [Product].[Category].[Paper],
                    DESCENDANTS( [Product].[Category].[Product Line 1],
                    [Product].[SKU] )
          } ON ROWS

FROM [Staples]
WHERE ([Account].[Account Family Id].[Account Family Name 1] , [Sales Type].[Type].[Sales Type 1]

Here we're asking for the last 13 weeks of data on a daily level, including all products from Product Line 1, and filtering out anything that isn't intersected with Account Family Name 1 and Sales Type 1. Let's pretend that the customer is requesting a report that allows a choice of Account Families and Sales Types.

This is easily added to our query parameters . The WHERE clause (above) becomes

="…WHERE ([Account].[Account Family Id].["+ Parameters!Account_Family.Value + "] , [Sales Type].[Type].[" + Parameters!Sales_Type.Value + "])"

What if in addition to these parameters, a customer requested the ability to filter on Product Category and Product Line (our vertical elements)? Could we simply substitute [Paper] and [Product Line 1] with escaped strings? Yes.

Our new Y-axis would be represented as

NON EMPTY
     { [Product].[Category].&amp;["+ Parameters!Product_Line.Value +"],
          DESCENDANTS( [Product].[Category].&amp;["+
                    Parameters!Product_Line.Value +"],
          [Product].[SKU] )
     } ON ROWS

The report parameters would need to be defined, and the values available. Using report parameters in your MDX query means you do not need to filter the data using the Reporting Services filter method, which can't be done on multidimensional data anyway. If there were a way to use post query filtering through MDX, you would still need to return ALL of the data first, and then apply the Reporting Services filter method. This could lead to some very long running MDX queries that may be detrimental to the user experience, and in some cases, may cause the Reporting Services/IIS server to stop responding long enough to timeout, resulting in a failure to render the report.