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):
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:
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:
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.
