Post reply

MDX Query Calculated Member

  • March 15, 2011 at 2:53 pm

    #145311

    Ok so I'm baffled and it's something i've been coming back to every now and again to revisit. I have to say i'm stumbling through MDX like a blind drunkard, so excuse my noobinism.

    I have a calculated member, below.

    CREATE MEMBER CURRENTCUBE.[Measures].[Average Contact Duration]

    AS median(descendants([Timeframes DATE].[Year - Month - Date].CurrentMember.children), ([Measures].[Int Duration]/[Measures].[DC of Contacts])),

    NON_EMPTY_BEHAVIOR = { [DC of Contacts], [Int Duration] },

    VISIBLE = 1;

    So this works. I have several dimensions, one of them being the users dimension. Now, I want this calculated member to demonstrate each individual users Average Contact Duration (which it does) but I also want another calculated measure which shows the average for the group of users.

    The dimension for the skill groups are [USERS].[Skill Group]. Is there anyway I can have the above calculation only show information for the overall group? when I pull the data into SSRS I would like to see rows of information like....

    Username, Average Contact Duration, Group Avg Contact Duration,

    John 300 425

    Simon 500 425

    Paul 475 425

    etc. Group Avg Contact Duration is the same for each user as they're in the same group.

    I may just smash my head into a wall in frustration if you can't help!!!

    Thanks in advance.

  • March 19, 2011 at 7:37 am

    #1300919

    No help so far?

    I'm so close to smashing my head into concrete! I imagine the logic is about SCOPING the dimension to only the skill group top level, performing the calculation, and then closing the scope.

    It's probably a calculated member scoping issue. The thing I would need to know is the syntax to specify only ene level of a dimension hierachy within a SCOPE statement. I can muddle through the rest.

    I assume the syntax is something like

    [Users].[Skill Group].currentmember

    but what happens when this is evaluated at a level lower?

    [Users].[Skill Group].[Frontline].xxx

    Would it still only calculate at the skill group level?

    I have currently in my issue a SAMS Unleashed SQL Server 2008 - Analysis Services book but I am finding it unclear in application of it's theory. I am happy to be pointed in the direction of something that may help?

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

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