When (null) is not NULL

Here is an interesting SSRS-SSAS integration gotcha that got me recently. Suppose that you have the following MDX query.

Needless to say, this query is most likely wrong to start with. It brings the members of three dimensions even if they don’t exist with each other. The correct version should probably be to cross-join the attribute hierarchies which is what the MDX Query Designer does behind the scenes when you place attribute hierarchies side by side. As you could see, as it stands, this MDX query brings 12 rows. However, much to your surprise, when you bind this dataset to a table region, you will see only three rows (the ones that don’t have (null)). So, what’s going on?

It turns out that the rows with (null) are server aggregates. The (null) in this case in this case indicates that this is a subtotal row. Since such a row is not a detail row, the SSRS Analysis Services data extension drops the row when you bind the dataset. If you add groupings on the table (group1: AgeGroup, group2: Year, group3: Month), and use the Aggregate() function accordingly in the group headers/footers, the table will show the appropriate aggregated measure values from the AS flattened rowset in the correct scopes.

What if you want to show all 12 dataset rows as detail rows on your report? The only way to do this is to switch the data source from “Analysis Services” to “OleDB” and select the MSOLAP.3 (OleDb for OLAP 9.0) or MSOLAP.2 (OleDB for OLAP 8.0) provider. However, once you switch to the OleDB provider, new features such as the graphical MDX query designer, query parameters for MDX queries, etc. are no longer available. You have to use the text-based generic query designer. You could design the query with the graphical designer as usual and then copy & paste the non-parameterized MDX query string to use it with the old OleDB provider.

One cautionary note: As I noted, most likely the original MDX query is wrong. While changing the data extension will give you all the rows, make sure that this is what you want because the report now will show duplicate rows that don’t exists within the cube space. This will probably skew your report results and confuse the end users.

The SSRS team is considering extending the RDL schema to so that server aggregate rows (currently determined by if a data extension implements the IDataReaderExtension interface) can be explicitly interpreted as real server aggregates (behavior of the AS 2005 AdoMd data extension) or as detail rows (behavior of the AS 2000 OleDB provider).