Passing MDX Parameters using StrtoMember and Mid function

• harvey.farrington

  Valued Member

  Points: 69

  More actions

  December 20, 2010 at 8:22 pm

  #141802

  Using SSRS 2008 R2

  I have a report parameter that allows a user to select the month (@ReportMonth) and based on that selection I want to take the year related to the month selected.

  I can pass the month into a query easily using strtomember(@ReportMonth) but am have major issues trying to use a mid function to take the year from the month parameter.

  @ReportYear has been declared and is defined as "=mid(Parameters!ReportMonth.Value,27,4)" and returns "2010" in a text box on the report.

  I am trying to get the following into the MDX query [ImdexGLTime].[Default].&[2010]

  Here is where I have got to:

  SELECT {[Measures].[Value]} ON COLUMNS,

  {strtomember("[ImdexGLTime].[Default].&[" & Parameters!ReportYear.Value & "]")} ON ROWS

  FROM [IMDEXGL]

  WHERE ([Classification].[Default].&[CONSOL],

  [ImdexGLCostCentres].[Default].&[Group], [ImdexGLTimePerspective].[Default].&[BASE],[ImdexGLVersions].[Default].&[ACT],[Reporting Currency].[Default].&[AUD],[ImdexGLAccounts].[Default].&[1])

  This generates an error:

  "The syntax for & (before Parameters!...) is incorrect"

  Any help appreciated.:-)

• Tom Vergote

  SSC Rookie

  Points: 30

  More actions

  January 5, 2011 at 1:53 am

  #1269341

  use + signs