HOWTO: Select a time range that is dynamic (updates over time for a rolling forecast)

  • Article

There are 2 kinds of ranges that the PPS Planning Member Picker supports: 1) From the selected member to an offset from current period (or vice versa). 2) Or from an offset from the current period to another offset from the current period. (It's also possible to simply use an offset from the current period without a range specified). When using the member picker with Time look for the special dropdown that appears - hopefully this isn't surprising - called...."Time"!

Report5-MemberPickerTimeHiLite

Very commonly I see demos and questions about seeing actuals for the past and forecast for the future. In fact I blogged about how to do this asymmetric crossjoin previously. Now I can go back to that sample and update it so that the matrix could be used in a rolling forecast scenario. (Where the form is authored once, but used in a recurring assignment cycle...)

As a reminder this was the last selection for time:

Now we will add a couple dynamic ranges for showing the last quarter of actuals, and the next 3 quarters of forecast. Here's the next 3 quarters as an example:

 Report5-next3quarters

So now if you recall I have actual/forecast and last 3 months/next 9 months and defining intersections is trivial. And my report looks like this (current period is still set to ASH default):

Report5

I'll leave it as an exercise for the reader to experiment with the other 2 options related to current period. Looking at the previews of the members they will insert should now be pretty self explanatory...

 

What about custom MDX, this current period thing doesn't exist in AS/OLAP?

Commonly current period functionality is handled in OLAP by using a calculated member or by setting the default member in a hierarchy. But PPS has cycle's which inherit the current period when they are instantiated. (So that, of course, data entry form's don't jump during an assignment) To do this PPS will replace the text "$CurrentPeriod$" in the query before sending the MDX to AS. So custom MDX authors can simply use the same text in place of a specific member reference. (Probably I'll have more examples in later posts, or you can experiment with view MDX to see the MDX that the add-in generates to see $CurrentPeriod$ used...)

 

Playing in the land of Peter Eb. while writing this post...Tiki God by Presidents of the United States of America from Presidents of the United States of America: II