locked
how to convert mdx to calc member. RRS feed

  • Question

  • I have the following MDX.

    How do I store it as a Calculated Member?

     SELECT
          NON EMPTY { [Measures].[RowCnt] }                                                                                            ON COLUMNS
        , NON EMPTY { ([Date].[Calendar Date].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME    ON ROWS
     
     FROM (
                SELECT
                        ( { [Status].[Status].&[Hold] } ) ON COLUMNS
     
                FROM [MyCube]
            )
    WHERE ( [Status].[Status].&[Hold] )


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Monday, August 12, 2013 1:37 AM

Answers

  • Prav,  the calc member will not take SELECT at all for me.  How you've get by with using SELECT in calc member?

    after banging my head to the wall several times, this snippet come out. :) and is what i needed.

    sum(
          [RO Status Current Date Sk].[Calendar Date].CurrentMember * { [Status].[Status].&[CANCELED] }
        , [Measures].[ROStatusCurrent_ALL]
    )


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    • Marked as answer by light_wt Monday, August 12, 2013 2:41 AM
    Monday, August 12, 2013 2:41 AM

All replies

  • Hi

    Try this, I haven't test but you need to get the same results in both measures

    WITH MEMBER [Measures].[CalculatedRowCnt] AS
     ( [Status].[Status].&[Hold] , [Measures].[RowCnt])
    SELECT 
           { [Measures].[RowCnt],[Measures].[CalculatedRowCnt] }  ON COLUMNS
         , NON EMPTY { [Date].[Calendar Date].[Month].Members  } 
           DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME    ON ROWS 
      FROM ( 
                SELECT 
                        { [Status].[Status].&[Hold] }  ON COLUMNS 
                  FROM [MyCube]
             ) 
    
    Let me know your results

    Prav

    Monday, August 12, 2013 2:26 AM
  • Prav,  the calc member will not take SELECT at all for me.  How you've get by with using SELECT in calc member?

    after banging my head to the wall several times, this snippet come out. :) and is what i needed.

    sum(
          [RO Status Current Date Sk].[Calendar Date].CurrentMember * { [Status].[Status].&[CANCELED] }
        , [Measures].[ROStatusCurrent_ALL]
    )


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    • Marked as answer by light_wt Monday, August 12, 2013 2:41 AM
    Monday, August 12, 2013 2:41 AM
  • Hi

    Good to know that you got it. However please go through your question once

    1. Have you provided all the information related to your calculations

    2. We don't have any info on using [Measures].[ROStatusCurrent_ALL]

    3. We also don't have info on [Measures].[RowCnt] as your calculated measure so it looks like a base measure to me.

    4. I think, the original question is missing scenario of calculation


    Prav

    Monday, August 12, 2013 3:22 AM