Developer ToolsFree generatorPDF, Excel, Word exportOracle Hyperion / EssbaseAll Oracle Hyperion and Essbase tools

Essbase Report Script Generator for Fast Flat Extracts

This generator writes an Essbase Report Writer script that returns a flat, tab-delimited grid with no headings, brackets, commas or indentation, plus the MaxL command that runs it to a file. Report scripts work on block storage and aggregate storage cubes in Essbase 11.1.2.x and 21c.

Fill in

Application

Database

PAGE dimensions

PAGE members

COLUMN dimension

COLUMN members

ROW dimensions

Member selection for first row dimension

Member selection for second row dimension

Decimal places

Report script path (for MaxL)

Output file path

Generated code

Download:

Report script (save as .rep)

{SUPHEADING}
{SUPMISSINGROWS}
{SUPBRACKETS}
{SUPCOMMAS}
{SUPFEED}
{NOINDENTGEN}
{ROWREPEAT}
{TABDELIMIT}
{DECIMAL 2}

<PAGE ("Scenario", "Measures")
"Actual" "Sales"

<COLUMN ("Year")
"Jan" "Feb" "Mar"

<ROW ("Market", "Product")
<DESCENDANTS "East"
<ICHILDREN "100"
!

Run it with MaxL (essmsh)

login admin identified by 'password' on 'essbase-host';
export database Sample.Basic using report_file '/u01/essbase/scripts/east_sales.rep' to data_file '/u01/exports/east_sales.txt';
logout;
exit;

Commands checked against the Report Writer Command Reference in the Essbase Technical Reference (SUPHEADING, SUPMISSINGROWS, SUPBRACKETS, SUPCOMMAS, SUPFEED, NOINDENTGEN, ROWREPEAT, TABDELIMIT, DECIMAL, PAGE, COLUMN, ROW, DESCENDANTS, IDESCENDANTS, CHILDREN, ICHILDREN, DIMBOTTOM, !) and the MaxL export database ... using report_file ... to data_file statement. On Windows, MaxL needs two backslashes for each backslash in a path. Member names default to Sample.Basic.

Need a nightly Essbase extract?

Tell us the cube, the slice and the target table; we will send back a reviewed report script and the MaxL job around it.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Choose which dimensions go on the page, column and row axes and which members to show. Every dimension must be placed somewhere or it defaults to the top member. The format commands at the top strip everything a loader would choke on: SUPHEADING removes the page and column headings, SUPMISSINGROWS drops rows where all values are missing, SUPBRACKETS and SUPCOMMAS remove number formatting, SUPFEED removes page breaks, NOINDENTGEN removes indentation by generation, ROWREPEAT repeats row member names on every line and TABDELIMIT separates columns with tabs. Save the script with a .rep extension and run the MaxL block.

The exclamation mark ends the report; anything after it starts a new report.
Member selection commands such as <DESCENDANTS apply to the dimension whose member you name.
Use {MISSINGTEXT "0"} if the target system cannot accept #Missing.

Member selection commands

<CHILDREN returns the children of a member and <ICHILDREN returns the member plus its children. <DESCENDANTS returns every member below, <IDESCENDANTS includes the member itself, and <DIMBOTTOM returns the level-0 members of the dimension of the member you name, which makes it the quickest way to build a level-0 extract. The Report Writer reference also lists <LEV, <GEN, <UDA, <ATTRIBUTE, <MATCH and <LINK for more selective extracts, and sorting commands such as <SORTASC. Keep row dimensions in the order you want the output columns, since ROWREPEAT writes them left to right in the order of the <ROW command.

Worked example

With the defaults on Sample.Basic, the page is fixed to Actual Sales, columns are Jan, Feb and Mar, and rows are every descendant of East crossed with 100 and its children. With SUPMISSINGROWS on, combinations with no data are dropped. Each output line holds Market, Product and three numbers separated by tabs, with two decimals. The MaxL statement export database Sample.Basic using report_file ... to data_file ... runs the script on the server and writes the file. Many teams use this pattern for a quick level-0 extract into a staging table because it needs no calc script and runs against ASO as well as BSO cubes.

Gotchas

Report scripts read data and calculate Dynamic Calc members on the fly, so pulling many dynamic members on rows can be slow; for full level-0 BSO exports, DATAEXPORT in a calc script or export database ... level0 data is often faster. If your output has multiple title rows, a format command is missing or placed after the first layout command; put all the brace commands at the top. Large reports can be limited by retrieval buffer settings. If member names contain spaces, keep them in double quotes. Check the application log for Report Writer errors, which name the line that failed.

Frequently Asked Questions

How do I suppress headers in an Essbase report script?

Add {SUPHEADING} at the top of the script, before the first layout command. It suppresses the default page and column headings. Combine it with {SUPBRACKETS}, {SUPCOMMAS}, {NOINDENTGEN}, {ROWREPEAT} and {TABDELIMIT} for a file that loads cleanly into another system. {SUPPAGEHEADING} and {SUPCOLHEADING} suppress only one of the two.

Why does my Essbase report contain multiple title rows?

Headings are repeated at each page break and whenever the page member changes. Add {SUPHEADING} and {SUPFEED}, and make sure they appear before <PAGE, <COLUMN and <ROW. If you have more than one page member, each combination becomes a new page with its own heading, so consider moving that dimension to rows.

How do I run an Essbase report script from MaxL?

Use export database app.db using report_file 'path_to_script.rep' to data_file 'path_to_output.txt'; after logging in. The paths are evaluated by the MaxL shell, and on Windows each backslash must be doubled. You can schedule the MaxL file with essmsh from cron or Windows Task Scheduler.

Is a report script faster than a calc script DATAEXPORT?

For small and mid-size slices a report script is quick and easy. For full level-0 exports of large BSO cubes, DATAEXPORT or a level-0 database export is usually faster because it reads blocks directly rather than building a grid. For ASO cubes, report scripts and MDX are the common extract options.

If you want people to ask Essbase for these extracts in plain English and get the file back, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools