Developers who develop business rules rarely get a function that is purely designed for them. Groovy, for example, allows us to debug a script by printing the outcomes in the job console log, but what about regular business rules? In calculation manager, there is a debug option that has been available for a long time, but I have never been able to use it fully when the functions become complicated or, to be honest, I may not know how to use it. I have been developing a few business rules recently, and @CalcMgrLogMessageTrace function has helped me to understand other functions more effectively and I have been able to get the required results more easily.
Why you need to use @CalcMgrLogMessageTrace?
- To debug business rules by printing out the values of variables, expressions, and other intermediate results.
- To trace the flow of execution through a business rule.
- To identify and troubleshoot errors in business rules.
- To gain a better understanding of how business rules work.
Please note that this function is purely for development purposes and cannot be used in the actual business rule 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
@CalcMgrLogMessageTracefunction to print out the value of the
- Compare the value of the
Yeardimension member to the members in your FIX region.
- If the value of the
Yeardimension 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.
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.
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.
EXECUTING ESSBASE FUNCTIONS
In the previous examples, I used the
@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.
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.
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.
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:
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.
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
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.
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.