MDX query parameter passing issue

• niha.736

  Ten Centuries

  Points: 1210

  More actions

  November 22, 2012 at 9:51 pm

  #271143

  Hi everyone, i have a requirement i.e if i select any value in areport filter for week example 45, then report has to show data upto 44th week only.

  for this i tried the following query. here i can only give a fixed value.

  if i change 19 value to any number(example 33) it will give me upto 32nd week.

  but what i need is , it should be dynamic.

  can anybody have any idea on this please help in this.

  select

  subset({

  [Date DIM].[ISO Week Short FI].members }, 0,19)

  on rows,

  {[Measures].[B2B sales with tax] }on columns

  from [POS OLAP Daily Material Sales]

  thanks in advance

  Niharika

• Spiff

  SSCertifiable

  Points: 7766

  More actions

  November 26, 2012 at 7:12 am

  #1562218

  Hi Niharika

  I'm assuming you are using SSRS when you say a report filter parameter?

  My MDX isn't great but what I'd do is create set using a range. So using you example instead of the SUBSET I'd have something like this:

  {[Date DIM].[ISO Week Short FI].[1] : STRTOMEMBER(@Date).PrevMember} ON ROWS

  Where your date parameter would have the form of a member (e.g. "[Date DIM].[ISO Week Short FI].[19]").

  Hope this works for you.