Passing parameters to MDX data-set

• Duncan Lawrence

  SSCrazy

  Points: 2047

  More actions

  October 1, 2015 at 3:33 am

  #399125

  Hi, I have an MDX data-set query as follows: -

  Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts]

  Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit])

  Select {[Measures].[Measure1]

  ,[Measures].[Measure2]

  ,[Measures].[Measure3]

  ,[Measures].[Measure4]

  } On Columns

  ,Non Empty LastPeriods(@LagMonths,[Time].[Fiscal Time].currentMember) On Rows

  FROM ( SELECT ( STRTOSET(@TenancyTenure, CONSTRAINED) ) ON COLUMNS

  FROM [CBIHousing])

  Where(

  Iif(StrToSet(@TenancyTenure, Constrained).Count = 1, StrToSet(@TenancyTenure,Constrained), [Tenancy].[Tenancy Tenure].currentmember)

  )

  ;

  There are two parameters; the value of lag passed to the LastPeriods function, and tenancy types passed in the where clause

  I have a third parameter I would like to use called FirstDate and this I would like to pass to the LastPeriods function as the second argument

  FirstDate is a value from my Time dimension, something like [Time].[Fiscal Time].[Sep. 15]

  When editing the query to: -

  Member [Measures].[Measure3] As [Measures].[Value Rent Period Receipts]

  Member [Measures].[Measure4] As (([Measures].[Value Rent Period Receipts]*-1)/[Measures].[Value Rent Period Debit])

  Select {[Measures].[Measure1]

  ,[Measures].[Measure2]

  ,[Measures].[Measure3]

  ,[Measures].[Measure4]

  } On Columns

  ,Non Empty LastPeriods(@LagMonths,@FirstDate) On Rows

  FROM ( SELECT ( STRTOSET(@TenancyTenure, CONSTRAINED) ) ON COLUMNS

  FROM [CBIHousing])

  Where(

  Iif(StrToSet(@TenancyTenure, Constrained).Count = 1, StrToSet(@TenancyTenure,Constrained), [Tenancy].[Tenancy Tenure].currentmember)

  )

  ;

  The code errors. I have tried all sorts of formats such as LastPeriods(@LagMonths,[@FirstDate]) and LastPeriods(@LagMonths,[Time].[Fiscal Time].[@FirstDate]), but nothing works

  Has anyone any ideas?

  Thank you for your attention

  Regards

  Duncan

  ---

  All the best,

  Duncan