Drill Down for Items at Different Levels

[wyconian]

Hi everyone, I'm hoping someone can help me with this.

I've got a crosstab report in report studio based on DMR.  I'm trying to set the report up so that I can drill down on the data items.  It seems to work OK for data items in the same level of a hierarchy but it's not working when I bring in data items from a different level in the same hierarchy.

For example I've got the following items from level 4
Current Assets
Other Assets
Current Liabilities
Long Term Liabilities

The drill down works fine on these items

But I've also got the following from level 3
Stockholders Equity
Operating Revenue

Again the drill down works fine on just the level 3 items but it doesn't work when I combine items from level 3 and level 4 in the same query.

Anyone got any ideas on how I can get the drill down to work with the different levels in the same query?

Thanks for your help :-)

[wyconian]

Hi

I managed to find a solution to this by adding the parent of the items I wanted to drill down on as a crosstab item before the drill down item.

You might find the following useful (replace operating revenue with the data item you want to drill on)

Create a crosstab report, set the Define Member Sets property for the crosstab query to yes.
Drop the Operating Revenue member into the query.
Drop the Operating Revenue member into the query again and rename as Operating Revenue (member) (this will become the total)
Create a data item called Operating Revenue (set) with expression set([Operating Revenue (member)])
Create a data item called Operating Revenue (children) with the expression children([Operating Revenue (member)])
Create 2 data items with the expression emptySet(hierarchy([Operating Revenue (member)])), name one Operating Revenue (empty set) and the other Operating Revenue (empty set 2)
Switch to the Member Sets page and create the nested group as pictured

Open Drill behaviour, tick Allow drill-up and drill-down but disable drill up and down for the Operating Revenue (member) data item
Switch to the advanced tab and set the drill up and down behaviours as below:
Operating Revenue, Up = Preserve, Down = Preserve
Operating Revenue (set), Up = Empty Set, Down = Empty Set
Operating Revenue (children), Up = Replace Expression, Down = Replace Expression
Operating Revenue (empty set), Up = Default, Down = Change Expression, Data Item = Operating Revenue
Operating Revenue (empty set 2), Up = Default, Down = Change Expression, Data Item = Operating Revenue (children)
Drop your other data item in and format the crosstab, such as pictured (note I repeated the same steps above for Operating, Admin and Other to demonstrate the concept working at different levels of the same tree)

results:

Operating Revenue (empty set) is an empty set, so will not display
Operating Revenue (empty set 2) is an empty set, so will not display

drill on operating revenue:

Operating Revenue (set) is now an empty set, so will not display
Operating Revenue (empty set) is now Operating Revenue
Operating Revenue (empty set 2) is now Operating Revenue (children), padded left and in italics
You can't drill down on Operating Revenue but can continue drilling on Operating Revenue (empty set 2)
Drilling up on Operating Revenue (empty set 2) will eventually reset it.
Drilling up and down on also works regardless of level and members are independent, i.e. drilling on Operating Revenue does not affect Operating, Admin and Other.