Hierarchy-driven Smart Lists and using them in Calc scripts 9

You might be already familiar with this term Hierarchy-driven Smart Lists (Or Smart List from members). I talked about it in my groovy series and even talked about how it can be replicated on an on-premises Hyperion Planning application here.

When I was doing the Groovy series, many people told me that “It is a great feature”. I’m already in love with this one. The main question I heard was “Well it is great, however now how’ll we use this in Calc scripts?”

The people who asked that question, do have a point. Well, you already know what I’m going to say, if not, please bear with me for a while.

When I create a Smart List and assign it to a member I can use it in the calc in two different ways.

Let’s look at an example.

I’m going to create a Smart List for months

1. Use the ID of the Smart List and perform some operation (If(StartMonth == 1)
2. Use Smart List.Entry notation (Sl_Month.Jan)

Now the trouble with Hierarchy-driven Smart List is that the ID is auto-generated and it changes after you perform an Essbase artifact import. I still have to go back and check this, but I’m pretty sure it does as this is a database generated number.

So I cannot use IDs, and I don’t want to write a lot of IF conditions? What if you are generating a Smart List from a hierarchy that changes all the time, how can I reference those as members in a calc script?

I’m going to spill the beans about a syntax that you can use.

The example that I’m going to use is a typical Workforce scenario. I got some new hires and I need to spread their annual salary based on their start month.

StartMonth is a Smart List created from members.

1. New Hire 1 is going to start in April
2. New Hire 2 is going to start in July

That’s it, just 1 line of code!!!!

HSP_ID_ is the key word. All you’ve to do is grab the ID (the one which Essbase stores) and concatenate with HSP_ID_ and use that in the script.

Now “How did I come to know about this?”,

I’m blessed to be a part of a smaller group which exchanges some ideas (read crazy) and this particular one involves (happened in Nov last year)

1. My older brother from completely different set of parents, Cameron Lackpour
2. The guy from down under, Pete Nitschke

I don’t recollect what our topic was (we just move from one to another 😉 ) and Pete came up with this code and for the life of me, I couldn’t make it work. (I was testing this on an On-Premises version, then on PBCS with a regular Smart List)

I completely forgot about this until yesterday. (I had to create the above-given Workforce calc and I didn’t want to do it the regular way ;)) Also I forgot the part that I was trying with a normal Smart List earlier.

Now you are curious about how this one work, aren’t you? 🙂

Let’s look at the plumbing work. Pete (and me too) had a strong feel that it could be a hidden alias table. How can it be proved now.

I did talk about moving PBCS to an On-premises Essbase application here. I created an application and database and looked at the outline properties.

What do I see there 🙂

That’s our hidden alias table, SLAliases.

Here are the entries from the Smart list.

Set SLAliases as the active alias table and what do we see!!!

Surprisingly it adds an alias for the members that are not involved in the SL creation.

Well, there you go a new way to call Hierarchy-driven smart list members in calc scripts. Something which can be replicated in on-premises along with Hierarchy-driven Smart Lists.