locked
need help with create member currentcube RRS feed

  • Question

  • I have a user requesting a new cube member to display total employees but when slcing by qtr they want it to be the avg over the qtr to display for that member else use the total employee?

    I have this in te cube calc tab

    CREATE MEMBER CURRENTCUBE.[Measures].[Total Emp]
     AS 
    [Measures].[Total FullTime Emp]+
    [Measures].[Total PartTime Emp]+
    [Measures].[Total Seasonal Emp]
    ,
    FORMAT_STRING = "#,##0", 
    VISIBLE = 1;

    Currently I'm showing

    I know I have to add the Time Dim

    [Time].[Hierarchy].[Month].members

    Can SOme help on the code?
    Friday, April 24, 2015 2:59 AM

Answers

  • Hi Anonymous123sdaf,

    According to your description, you want to show average number of employees when slicing on quarter level. Right?

    In MDX we can use scope() statement to assign different calculation to a limit scoped MDX. Please use the MDX query below:

    SCOPE([Measures].[Total Emp]);
        SCOPE([Date].[Calendar].[Calendar Quarter].members);
             This=Avg( 
                   Descendants( 
                     [Date].[Calendar].currentmember,
                     [Date].[Calendar].[Calendar Quarter]
                   ), Measures.[Total Emp] 
                  );
        END SCOPE;
    END SCOPE;
    
    

    Reference:
    SCOPE Statement (MDX)

    Best Regards,


    Simon Hou
    TechNet Community Support


    Monday, April 27, 2015 10:13 AM