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 !
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.
To list only level 0 data for specific members, use the CHILDREN command with the level 1 member as a parameter above the data that you want to print.
To list all level 0 data for the dimension to which a given member belongs, use the DIMBOTTOM command with any member in the dimension that contains the data that you want to print.
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 !
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.