Using SSAS Tabular and Excel Pivot table is a common scenario in real life. However, it brings challenges as well ,such as proper implementation of DrillDown functionality from excel pivot table. I’m not sure how many users use this in real life, but our users do. Out of the box DrillDown behavior of Pivot Table works well for simple measures but when you use even a slightly complex calculation in your DAX measure you will get an empty dataset or even worse – wrong data.
We can simulate this behavior quite easily on Adventure Works samples. (AW Internet Sales Tabular Model 2014) we have two measures to compare here: Current and Previous Quarter values. Here is the definition of the measures:
Internet Current Quarter Sales := TOTALQTD ( [Internet Total Sales], ‘Date'[Date] )
Internet Previous Quarter Sales := CALCULATE ( [Internet Total Sales], PREVIOUSQUARTER ( ‘Date'[Date] ) )
In the following picture we can see usage of the measures in simple Pivot Table analyses.
Figure 1 – AW Quarter Sales Measures in Pivot Table
If I double click on the green cell in the example above I get the right list of detailed records. See sum amount in the following figure.
Figure 2 – Simple Measure DrillDown Result-set
However if I double click on the red cell I got only an empty result-set/sheet as we can see in the following figure.
Figure 3 – Empty Result-set for Complex Measure
So what can we do about this?
SSAS Custom Actions
The short answer is we need to implement custom action. But the short answer has quite a long explanation behind it. Unfortunately we need our custom action to work in any combination of chosen dimensions/attributes. This means we have to check for current context and we have to do this for every selected value in each attribute during the DrillDown action. So let’s explain how to do this:
Tabular model do not have out of the box custom actions but thankfully there is a bids helper which adds this functionality to SSAS Tabular. But first we need to create a query which would perform custom action query. DAX query directly is not supported for Custom Actions but you can define DAX query in Custom Actions Editor via RowSet and writing DAX into a string as you can see in the following figure.
Figure 4 – How to Add DAX Query Custom Action into SSAS Tabular
As our DAX query is a string combination with MDX code (which evaluates again to string) it is a good idea to write a skeleton for query in DAX Editor first to test functionality.
Let’s check how our DAX query looks:
Figure 5 – Drill Down Query Template
Yellow Part – beginning of DAX query. We have the following logic here:
- AddColumns command which adds our measure at the end of the command.
- Get to 10000 records so we will protect Server and Excel from overload when too many details for improper combination of attributes is selected.
CalculateTable command to implement custom logic of measure (in our case PreviousQuarter function filter.
- We need to pass the right context into this filtering criteria. In our case we expect this measure will always be used with Calendar Year and Calendar Quarter attribute selected in your analyses. Therefore we can pass current member value from MDX statement.
- Orange Part – will basically pass currently selected members into my analyses. (in this sample we implemented only CustomerID) We need this part for each attribute in our SSAS which can be used in excel for analyses. For this purpose I created Semi-Automated solution in excel which I’ll explain further.
- Violet part – add measure into our drill down result to get values user wants to see. (this part is necessary for instance when you do currency conversion as well in your measure and the value of the measure is no longer possible to calculate from base columns)
As my model had a lot of dimensions and attributes I did not want to create such a complicated and error prone code for each attribute. So I created CustomAction Helper Excel where I put a formula for creating this code based on attribute name which I’ll explain in the next section.
This is how users can then get detailed data:
SSAS Custom Action helper
As Mentioned above I created a helper to avoid repetitive manual work. I’m publishing this so you can reuse this for your solution.
How to work with Excel:
- Open Excel and Get query which is in the first line. This query will help you to get a list of dimensions and attributes in your tabular solution. You need to customize query of course with the name of your SSAS DB and as well dimension/table list for which you want to avoid this functionality.
- Run query and copy paste result into excel sheet.
- Copy custom action attribute part from excel to SSAS Custom Action editor – orange part. (just mark rows in excel and copy paste it)
Figure 6 – Custom Actions DrillDown – Attribute Part Helper
Having Drill Down functionality in SSAS Tabular for Excel Pivot table can be challenging for some measures. Especially for complicated measures where you implement logic in DAX such as exchange rates time intelligence or parameter table pattern. I created a helper which can help with solving this issue but the most problematic part might still be to write first part of DrillDown command logic which in fact replicates part of the logic of your measure.
DAX code is beautified by DAX formatter.