COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: eliza_jane on 06 Sep 2013 05:40:11 AM

Title: MDX Cumulative Total with Period Prompt Selection
Post by: eliza_jane on 06 Sep 2013 05:40:11 AM
We have a single select Period tree prompt. The chart should show the last 10 periods upto and including the chosen period. A Period could be Year, Quarter or Month. The total displayed needs to be a cumulative total.

So if the user selects year 2013. The chart should show 2010 to 2013 periods with cumulative total ranging from 2010 to 2013.

I have been trying to use Periodstodate function but with no luck.

This is against DMR, DQM.
Title: Re: MDX Cumulative Total with Period Prompt Selection
Post by: CognosPaul on 09 Sep 2013 04:25:14 AM
If the user selects 2013, shouldn't the start be 2003? Or does the data only start in 2010?

Let's take September 2013. If I understand the requirement, the chart should start at December, 2012 and show each month until September 2013. For each month there should be two measures displayed. The actual value for that month, and the cumulative total for the displayed months.

The categories of the chart is easy. We'll call that last10Periods:
lastPeriods(10,#prompt('Period','mun','','','[Cube].[Time Dim].[Time Hierarchy]')#)

Showing the cumulative total for only displayed months makes it a bit more difficult. The standard way to do a running total in OLAP is:
total([Measure] within set periodsToDate([Cube].[Time Dim].[Time Hierarchy].[All Level],currentMember([Cube].[Time Dim].[Time Hierarchy])))

Instead, let's try tweaking that a bit.


total(
  [Measure]
  within set
  except(
    [last10Periods]
    , except(
      [last10Periods]
      , lastPeriods(
        10
        , currentMember([Cube].[Time Dim].[Time Hierarchy])
      )
    )
  )
)


First it generates a set of the last ten periods from the current month in the chart, so for December it would be March - December. It then runs the except on the last10Periods set, removing all matching members. So, on the December node it would January - September. Finally it does an except on last10Periods again removing the set. So the December node would return December, February would return December;February, and so on.

This solution works very well in my SSAS cube, and the generated MDX looks very reasonable. Unfortunately I don't have a DMR environment to test this in, so let me know how it goes.
Title: Re: MDX Cumulative Total with Period Prompt Selection
Post by: eliza_jane on 10 Sep 2013 06:29:40 AM
Hi Paul, Thanks a ton for the solution...You continue to amaze me.