I have a report with chart and List which is using Powerplay Cube as the Datasource.
I allow drilldown on the Sales Hierarchy in this report, but users want to have the path shown(on what did they drilldown on).
For Example Sales Hierarchy has:
Country-->Sales Territory-->Sales Person-->Brand.
Take this case.
When run the report will show Countries in chart and List.(Say USA,Mexico,Canada).Users will drill down on USA and it breaks down into Sales Territory(North East, Mid West, West).User now drillsdown on North East and has person(xyz,abc,efg) and drills down on xyz. Finally has brands sold by xyz.
Users like to see the path: USA-->North East-->xyz
Is this possible?
Please let me know
Its possible - Need to define the member set hierarchy:
Take a cross tab report and a list report and assign the two to the same query
Ex: take 3 data items in a query
1. Reference Member (Define the Member Unique Name)
Ex: [Great_Outdoors_Sales].[Products].[Products].[Products]->:[PC].[@MEMBER].[Product line code]
2. Reference Member for Children (Descendants (Reference Member),1)
3. Ancestors for Reference Member
Ex: union(generate(except([Great_Outdoors_Sales].[Products].[Products], descendants(level([Reference Member]), 0, after)), _remainderSet(currentMember([Great_Outdoors_Sales].[Products].[Products]), intersect([Reference Member], descendants(currentMember([Great_Outdoors_Sales].[Products].[Products]), 0, after)), 0)), [Reference Member])
This is a combination of functions that achieve the desired result. Note that the [Great_Outdoors_Sales].[Products].[Products] part of the expression is the hierarchy name.
o Add any other data items as needed.
o Enable and define a Member Set
Define the Member Set hierarchy as below:
Reference Member - Ancestors for Reference Member
- Reference Member for Children
Drag the Ancestors for Reference Member into the column area of the crosstab.
Add a text item " >" to the right of the column
Drag the Reference Member for Children into the list and take the measures.
•Now we can specify the drill behaviors below in the advanced options
Reference Member - Replace Item
Reference Member for Children - Replace Expression
Ancestors for Reference Member - Preserve
Run the report. You can achieve the Hyperlink at the top of list.
Recently, I have done dis with the above procedure. Its working fine with me.
Note: It will not work with unbalanced hierarchy