SSAS and minimum reporting size...

  • swjohnson

    Hall of Fame

    Points: 3254

    Can SSAS handle this...let's say we have a hierirachy of divisions, teams or whatever and we have drill down capability. something like

    Company Overall

    + Division 1

    + Team 1

    + Team 2

    + Team 3

    + Division 2

    + DTeam1

    + DTeam2

    So now we are reporting say statistical data at a line level (Team 3) that has to have a minimum size of say 5 responses. Obviously Company Overall and Division 1 meet this and let's assume even Team 1 and Team 2 do but Team 3 does not.

    In SSAS (or MDX) is there any way to enforce this minimum requirement, so the drill down doesn't display or displays insufficient data?



  • Greg Edwards-268690


    Points: 20587

    This sounds more like you'd want a KPI on a dashboard to point out those who haven't met some minmum requirement.

    We let our rocks show as the water level gets lower.

    I'd tend to look at this as hiding a possible problem, at least from what I read into your post.

    Greg E

  • swjohnson

    Hall of Fame

    Points: 3254

    In this case, it is survey statistical data and the minimum response size is there to protect anonymity/confidentiality of respondents. Once you fall below a certain number of responses, it is easy to determine who in the group answered and the responses they gave for individual items. If it is discovered who answered lowly, then retribution could occur and that undermines the whole employee engagement/organization assessment process.

    In our Reporting Services PDF/XLS reports and crunching engine, we can supress this data however we are looking to see if Analysis Services could do this directly or if we are going to code for it at the web interface level.



  • happycat59

    One Orange Chip

    Points: 29308

    The short answer is Yes, SSAS can do what you are after. You need to create a calculated member (measure) that checks whether the number of responses (using the COUNT function) meets your criteria. Perhaps something like

    IIF (COUNT(Measures.Responses) > 5, SUM (Measures.Responses), NULL)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply