locked
Get the previous member from the parameter RRS feed

  • Question

  • Hi,

       I got in my report a parameter who i called : week_year (for exampe : week 29 2014) and in a query mdx i wanna display the five week before this day of amount for example, i wanna a result like that :

       week 29 2014   week 28 2014 week 27 2014

    amount 40    50 60

    But by using the parameter. This is an example of my query :

    MEMBER [Measures].[Amount] AS [Amount]

    SELECT

    {

    [Measures].[Amount]

    } ON 0,

    {

    [Product].[Product]

    }

    FORM [Cube_test]

    Where StrToSet(@week_year)

    Someone have an idea pls?

    Wednesday, March 23, 2016 2:10 PM

Answers

  • Hi Adghar,

    In this scenario, since you already populate members into parameters, you just need to convert the parameter into a single member first, then we can use ParallelPeriod() function to get all 5 previous sibling members. Please refer to MDX below:

    SELECT {[Measures].[Amount]} ON 0,
    {[Product].[Product]} on 1
    FORM [Cube_test]
    Where 
    {parallelperiod([Date].[Calendar Weeks].[Calendar Week],5,StrToMember(@week_year))
    :StrToMember(@week_year)}

    Regards,

    Simon Hou
    TechNet Community Support


    Thursday, March 24, 2016 9:48 AM

All replies

  • Hi,

      Try using the range operator as shown in the below example. For computing the 5 weeks before value you can do it at SSRS level and pass the parameter as

    @week_year = '[Date].[Week Of Year].&[6]:[Date].[Week Of Year].&[10]'.

    Without seeing the complete attribute structure , it will be difficult to say how to calculate 5 weeks before. Hope this gives you an idea on how to achieve this.

    SELECT [Date].[Week Of Year].[Week Of Year] ON 1
         ,[Measures].[Internet Total Sales] ON 0
      FROM 
    	(
    	 SELECT STRTOSET('[Date].[Week Of Year].&[6]:[Date].[Week Of Year].&[10]') ON 0 FROM [MODEL]
    	)


    Best Regards Sorna

    Wednesday, March 23, 2016 8:26 PM
  • Hi Adghar,

    In this scenario, since you already populate members into parameters, you just need to convert the parameter into a single member first, then we can use ParallelPeriod() function to get all 5 previous sibling members. Please refer to MDX below:

    SELECT {[Measures].[Amount]} ON 0,
    {[Product].[Product]} on 1
    FORM [Cube_test]
    Where 
    {parallelperiod([Date].[Calendar Weeks].[Calendar Week],5,StrToMember(@week_year))
    :StrToMember(@week_year)}

    Regards,

    Simon Hou
    TechNet Community Support


    Thursday, March 24, 2016 9:48 AM