aggregating data according to user selection | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

aggregating data according to user selection

Hi, I have built a cube which has a product dimension and sales fact.
There is a proper hierarchy defined in the product dimension which goes from Department level to Unique product code (UPC )level. However there is an issue. I want that data should be aggregated based on the selection made by the user. Example:
Dept = T21
Dept = T21 Range = R01
Range = R02 Stroke Colour = S1234
Stroke Colour = S1234A UPC 1
UPC 4
UPC 2
UPC 5
UPC 3 UPC4(child) is Linked with UPC3(Parent) Now,
If a report contains details of Stroke Colour 1234 only, then it should show the sales of UPC 1,UPC 2, UPC 3 and UPC 4(since UPC 4 is the child of UPC3)
Similarly if the report contains details of Stroke Colour 1234A only, then it should show the sales of UPC 4 and UPC 5 only.
However, in case the report shows stroke colour 1234 and 1234A, then the sales of Stroke colour 1234 should include sales of UPC 1,2,3 and 4; but the sales of Stroke colour 1234A should include sales of only UPC 5. How can i implement this using a cube???? If any of you have ever come accross a similiar situation, please help me out…. thanks in advance!!
]]>