Exporting Text Data Using Report Scripts

Report Writer provides extensive flexibility in selecting output data and formatting it to meet the needs of other programs. Report scripts work with all member types (for example: stored members, Dynamic Calc members, attributes, label-only members, shared members, and aliases.) However, report scripts generally are slower because they use query-based data extraction, which probes data blocks that are not materialized in the database. See “Executing Report Scripts” in the Oracle Essbase Administration Services Online Help and Executing Report Scripts. Report script-based export provides more flexibility in formatting the data and is ideal for generating professional-looking reports.

When you export data to a program that uses a two-dimensional, fixed-field format, you need not specify page or column dimensions. To create a two-dimensional report, you can specify every dimension as a row dimension. Use the ROWREPEAT command to add the name of each member specified to each row (rather than the default, nested style). The following script example and report illustrate this situation for a five-dimensional database:

<ROW (Year, Measures, Product, Market, Scenario)
{ROWREPEAT}
<ICHILDREN Year
Sales
<ICHILDREN "400"
East
Budget
    !

Resulting report:

Qtr1          Sales        400-10       East      Budget      900 
Qtr1          Sales        400-20       East      Budget    1,100 
Qtr1          Sales        400-30       East      Budget      800 
Qtr1          Sales          400        East      Budget    2,800 
Qtr2          Sales        400-10       East      Budget    1,100 
Qtr2          Sales        400-20       East      Budget    1,200 
Qtr2          Sales        400-30       East      Budget      900 
Qtr2          Sales          400        East      Budget    3,200 
Qtr3          Sales        400-10       East      Budget    1,200 
Qtr3          Sales        400-20       East      Budget    1,100 
Qtr3          Sales        400-30       East      Budget      900 
Qtr3          Sales          400        East      Budget    3,200 
Qtr4          Sales        400-10       East      Budget    1,000 
Qtr4          Sales        400-20       East      Budget    1,200 
Qtr4          Sales        400-30       East      Budget      600 
Qtr4          Sales          400        East      Budget    2,800 
  Year        Sales        400-10       East      Budget    4,200 
  Year        Sales        400-20       East      Budget    4,600 
  Year        Sales        400-30       East      Budget    3,200 
  Year        Sales          400        East      Budget   12,000

To create a two-dimensional report that contains only bottom-level (level 0) data, use CHILDREN or DIMBOTTOM to select level 0 members.

For example, the following script uses the CHILDREN command to select the children of Qtr1, which is a level 1 member, and the DIMBOTTOM command to select all level 0 data in the Product dimension.

<ROW (Year, Measures, Product, Market, Scenario)
{ROWREPEAT}
{DECIMAL 2}
<CHILDREN Qtr1
Sales
<DIMBOTTOM Product
East
Budget
     !

Resulting report:

Jan      Sales    100-10     East       Budget        1,600.00 
Jan      Sales    100-20     East       Budget          400.00 
Jan      Sales    100-30     East       Budget          200.00 
Jan      Sales    200-10     East       Budget          300.00 
Jan      Sales    200-20     East       Budget          200.00 
Jan      Sales    200-30     East       Budget        #MISSING 
Jan      Sales    200-40     East       Budget          700.00 
Jan      Sales    300-10     East       Budget        #MISSING 
Jan      Sales    300-20     East       Budget          400.00 
Jan      Sales    300-30     East       Budget          300.00 
Jan      Sales    400-10     East       Budget          300.00 
Jan      Sales    400-20     East       Budget          400.00 
Jan      Sales    400-30     East       Budget          200.00 
Feb      Sales    100-10     East       Budget        1,400.00 
Feb      Sales    100-20     East       Budget          300.00 
Feb      Sales    100-30     East       Budget          300.00 
Feb      Sales    200-10     East       Budget          400.00 
Feb      Sales    200-20     East       Budget          200.00 
Feb      Sales    200-30     East       Budget        #MISSING 
Feb      Sales    200-40     East       Budget          700.00 
Feb      Sales    300-10     East       Budget        #MISSING 
Feb      Sales    300-20     East       Budget          400.00 
Feb      Sales    300-30     East       Budget          300.00 
Feb      Sales    400-10     East       Budget          300.00 
Feb      Sales    400-20     East       Budget          300.00 
Feb      Sales    400-30     East       Budget          300.00 
Mar      Sales    100-10     East       Budget        1,600.00 
Mar      Sales    100-20     East       Budget          300.00 
Mar      Sales    100-30     East       Budget          400.00 
Mar      Sales    200-10     East       Budget          400.00 
Mar      Sales    200-20     East       Budget          200.00 
Mar      Sales    200-30     East       Budget        #MISSING 
Mar      Sales    200-40     East       Budget          600.00 
Mar      Sales    300-10     East       Budget        #MISSING 
Mar      Sales    300-20     East       Budget          400.00 
Mar      Sales    300-30     East       Budget          300.00 
Mar      Sales    400-10     East       Budget          300.00 
Mar      Sales    400-20     East       Budget          400.00 
Mar      Sales    400-30     East       Budget          300.00 

For another example of formatting for data export, see “Sample 12 on the Examples of Report Scripts” page in the “Report Writer Commands” section of the Oracle Essbase Technical Reference.