locked
Parameter value in MDX RRS feed

  • Question

  • I would like to get value of a parameter in MDX query. My query is as follows  

     

    With

    Member Measures.p AS

    StrToMember(@ReportingDateMonth )   -- How to get value of this parameter

     SELECT {Measures.p.item(0) ,  [Measures].[XXX] } ON COLUMNS,

     NON EMPTY {([Date].[Month].[Month].ALLMEMBERS ) } ON ROWS

    FROM (

         SELECT ( STRTOSET(@ReportingDateMonth, CONSTRAINED) )

        ON COLUMNS

    FROM [Default])  

     

    In this MDX query I pass report parameter ReportingDateMonth

     

    Any ideas as to how this can be achieved in the MDX statement.

     


    Thanks and Regards,

    Sunil Nair

    • Edited by SAvis Tuesday, October 6, 2009 4:27 PM signature
    Tuesday, October 6, 2009 4:26 PM

Answers

  • Hi,

     

    I’m not sure I understand your question correctly, but from your MDX query, if seems you want to display the parameter you selected.

     

    ·         With

    Member Measures.p AS

    StrToMember(@ReportingDateMonth ).membervalue  

    -- since you use StrToMember, I think it’s a single member

     SELECT {Measures.p ,  [Measures].[XXX] } ON COLUMNS,

     NON EMPTY {([Date].[Month].[Month].ALLMEMBERS ) } ON ROWS

    FROM (

         SELECT ( STRTOSET(@ReportingDateMonth, CONSTRAINED) )

        ON COLUMNS

    FROM [Default]) 

     

    But it will just add a duplicate column in dataset, I think I must be missing something here. could you give a example what’s the expected result?

     

    Also, if you just want to know how to use parameter with MDX query, then you can refer to:

    http://msdn.microsoft.com/en-us/library/ms155824.aspx

    the parameter will created by BIDS automatically.

     

    Hope this helps.

    Raymond

    • Proposed as answer by Raymond-Lee Friday, October 16, 2009 1:34 AM
    • Marked as answer by Jerry Nee Friday, October 16, 2009 7:35 AM
    Thursday, October 8, 2009 3:24 AM

All replies

  • I'm not 100% sure what you are asking here.  Are you trying to use the value of the parameter in this query?  If so, it will all depend on the format of the value of the @ReportingDateMonth parameter and how it maps to the member name in the cube.  If the parameter is not sourced off the same SSAS data, then you will likely have to use an expression to translate it into the same format that exists in SSAS.

    -Sean
    Program Manager, SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/seanboon http://www.twitter.com/ssrs
    Tuesday, October 6, 2009 5:33 PM
  • Hi,

     

    I’m not sure I understand your question correctly, but from your MDX query, if seems you want to display the parameter you selected.

     

    ·         With

    Member Measures.p AS

    StrToMember(@ReportingDateMonth ).membervalue  

    -- since you use StrToMember, I think it’s a single member

     SELECT {Measures.p ,  [Measures].[XXX] } ON COLUMNS,

     NON EMPTY {([Date].[Month].[Month].ALLMEMBERS ) } ON ROWS

    FROM (

         SELECT ( STRTOSET(@ReportingDateMonth, CONSTRAINED) )

        ON COLUMNS

    FROM [Default]) 

     

    But it will just add a duplicate column in dataset, I think I must be missing something here. could you give a example what’s the expected result?

     

    Also, if you just want to know how to use parameter with MDX query, then you can refer to:

    http://msdn.microsoft.com/en-us/library/ms155824.aspx

    the parameter will created by BIDS automatically.

     

    Hope this helps.

    Raymond

    • Proposed as answer by Raymond-Lee Friday, October 16, 2009 1:34 AM
    • Marked as answer by Jerry Nee Friday, October 16, 2009 7:35 AM
    Thursday, October 8, 2009 3:24 AM