Simple MDX Question

  • Hi Folks,

    My query is like below:

    SELECT

    {STRTOSET('[Dim Time].[Week Of Year].&[49]') ,

    STRTOSET('[Dim Time].[Week Of Year].&[48]')} ON Columns,

    [Dim Stores].[Store Name].AllMembers on rows

    FROM

    [HCRetailSales]

    When I copy and paste it to the SSRS query designer

    SELECT

    {STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,

    STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')} ON Columns,

    {[Dim Stores].[Store Name].allmembers } on rows

    FROM

    [HCRetailSales]

    Then I retrieve nothing. I have also attached screenshots for both queries

    Any Idea?

  • Firstly, Reporting Services wants to have measures on columns and all other information required to be shown on the report needs to be rows. This is part of the underlying design of the way SSRS deals with all datasets - all report grouping, pivoting, summation etc is down during the reporting rendering.

    So, try changing your MDX to something like

    SELECT ([Measures].[Your Measure Name Goes Here] } ON Columns,

    {STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,

    STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')}

    *

    {[Dim Stores].[Store Name].allmembers } on rows

    FROM

    [HCRetailSales]

  • Thank you very much for your reply.

    I have tried to apply your code but I got the following error:

    SELECT [[Measures].[Satis Miktari] ON Columns,

    {STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,

    STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')}

    *

    {[Dim Stores].[Store Name].allmembers } on rows

    FROM

    [HCRetailSales]

    ---------------------------------------

    Executing the query ...

    Query (1, 8) The dimension '[[Measures]' was not found in the cube when the string, [[Measures].[Satis Miktari], was parsed.

    Execution complete

  • Try the following

    SELECT { [Measures].[Satis Miktari] } ON Columns,

    {STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater]') ,

    STRTOSET('[Dim Time].[Week Of Year].&[@MyWeekParamater].PrevMember')}

    *

    {[Dim Stores].[Store Name].allmembers } on rows

    FROM

    [HCRetailSales]

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

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