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

    Any idea?

     

    King Regards

     

     

    Wednesday, June 22, 2011 9:08 PM

Answers

  • Hi innocent1973,

    Please understanding that here we generally focus on the troubleshooting of reporting services issues and the problem we are encountering has beyond our specialty. I suggest you post your question on the Analysis Services forum, because it seems you are now facing a MDX issue rather than a Reporting Services issue.

    Hope you can get the issue resolved soon with helps from appropriate team.

    Thanks,
    Eileen


    • Marked as answer by Eileen Zhao Thursday, July 7, 2011 2:48 AM
    Tuesday, June 28, 2011 3:14 PM

All replies

  • Hello Again,

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

     

    Thursday, June 23, 2011 2:10 PM
  • Hi innocent1973,

    Please understanding that here we generally focus on the troubleshooting of reporting services issues and the problem we are encountering has beyond our specialty. I suggest you post your question on the Analysis Services forum, because it seems you are now facing a MDX issue rather than a Reporting Services issue.

    Hope you can get the issue resolved soon with helps from appropriate team.

    Thanks,
    Eileen


    • Marked as answer by Eileen Zhao Thursday, July 7, 2011 2:48 AM
    Tuesday, June 28, 2011 3:14 PM