Transfer of Smart List Values to Members in Calculation Manager

 

This blog features instructions for retrieving and transforming a Smart List entry (stored against a dimension member) into a member name to be used within a calculation script. This member name is in a valid format for use with cross dimensional operators.

This functionality is particularly useful for Staff Costing models. It can be used to retrieve a grade or payscale point from a staff member and lookup its relevant base salary.

I came across this trick when writing a calculation script that would pass through each employee in a Staff Cost model and retrieve their pay scale point – a Smart List value – and then transform this into a member in order to retrieve the corresponding base pay associated to that pay scale point.

Pay scale points are members in my ‘Employee’ dimension. Upon configuration of the pay scale Smart List, I selected ‘Create from Members’. This is compulsory in order for the trick to work! I have attached this Smart List to a member in the Metric dimension.

The tricky part was getting PBCS to recognise the Smart List value as a member rather than a number - since Smart Lists are stored as a numeric ID in the back-end.

The following code will demonstrate how to convert the ‘Smart List’ value in “SLMember” into the dimension member it was driven from and use the member in a calculation to retrieve data stored against the member:

FIX(...)

"Base"(

  VAR VarSL;

     IF(...)

     VarSL = "SLMember";

        IF (“SLMember” <> #Missing )

     “Salary” = “Base Salary”->@MEMBER(@CONCATENATE("HSP_ID_",@Name(@HspNumtoString(VarSL)))+“Allowances”;

      ENDIF

)

Where SLMember is the dimension member that the Smart List is associated with and Smart List values are input to.

Here is a breakdown into each component and its purpose:

@HSPNUMTOSTRING()	Convert the Smart List’s 5-digit ID into a string for use in the @CONCATENATE function
@NAME()	Passes the string to the @CONCATENATE function – This is probably redundant, but I am yet to take it out and test it
@CONCATENATE()	HSP_ID_ is concatenated with the SmartList ID. This will identify the column in the Planning Tables to perform a search on.
@MEMBER()	This function will take HSP_ID_***** and output the associated member from the Planning Tables.
->	@MEMBER can be used after a cross dimensional operator. The cross dimensional operator will treat the code as it would a member name enclosed in quotes.
VAR VarSL;	The variable VarSL holds the value in the Smart List member during the calculation. I believe this is optional. If you do use this - place it within a member block inside the FIX statement.

How it Works

Oracle Planning and Budgeting Cloud Service is supported by a relational repository that holds tables of names and configuration settings for the system. We call these ‘Hyperion Planning Tables’.

To achieve our goal, we need to use the numerical ID that is associated to each Smart List value in a lookup upon the Hyperion Planning Tables to retrieve the respective member name.

When a Smart List is created from members in a dimension, each of the values have a unique 5-digit ID associated to them. This ID is stored as an ‘Alias Name’ – an alternate name for the member that the Smart List value is based upon - in the Hyperion Planning Tables.

Therefore, using those tables you can take any dimension member and lookup the unique ID that will be assigned if the member is used as a value in any Smart Lists.

Note: This is only applicable if the ‘Create from Members’ setting is ticked in the definition of the Smart List.

In our scenario we reverse this process – we have the ID and we want the Member Name in order to pick up data that is associated to that member and use it in a calculation.

--------------------------------------------------------------------------------------------------------------------------------------

Additionally, for those that are interested, it is possible to concatenate a string with the Smart List value that has been transformed into a member. Now, I admit, this is a niche requirement, but it was necessary in my model and proved a little tricky!

It was difficult to arrive at the correct sequence of functions since I was repeatedly faced with the same error message in many of my initial attempts. I didn’t even know if it would be possible! 

However, I broke through and the following format worked for me:

Plain Code:

"Working" = "Base Salary"->"No Entity"->"No Account"->"BegBalance"->@MEMBER(            @CONCATENATE(@CalcMgrRemoveQuotes(@NAME(@MEMBER(@CONCATENATE("HSP_ID_", @NAME(@HspNumtoString(SLVal)))))), "Inner London"));

Commented Code:

"Working" = "Base Salary"->"No Entity"->"No Account"->"BegBalance"->@MEMBER(

 /* Reformatting SmartList:XR0201 to Member:"XR0201Inner London": Strip quotes & Concatenate with 'Inner/Outer/Fringe'*/

@CONCATENATE(@CalcMgrRemoveQuotes(@NAME(

/* Using SmartList ID in VAR:SLVal to pull out associated dimension member */

 @MEMBER(@CONCATENATE("HSP_ID_", @NAME(@HspNumtoString(SLVal))))))

 /* Concatenating with: */

 , "Inner London"));

Yes, there are a lot of brackets, and yes… they caused a lot of hassle!

But I do assure you, it works.