Use DAX Search Function to Retrieve Subset Data in SSRS Report

Most of you would be familiar with substring search in T-SQL. If you have a SSRS report using DAX, do you ever winder how to do the search using the pass in parameter?

Here is the sample DAX query used to retrieve the Order Count and Sales Amount for Customers from Internet Sales:

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales'    
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Here is the sample result:

 

 

If you only want to return certain customer, you will need to add a filter by pass in the Customer name as the report parameter.  Here is the sample DAX query:

 

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  DimCustomer[CustomerName] = @CustomerName
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

Then you could get the result that has exact match to the customer name passed in.

 

If you need to return all the customers that has name like “Peter”, you could use DAX Search function to retrieve the list.  Here is the example:

 

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0
        )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

The above query equals to the following T-SQL:

SELECT 
[CustomerName] ,[Gender], [Order Count],[Sum of Sales Amount]
FROM [dbo].[FactInternetSales] F 
INNER JOIN [dbo].[DimCustomer] C
On C.CustomerKey = F.CustomerKey
WHERE C.[CustomerName] like @CustonerName

You can see the result as below.  All the customers with name containing “Peter” got returned.

 

 

Sometime you may also want to handle the situation when user passed in Space or Blank parameter.  Here is a technique you could use.  When Blank customer name entered, the report will return all the customer without any filter.

EVALUATE

SUMMARIZE (   

CALCULATETABLE(
    'FactInternetSales',  
    IF(@CustomerName = blank(), 1=1, IFERROR(Search(@CustomerName, DimCustomer[CustomerName]), -1) > 0 )
                    )
,DimCustomer[CustomerName]
,DimCustomer[Gender]
,"Order Quantity", [Order Count]
,"Sales Amount", [Sum of Sales Amount]

)

 

Here is the result:

 

 

Reference

http://technet.microsoft.com/en-us/library/ee634235.aspx

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:

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.

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

• 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.

 

• 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

• 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.

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

• 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

The Simple DAX Functions as SELECT Statement

Do you even wonder how to use DAX function to query BISM to return a simple dataset or a dataset that is grouped by some attributes? 

Evaluate is the core DAX function that returns a table of data. It is similar to the SELECT statement in T-SQL.

Here is the Syntax:

EVALUATE <table>  

Example:

We want to select all the data from the Internal Sales table.

EVALUATE('Internet Sales')

By using the Evaluate function, the query returns all the rows and columns from the Internet Sales as below:




To sort the result,we could add ORDER BY at end. For example, we want to sort the result by Customer Id and product ID, we could do the following:




CalculateTable is a function that return a table that modified by the giving filters. It is similar to the SELECT * statement with WHERE Clause in T-SQL

Here is the Syntax:

CALCULATETABLE(<expression>,<filter1>,<filter2>,…)

This function takes expression for a table as the first parameter and any number of Boolean expressions as filter.

Example:

We want to return the Internal Sales records that were ordered in 2007 and the [Product Category] equals to “Bikes”.

EVALUATE

CALCULATETABLE(

 'Internet Sales',

 'Date'[Calendar Year] = "2007", 

 'Product Category'[Product Category Name] ="Bikes" 

)

ORDER BY 'Internet Sales'[Order Date]

By using the CalculateTable function, the query returns the records that match the filters provided.

Summarize is a DAX function that returns a table for the requested totals over a set of groups. It is similar to the SELECT statement with Group By in T-SQL.

Here is the Syntax:

SUMMARIZE(<table>, 

<groupBy_columnName>[, <groupBy_columnName>]…[, <name>, 

<expression>]…)

This function takes table of data as the first parameter, any number of columns as group by parameter, the name given to the Sum of the column, and the expression

Example:

We want to Sum up the total of [Internet Total Sales], total of [Internet Total Tax Amt], and total of [Internet Total Margin] FROM the [Internet Sales] table grouped by the [Calendar Year] and the [Product Category].

EVALUATE

SUMMARIZE('Internet Sales',             
    '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]            
)ORDER BY 'Date'[Calendar Year],'Product Category'[Product Category Name]

By using the Summarize function, the query returns the sum of the measures that are grouped by the columns provided.

Reference:

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

http://msdn.microsoft.com/en-us/library/ee634760(v=sql.105).aspx

http://technet.microsoft.com/en-us/library/gg492171.aspx

Use DAX Earlier Function in Calculated Column to Mimic Group By Clause

It is very easy to group or slice the measures in the Pivot table by adding a attribute to the row or slicer.  But, how do we create the same effect in the Calculated Column?

Here is the Sample Data

If we treat this table as a SQL table, we could use the following T-SQL query to find the total queued time for each queue per date.

SELECT [Date], [QueueName], SUM([QueuedTime]) AS [TotalQueuedTime]

FROM CallDetail

GROUP BY [Date], [QueueName]

In PowerPivot, we could use the EARLIER function to achieve the same effect.  EARLIER is a very useful DAX function when you want to use a certain value as input to produce a calculation.

Syntax:

EARLIER(<column>, <number>) 

Solution:

Step 1: Calculate the total of the QueuedTime by using SUM(), and then use FILTER() to obtain the subset of the table for the Date and the QueueName needed.

=CALCULATE(

    SUM(CallDetail[QueuedTime]), 

    FILTER(CallDetail, CallDetail[Date] = ?) 

           && CallDetail[QueueName]= ?

            )

    )

Step 2: In the question mark area, we need to plug in a value/expression to restrict the rows been returned.

If we set they to the static value as “2012-02-18” and “Queue A”, the Sum of QueuedTime will be 900 for all the rows which won’t be correct for the Queue not equal to “Queue A” or the Date not equal to “2012-02-18”.

Step 3: To pass in correct expression, we could use the EARLIER() as the input for the certain value (i.e. Date or Queue name) that the calculation will be based on it. 

=CALCULATE(

    SUM(CallDetail[QueuedTime]), 

    FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date])

           && CallDetail[QueueName]= EARLIER(CallDetail[QueueName])

            )

    )

You can see the result now is correct based on the Date and QueueName

The EARLIER function is based on the current row context. If there is no row context, it will return an error.

Implementation Example:

Here is a scenario that I have encountered: Create a measure that contains the cumulated Queued Time only for the last queue.

To solve it, I add the following condition to the pervious formula based on the LastQueue flag

=IF(CallDetail[LastQueue] = 1, 

    CALCULATE(

        SUM(CallDetail[QueuedTime]), 

        FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date]) 

           && CallDetail[CallID]=EARLIER(CallDetail[CallID])

            )

        )

     ,BLANK()

    )

This way, only the last queue will have the total queued time in the column.

When summing up in the Pivot table, the total of QueueTimeHandled per day will now match the total of QueuedTime.

My colleague Javier Guillen has another nice blog entry to discuss how to use the EARLIER() in DAX measure. You may want to check it out.

Reference:

http://technet.microsoft.com/en-us/library/ee634551.aspx