Hello,
Using the Sales and Marketing sample cube within Report Studio, I have a two level crosstab, from the Retailers dim:
Revenue
Region level | Retailer Country level
So it looks like:
Americas Canada 306,159,341.39
United States 826,912,571.54
Mexico 167,187,013.22
Brazil 138,276,67
Central Europe Germany 262,313,028.79
France 286,569,502.15
United Kingdom 277,509,546.56
Switzerland 100,731,875.51
Belgium 113,031,617.63
etc...
What I would like to do is change the drill up/down behavior for the country so the drill down is to the Retailer Site, skipping level Retailer Name. I can accomplish this by adding the Retailer Site into the query, and using the Advanced Drill Behavior option of "Change Expression", with value of Retailer Site. This works perfectly on the drill down, as both Region column and Retailer Country column get drilled down upon, showing the values I expected.
The problem is drilling up, in that I cannot figure out how to drill up on either column (not the measure cell) so that the correct data is shown. It seems that drilling up on the inner column does not cause the outer column to drill up. And drilling up on the outer column causes the inner column to drill up only one level, not two.
Any ideas on how to set the Advanced Behavior to make the drill up work as desired? Note that I have not been able to get Ancestor or Depth Based Expression to work as I want either, or I just haven't found the right combination.
I have attached the report spec. This is Cognos v10.2.
Thank you.
Any ideas here?