How to filter multidimensional OLAP cubes in SSRS reports

Ever since the early days of my career, SQL Server Reporting Services (SSRS) has been one of my preferred data visualization tools simply because end users and developers alike use it for free. Although a majority of my SSRS solutions have been based off a relational dataset that uses Transact SQL (T-SQL), I have also produced several reports that used Multidimensional Expressions (MDX) to connect and retrieve data from SQL Server Analysis Services (SSAS) multidimensional OLAP cube. Recently, I found myself having to refactor some of these SSAS based SSRS reports, particularly converting a single value SSAS-populated parameter into a multi-value parameter. In this article, I explore how you can go about making these changes using SSRS query designer’s design and query modes.

Working with query parameters in design mode

Just like using a relational database to define your report dataset requires an understanding of T-SQL, referencing multidimensional OLAP cube data requires a basic understanding of MDX. However, you can circumvent the MDX learning process by using a GUI – Graphical User Interface – to build and prepare your report dataset. In the context of SSRS, the GUI includes a dataset query designer’s design mode. It involves navigating to a new dataset, choosing your SSAS data source and then clicking and dragging the measures and dimensions you want to report on. In my FruitSales demo cube shown in Figure 1, I have chosen Count as my measure and Fruit and MOP (method of payment), as my dimensions.

Figure 1

Adding parameters to our dataset using the designer is also effortless. All you need to do is navigate to the top right filter section of the designer, specify the dimension you want to filter on and check the box underneath the Parameters field. In this example, I have chosen to use MOP as a filter with a default value of CASH. The rest of the dataset looks as shown in Figure 2.

Figure 2

Once you close the query designer, you get to see an MDX script that was generated based on the selections you made. The script generated based on Figure 2 is shown in Script 1.

SELECT non empty { [Measures].[Count] }

ON columns,

non empty { ([DimFruits].[Fruit].[Fruit].ALLMEMBERS * [DimFruits].[MOP].[MOP].ALLMEMBERS ) } dimension properties member_caption,

member_unique_name

ON rows

FROM (

SELECT ( strtoset(@DimFruitsMOP, constrained) )

ON columns

FROM [FruitsSales]) cell properties value,

back_color,

fore_color,

formatted_value,

format_string,

font_name,

font_size,

font_flags

Script 1: Generated MDX Script

Having successfully set up a SSAS dataset using GUI, we can proceed to develop a report and Figure 3 shows us a preview of the newly developed report.

Figure 3

Although the GUI makes it easy for novice developers to set up a parameterized SSRS report that is based off a multidimensional cube, there are several drawbacks to this approach:

The auto-generated script (in Script 1) includes unnecessary formatting information (i.e. BACK_COLOR, FORE_COLOR) that is embedded in the query – which makes the script untidy and not easy to read.
The dataset parameter name (and subsequently, report parameter name) is not business friendly as it is derived from a concatenation of dimension name (DimFruits) and dimension member (MOP).
Another issue relating to parameter is that parameter values contains an MDX notation (i.e. [Dimension].[Member].&[Value]) that is not business friendly.

Working with query parameters in query mode

The drawbacks mentioned in the preceding section can be resolved by writing your own MDX script which gives you control of the dataset and ultimately the report. To edit MDX script we will switch from design mode into query mode.

Single-value parameters using STRTOMEMBER

MDX has several built-in functions that can be useful when it comes to parameterising query datasets. One of those functions is the STRTOMEMBER which returns a member specification. Script 2 shows a refactored version of Script 1 that makes use of the STRTOMEMBER function.

SELECT NON EMPTY { [Measures].[Count] } ON COLUMNS,

NON EMPTY

{

( [DimFruits].[Fruit].[Fruit].AllMembers ) *

{ StrToMember ( @MOP ) }

} ON ROWS

FROM [FruitsSales]

Script 2: MDX Script Using STRTOMEMBER

Script 2 makes references to a parameter called @MOP. Unlike in design view, this parameter will not be automatically added into the dataset instead we have to manually add it. Figure 4 shows the definition of the @MOP parameter with a default value of CASH.

Figure 4

Following the changes made to our shared dataset, a preview of our demo report is shown in Figure 5. In terms of the layout, not much has changed since we updated the script to use STRTOMEMBER except for the parameter name which is no longer prefixed by a dimension name.

Figure 5

Evidently, we still have an issue of a predefined parameter value that contains MDX expression. We need to refactor this in such a way that it displays member value only (i.e. CASH, ELECTRONIC etc.). We can do this by having the MOP parameter populated by a separate lookup dataset. Script 3 shows an MDX script for a dataset that will be used to populate the MOP parameter.

WITH

MEMBER [Measures].[Label] AS

[DimFruits].[MOP].currentmember.member_caption

MEMBER [Measures].[Value] AS

[DimFruits].[MOP].currentmember.uniquename

SELECT

{

[Measures].[Label],

[Measures].[Value]

} ON COLUMNS,

[DimFruits].[MOP].allmembers ON ROWS

FROM [FruitsSales]

Script 3: MOP Lookup MDX Query

The updated report which references the newly added dataset is shown in Figure 6. As it can be seen, this view of the report allows end users to choose the method of payment without having to specify an MDX expression.

Figure 6

Multi-value parameters using STRTOSET

STRTOMEMBER function resolved several limitations of an MDX script generated via design mode. Unfortunately, STRTOMEMBER function has its own limitation which is – it doesn’t allow us to choose multiple parameter values. For instance, if we attempt to choose multiple MOPs, we receive an error as shown in Figure 7.

Figure 7: STRTOMEMBER function expects a member expression error

In order to address this limitation of STRTOMEMBER function, we make use of another MDX built-in function –STRTOSET. The revised script that uses STRTOSET function is shown in Script 4.

SELECT non empty { [Measures].[Count] }

ON columns,

non empty { ([DimFruits].[Fruit].[Fruit].ALLMEMBERS) * {strtoset(@MOP)} }

ON rows

FROM [FruitsSales]

Script 5: MDX Script Using STRTOMEMBER

The MOP parameter was also modified to include multi-values as shown in Figure 8.

Figure 8

A preview of the updated report that uses the script that references STRTOSET is shown in Figure 9. As it can be seen, it is now possible to choose multiple methods of payments without breaking report execution.

Figure 9

Conclusion

In this article, we have demonstrated that unlike using T-SQL, populating an SSRS dataset using a multidimensional OLAP cube requires, at the very least, a basic understanding of MDX. We also demonstrated that out of the two modes, design and query mode, the query mode gives you more control of the dataset properties and handling of query parameters using the STRTOMEMBER and STRTOSET built-in MDX functions.

Sifiso Ndlovu

Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

Sifiso's LinkedIn profile

View all posts by Sifiso W. Ndlovu

Working with query parameters in design mode

Working with query parameters in query mode

Conclusion

References

Related posts: