none
Month Over Month Difference across Matrix report

    Question

  • I am using SSRS 2008 on SSAS cubes . I have 3 report parameters ie., From Date, To Date(added in Query Parameters too) and then  Selected_Measure (3 measures ie., 2 Rates X & Y, amount Z: These ll pop from the exp for Selected_Measure col beside Diff col)
    The MDX is as follows
    SELECT
    {[Measures].[X],[Measures].[Y],[Measures].[Z]}
    ON COLUMNS,NON EMPTY
    {[Date].[By Year].[Month].members}*{[Offer].[By Item].[Item].members}ON ROWS
    FROM
    (select{StrToMember(@FromDate):StrToMember(@ToDate)}on 0
    from [Cube])

    I am using matrix component for dynamic month group b/w selected dates with Selected_Measure and Difference(Month over month for the selected measure)
    I cant use Sum(Field name) for Selected_Measure as it ll give the % more than 100 which is not appropriate
    Exp for Selected_Measure Column is
    =IIF(Parameters!Relative.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y.Value),Sum(Fields!Z.Value)))


    I cant use field value for previous as it ll not accept
    Exp for Difference is
    =iif(Previous(IIF(Parameters!Selected_Measure.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y),Sum(Fields!Z.Value))),"Month") is nothing,nothing,IIF(Parameters!Selected_Measure.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y),Sum(Fields!Z.Value)))-Previous(IIF(Parameters!Selected_Measure.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y),Sum(Fields!Z.Value))),"Month"))


    But infact, If previous fn accepts field value, I would have got the result.. but it doesnt
    Report design is attached for your reference.

    • Edited by Bonvivant Sunday, October 11, 2009 7:16 PM Attachment
    Sunday, October 11, 2009 7:15 PM

Answers

  • The problem is with the Delta col where its getting the difference for aggregated values which is not I am expecting. Is there anyway, I can get the appropriate dfference with field values

    Hi Bonvivant,

     

    I’ here, since the only problem is the ‘Delta’ column could you please let me know the logic of that column? From the picture you upload:

     

     

    I cannot see the logic here. For example -> how to calculate the result ‘-3.90%’ of (Dec 2008, ItemA)?

    (60.428-60)/?  Anyway, I'm still not sure your requirement .

     

    I must be missing something here. Is that the respected report I asked you to upload or it’s the wrong report you got?

     

    Anyway, I will try to surmise your requirement - I don’t want you waiting another day.  I guess ‘-3.90%’ of (Dec 2008, ItemA) is not what you want, right? It should be 60 - 62.428 = -0.428

     

    If so, why not creating a calculated member in your dataset:

    with member CAL_Delta as

    ([measures].[x], [time].[month].currentmember) - ([measures].[x], [time].[month].currentmember.prevMember)

     

    After that, set the expression for column Delta with:

    =fields! CAL_Delta.value

     not =sum(fields! CAL_Delta.value )

     

    If you still cannot get what you want, post the picture the report you got, and post a picture of what’s the report should be.


    Regards,

    Raymond


    Tuesday, October 13, 2009 2:45 AM
  • In addtion to, above post is based on the dataset:
    SELECT
    {[Measures].[X],[Measures].[Y],[Measures].[Z]}
    ON COLUMNS,NON EMPTY
    {[Date].[By Year].[Month].members}*{[Offer].[By Item].[Item].members}ON ROWS
    FROM
    (select{StrToMember(@FromDate):StrToMember(@ToDate)}on 0
    from [Cube])

    If you cross jion other dimensions, it may not wrok. Also you' better to post the datast you got, not the MDX query. It will be better for undersanding the issue

    Another similar thread:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c3788bef-b210-474a-ab72-233a3ab67b38

    -Raymond
    Tuesday, October 13, 2009 2:49 AM

All replies

  • I am using SSRS 2008 on SSAS cubes . I have 3 report parameters ie., From Date, To Date(added in Query Parameters too) and then  Selected_Measure (3 measures ie., 2 Rates X & Y, amount Z: These ll pop from the exp for Selected_Measure col beside Diff col)
    The MDX is as follows
    SELECT
    {[Measures].[X],[Measures].[Y],[Measures].[Z]}
    ON COLUMNS,NON EMPTY
    {[Date].[By Year].[Month].members}*{[Offer].[By Item].[Item].members}ON ROWS
    FROM
    (select{StrToMember(@FromDate):StrToMember(@ToDate)}on 0
    from [Cube])

    I am using matrix component for dynamic month group b/w selected dates with Selected_Measure and Difference(Month over month for the selected measure)
    I cant use Sum(Field name) for Selected_Measure as it ll give the % more than 100 which is not appropriate
    Exp for Selected_Measure Column is
    =IIF(Parameters!Relative.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y.Value),Sum(Fields!Z.Value)))


    I cant use field value for previous as it ll not accept
    Exp for Difference is
    =iif(Previous(IIF(Parameters!Selected_Measure.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y),Sum(Fields!Z.Value))),"Month") is nothing,nothing,IIF(Parameters!Selected_Measure.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y),Sum(Fields!Z.Value)))-Previous(IIF(Parameters!Selected_Measure.Value="X",sum(Fields!X.Value),IIF(Parameters!Selected_Measure.Value="Y",Sum(Fields!Y),Sum(Fields!Z.Value))),"Month"))


    But infact, If previous fn accepts field value, I would have got the result.. but it doesnt
    Report design is attached for your reference.

    The problem is with the Delta col where its getting the difference for aggregated values which is not I am expecting. Is there anyway, I can get the appropriate dfference with field values
    Sunday, October 11, 2009 7:27 PM
  • I am still having this issue pending.. If anybody could help me out in this.. I ll really appreciate them.
    Monday, October 12, 2009 1:44 PM
  • The problem is with the Delta col where its getting the difference for aggregated values which is not I am expecting. Is there anyway, I can get the appropriate dfference with field values

    Hi Bonvivant,

     

    I’ here, since the only problem is the ‘Delta’ column could you please let me know the logic of that column? From the picture you upload:

     

     

    I cannot see the logic here. For example -> how to calculate the result ‘-3.90%’ of (Dec 2008, ItemA)?

    (60.428-60)/?  Anyway, I'm still not sure your requirement .

     

    I must be missing something here. Is that the respected report I asked you to upload or it’s the wrong report you got?

     

    Anyway, I will try to surmise your requirement - I don’t want you waiting another day.  I guess ‘-3.90%’ of (Dec 2008, ItemA) is not what you want, right? It should be 60 - 62.428 = -0.428

     

    If so, why not creating a calculated member in your dataset:

    with member CAL_Delta as

    ([measures].[x], [time].[month].currentmember) - ([measures].[x], [time].[month].currentmember.prevMember)

     

    After that, set the expression for column Delta with:

    =fields! CAL_Delta.value

     not =sum(fields! CAL_Delta.value )

     

    If you still cannot get what you want, post the picture the report you got, and post a picture of what’s the report should be.


    Regards,

    Raymond


    Tuesday, October 13, 2009 2:45 AM
  • In addtion to, above post is based on the dataset:
    SELECT
    {[Measures].[X],[Measures].[Y],[Measures].[Z]}
    ON COLUMNS,NON EMPTY
    {[Date].[By Year].[Month].members}*{[Offer].[By Item].[Item].members}ON ROWS
    FROM
    (select{StrToMember(@FromDate):StrToMember(@ToDate)}on 0
    from [Cube])

    If you cross jion other dimensions, it may not wrok. Also you' better to post the datast you got, not the MDX query. It will be better for undersanding the issue

    Another similar thread:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c3788bef-b210-474a-ab72-233a3ab67b38

    -Raymond
    Tuesday, October 13, 2009 2:49 AM