none
Complex Calculated Measure Issue RRS feed

  • Question

  • Hello,

    I have to create a report (using reporting services 2005) which shows the daily rolling value of an analysis services 2005 calculated measure (e.g. for each date between May 1st to 31st have a column which shows the value of the calculated measure for the last 30 days -> First Col - calculated measure value from April 2nd - May 1st, 2nd col - calculated measure value April 3rd - May 2nd,...,last col - calculated measure value from May 2nd - May 31st).

    Initially I thought I could use time based reference methods (Lag, LastPeriods etc.) to implement this. Unfortunately these methods appear to only help me if the value of the calculation for the thirty day period was an avg/sum/other aggregation of the daily value (the calculated measure is a score based on how a transaction count is relative to a mean and standard deviation over the specified period)

    What I really need to do is to be able to execute an mdx statement for each day (e.g. if we are talking about May, 31 MDX statements need to be executed), then roll these up into one result set for reporting services. What would be the best way to do this?

    Any advice that could be provided would be much appreciated.

    thanks in advance,
    Gareth Collins

    Tuesday, June 24, 2008 7:12 PM

All replies

  •  

    Hey Gareth,

     

    slow down. Most probably you need only one, a pretty complex MDX, to get the data you want. MDX, like T-SQL, is a set oriented language, so you can work with sets in one go, no need to iterate in separate commands.

     

    Well, describe your math to us. And give some examples, for a day or two. Maybe we can help you, if it's not too complex for us :-)

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

    Tuesday, June 24, 2008 10:52 PM
    Answerer