none
Order and Restrict a Parameter in SSRS RRS feed

  • Question

  • Hi all,

    I have a simple parameter in SSRS @quarter with years and quarters from 1995 to 2020. I want to order it descenting and i want to restrict it for the years 2018 - 2019. The MDX code for the parameter is the following:

    WITH MEMBER

    [Measures].[ParameterCaption] AS [calender_quarter].[Quartal].CURRENTMEMBER.MEMBER_CAPTION MEMBER 

    [Measures].[ParameterValue] AS [calender_quarter].[Quarter].CURRENTMEMBER.UNIQUENAME MEMBER

    [Measures].[ParameterLevel] AS [calender_quarter].[Quarter].CURRENTMEMBER.LEVEL.ORDINAL

     

    SELECT

     

    {

    [Measures].[ParameterCaption],

    [Measures].[ParameterValue], 

    [Measures].[ParameterLevel]

    }

     

    ON COLUMNS , [calender_quarter].[Quarter].ALLMEMBERS ON ROWS

    FROM [Model]

    I am not very familiar with MDX. Does someone know how to adjust the code? Or is it may possible to realize it in SSRS? I didnt see a way to do it in SSRS.

    Many thanks in advance

    Pokewiki :)

    Saturday, December 14, 2019 9:43 AM

All replies

  • Hi Pokewiki,

    The MDX query could be modified in such a way like 

    WITH MEMBER
    
    [Measures].[ParameterCaption] AS [calender_quarter].[Quartal].CURRENTMEMBER.MEMBER_CAPTION MEMBER 
    
    [Measures].[ParameterValue] AS [calender_quarter].[Quarter].CURRENTMEMBER.UNIQUENAME MEMBER
    
    [Measures].[ParameterLevel] AS [calender_quarter].[Quarter].CURRENTMEMBER.LEVEL.ORDINAL
    
     
    
    SELECT
     
    
    {
    
    [Measures].[ParameterCaption],
    
    [Measures].[ParameterValue], 
    
    [Measures].[ParameterLevel]
    
    }
    
     
    
    ON COLUMNS , 
    ORDER
    (
        
        [calender_quarter].[Quarter].ALLMEMBERS,
        [calender_quarter].[Quarter].ALLMEMBERS,
        ASC
    
    ) ON ROWS
    FROM [Model]
    WHERE ([calender_year].[Year].[2018]:[calender_year].[Year].[2019])

    You could try this , but I am not that sure since I don't understand you report quite clear.

    I also recommend you that you could do something from SSRS side, such as, give order to particular table by adding sorting property for that tablix and filter property to dataset for restrict time period. You could find those in tablix/dataset property.

    Hope this helps.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, December 16, 2019 3:40 AM