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:

image

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 ? Smile)
  • 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.

image

 

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

image

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

image

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

image

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:

image

Finish the wizard and run the report:

image

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:

image

This will add the parameters over my report:

image

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:

image

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

image

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:

image

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

image

Now you can create the report you want:

image

And run it:

image

As you can see I added some expressions to make it look pretty Smile. 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:

image

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

image

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:

image

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:

dspar

 

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:

query

 

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

param

 

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:

image

Adding it all together you get this report:

image

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.

  • Hi Kasper,

    Can I give a cheeky mention for my post last year on handling multi-value SSRS parameters in DAX too?
    http://cwebbbi.wordpress.com/2012/06/01/handling-ssrs-multi-valued-parameters-in-dax-queries/

    Chris

  • Kasper de Jonge

    thanks @Chris Webb , I missed this post. I retried the parameters as you had them in your post and it worked so I updated my post. I must have done something wrong along the way..

  • Graeme

    I tried opening the solution in VS2012 Update 1 – I thought this had SSDT built in, but it does not recognise .rptproj or .smproj projects.

    What am I missing?

    • Kasper de Jonge

      Hi Greame,

      You need to install SSDT from the SQL Server installation. VS comes with the SQL Server templates, not the BI templates.

      Kasper