I have the following Crosstab:
YTD Sls$ Other Measures
Product Line Dept1
Dept2
OtherGrp1
OtherGrp2
Dept1 and Dept2 are members within Department (which is part of the Product Line Hierarchy) in the Cube.
OtherGrp1 and OtherGrp2 are Calculated Membesr as aggregate(currentMeasure within set set([Dept3],[Dept4]....)
I need to sort by YTD Sls$ which is a calculated member as tuple([YTD], [TY], [Total Sls]).
If I leave layout as above, and create a Sort on YTD Sales, it marks the Sort on Product Line, but it does not sort.
If I change layout to below - pulling aggregate measures out of current nesting and use Department rather than Product Line:
YTD Sls$ Other Measures
Department Dept1
Dept2
OtherGrp1
OtherGro2
It now sorts Dept1 and Dept2 correctly within Department, but the OtherGrp's are not sorted (this makes sense to me).
I need to Sort all Departments and Groups of Departments (OtherGrp's) descending by YTD Sls$.
Any thoughts would be greatly appreciated.
Thanks in advance,
Adam.