Infinite drill-down in SSRS using DAX

Despite SSRS natively doesn’t support infinite drill-down, it can be realised with a help of invisible parameter. The only disadvantage is that the solution requires server-side refresh.

After clicking node “1000C110” the report displays it’s children (10001100 – 10001192):

Infinite DrillDown in SSRS - Result

Each box with a name is a link to the same report but the invisible parameter should be appended with a code of the clicked node:

Infinite DrillDown in SSRS - Action

If the node code is already there – it should be removed (collapsing), if not – should be added (expanding):

=IIF(InStr(Parameters!PrmExpandedNodes.Value, "|"+Fields!ID_Fund_ID_.Value)>0,
Replace(Parameters!PrmExpandedNodes.Value, "|"+Fields!ID_Fund_ID_.Value, ""),

Hidden parameter’s settings:

Infinite DrillDown in SSRS - Parameter

And this is how parameter should be utilised in a DAX-query:

,'Fund'[Derived Parent Fund Code]
,'Fund'[Derived Fund Code]
,'Fund'[Fund Code]
,'Fund'[Hierarchy Level]
,"Actual YTD",[Actuals $ (YTD)]
-- Taking immediate children of a node which serves as a root in the report
SEARCH("ABC12345", PATHITEMREVERSE('Fund'[Path], 2), 1, -1) <> -1
-- Taking immediate children of nodes user already clicked
-- (they can be only descendants of the root node "ABC12345")
PATHCONTAINS(@PrmExpandedNodes, PATHITEMREVERSE('Fund'[Path], 2))
"Fund ID", 'Fund'[Derived Fund Code],
"Fund Name", 'Fund'[Fund Code],
"Fund Level", 'Fund'[Hierarchy Level]
ORDER BY 'Fund'[Path]

The clause “ORDER BY ‘Fund'[Path]” guarantees correct sort order when all children are placed under their parent.

To display correctly bold dots can be used the next expression:

=LEFT("••••••••••••••••", Fields!ID_Fund_Level_.Value-Min(Fields!ID_Fund_Level_.Value, "dsFundBudgetTable_Recurrent_Funds") + 1) + " " + Fields!ID_Fund_Name_.Value

If a report requires several infinite drill-downs then it will take to create a hidden parameter for each one.


About fdtki

Sr. BI Developer | An accomplished, quality-driven IT professional with over 16 years of experience in design, development and implementation of business requirements as a Microsoft SQL Server 6.5-2014 | Tabular/DAX | SSAS/MDX | Certified Tableau designer
This entry was posted in programming, Visualisation and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s