locked
MDX / Calculated member to get Current Quarter and Previous Quarter Revenues RRS feed

  • Question

  • Hi, i am new to SSRS reporting but have been put on a customer project for building SSRS reports. I have worked on SSAS but little weak on SSRS.

    I have an SSAS cube that has a measure called "Revenue" and a Dimension called "Time", having a hierarchy Year > Quarter > Month. My client has asked me to prepare a report which gives current quarter (can be put as a selection) and previous quarter revenues. For example, if i select the current quarter as Q1-2013, then he is also expecting the report to show Q4-2012 revenue number alongside.

    Format is as follows. I need someone to help me in understanding how this can be achieved. I am told by client manager that the cube cannot be modified by us. I need to handle this in SSRS. I am confused whether I need to add a calculated member or not, but what MDX formula is to be used.

    Year 2013
    Quarter Q1
       
    Revenue - this quarter Revenue - previous quarter
    $100,000 $90,000



    Monday, April 8, 2013 11:33 AM

Answers

  • Hi,

    This can be achieved by creating a calculated member in MDX in SSRS. It seems, always the prior quarter has to be required, use

    <Calendar Member>.CurrentMemeber.].PrevMember, <Your Measure>. This works even if you select a Month  or Year as the current member, in those cases the PrevMember is Prevoius month or Year.

    Regards

    Srini.

    • Proposed as answer by Fanny Liu Tuesday, April 9, 2013 5:14 AM
    • Marked as answer by Anisha Gaitonde Tuesday, April 9, 2013 6:07 AM
    Monday, April 8, 2013 4:26 PM

All replies

  • Hi,

    This can be achieved by creating a calculated member in MDX in SSRS. It seems, always the prior quarter has to be required, use

    <Calendar Member>.CurrentMemeber.].PrevMember, <Your Measure>. This works even if you select a Month  or Year as the current member, in those cases the PrevMember is Prevoius month or Year.

    Regards

    Srini.

    • Proposed as answer by Fanny Liu Tuesday, April 9, 2013 5:14 AM
    • Marked as answer by Anisha Gaitonde Tuesday, April 9, 2013 6:07 AM
    Monday, April 8, 2013 4:26 PM
  • Hello,

    Just as Srini post above, you can try to use PrevMember function to create a calculated member. In order to filter the report data by select quarter-year value(Q1-2013), you can add a parameter by defining a query parameter in MDX Query Designer.

    The following thread is about similar issue, please see:http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/265e486c-3986-4244-86e0-7924d70fcc73/

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Tuesday, April 9, 2013 5:28 AM
  • Hi Srini,

    I tried it as per your suggestion and tried creating a calculated member with following expression

    ([15 Time].[Quarter].PREVMEMBER, [Measures].[Revenue USD])

    Parent Hierarchy kept as "Measures"

    The output is giving " (null) "when I try to put this new calculated member into the query results. I am not sure what is wrong.

    Tuesday, April 9, 2013 5:34 AM
  • Thank you both, I have got the right values now. The format i changed it to is:

    ([15 Time].[Time Tree].PREVMEMBER, [Measures].[Revenue USD])

    which means the format is

    ([Dimension].[Hierarchy or Tree].PREVMEMBER,<Measure>)
    Tuesday, April 9, 2013 6:07 AM