SQL Server Performance

aggregating data according to user selection

Discussion in 'SQL Server 2005 Analysis Services' started by ritika.thakkar, Feb 27, 2007.

  1. ritika.thakkar New Member

    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!!









Share This Page