locked
Help needed with parameterized MDX for YTD calculations RRS feed

  • Question

  • Hi,

          I need to calculate the YTD values for fiscal years using the parameterized MDX expression.My fiscal hierarchy looks like this [Fiscal month order] ->[fiscal Year].My requirement is that I have to use a paramater which accepts the month as a parameter and calculates the YTD value for the current year.I am available with a MDX expression which automatically finds the current month and travels back to the starting of the year and gives the YTd value for that year...

    here is the MDX expression which picks the current month

     

     

    PERIODSTODATE([Dim Invoice Date].[Fiscal Year - Month].[Fiscal Year], tail(nonemptycrossjoin(descendants([Dim Invoice Date].[Fiscal Year - Month].[Fiscal Month Order].members, 0, SELF), 1)).item(0) )

     

    But my requirement is to take the month whatever the user passes in and need to travel back to the starting of the year.

    I am currently working with this expression ,eventhough this is not the parameterized if i make it workable i want to replace this expression with a parameter.

    with

     

    member measures.PeriodsToDate1

    AS

    PeriodsToDate

     

    ([Dim Invoice Date].[Fiscal Year - Month].[Fiscal Year],strtomember("[Dim Invoice Date].[Fiscal Year - Month].[Fiscal Year].&[2009].&[Q1-1-Sep]"))

    Select

     

    {measures.PeriodsToDate1 ,[Measures].[Usage Electric] }on columns From [DM ITI Utility]

    But the error I am getting is that "The function expects a string or numeric expression for the argument.A tuple set expression was used"

     

    Monday, August 23, 2010 9:08 PM

Answers

  • u can pass the parameter on rows

    some thing like this

    {

    PARALLELPERIOD([Date].[Fiscal].[Month],12,STRTOMEMBER('[Date].[Fiscal].[Month].&[2003]&[11]')) : STRTOMEMBER('[Date].[Fiscal].[Month].&[2003]&[11]') }

    Regards

    Gokul

    • Marked as answer by Raymond-Lee Friday, September 3, 2010 4:26 AM
    Monday, August 23, 2010 10:02 PM

All replies

  • Hi,

    Try this query on formulated on adventure works

    with

     

    member

     

    measures.PeriodsToDate1

    AS

     

     

    SUM( PERIODSTODATE([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].Currentmember),[Measures].[Sales Amount])

     

     

    Select

    {measures.PeriodsToDate1,[Measures].[Sales Amount]}

    on columns,

    {

    PARALLELPERIOD([Date].[Fiscal].[Month],12,[Date].[Fiscal].[Month].&[2003]&[11]) : [Date].[Fiscal].[Month].&[2003]&[11] } on rows From [Adventure Works]

     

    Regards

     Gokul

    Monday, August 23, 2010 9:59 PM
  • u can pass the parameter on rows

    some thing like this

    {

    PARALLELPERIOD([Date].[Fiscal].[Month],12,STRTOMEMBER('[Date].[Fiscal].[Month].&[2003]&[11]')) : STRTOMEMBER('[Date].[Fiscal].[Month].&[2003]&[11]') }

    Regards

    Gokul

    • Marked as answer by Raymond-Lee Friday, September 3, 2010 4:26 AM
    Monday, August 23, 2010 10:02 PM