Creating a Essbase Report script is one of the ways used to export data out from the Essbase Database. Other ways being
1. Using a Calculation Script (Starting version 9.3.1)
2. Using the database export option and
3. Using a MDX (Multi Dimensional Expressions) query.
The report scripts are the simplest of all, to create an export file (either a .txt or .rep o/p file).
While creating the Report Script, first, we define some properties for the output file, like
1. Output File Properties
TABDELIMIT – to specify TAB as a delimiter for the output file
ROWREPEAT – To repeat the rows (just like we would use it in reports)
NOINDENTGEN – To indent or avoid indentation based on Generation
Suppression Options :
SUPEMPTYROWS, SUPMISSINGROWS, SUPCOMMAS (for thousand separators), SUPHEADING (if you don’t want to print the headings in the report output file
2. Page, Row and Column Dimension Definitions.
Having been specified the output file properties, the next step is to specify the ROW, COLUMN and PAGE dimension definitions.
Remember, the report script will process the output for the same member combinations that have been specified in this section.
An example of the ROW, COLUMN and PAGE listings is as follows (For ease, we have used the Sample:Basic DB)
<PAGE(“Measures”)
“Sales”
<COLUMN(“Scenario”, “Year”)
“Scenario”
“Jan” “Feb” “Mar” “Apr”
<ROW(“Market”, “Product”)
“New York”
“Product” “100” “100-10” “200-10″”300-10”
!
The output text file will look as follows:
The report script output can be formatted, arranged, sorted and modified to match the output requirements.
Lets see a simple scenario, where, we want to Reverse the sign of the output.
For achieving the sign reversal, we can add a column to the end of the Report Output file, like in the example below:
<COLUMN (“Scenario”)
“Actual”
{CALCULATE COLUMN “Inverse” = 5 * -1.}
{ORDER 0 1 2 3 4 5 6}
The output file has a newly added column (specified as column 6 in the ORDER command), as follows:
The most commonly used Report Script Commands can be found in the Administration Services Online Help, Here.
