1

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.