9 March 2016

Create SSRS Report using DAX

Tabular and Multidimensional models can be queried by using either MDX and DAX. We are used to create reports with data sets using MDX. This process is very easy and we can even drag and drop the dimension attributes, hierarchy levels and measures to the query designer and the MDX query is automatically generated for us.

The process of creating a data set from a DAX query is not straightforward. We don’t have yet a DAX editor and of course we don’t have also an automatic query generator. Because of this you should edit and test your queries in SSMS or DAX Editor before paste them into a report data set.

So you can follow these steps to create a simple SSRS report that get sales data from an SSAS tabular database (for instance, I used the AdventureWorks Tabular Model SQL 2012 database) and receive some inputs from two parameters.

1. Create a shared data source to connect to a tabular or multidimensional model (in this scenario I use a tabular one)

2. Create a report with the shared data source previously defined

3. Create data set with DAX query

When you open the Query Designer window, the editor mode selected by default is “Command Type MDX” with graphical query designer activated.

To create a data set using a DAX query, you need to switch to “Command Type DMX” option. In the message box, click Yes.

Then click on “Design mode” button to switch to query mode designer.

As I told you before, you should write and test your query outside the DMX editor (for example, in SSMS):

Then you can paste your query in DMX editor and click OK. If you have any DAX syntax error, a message will appear with the error and you cannot save the data set without fixing it.

4. Add a table to your report with the previous data set as source

Click on “Preview” button to see the result:

5. Add report parameters

Now we are going to add some report parameters, for instance, the product category and sales year.

First of all, you should create the data sets that will return the list of distinct values for each parameter. For this purpose, we need to follow the same steps that we use to create the sales data set changing only the DAX query used.

To create the Product Category data set, use the DAX query shown in the following image:

To create the Product Category parameter and specify the available values using the previous data set:

We can do the same steps for Date parameter, using the following DAX query (notice that this data set will return the calendar years in descending order):

EVALUATE

VALUES('Date'[Calendar Year])

ORDER BY 'Date'[Calendar Year] DESC

6. Change main data set to receive parameter values

Now we need to get back to Sales data set and change a little bit the DAX query used before. The SUMMARIZE function that was receiving Internet Sales table should now receive the same table filtered by the values received from report parameters. For this purpose, we use the CALCULATETABLE function.

After changing the query, we should specify the query parameters and assign their default values.

Notice that for year comparison we use the VALUE function to cast the Year parameter from text to integer because the Calendar Year from Date table is numeric. This is necessary even if the report parameter is an integer one.

If we click on “Preview” button, we can now filter the sales report:

If we want a multivalue parameter, we need to change the parameter definition but also the DAX query that receive the parameter. For instance, we will change the Category parameter to allow multivalue selection:

After this change, when we click again on “Preview” button the Category parameter now allows you to select multiple values.

However, when you click on “View Report” to apply the parameters to the report there is no errors but the table will be empty:

Since the Category parameter is now multivalue, we cannot use the equal sign (“=”). Instead we should use the PATHCONTAINS function in the filter part of DAX query and change the expression passed to Category data set parameter using the JOIN function to concatenate select values separated with a pipe symbol (“|”).

EVALUATE

(

SUMMARIZE

(

CALCULATETABLE

(

'Internet Sales',

PATHCONTAINS(@Category, 'Product Category'[Product Category Name]),

'Date'[Calendar Year] = VALUE(@Year)

),

'Product'[Product Name],

'Product Subcategory'[Product Subcategory Name],

'Product Category'[Product Category Name],

'Date'[Calendar Year],

"Total Sales Amount", sum('Internet Sales'[Sales Amount]),

"Total Product Cost", sum('Internet Sales'[Total Product Cost]),

"Total Tax Amount", sum('Internet Sales'[Tax Amount])

)

)

Now we can filter the report by selecting multiple product categories:

 

.

.

.

.

      Helena Cabral
  Associate Manager
Solutions Development
Blog