Developing Free-Form Reports

Free-form reports are often easier to create than structured reports. The free-form reporting style is ideal for ad hoc reporting in the Report Script Editor window.

A free-form report does not include PAGE, COLUMN, or ROW commands and instead gathers this information from a series of internal rules that are applied to the report script by the Report Extractor when you run the report.

The following example script and report illustrate free-form reporting:

Sales Colas
Jan Feb Mar 
Actual Budget
Illinois
Ohio
Wisconsin
Missouri
Iowa
Colorado
{UCHARACTERS}
Central
     !

Resulting report:

                                Sales 100

                 Jan               Feb               Mar
           Actual   Budget   Actual   Budget   Actual   Budget
          =======  =======  =======  =======  =======  =======
Illinois      829      700      898      700      932      700
Ohio          430      300      397      300      380      300
Wisconsin     490      300      518      400      535      400
Missouri      472      300      470      300      462      300
Iowa          161        0      162        0      162        0
Colorado      643      500      665      500      640      500
========      ===      ===      ===      ===      ===      ===
 Central    3,025    2,100    3,110    2,200    3,111    2,200

You can use formatting commands to add specific formats to a free-form report. When PAGE, COLUMN, and ROW commands are omitted, Essbase formats free-form reports according to the following rules:

  1. The Report Extractor finds the last member or members of a single dimension defined in the report specification (before the report output operator !). This dimension becomes the ROW dimension for the report. All remaining selections become PAGE or COLUMN dimensions, as defined by rules 2 and 3.

  2. The Report Extractor searches for any single-member selections. If a single member is found that does not satisfy rule 1, that dimension becomes a PAGE dimension.

  3. The Report Extractor searches for all remaining dimension members that do not satisfy rules 1 or 2. If any remaining members are found, those dimensions become COLUMN dimensions. COLUMN dimensions are nested in the order of selection in the free-form script.

  4. The Report Extractor searches the database outline for any dimensions not specified in the report specification. If unspecified dimensions are found, they become PAGE dimensions (the default for single-member selections, as defined in rule 2).

  5. A subsequent selection of one or more consecutive members from a given dimension overrides any previous selection for that dimension.

For example, the following report recognizes California, Oregon, Washington, Utah, Nevada, and West as members of Market.

Sales
Jan Feb Mar 
Actual Budget
Apr May Jun
California
Oregon
Washington
Utah
Nevada
{UCHARACTERS}
West
    !

Report Extractor applies free-form formatting rules to this report:

  1. Because California, Oregon, Washington, Utah, Nevada, and West are listed last, the Report Extractor treats them as if ROW (Market) had been specified (rule 1).

  2. Sales is a single-member selection from dimension Measures. The Report Extractor treats this member as if PAGE (Measures) had been specified (rule 2).

  3. After searching the remaining members, the Report Extractor finds members of dimensions Year and Scenario, which it treats as COLUMN (Year, Scenario) (rule 3).

  4. The Report Extractor searches the database outline and finds that dimension Product is not specified in the report specification. Because Product is a single-member selection, the Report Extractor treats this member as if PAGE (Product) had been specified (rule 4).

  5. Finally, the Report Extractor finds that Apr May Jun is from the same dimension as Jan Feb Mar and is displayed on a subsequent line of the script. The Report Extractor discards the first specification (Jan Feb Mar) and uses the second (Apr May Jun).

Resulting report:

                                Product Sales

                        Actual                  Budget
                 Apr     May     Jun     Apr     May     Jun
             =======  ======  ======  ======  ======  ======
California     3,814   4,031   4,319   3,000   3,400   3,700
Oregon         1,736   1,688   1,675   1,100   1,000   1,100
Washington     1,868   1,908   1,924   1,500   1,600   1,700
Utah           1,449   1,416   1,445     900     800     800
Nevada         2,442   2,541   2,681   1,900   2,000   2,100
======         =====   =====  =====    =====   =====   =====
  West        11,309  11,584  12,044   8,400   8,800   9,400

Note:

You cannot use substitution variables in free-form mode.