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, ""),
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:

EVALUATE
ADDCOLUMNS(
CALCULATETABLE(
SUMMARIZE(
'Fund'
,'Fund'[Derived Parent Fund Code]
,'Fund'[Derived Fund Code]
,'Fund'[Fund Code]
,'Fund'[Hierarchy Level]
,'Fund'[Path]
,"Actual YTD",[Actuals $ (YTD)]
)
,
FILTER('Fund',
(
-- 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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s