Want to know The Truth About CPM?

25 July 2014

Stupid Programming Trick No. 18 -- Modifying the Period dimension in Planning


The genesis of this Stupid Programming Trick comes both from this post over on Network54 and because this has bugged me for, oh, ever (as long as ever = how long Planning has been a product so 13-odd years at this point).  It’s not that summary time periods in a custom time period Planning application is hard, it’s just that it’s all a bit unintuitive and I’ve never seen it documented anywhere.

For those reasons, what you will see is how to manipulate custom periods in a Planning Period dimension to get quarter totals.  I will also show how custom Period members outside of YearTotal.

Create the application

Note that I am picking Custom as my base time period.

Do I have a custom time period Planning application?  It sure looks like it.  Let’s create the application.


Glorious 12th +1

So now I have a custom Period Planning application.  How do I add quarters into my YearTotal hierarchy?

Let’s add those quarters

Don’t do this

If you want to add siblings to BegBalance and YearTotal, click on Add Child.  If you want to add children to BegBalance or YearTotal, don’t click on Add Child.  That makes sense, does it not?  No, I suppose not.

Do this

Click on the first level zero period under YearTotal and then click on Add Summary Time Period.

Nothing new to see here

Name it, define its storage property, and click on Save.

13 Periods in a quarter?

Not forever, but for now I have now added the first summary time period under YearTotal.  I will need to do this three more times.

Now add the next one

Select the next first child of Q2.  If Q1 is P1 through P3, the first child of Q2 will be P4.

Easy peasy lemon squeezy

Nothing new to see here, folks.  Move along.

Here’s Q2

Weird enough for you?  Yet it works.  And it happens again in Q3 and Q4.

Do it again

You should know the drill by now.  Click on P7 and then Add Summary Time Period.
Ta da

One more time

Q4 is almost done.

It’s done

Why does Period behave this way?  Ask the Oracle Product Manager, but I wouldn’t blame him – it has been this way since I first laid eyes on Planning 1.5.

Adding a sibling to YearTotal

So that’s the weirdness when it comes to quarters.  What about other custom time periods?  More weirdness, of course.

Note how you do not click on YearTotal and Add Sibling to add, oh, a sibling to YearTotal as that would likely be too intuitive.  

Nope, instead you click on Period and then click on the toolbar button Add Child.

Put in a value.

Ta-da, now you have a sibling to YearTotal

Add a child to P1YTD.
Now you are (or at least I am) done.

Don’t do this at home, kids

Oooo, scary

All done


What exactly can we conclude from this?
  • Adding siblings and parents in Period is just…weird
  • It ain’t intuitive, but much of life is that way
  • You can add siblings and parents so long as you follow the heretofore undocumented steps
  • Yr. obt. svt. answers pleas for help even whilst he is on vacation.  Am I nuts?  Probably.

Be seeing you.


Anonymous said...

Just thought I needed to leave a comment because it is bare. Been loving your blog for years and I have not commented much. Please keep it up, we do read and we are grateful! Periods in Planning have been bugging me for ages too. Do you use DTS? I have always avoided it due to earliest bugginess and have never found the long aliases very report friendly. Any other "Period" tips would be appreciated. We have been implementing Rolling Forecast setups (all the rage now) that are very custom. Would love to have a best practice contribution from you. - Joachim

Philip Hulsebosch said...

This blog post saved the day. Tried to create the summary time periods for many times withouth luck. Could not find the cause because the error was "Invalid Dimension Member Name". Then "Unable to load the following resource: Invalid Dimension Member Name." I can tell you, finding different names for summary time periods is difficult. Now I could use the ones we wanted to take.

@Oracle Development...Why is it possible to select the End Period at all? Maybe add a small note there. Oh, there is much to improve, once working with it.