locked
MULTIPLE DISTINCT COUNT PROBLEM RRS feed

  • Question

  • Hello experts,

    My inventoryfact table consists three dimensions such as Stores,Dates and Products. In SSRS environment I have used parallelperiod to calculate the stock levels of each stylecodes.

    Here is the code:

    WITH 
    MEMBER Measures.[Store Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[Quantity] ) 


    When I used this measure in my reports , I retrieve the correct results.

    I have create two measures from DimProduct dimension attributes and set their aggregation type as "DISTINCT COUNT". When I try to open this combined resultset then I get wrong results in addition my previous stock levels.

    Here is my complete code SSRS code:

    WITH 
    MEMBER Measures.[Store Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[StockLevel] ) 
    SELECT NON EMPTY { [Measures].[Store Stock Level] } ON COLUMNS, NON EMPTY { ([Products].[ProductHierachy].[Sub Class ID].ALLMEMBERS * [Products].[Style Code].[Style Code].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimStoresSegmentation, CONSTRAINED) ) ON COLUMNS FROM [MNG_MASTER])) WHERE ( IIF( STRTOSET(@DimStoresSegmentation, CONSTRAINED).Count = 1, STRTOSET(@DimStoresSegmentation, CONSTRAINED), [Dim Stores].[Segmentation].currentmember ), IIF( STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED).Count = 1, STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED), [Dim Date].[WeeklyCalendar].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

     

    I am now able to get correct inventory stock levels of each product. But DISTINCT COUNT aggregation type does not work properly for these two measures. This aggregation considers just DISTINCT COUNT values of the specifed date range. For example:

    DateID StoreID StyleCode Quantity
    20110101      1001    A -5
    20110102 1001    A 15
    20110103 1001    A 5
    20110101 1001    B 3
    20110102 1001    B -1
    20110101 1001    C 12

    And in addition, I have two Stylecodes

    D (stock level =10),

    E (Stock Level=5)

    Finally I get the following results:

    DistinctStyleCode count = 3 ----> This must be "5"

    Inventory Level = 44 -----> This is correct.

    Please help!!

    Tuesday, June 28, 2011 3:34 PM

Answers

  • But DISTINCT COUNT aggregation type does not work properly for these two measures. This aggregation considers just DISTINCT COUNT values of the specifed date range. For example:

    DistinctStyleCode count = 3 ----> This must be "5"

    Hi innocent1973,

    To do this, you can create the calculated member (DistinctStyleCode count) using the following code in the CUBE calculation tab:

    =Distinctcount([Products].[Style Code].[Style Code].members)

    then, the calcualted measure will always return 5 regardless of your MDX context.

    thanks,
    Jerry

    • Marked as answer by Jerry Nee Thursday, July 7, 2011 9:57 AM
    Thursday, June 30, 2011 6:20 AM