locked
Issue with COUNT of Distinct in Grand Total of the Cube RRS feed

  • Question

  • Hi

    I've an issue in one of my cube...Please see the below screen shot.

    I am using Sql server 2008 and Office 2010.

    Above is the screen shot taken from the Excel Pivot table which is taking data from SSAS cube.

    From the above Store Count, Response and Non Response are the measures.

    And in the Row label we have Dimension as Survey Name.

    Here Store Count is COUNT (DISTINCT Stores) whereas Response is SUM (Response) and

    non Response is COUNT(DISTINCT Stores) - SUM (Response).

    Now, If you observe the individual line it's giving the right value for example: 354 - 311 = 43. but when you look at the Grand Total it's not correct. Here 1606 is not the sum of all the above Stores instead it is taking as DISTINCT COUNT in Grand Total also.

    Now my question is How to make the Store Count in GRAND TOTAL AS SUM that means from the above screen shot the Grand Total for Store count should come as 4540 and not 1606?

    Please help in achieving this.

    Thanks in advance.

    Noor

    Friday, April 20, 2012 4:29 AM

Answers

  • you may use SCOPE-assignments for this

    SCOPE([Measures].[Store Count], [Survey].[Survey].levels(0).item(0)); -- All-Member of Survey-Hierarchy
         this = SUM([Survey].[Survey].[Survey].members, [Measures].[Store Count]);
    END SCOPE;

    hth,
    gerhard


    - www.pmOne.com -

    • Proposed as answer by Jerry Nee Tuesday, April 24, 2012 5:54 AM
    • Marked as answer by SQL2012BI Wednesday, June 13, 2012 10:47 AM
    Friday, April 20, 2012 6:34 AM
    Answerer

All replies

  • you may use SCOPE-assignments for this

    SCOPE([Measures].[Store Count], [Survey].[Survey].levels(0).item(0)); -- All-Member of Survey-Hierarchy
         this = SUM([Survey].[Survey].[Survey].members, [Measures].[Store Count]);
    END SCOPE;

    hth,
    gerhard


    - www.pmOne.com -

    • Proposed as answer by Jerry Nee Tuesday, April 24, 2012 5:54 AM
    • Marked as answer by SQL2012BI Wednesday, June 13, 2012 10:47 AM
    Friday, April 20, 2012 6:34 AM
    Answerer
  • Hi Gerhard,

    Thanks for the reply. In my case I don't have any hierarchy in Survey dimension. Survey Dimension only contains Survey Name.

    So, Couldyou please provide syntax and also where exactly we include this script.

    In calculations tab we are already have script as CALCULATE. do I need to write the script below CALCULATE or in a new script?

    It would be helpful if you could provide the right syntax.

    Thanks.

    Friday, April 20, 2012 9:52 AM
  • yes, you have to write it below the CALCULATE-Command

    by "hierarchy" i mean the [Survey Name]-attribute (in terms of MDX we are still talking about an hierarchy even tough it contains only one level)


    - www.pmOne.com -

    Friday, April 20, 2012 10:34 AM
    Answerer
  • Irrespective of the dimension..If user is dragging and Dropping Measures -

     Store Count, Response and Non Response on to the Measure field area then the Store count should be come as SUM in grand total not as count of Distinct store.

    I've written below code in Calculations Tab in CALCULATE command

    CALCULATE;

    SCOPE ([Measures].[Store Count]);

    this = SUM([Measures].[Store Count])

    END SCOPE;

    Its not working. Please help in how to write.

    Thanks

    Tuesday, April 24, 2012 6:11 AM
  • have you tried the scope i provided in the first response?

    also your requirements are a bit strange for, first you do distinct counts and then you want a sum?

    what does your original fact-table look like?
    maybe you can prepair the data in the relational db so you can use sum for all measures in the cube?


    - www.pmOne.com -

    Tuesday, April 24, 2012 6:17 AM
    Answerer
  • Yes, I've tried the sample you have provided but it was giving error.

    This is a Survey Cube..where Survey is done for the stores located at different locations. If you consider a single survey then it will not be a problem as it gives you correct result like say

    for the survey - 1 ..if we have 100 stores (Count of Distinct stores) and Responses as 80 and Non response is 100-80 = 20.

    for survey - 2.. if we have 20 stores (count of distinct stores) and responses as 10 and Non Responses as 20-10= 10

    Now, if you consider the Grand Total .. the responses is coming as 90 (i.e. 80 + 10) and non Responses is coming as 30 (i.e. 20+10) or 120-90=30.

    But the Store count is coming as 100 (lets say 100 are the distinct for the two surveys) not 120.

    So, Now my aim is to how to display SUM in the Grand total for the Store Count.

    I hope now it is clear.

    Tuesday, April 24, 2012 7:16 AM
  • how do you distinguish between responses and non-responses

    you say you have 100 distinct stores, which means you have at least 100 fact rows
    of which only 80 are responses?

    how do these 100 rows look like and how are responses marked?


    - www.pmOne.com -

    Tuesday, April 24, 2012 7:59 PM
    Answerer