Planning expressions in Calc Mgr – Part I 2


Don’t give me that expression!!! I’m not talking about that expression. Well, I cannot blame you I had the same expression when I first heard the term “Planning expression, Ah what?”

Feb PBCS update did mention this as a one-liner

Ability to Use Planning Formula Expressions in Calculation Manager

But what is the Planning expression? Well, if I tell you about [openinputvalueblock], [closeinputblock] that might ring a bell. Still didn’t?

Ok then this PBCS documentation might help. It is called as Formula Expression, for now, treat them as the same.

From Feb’17 release, these expressions are now available in Calc Manager, not only those there are some awesome expressions available too. I started writing this in March and it was there in my drafts till now. 🙁

Let’s look at some of them shall we.

I recently used some of these in a project of mine and will talk about those ones.

The first request was a basic budget seeding rule. The client will decide a month where they want to start the budget and Jan to that Month actuals must be copied to Budget and from next month to Dec Forecast must be copied to budget.

Now you can get this done using substitution variables, well I like that approach, however, I wanted to use the start and end month of scenarios 😉

Here is what I wrote in Calc Manager

As you can see in the Script tab, Calc Manager already pulled the End Month of Actual and Start Month of Forecast!!!

Similar way you can use getStartYear and getEndYear to get the years. Here is the syntax that you can use in scripts

  • [[getStartYear(“ScenarioName”)]]
  • [[getEndYear(“ScenarioName”)]]
  • [[getStartMonth(“ScenarioName”)]]
  • [[getEndMonth(“ScenarioName “)]]

Isn’t that neat?

Smartlists

I think many of us might have unknowingly used this one, calling the Smartlist label in calc scripts, well it is now officially available 😉

[[SLName.entryname]]

Cross Reference

Well you don’t have to type the default members anymore if you are doing a default cross dime

  • [[CrossRef(accountName)]]
  • [[CrossRef(accountName, “prefix”)]]
  • [[CrossRef(accountName, “prefix”, true)]]

Refer to the CrossRef section of Formula Expressions link above.

Numbers

You can get the number of periods and number of years in the application if you want to. I don’t know they might come in handy.

  • [[NumberOfPeriodsInYear]]
  • [[NumberOfYears]]

I’ll talk about how to use User variable in calc manager rule (without the hack) in next post.

Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.


Leave a Reply

2 thoughts on “Planning expressions in Calc Mgr – Part I

  • Chris Rothermel

    I stumbled across these commands reading the calc manager documentation and immediately put them in my code. I love them as well. So glad you posted this and noted they were available in February 2017 — when I found this functionality I was slightly embarrassed that I did not know about it sooner and wondered how long they’ve been around. I’m able to have fewer substitution variables with commands like [[getStartYear(“ScenarioName”)]] and that makes application development and maintenance easier. Thanks for the post. Hope you’re doing well.