Using Traditional SSRS parameterized reports against a Analysis Services Tabular model using DAX

I get this question every now and then: Is it possible to create a traditional SSRS report with parameters against a tabular model using DAX. Marco already created a great blog post that sums it all up here, but I decided to create a step by step example.

Ok I created a simple Tabular model in SSDT:

The model does not contain any measures, just tables and relationships and marked the date table. I didn’t want to complicate the Tabular model by adding measures specific for a single project.

From this Tabular model I want to create a SSRS report that allows:

• Selection of year, default select the last year we have data (what defines the current year ? )
• Select a product color

And shows:

• Sales per product for the selected year
• Sales per product for the previous year
• Sales per product YoY
• Sales per product YoY %
• Sales YoY product rank

Ok lets get started. In SSDT where created the Tabular Project I added the Report project to the same solution.

As Datasource for my RS project I used the workspace database that I have set up for my tabular model:

Next I created a new report and followed the wizard until I got to the Query Builder:

Press the Query Builder button and click on the DMX data mining button and switch to manual query mode:

The reason that we need to switch to DMX data mining mode is that the designer tries to parse the MDX, and as we are going to write DAX the parser will not understand. Marco explained this bit very well.

Next I paste in a simple DAX query expression to get through the wizard, it doesn’t take well to complex DAX:

Finish the wizard and run the report:

Now we have our first DAX based report.

Ok now lets add the parameters. I usually create shared datasets for these. So I create one for years:

evaluate
SUMMARIZE
(
FactInternetSales,
DimDate[CalendarYear]
)

order by DimDate[CalendarYear]

One to get the last year we have sales

EVALUATE
TOPN(1,
SUMMARIZE
(
FactInternetSales,
DimDate[CalendarYear]
),
CALCULATE(MAX(DimDate[CalendarYear])))

and one for Product Color (we will change this one later)

EVALUATE
SUMMARIZE
(
FactInternetSales,
DimProduct[Color]
)
ORDER BY DimProduct[Color]

What you might notice here is that I use Summarize, this will make sure it only return values that are used in a relationship with the FactInternetSales table. I also order the outcome to make sure we get the “right” order in the report.

Add all the parameters to the report:

This will add the parameters over my report:

Now for the main report I need a more extensive query that allows filtering by parameters and creates some interesting measures on the fly. This is the measure that I created for this purpose:

DEFINE MEASURE FactInternetSales[SumofSales] = SUM(FactInternetSales[SalesAmount])
MEASURE FactInternetSales[SumofSales PY] = CALCULATE(FactInternetSales[SumofSales], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
MEASURE FactInternetSales[SumofSales YoY] = FactInternetSales[SumofSales] – FactInternetSales[SumofSales PY]
MEASURE FactInternetSales[SumofSales YoY pct] = FactInternetSales[SumofSales]  / FactInternetSales[SumofSales PY]

EVALUATE
CALCULATETABLE
(
ADDCOLUMNS
(
FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
),
“Sum of Sales”,FactInternetSales[SumofSales],
“Sales last year”,FactInternetSales[SumofSales PY],
“Sales YoY”,FactInternetSales[SumofSales YoY],
“Sales YoY pct”,FactInternetSales[SumofSales YoY pct],
“Rank”, RANKX(FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
)
,FactInternetSales[SumofSales YoY])
),
DimDate[CalendarYear] = 2003,
DimProduct[Color] = “Red”
)
ORDER BY [Rank]

Lets take this step by step. First I create the measures in the define statement, these are pretty straightforward (except the RANK, I will come to that in a bit):

DEFINE MEASURE FactInternetSales[SumofSales] = SUM(FactInternetSales[SalesAmount])
MEASURE FactInternetSales[SumofSales PY] = CALCULATE(FactInternetSales[SumofSales], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))
MEASURE FactInternetSales[SumofSales YoY] = FactInternetSales[SumofSales] – FactInternetSales[SumofSales PY]
MEASURE FactInternetSales[SumofSales YoY pct] = FactInternetSales[SumofSales]  / FactInternetSales[SumofSales PY]

Next I start with the Evaluate to return a tabular result and order the result set by Rank:

ORDER BY [Rank]

Then I add CALCULATETABLE that will make sure the returned table will be filtered by

DimDate[CalendarYear] = 2003,
DimProduct[Color] = “Red”

These will be parameterized later.

The core of the query is result set of a CROSSJOIN between CalendarYear and  EnglishProductName. The results are FILTERed where the crossjoin combination has make any sales.

FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
)

to this CalendarYear and  EnglishProductName combination that have sales we add some columns

ADDCOLUMNS
(
FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
),
“Sum of Sales”,FactInternetSales[SumofSales],
“Sales last year”,FactInternetSales[SumofSales PY],
“Sales YoY”,FactInternetSales[SumofSales YoY],
“Sales YoY pct”,FactInternetSales[SumofSales YoY pct],
“Rank”, RANKX(FILTER(
CROSSJOIN(
VALUES(DimDate[CalendarYear]),
VALUES(DimProduct[EnglishProductName]))
,FactInternetSales[SumofSales] > 0
)
,FactInternetSales[SumofSales YoY])
)

Interestingly in order for us to get RANK properly evaluated we couldn’t use the measure as that would push the calculation to outside the crossjoin calculation filter context. Adding it inline with the addcolumns allowed the filter context to be properly evaluated.

Executing this in SSMS gives us the following table:

Now I went back to RS and opened the dataset properties and pasted the above query into the query field:

Now this doesn’t refresh the fields, so you need to work around this again. Right mouse click on the query, this opens the query but with an error message cannot find cube. Press OK and select the “Model” cube. Now click on the DM icon again, say yes to result loss message and turn off query UI mode (yes its cumbersome) and paste in the query:

Press OK and now you will see the Dataset has been updated with the right fields:

Now you can create the report you want:

And run it:

As you can see I added some expressions to make it look pretty . Now for the last step we need to have the query respect the parameters.

Select the dataset and as you could see in Marco’s blog post we need to change the query into an expression. Go back to the dataset properties:

And click on the formula button. Now instead of a plain string we need to make it a VB like string:

Make sure lines are ended with vbCrLf (A big thanks to Jason Tremper from the RS team that helped me figure this out).

In the bottom part replace the hard coded parameters values with the parameter values:

Update: both Chris Webb and Greg Galloway pointed out that I should be able to use parameters directly in the dataset. I tried that before and got a error message. I tried again today and it worked so I must have done something wrong last time. So here is the preferred alternative to the method above.

Open the dataset properties and select parameters. Add both parameters:

Now  go back to the query and just past the entire query in, as is. No need to make it into a string as I had to do above:

Now for the last part, replace the hardcoded strings with the parameters:

And voila that is all there is to it.

I also applied the same logic to the Color parameter where I wanted to make sure I only show the colors that have sales in the same year:

Adding it all together you get this report:

I think this is a very valuable way of creating reports, I wish I had this when I still was a consultant. Its pretty easy to template and use over time and time again without having to update and change your model. I feel that it also is a little easier this way than using MDX, although it might have to do with a personal preference.

You can download the project here. Its based on Adventureworks.