Saturday, September 22, 2012

Create SSRS Report using DAX

 

We could query BISM Tabular model using either MDX or DAX. People are very familiar with how to use MDX in SSRS.  Using DAX in SSRS is totally different from using MDX.  Here is an example to create SSRS report using DAX language:

image

List of Steps:

1. Open SQL Server Data Tools to create a SSRS report.

2. Add an existed BISM Tabular Model as the Shared data source.

3. Write the DAX in the SSMS or DAX Studio first. The DAX Studio is an Excel Add-Ins that you could download from the CodePlex.

EVALUATE
SUMMARIZE
    (    
        CALCULATETABLE( 'Internet Sales'
        ,'Product Category'[Product Category Name] = "Bikes"       
    ) ,
                  
 'Date'[Calendar Year],
 'Product Category'[Product Category Name],               
  "Total Sales", 'Internet Sales'[Internet Total Sales],               
  "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt],              
  "Total Margin", 'Internet Sales'[Internet Total Margin]           
 )

 


4. Create SSRS dataset




    • Add a new dataset and connect to the BISM Tabular Model
    • Open Query Designer
    • Click on the Command Type DMX button on the toolbar to switch to the DMX query designer.

image




    • Click on the Query/Design Mode button to switch to the Query mode.

image




    • Paste or type your DAX query into the Query window and then click on OK button.
    • Click on the Fields tab to fix/rename the field name to make it more readable. The field name generated by the query designer will not lineup with the column name you have in the DAX query.

 image




    • Now, you could add those fields to the report.


5. Add parameter



  • Go to dataset query, open the query designer.
  • Change the DAX query by replacing the filer part of Query with parameter name and click on the Parameter Button to open the Query Parameters window

image


image



  • Add Parameter and set the default value, then click OK to exit out the designer
  • On the report data window, you will find a parameter been created with default value that you typed in.

image


6. Create parameter dataset



  • There is no parameter query generated by the tool. You need to create it if you allow user to pick from the drop down list.
  • Write a DAX query with VALUES() function using the same steps before to create a dataset that will contain the list of parameter values.

EVALUATE 
values('Product Category'[Product Category Name])
ORDER By 'Product Category'[Product Category Name]




  • Associate this dataset to the report parameter

image



  • Now your SSRS report should work with single value selection.

Report Parameter Allows Multiple Values


If you allow multiple values for the report parameter, you could not use filter with equal sign (“ = ”) in the DAX query.  You could use PATHCONTAINS() function in the filter part of query to filter the result set.


PATHCONTAINS(<path>, <item>)


Since the multi select parameter string pass in will contain extra special characters that could not be used for the Path() function, you need to use Substitute() function to replace them.


Here is the complete DAX query for the report.



EVALUATE
SUMMARIZE(
    CALCULATETABLE( 'Internet Sales'
            ,PATHCONTAINS(
                    substitute( 
                        substitute( 
                            substitute( 
                                    @CategoryName
                              , "{ ", "") 
                        , " }", "") 
                    , ",", "|") 
        ,'Product Category'[Product Category Name] ) 
    
),                
 'Date'[Calendar Year],
 'Product Category'[Product Category Name],               
  "Total Sales", 'Internet Sales'[Internet Total Sales],               
  "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt],              
  "Total Margin", 'Internet Sales'[Internet Total Margin]           
 )

 

After you update your query, the SSRS report will work for multiple selections from the user now.

 

 


Reference:


http://daxstudio.codeplex.com/


http://msdn.microsoft.com/en-us/library/gg492182.aspx

4 comments:

  1. Great post.. There are very few blogs out there which explains DAX in SSRS. Thanks for sharing :)

    ReplyDelete
  2. @Fendy, thank you for the post. I was looking for the pick ax button in Report Builder 3.0. I can't seem to find it. Do you know if you can build DAX queries in Report Builder 3.0?

    ReplyDelete
    Replies
    1. You can find instructions for using Report Builder in my blog post here: http://sqlblog.com/blogs/marco_russo/archive/2014/05/21/write-dax-queries-in-report-builder-ssrs-dax-ssas-tabular.aspx

      Delete
  3. Thanks for the sharing!
    I got a question. I want to do a summary report with a DISTINCTCOUNT measure and hierarchy, which I hope it looks like below:
    distinctcount(Measure)
    H1
    H1-h1 30
    H1-h2 80
    H1 Subtotal 100
    H2
    H2-h1 10
    H2-h2 30
    H2-h3 20
    H2 Subtotal 40
    Total 120

    It will be very easy if i do the report with Excel + Analysis Services. But is there efficient way to make it without using the COUNTDISTINCT function embedded in SSRS, as I have to load a big flatted dataset into SSRS, and the performance is unacceptable.

    ReplyDelete