Skip to main content


Essbase Substitution Variables

Essbase substitution variables are placeholders that hold a value and can be passed in a calc script, load rule, reports etc. Substitution variables can be created at the Essbase server level, the application or at the database level. Forward slash / is the escape character that can be used if single quote is in the substitution variable.

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

Automating the updating of a substitution variable can be done in:

  1. MAXL by using the following command
    • alter database appname.databasename set variable ‘CurMonth’ ‘Aug’
  2. ESSCMD by using following command
    • updatevariable CurMonth local appname databasename “Aug”

Some of issues and particular scenarios that I have dealt with in regards to a substitution variable are discussed below.

  • Resolving an issue related to a member name in Essbase – We had a member named $-REV in the Essbase Outline which was basically dollars related to revenue. Using it in FR for creating a report was causing a problem because “$” is a reserved keyword and symbolizes a substitution variable. The report was not even saving, and causing a validation issue. We were able to work around it by actually creating a substitution variable and assigning it the value “$-REV”. And using that substitution variable in the report. This basically proves that substitution variable is resolved at run time. The other idea would have been to add a member with a dynamic member formula referring to the $-REV member. And using this new member in the reports. Strange enough there is no restriction per Oracle documentation on using a $ symbol before a member name.
  • Using a substitution variable in a SQL Essbase load rule. Substitution variables can be used in a SQL query in a load rule by using single quote around it. Syntax would be: ‘&Variablename’ and can be in used the where clause or any other place in the SQL query.
  • Using server name as the substitution variable – There was a particular scenario where we had to export data to a particular folder on the server. So we were passing the server name along with the file path in the data export command. Every time the script was migrated from Dev to QA to PROD environment, we would forget changing the server name in the script. So we added it as a substitution variable and set the server names in the substitution variable. So only when substitution variables were migrated (which was rarely done) we would have to reset the values.
  • Another tip – if there are multiple applications using the same substitution variable then its better to create a variable at the Essbase server level. Essbase uses Database level variable first if available, else looks for application level and then goes to Server level variable if it’s not available at the application/database level.

Substitution variables in Essbase are a powerful tool for an Essbase admin as well as an Essbase developer. They allow for flexibility in terms of managing and maintaining calculations, loads and reports (In Smart View or Financial Reports). Latest feature of Essbase Runtime substitution variables basically mimics the run time prompts that are available in Calc manager. As I explore more about Essbase runtime substitution variables, I will keep you all posted as well.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nandini Nehru

Nandini Nehru is a Sr. Solutions Architect at Perficient in the Oracle EPM group. She has worked for ten plus years on various projects performing analysis, design, development, configuration and testing of applications. She has implemented Oracle Hyperion planning, HFM and Essbase applications for various industries like Healthcare, Public sector, Manufacturing and Oil and gas. She is a certified Oracle Trainer for Oracle Hyperion Financial reports, Planning Create and manage and Administrator class. She is also Oracle Hyperion Planning 11 Certified Implementation Specialist and Oracle Enterprise Planning and Budgeting Cloud Service 2017 Certified Implementation Specialist. Nandini works closely with the financial planning team at her clients across different industry verticals and implements IT solutions to meet their financial planning and budgeting needs. Nandini holds an honors degree in Bachelor of Engineering in Information technology and an MBA in Finance from Rice University. She was also a recipient of the renowned Jones Scholarship at Rice University, Houston.

More from this Author

Follow Us