EPM CALC MANAGER – CalcMgrLogMessageTrace Function: A Swiss Army Knife for Business Rule Functions Learning, Creation, and Debugging

In the world of business rule development, finding tools designed specifically for our needs can feel like a rare treat. Groovy offers script debugging with console logs, but what about regular business rules? Calculation Manager’s debug mode has its limitations, especially when functions get complex. Recently, I discovered a game-changer: the @CalcMgrLogMessageTrace function. It’s unlocked a deeper understanding of other functions and helped me achieve desired results with much less effort. Join me as I explore this hidden gem and how it can simplify your business rule development journey.

XRAY For your Rules:

@CalcMgrLogMessageTrace is more than just a debugging tool; it’s a catalyst for improved efficiency, accuracy, and mastery in the world of business rule development. @CalcMgrLogMessageTrace unlocks a powerful diagnostic toolbox, empowering you to elevate your business rule development with:

  • Precise Value Visibility: Print variable values and intermediate results on-demand, shedding light on data behavior throughout the rule execution.
  • Flow Visualization: Trace the step-by-step journey of your business rule, gaining insightful transparency into its decision-making process.
  • Streamlined Error Identification: Pinpoint issues with pinpoint accuracy, eliminating guesswork and accelerating resolution times.
  • Deeper Rule Understanding: Observe the inner workings of your rules in real-time, fostering a comprehensive understanding that goes beyond syntax and into the very core of their logic.

How do I use @CalcMgrLogMessageTrace?

I would use the @CalcMgrLogMessageTrace function with a simple argument to print out my current years to verify if the calculation executes within my scope of FIX.

  • Define your calculation region with the FIX and ENDFIX commands.
  • Use the @CalcMgrLogMessageTrace function to print out the value of the Year dimension member.
  • Compare the value of the Year dimension member to the members in your FIX region.
  • If the value of the Year dimension member is not within your FIX region, then your calculation will run for unnecessary members.
  • Modify your FIX region to ensure that your calculation is executed within the defined region.

This is applicable to any dimension member. I used Year as an example because it is easier to talk about.

The @CalcMgrLogMessageTrace function can take multiple string arguments. In this example, the only argument is @CurrMbr(“Year”). The @CurrMbr function returns a member, which has a value in the Essbase application. To print the value of a member in the Calculation Manager Log tab, the member name must be converted to a string using the @Name function. Therefore, the code @CalcMgrLogMessageTrace(@Name(@CurrMbr(“Year”))) is used to print the value of the Year dimension member in the Calculation Manager Log tab.

Once you execute this business rule, the Log Messages Tab below will become empty. I usually switch to the Errors and Warnings tab below and then come back to the Log Messages tab that would show my Log Messages from the latest execution.

After Execution:

Upon executing the business rule, the Log Messages tab will be cleared. To view the latest log messages, simply switch to the Errors and Warnings tab and then return to the Log Messages tab.

After Switching Tabs:

IF, ENDIF EVALUATION

To ensure that my calculation is only run for the scenario start year, I will wrap the @CalcMgrLogMessageTrace function in an IF condition. This will ensure that the log message is only printed if the current year is equal to the scenario start year.

Log Message:

By restricting the year to the plan start year, I can ensure that the @CalcMgrLogMessageTrace function is only evaluated for the correct year. This is especially helpful for rolling forecast scenario calculations, where it is important to evaluate the months properly.

EXECUTING MULTIPLE @CalcMgrLogMessageTrace FUNCTIONS

By executing multiple instances of the @CalcMgrLogMessageTrace function, we can gain a comprehensive understanding of the changes made by a calculation script. This allows us to visualize the “before” and “after” results of the script, ensuring that the desired changes have been made.

Here I am using a simple @CalcMgrReplaceAll function to verify if it is working as intended.

Log Messages:

EXECUTING ESSBASE FUNCTIONS

In the previous examples, I used the @Name and @CurrMbr functions to demonstrate how to print member values to the Calculation Manager Log tab. In this example, I will show how the @Alias function can be used to print alias names to the Log tab. The @Alias function is an Essbase function that returns the alias name for a given member.

Log Messages:

EXECUTING MEMBERSET FUNCTIONS

Member set functions are functions that return one or more members based on a set of criteria. In some cases, they may return zero members. Because member set functions return members, it is necessary to convert them to a string list using the @Name function in order to view the results in the Log Messages tab.

In this example, I have used the @IRSiblings function. This function returns a list of members that are related to the current member. As Essbase processes each year, the list of related members shrinks.

Log Messages:

EXECUTING DATE FUNCTIONS

In Essbase, most date functions return numeric values. To visualize the dates in a readable format, it is necessary to convert them to a string. Dates play a crucial role in workforce planning, capital expenditure planning, or project planning. In this example, the @CalcMgrGetCurrentDate() function is used to return the current date as a number. The @HspDateToString() function is then used to convert the numeric date to a string.

EXECUTING BOOLEAN FUNCTION

Boolean functions can be easily checked by adding IF and ENDIF blocks to the code. However, during debugging, it may be desirable to avoid adding additional IF blocks just to verify if a condition is true or false. In this case, the existing functions can be used to convert the Boolean function values to the strings “True” or “False”. This can make it easier to see the results of the Boolean function without having to add additional code.

Log Messages:

VISUALISE ESSBASE DATA

Up to this point, we have been looking at members, relationships, and custom functions. However, our ultimate goal is to produce the desired numbers in the system. For this reason, it is essential for a debugging script to show the data values from Essbase. Since the @CalcMgrLogMessageTrace function only supports strings, we would use an existing number-to-string conversion function to display the data in the Log Messages tab. This will allow us to see the actual values that are being calculated by Essbase.

The following code will show how the data is changed by increasing the current value by 10%:

Data before execution:

Log Messages:

VISUALISE ESSBASE DATA (EXTENDED – FINANCIAL FUNCTIONS)

Financial functions in Essbase do not return any members. Instead, they dynamically change the members during execution and get the data from Essbase. This can make it difficult to know where the value is taken from. Additionally, if the function is not used properly, it can return random or incorrect values. In this example, the Prior function is used. The Prior function helps to switch periods based on the parameters provided. However, if the Prior function is not used properly, it can return incorrect values.

I have created a simple sheet that has numbers associated with periods. Based on the values that are returned by financial functions, I would get to know from which member the data was taken.

Data

Using @Prior with Positive Shift

Log Messages for the Positive Shift

Based on the numbers that were returned above, it is now clear the value for August was taken from July.

Using @Prior with Negative Shift

Log Messages:

Based on the numbers returned, I can now confirm that the negative shift fetches the September value for the August month. This is a very simple example. However, by using XRANGES and combinations of multiple financial functions, we can write complex logic easily.

CONCLUSION

By using the @CalcMgrLogMessageTrace function, you can gain a better understanding of how Essbase is calculating data. This can be helpful for ensuring that calculations are producing the correct results. I hope this information if helpful.