Parameterize SSRS Reports with DAX Queries

With all the effort that goes into building a rich, high performing SSAS tabular solution (and its underlying ETL and data warehouse architecture) it would be quite a disappointment to only use this data source for self-service analysis in Excel, in Power View for SharePoint, a Power BI deployment, or a Datazen mobile solution. These are all great options to consider but SSAS Tabular also plays very nicely with SQL Server Reporting Services (SSRS) and there are many advantages to integrating these two tools relative to using T-SQL or stored procedures against relational data sources. With some experience in SSAS Tabular and the DAX query language the equivalent (yet enhanced) queries can be written and can be further optimized and parameterized to deliver more effective reporting and analysis. 

A couple weeks ago I introduced this DAX query with hard coded filter parameters:

Now I’ve modified the SSRS report mentioned in that blog post with parameters for Product Category and Calendar Year:

So now all the data elements in the report will be calculated in the context of the user’s parameter selections. Maybe more importantly, these parameter selections are passed back to the SSAS Tabular database engine such that only the data required for the given report/visualization is brought back across the network to the report server. Furthermore, based on the design of the query with the CALCULATETABLE function we can be comfortable knowing the Vertipaq engine will handle the simple filters so if necessary we could add more complex metrics without encountering performance issues. (I’ll definitely dive deep into DAX query optimization and tuning in a future post)

A slightly different parameterized report is as follows:

In this example, we have the sales and units for customers and products, respectively, both respecting the starting and ending date of the parameters. Nothing fancy at all here from a report standpoint – the point in this case is that I’m using Tabular’s DatesBetween function wrapped in CALCULATE to control filter context. So we can take any metric with fact table to date/calendar relationships and parameterize it in SSRS reports. The recurring theme and benefit of SSAS and SSRS integration is that you can leverage much of the design of the SSAS model in your reports saving time and manageability issues.

How to do this:

With your connection to the SSAS Tabular data source you need to build your datasets via DAX. You do this by the following:

1. In Visual Studio add a dataset with SSAS Tabular as your data source and click on ‘Query Designer’

2. Query Designer gives you the standard MDX GUI window but for DAX queries click on the Data Mining Editor icon.

3. From the Data Mining window click on the design mode icon (furthest icon on the right)

You should see this excluding the “enter DAX here” comment

4. With your DAX queries entered you create other DAX query datasets to support your report parameters. You’ll need a VALUES() or DISTINCT query to return the distinct values of a column and, in my opinion, a dataset to drive your parameters default values. Having these supporting datasets rather than static values just helps keep your reporting current/relevant and more manageable. (This is all the same process as T-SQL based report parameters in SSRS, no need to go into these details here)

5. Associate your new DAX-based report parameters with your DAX query in the Data Mining Window

Just replace the hard coded filter arguments with @Parameter syntax and select the parameter icon from the Data Mining Editor toolbar. Click OK and test out your report to ensure the query/report is respecting your parameter selections.

Here’s part of the query involving the DatesBetween:

*I just define the local measures and then reference them in a simple Summarize DAX query to aggregate the data by either customer or product. Remember that you need to define your Calendar table in SSAS Tabular as a Date table and reference your date column in this table in order to utilize the special TimeIntelligence DAX functions. (You ‘could’ also accomplish the same goal via FILTER or CALCULATETABLE functions without these prerequisites but I think Datesbetween is more readable and intuitive.

Wrapping Up (for now):

Build a powerful query that contains important and possibly complex logic leveraging your SSAS tabular solution and the inherent analytical advantages of SSAS and the DAX language. With good performance and business adoption/support, further augment the reports with parameterization on key dimensions and variables. Just one example might be the user passing in budget or target assumptions and your DAX-based report showing different scenarios.

*Thanks for visiting my data blog and feel welcome to post comments (or criticism). There’s always so much to cover and I’m very new to blogging but I’ll try to get better.

-Brett