Using A Smart List To Dynamically Select Members In A List
With more and more time dependent related data, grabbing the month from a Smart List is becoming more common. Associating that with a value in the application is required for a number of calculations, including things like
- An employee’s start or termination date
- The month an employee’s merit takes affect
- The month a product becomes inactive
- When an asset is put into use
- When a customer is new or leaves
- When a contract starts
- …
There are several ways to solve this, but the following is very simple and effective. If you are unaware of the @MEMBERAT function, take a look at this. It can be used for many other uses, but the following is an example of how to get the value in a period from the month selected in a Smart List.
@MEMBERAT returns the specific member in a list based on an index identified. This function requires 2 parameters. The first is list of members. The second is the index in that list in question.
If we have a Smart List that includes 12 Members, January through December, with a value of 1 through 12 respectively, it can be used in this function to grab the value in the corresponding selected month.
Assume we have a member called Merit Month, which is connected to the Smart List previously explained. When the user selects the month, the value is stored in that member in Essbase. When selecting March, a 3 is stored. Using the @MEMBERAT function, we can dynamically reference the period member.
Crazy Joe Davola has the following properties.
The following calculation would apply the merit increase to the months equal to the Merit Month, and all months after that.
IF( @ISMBR( @MEMBERAT("Jan":"Dec", "Merit Month"->"BegBalance"):"Dec" )
“Effective Salary” = “Salary”->”BegBalance” * (1 + “Merit Increase”->”BegBalance”) / 12;
ELSE
“Effective Salary” = “Salary”->”BegBalance” / 12;
ENDIF
The result would be an increase in monthly salary for all months from March through December.
Summary
This can be used for all types of applications. The same logic could be used for a Smart List with years, or really any application that correspond a list (Smart List) and a list of members in a hierarchy.
Did you create the Smartlist from members? I’ve followed your steps and it all looks straight forward but when I execute the business rule I get the following message :
Essbase Error(1200317): Error executing formula for [2010] (line 37): index out of range
which would seem to indicate that the Smartlist member ID is not the value between 1 and 12 (for Jul to Jun) that I would expect? Thx
Yes, the merit month is a smart list. The functions enumerates from start of the list (for example, 1 returns the first member in the list). The index of the Smartlist value is irrelevant. Make sure that your first param actually returns members. My guess is that the result is asking for an index of 10, for example, and the list of members in the first param only has 8 members.
Hi kylie,
Maybe I’m missing something but I have a similar setup to that described in your post and it just errors out with the same Index Out of Range message. My Smartlist SL_Month has 12 members Jul to Jun (in that order). I have tried with Smartlist created from members or defined separately, with and without Auto Generate ID. I have a member, “Start Month” defined as data type Smartlist linked to SL_Month. The users select the “Start Month” in a form. The business rule looks like this:
“OFS_Direct Input”(
IF ( @ISMBR(@MEMBERAT(“Jul”:”Jun”, “Start Month”->”BegBalance”):”Jun” )
“OFS_Direct Input”=”Total Employee Cost”->”BegBalance”/”Number of Months”->”BegBalance”;
ENDIF
);
If I replace “Start Month”->”BegBalance” with an integer constant such as 4 or 5, the @Memberat function works but using the Smartlist index generates the out of range error message. Any ideas would be appreciated otherwise I’ll have try some other way of trying achieve the desired outcome. Thanks
If you replace it with a number and get the expected result, then the value stored for the smart list selection is not what you think it is. If you value for your first month is 1, and that is selected, 1 should be in Essbase. All this is doing it taking the value and using that to get the x number in the list you have provided. If you are getting an out of range error, it likely means that the value you have for the smart list selection is not in the range of 1 to 12.
Hey Guys,
If you’re using the PBCS Smartlist create from members function, the values inherent won’t necessarily be integers (it basically creates unique value \ member combinations).
That said, there is another method to grab members that are created in that mechanism because that process dynamically creates a secondary alias table (SL_Alias) which has “HSP_ID_”
As such, this should return the member:
“@MEMBER(@CONCATENATE(“HSP_ID_”,@HspNumToString(“Start Month”->”BegBalance”)))”
For whichever ‘Month’ you have selected.
Cheers
Pete
That is great, thanks for sharing. I wish I had the time to dig into this type of stuff!
Hi kylie,
Maybe I’m missing something but I have a similar setup to that described in your post and it just errors out with the same Index Out of Range message. My Smartlist SL_Month has 12 members Jul to Jun (in that order). I have tried with Smartlist created from members or defined separately, with and without Auto Generate ID. I have a member, “Start Month” defined as data type Smartlist linked to SL_Month. The users select the “Start Month” in a form. The business rule looks like this:
“OFS_Direct Input”(
IF ( @ISMBR(@MEMBERAT(“Jul”:”Jun”, “Start Month”->”BegBalance”):”Jun” )
“OFS_Direct Input”=”Total Employee Cost”->”BegBalance”/”Number of Months”->”BegBalance”;
ENDIF
);
If I replace “Start Month”->”BegBalance” with an integer constant such as 4 or 5, the @Memberat function works but using the Smartlist index generates the out of range error message. Any ideas would be appreciated otherwise I’ll have try some other way of trying achieve the desired outcome. Thanks
Hi Kyle,
First of all great article thanks!! I have a question about smartlist, there is a possibility that one smartlist depent from another. That means if the user select one member of the smartlist A the smartlist B only show certain members
Thanks in advance
My apologies for the tardiness. There is no way of doing this that I am aware of.