EPBCS – Improved Smart List Lookup in Calculations

EPBCS, PBCS, SmartLists 4 Minutes

UPDATE  7/31/19

I discovered this technique while I was working with the newly released workforce app.  Unfortunately, the client I was working with did not understand the value of the OOB content and I was asked to customize the application.  Over the last few years, this approach has changed and OOB is my goto approach and I enhance only if needed.  The point of this article though is not really about workforce calculations but how Smart Lists can be used more effectively in calculations.

ORIGINAL ARTICLE

Over the last month, I had to customize almost every feature in the EPBCS workforce planning application for a client. I have much to write about that experience but this one is my favorite. Do you want your Smart Lists to be dynamic but do not want to write long if/then statements for every possible value in the lists? Well, read on…..

When I first saw the feature to build a Smart List from metadata I started to get excited about the potential to use that in a calculation. After going through the Workforce app in detail I have seen the light. If you are not familiar with that module yet it basically has three plan types. The first two BSO cubes are for the plan and another one is for the rates. When you create a TBH it performs a lookup in the rate cube to get default values. Here is a simplified example of how the functionality works so you can use it in your applications as well.

I built a 2 database application one called FINPLAN and the other called RATES.

Here is the dimensionality of the two plan types.

FINPLAN

RATES

The idea is that a user can enter in information in the Finplan cube and the system will do a look up in the rate cube based on a region they have selected in the Smart List. Here is a quick preview of the form.

Creating the Smart List

Once the dimensionality is set up correctly you need to set up a Smart List that is based on your application metadata. I created a Smart List named REGIONS and configured it to point to the level 0 members of the regions dimension.

This creates a dynamic list based on the application metadata. Notice on the Entries tab the id for the members in the list. If you know the backend of planning this is the OBJECT_ID from the HSP_OBJECT table, planning is going to use this identifier in your calc scripts to identify the member in Essbase.

After the list is created I assign it to the account Opportunity_Region in the FINPLAN Cube.

RATES

I then built a web form against the RATES database to store the rates at the No Entity member and at the Regions.

I am just storing a monthly rate by region in this cube.

IN ACTION

Here is a quick view of it working:

CALCULATION

This is where the magic happens. Basically, we want to pull a value from the rate cube using the value assigned to the Smart List in the planning cube.

Here is the calculation:

Not as pretty version :

FIX (FY18,Plan,Working,USD,"No Entity",@RELATIVE("OPP",0))
/*Define a variable to assign the Smart List value to*/
VAR SLVal;

/*Opportunity Overhead cost, lookup in Rate cube*/
"OVERHEAD_COST"(
/*Assign Smart List Value*/
SLVal="OPP_REGION"->"BegBalance";
IF( NOT (@ISMBR ("BegBalance") ) ) /*Only Run on Months*/
IF( (SLVAL <> #MISSING) AND ("VOLUME"->"BegBalance" <>#MISSING)) /*Check if there is a Smart List Value and a volume*/
@XREF(_RATES_,"OVERHEAD_RATE","No Entity",@CONCATENATE("HSP_ID_", @Name(@hspnumtostring(SLVal)))); /*apply Rate cube value*/
ENDIF
ENDIF

)

ENDFIX

THE MAGIC

If you look closer at the Xref formula you will notice@CONCATENATE(“HSP_ID_”, @Name(@hspnumtostring(SLVal))))

Lets break this down.

  • The SLVal was assigned from the Smart List value and it is populated with the OBJECT_ID of the member in PBCS.
  • The CDF hspnumtostring converts the numeric id to a string
  • The @Name function really makes sure its a string. Probably redundant.
  • Then its concatenated with “HSP_ID_”

Using the NY member as a value it would create a member that looks like this.

HSP_ID_50097

Behind the scenes Planning then can use that definition to translate it into a member that Essbase can read. I think with an alias table in Essbase.

Pretty cool!!

NOTES

The Xref formula uses txt values but if you need to convert this into an Essbase member then wrap the formula around an @MEMBER like this:

@MEMBER(@CONCATENATE(“HSP_ID_”, @Name(@hspnumtostring(SLVal)))))

You will only be able to use this in calculations and not in a fix statement since you need the data value of the Smart List to drive the formula. I tried to get it to work in a member formula and was not successful, but I only spent a few minutes on it.  I finally got this to work in a member formula.  You have to wrap it in an if statement to check for the smartlist value for it to work. 

IF("SmartlistAccount"->"BegBalance"->"dim1" <> #missing)
"Targetaccount"->"dim1member"->"dim2member"->"dim3member"->"dim4member"->@MEMBER(@CONCATENATE("HSP_ID_",@HspNumToString("SmartlistAccount"->"BegBalance"->"dim1")));
ENDIF

CLOSING

This is a pretty nice enhancement that is a key to understand if you need to troubleshoot or enhance Workforce in EPBCS. Plus you can utilize it in your own applications to make your scripts and applications more dynamic and focused.

Here is the LCM if you need it.

Published by Anthony Manfredi

Published

29 thoughts on “EPBCS – Improved Smart List Lookup in Calculations”

  2. Very nice example Anthony. This technique can even be used in On Premise. It just doesn’t have the easy way to populate the Smartlist with Members

    Reply

  4. Hey Anthony, thanks for the well-explained write-up. It was exactly what I needed, I never would’ve been able to guess the right syntax for the XREF.

    Is this HSP_ID_* thing properly documented? I’ve seen similar tricks in other blogs, saying it was undocumented. What’s the point then in having dinamically populated smartlists if we’re missing the 2nd part of the equation?

    Again, many many thanks for this.

    Reply

    1. Interesting question. The Smart list creation documented in the blog is a feature of the software and that is well documented. The XREF is from rules that Oracle is releasing as part of EPBCS. So while they may not be documented this is coming from code released from Oracle and code that they support. I feel fairly comfortable about using it, but ultimately its up to you.

      Reply

  5. Thank you Anthony, its really helpful. but can you tell me how to take smart list member name instead of object ID. i have to use smart list member to store data. here smart list member is the member of other dimension.

    Reply

  6. Thanks Anthony for sharing this information. A client uses HP 11.1.2.4 on premise and we are attempting to use the formula above to incorporate a member that is selectable in a smart list to map it to the actual member. For example we would like account 100, available on a smart list to be mapped to the actual account 100 in the accounts hierarchy. Because we are on-premise I don’t think we are able to use the HSP_ID however we are trying to use the @hspnumstring to pull the Smartlist ID, which is identical to the account number in our calculation.

    What we have come up with is the following:

    @Xwrite(“100”,@loopback,@hspnumtostring(Select_Subsidiary));

    Select_Subsidiary being the smartlist. For testing purposes we have selected smart list ID 200.

    When we run this calculation it fails and we always receive the following message:

    Error executing formula for [Base] (line 18): invalid member name [-2147483648] in function [@_XWRITE]

    Also regardless of what smart list ID we put in into the xwrite function we always encounter the invalid member name [-2147483648].

    Would you have happened to see this in the past and know how to handle this in an on prem environment?

    Thank you.

    Reply

    1. Unfortunately your method will not work because essbase stores the ObjectID and not the actual member name. Check your outline on-prem, does it have an alias table that has the HSP_ format?

      Reply

      1. Hi Anthony,

        Thanks for getting back to me so quickly. I checked the alias tables available in easconsole in the outline under the properties tab and the only one that I see is the Default table.

        Do you know of another way we could possible work around to this?

        Thanks so much for your help.

  7. Hi Anthony,

    Thanks for your code which will helps us to fix when we have the single smarlist, In my scenario i have 3 different smartlists which is in columns as your design above, am not able to copy those three smartlist intersections to the members.

    Thanks
    Sandeep

    Reply

  8. Thanks for sharing this! I’m attempting to use this currently and am finding that the numeric value being stored for my hierarchy-driven Smart List ID is not the expected 5 digit (e.g., HSP_ID_50097) but rather a much longer number (e.g., 4304827013811210). As a result, my lookup for the member does not seem to work. Any thoughts on why I’m seeing such a different ID number?

    Reply

      1. Thanks for the reply. My retrieve is at level 0. I have a calculation with a FIX on a few intersections of level 0 members that is taking the value from the Smart List account and populating it into another account so I can see the number. If I change the Smart List selection and run the calc the associated number changes but is consistent (i.e., selection 1 always returns the same number). I have tried to just populate the value based on @MEMBER(“HSP_ID_4304827013811210”) but that does not work as it seems to not be the Object ID. I am hoping they didn’t change how they ID Smart List items to use something other than the Object ID when built off dimension members. I’ll continue to play around with it and report back if I discover anything but am definitely open to suggestions/theories.

      1. He never followed up with me. I am still using this technique with some current clients. Depending on dimensionality the smart view values can be very large. Let me know if you need help.

      2. Sorry meant to update this a while back. I never ended up getting it to work in that specific application. I have been able to get it to work in other applications though. I’m not sure what in particular made it not work in that other instance. It was basing the smart list on a dimension in an ASO only hierarchy FWIW, but I did not think that should make a difference on the HSP_ID.

      3. I was just reminded that I needed to follow up on this again. I actually did finally figure out what my issue was. I had done a copy-and-paste from this webpage and it included “smart” quotation marks. If you do a simple text paste so that it uses plain quotations instead of “smart” quotation marks it works fine (or if you just type it out yourself). Thanks again Anthony!

  10. Hi Anthony,

    Thanks for this beautiful document. I have a scenario in my requirement where I need to auto populate Smartlist by script.Could you please help on this please.

    Let me give you an example –

    I am having one smartlist in Project cube where I will have all the employee list. In my Workforce, based on certain criteria I will have different flag for each employee and I need to auto fill that smartlist with employee name based on the flag value. Basically its the reverse of what you wrote here.

    Could you please help on that

    Reply

  11. Hey Anthony,

    I hope you’re doing great.

    I am facing some issues using smartlists in my script.

    I created a smartlist called “REGIONS” where i attached all my lvl 0 countries.

    I have a dimension for products and I would like to calculate the sales for each product just by entering the qty of products solded and by choosing the country it will automatically get multiplied by the price of the product so i can have my calculated revenue.

    Thanks in advance.

    Reply

  12. Hi Anthony. I’ve been using the logic for a while now – not sure where I originally found it – maybe here. It’s always worked great for me. However, I just realized it is not working for when the SmartList value is a Parent member. The SmartList must contain a Level 0 member for this code to work. Is that what you have found as well? Thanks. Jason

    Reply

  13. Hi Anthony., I want to store the count of smartlist tagged values , example “Risky ProJet” values tagged to how many projects and “None risky projects” tagged to how many projects , Can you please help in getting this requirement.

    Reply

Leave a Reply