Developing Report Scripts

In This Section:

Introduction

Understanding Extraction and Formatting Commands

Understanding Report Script Syntax

Designing the Page Layout

Formatting

Selecting and Sorting Members

Restricting and Ordering Data Values

Converting Data to a Different Currency

Generating Reports Using the C, Visual Basic, and Grid APIs

Also see:

Introduction

When you understand the basics of creating report scripts, you can create more complex reports. You create a report using extraction commands, which specify member combinations for pages, columns, and rows. You use formatting commands to determine the visual design of the report and to control the display of the data values. Formatted data values are displayed in the report when you run the script, based on the combined extraction and report commands.

Understanding Extraction and Formatting Commands

Extraction commands perform the following actions:

  • Determine the selection, orientation, grouping, and ordering of raw data records extracted from the database. Extraction commands are based on either dimension or member names, or keywords. Their names begin with the greater-than symbol (>).

  • Apply to the report from the line on which they occur until the end of the report. If another extraction command occurs on a subsequent line of the report, it overrides the previous command.

Formatting commands perform the following actions:

  • Enable you to customize the format and appearance of a report and create report-time calculations. Formatting commands are generally enclosed in braces ({ }), although several formatting commands begin with the less-than (<) character.

  • Are either applied globally within the report script or are specific to a member.

Understanding Report Script Syntax

To build a report, you enter commands that define the layout, member selection, and format you want in Report Script Editor. The different elements of a script are color-coded to aid readability. You can enable autocompletion to help build scripts interactively as you type. See “About Report Script Editor” in the Oracle Essbase Administration Services Online Help.

When you write a report script, follow these guidelines:

  • Separate commands with at least one space, tab, or new line for readability. Report processing is unaffected by extra blank lines, spaces, or tabs.

  • Enter commands in uppercase or lowercase. Commands are not case-sensitive. If the database outline is case-sensitive, the members in the report script must match the outline.

  • To start report processing, enter the bang (!) report output command or one or more consecutive numeric values. You can place one or more report scripts, each terminated by its own ! command, in the same report file.

  • You can group more than one format command within one set of braces. For example, these formats are synonymous:

    {UDATA SKIP}
     {UDATA} {SKIP}
  • Enclose member names in quotation marks in the following cases:

    • Names beginning with an ampersand (for example, “&Product”).

    • Names containing spaces (for example, “Cost of Goods Sold”).

    • Names containing the word Default (for example, “Default Value”).

    • Duplicate member names, which must be entered as qualified member names (for example, “[2006].[Qtr1]”)

    • Names containing one or more numerals at the beginning of the name (for example, “100-Blue”)

    • Names containing any of the following characters:

      *

      asterisks

      -

      dashes, hyphens, or minus signs

      @

      at signs

      <

      less-than signs

      {}

      braces

      ()

      parentheses

      [ ]

      brackets

      +

      plus signs

      ,

      commas

      ;

      semicolons

      :

      colons

      /

      slashes

  • If a formatting command is preceded by three or more underscore, equal sign, or hyphen characters, respectively, the Report Extractor assumes that the characters are extraneous underline characters and ignores them. For example, ==={SKIP 1}.

  • (double slash) Use // to indicate a comment. Everything on the line following a comment is ignored by the Report Writer. Each line of a comment must start with a double slash, so you can include multiline comments.

  • Exercise caution in abbreviating command names. Many names begin with the same letters, and the results may be unexpected unless you use a completely unambiguous abbreviation.

Designing the Page Layout

Reports are two-dimensional views of multidimensional data. You can use page layout commands to incorporate additional dimensions that are defined as nested groups of columns or rows on a page, or additional pages in the report.

The page layout is composed of headings that make up the columns and rows of a page. You define the basic layout of a report using page, row, and column data extraction commands combined with specific member selections.

Each component of page layout has a different formatting command:

<PAGE 
<COLUMN 
<ROW

In addition, the <ASYM and <SYM commands override the default method of interpreting the column dimension member lists, and produce either an asymmetric or symmetric report format.

See Formatting Page, Column, and Row Headings.

Creating Page, Column, and Row Headings

*  To define report headings:

  1. Enter <PAGE(dimensionname, dimensionname)

    where dimensionname lists dimensions represented on the current page. All data values on the page have the dimensions in the page heading as a common property. Example:

    <PAGE (Measures, Market)
  2. Press Enter.

  3. Enter <COLUMN(dimensionname)

    where dimensionname equals the name of each dimension to display across the page. Example:

    <COLUMN (Year)

    Adding dimension names creates nested column headings.

  4. Press Enter.

  5. Enter <ROW(dimensionname)

    where dimensionname equals the name of each dimension to display down the page. Example:

    <ROW (Market)

  6. Press Enter.

    Note:

    You can select additional members to associate with the heading commands. Using a member name as a parameter for the PAGE, COLUMN, or ROW commands causes Report Extractor to associate the member with the appropriate dimension.

The following report script is based on the Sample.Basic database:

<PAGE (Product, Measures)
<COLUMN (Scenario, Year)
Actual
<ICHILDREN Qtr1
<ROW (Market)
<IDESCENDANTS East
    !

This script produces the following report:

              Product Measures Actual

                      Jan      Feb      Mar     Qtr1   
                 ======== ======== ======== ======== 
New York              512      601      543    1,656 
Massachusetts         519      498      515    1,532 
Florida               336      361      373    1,070 
Connecticut           321      309      290      920 
New Hampshire          44       74       84      202 
  East              1,732    1,843    1,805    5,380 

You can create page, column, and row headings with members of attribute dimensions. The following report script is based on the Sample.Basic database:

<PAGE (Measures,Caffeinated)
Profit
<COLUMN (Year,Ounces)
Apr May
"12"
<ROW (Market,"Pkg Type")
Can
<ICHILDREN East
    !

This script produces the following report:

                          Profit Caffeinated 12 Scenario 
                                     Apr      May 
                                ======== ======== 
New York         Can                 276      295 
Massachusetts    Can                 397      434 
Florida          Can                 202      213 
Connecticut      Can                 107       98 
New Hampshire    Can                  27       31 
  East           Can               1,009    1,071 

Modifying Headings

You can perform the following modifications to headings in the report:

Task

Report Command

Create a custom page heading in place of the default heading, which is displayed at the top of each page in the report or immediately following a HEADING command. Use the ENDHEADING command to specify the end of the custom heading.

STARTHEADING

Display the page heading, either the default heading or the heading as defined with the STARTHEADING and ENDHEADING commands.

Use this command to re-enable the page heading display if the SUPHEADING command has been used.

HEADING

Force the immediate display of the heading without waiting for the next unsuppressed data row, when heading suppression commands are in use.

IMMHEADING

Automatically turn on the display of the column header.

COLHEADING

Display the page heading before the next data output row.

PAGEHEADING

For descriptions of the SUPPRESS commands used to suppress headings, see Suppressing Page, Column, and Row Formatting.

Creating Symmetric and Asymmetric Reports

Essbase reports can contain symmetric or asymmetric column groups. Essbase determines the symmetry of column groups automatically, based on the members you select.

A symmetric report, shown below, is characterized by repeating, identical groups of members.

            East                           West
   Budget       Actual             Budget      Actual
Q1  Q2  Q3     Q1  Q2  Q3        Q1  Q2  Q3   Q1  Q2  Q3

An asymmetric report, shown below, is characterized by groups of nested members that differ by at least one member in the nested group. There can be a difference in the number of members or the names of members.

             East                           West
   Budget        Actual                    Budget
Q1  Q2  Q3      Q1  Q2  Q3               Q1  Q2  Q3

By default, Essbase creates a symmetric report unless you select the same number of members for all column dimensions.

For an example of an asymmetric report, see “Sample 13, Creating Asymmetric Columns,” in the Examples of Report Scripts page of the Oracle Essbase Technical Reference.

The Essbase evaluation of symmetry versus asymmetry takes place before any ordering, restriction on columns, or application of the effects of calculated columns.

Overriding Default Column Groupings

You can override the default column grouping that Essbase selects for reports with the <SYM and <ASYM commands. <SYM and <ASYM affect the member selection commands that follow them in a report.

  1. Use the <SYM command when the selection of column members meets the requirements of the rule for asymmetry, but you want to produce a symmetric report. The <SYM command always produces a symmetric report, creating all combinations of each column dimension.

  2. Turn off the symmetric format and restore the rules for asymmetric reports with the <ASYM command.

Changing Column Headings

If you need to change only the column headings rather than the symmetry of the report, the <PYRAMIDHEADERS and <BLOCKHEADERS formatting commands are useful.

  • Use the <BLOCKHEADERS formatting command to change the pyramid-style headers used in symmetric reports to block-style headers such as those used in asymmetric reports. A symmetric report uses the <PYRAMIDHEADERS mode of column layout by default.

  • Use the <PYRAMIDHEADERS formatting command to change the block-style headers used in asymmetric reports to pyramid-style headers such as those used in symmetric reports. An asymmetric report uses the <BLOCKHEADERS mode of column layout.

Formatting

Formatting commands, usually enclosed in braces ({ }), define the format of data and labels in the final report and can be either global or member-specific.

  • Global commands are executed when they occur in the report script file and stay in effect until the end of the report file or until another global command replaces them.

    For example, the {SUPMISSINGROWS} command suppresses all rows in the report script file that contain only missing values.

  • Member-specific commands are executed as they are encountered in the report script, usually the next member in the report script, and affect only that member. A format attached to a member is executed before that member is processed.

    For example, the {SKIP} command skips the specified number of rows between row dimensions in a report script. If you want additional rows to skip lines, you must use the SKIP command again.

Formatting Report Pages

You can use several formatting commands to design the look of the final report pages.

See the Oracle Essbase Technical Reference for examples.

Setting Page Length and Width and Centering

You can set the following page specifications in the report script:

Task

Report Command

Specify the column widths.

WIDTH

Set the left margin.

LMARGIN

Set the center of the page.

SETCENTER

Inserting Page Breaks

You can set the following types of page breaks in the report script:

Task

Report Command

Set the number of lines for each page.

PAGELENGTH

Force a page break regardless of how many lines have been generated for the current page.

NEWPAGE

Insert a page break whenever a member from the same dimension as the member in the command changes from one line in the report to the next. Use the NOPAGEONDIMENSION command to turn off this function.

PAGEONDIMENSION

Enable page breaks in a report when the number of lines on a page is greater than the current PAGELENGTH setting.

FEEDON

Formatting Page, Column, and Row Headings

Column and row formatting commands make up a special type of format setting commands.

Specifying Column Formats

Specifications for column formatting commands can precede or follow the columns to which they apply, depending on the desired format.

For example, in the following script, based on the Sample.Basic database, the first {DECIMAL} command is processed after two columns are set up, Actual and Budget. The {DECIMAL} command, however, refers to a column three, which does not yet exist. Essbase responds to this command by dynamically expanding the report to three columns. When the report specification expands to six columns, the {DECIMAL} formatting applies to columns three and six (and all multiples of three).

Essbase performs this pattern extension on the assumption that when another dimension is added, causing repetitions of previous column dimension groups, the formatting should repeat as well. The second {DECIMAL} formatting command is then applied to columns 1 and 4 only, as it occurs after the creation of six columns.

<PAGE (Measures, Market)
Texas Sales
     <COLUMN (Scenario, Year)
     Actual Budget
{DECIMAL 2 3 }
     Jan Feb Mar
{DECIMAL 1 1 4 }
<ROW (Product)
<DESCENDANTS "100"
    !

This script produces the following report:

                           Sales Texas

                 Actual                    Budget
          Jan      Feb      Mar      Jan      Feb      Mar
          ===      ===      ===      ===      ===      ===
100-10   452.0     465    467.00    560.0     580    580.00
100-20   190.0     190    193.00    230.0     230    240.00
100-30 #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

The following scripts demonstrate two approaches to column formatting that produce identical results. In the first script, the first two {DECIMAL} commands are positioned to format every first and third column by distributing the formats when Jan Feb displays after processing the {DECIMAL} command. These examples are based on the Sample.Basic database.

//Script One: Format Columns by Distributing the Formats

<PAGE (Measures, Market)
California Sales
     <COLUMN (Scenario, Year)
     Actual Budget Variance
{DECIMAL 1 1 }
{DECIMAL 2 3 }
     Jan Feb
     //     {DECIMAL 1 1 4 }   These lines are commented; the
     //     {DECIMAL 2 3 6 }   Report Extractor ignores them.
<ROW (Product)
<DESCENDANTS "100"
    !

The two {DECIMAL} commands are positioned to format the individual columns 1, 3, 4, and 6.

//  Script Two: Format Columns by Direct Assignment

<PAGE (Measures, Market)
California Sales
     <COLUMN (Scenario, Year)
     Actual Budget Variance
     //     {DECIMAL 1 1 }     These lines are commented; the
     //     {DECIMAL 2 3 }     Report Extractor ignores them.
     Jan Feb
{DECIMAL 1 1 4 7 }   
{DECIMAL 2 3 6 9 }
<ROW (Product)
<DESCENDANTS "100"
    !

Both scripts produce the following report:

                          Sales California

              Actual          Budget            Variance
           Jan     Feb     Jan       Feb     Jan       Feb
         =====    ====    ====      ====    =====      ====
100-10   678.0     645   840.00    800.0    (162)  (155.00)
100-20   118.0     122   140.00    150.0     (22)   (28.00)
100-30   145.0     132   180.00    160.0     (35)   (28.00 

Note:

By default, attribute calculation dimension members (for example, SUM, AVG) are displayed as columns. To display them in rows, you must include them in the ROW command.

Accommodating Long Column Names and Row Names

Member names too long to fit into columns are automatically truncated; the tilde character (~) signifies that part of the name is missing. Long member names are common when using aliases.

You can modify columns to display the entire member name:

Task

Report Command

Define the column width for all row members in the column.

NAMEWIDTH

Change where the row member column is displayed and shift the remaining columns left or right to allow long member names.

NAMESCOL

Suppressing Page, Column, and Row Formatting

You can suppress the display of page heading, columns, and rows in a report by using SUPPRESS commands.

Suppress

Report Command

The default column heading in the report.

SUPCOLHEADING

Rows that have only zero or missing values.

SUPEMPTYROWS

All rows that contain missing values. Use INCMISSINGROWS, INCZEROROWS, or INCEMPTYROWS to display rows that are empty or have missing data or zeros.

SUPMISSINGROWS

The page member heading whenever a heading is generated.

SUPPAGEHEADING

The page and column headings, all member names, page breaks, commas, and brackets in the final report. To turn on the display of columns of row member names, use the NAMESON command. To turn on the use of commas and brackets, use the COMMAS and BRACKETS commands.

SUPALL

The default heading (page header and column headers) or custom header, if defined, at the top of each page.

SUPHEADING

Row member names in the final report. Use the NAMESON command to include row member names in the report.

SUPNAMES

All output while continuing to process all operations, such as calculations and format settings.

Use the OUTPUT command to reverse the actions of SUPOUTPUT.

SUPOUTPUT

Currency information when you use the CURRENCY command to convert data values in a report to a specified currency.

SUPCURHEADING

Repeating Row Names

To repeat the row member names on every line of the report, use the <ROWREPEAT command. Use the <NOROWREPEAT command to prevent row member names from being repeated on each line of the report if the row member name does not change on the next line. NOROWREPEAT is enabled by default.

Using Tab Delimiters

You can use tabs rather than spaces between columns in report scripts; for example, when you want to export report output into another form.

To replace spaces with tab delimiters, type {TABDELIMIT} anywhere in the report script.

Adding Totals and Subtotals

Column and row calculations let you create additional calculations that are not defined as part of the database outline. For example, you can use column and row calculations to create extra columns or rows in a report, based upon selected data members, and perform calculations on these or existing columns and rows.

For examples of report scripts that contain column and row calculations, see “Sample 14, Calculating Columns” on the “Example of Report Scripts” page of the Oracle Essbase Technical Reference.

Totaling Columns

The CALCULATE COLUMN command lets you create a report column, perform on-the-fly calculations, and display the calculation results in the newly created column.

The following table summarizes column calculation commands:

Task

Report Command

Create a report column, perform dynamic calculations, and display the calculation results in the newly created column. Use the OFFCOLCALCS command to temporarily disable column calculations in the report, and ONCOLCALCS to re-enable calculations.

CALCULATE COLUMN

Remove all column calculation definitions from the report.

REMOVECOLCALCS

CALCULATE COLUMN adds up to 499 ad hoc column calculations to a report. Each new calculated column is appended to the right of the existing columns in the order in which it is created and is given the next available column number. These columns calculate the sum of data across a range of columns or an arithmetic expression composed of simple mathematical operators.

The CALCULATE COLUMN command supports standard mathematical operations. For syntax and parameter descriptions, see the Oracle Essbase Technical Reference.

If you use the same name for more than one column, Essbase creates only the last column specified in the CALCULATE COLUMN command. Use a leading space with the second name (and two leading spaces with the third name, and so on) to create a unique column name.

Alternately, you can add descriptive text far enough to the right that it is truncated to the column width. You can, for example, use the names Q1 Actual and Q1 Budget to distinguish similar column names without affecting the appearance of the report. Column names are printed with right justification until the column header space is filled. Excess characters are then truncated to the right.

Divide lengthy column name labels into multiple lines. The maximum number of lines across which you can divide a label is equal to the number of column dimensions designated in the report specification. To break a column name, insert a tilde (~) in the name at the point where you want the break. You must also specify at least two members for each column dimension to use the maximum number of lines.

This example is based on the Sample.Basic database.

{CALCULATE COLUMN "Year to Date~Actual Total" = 1 : 2}
{CALCULATE COLUMN "Year to Date~Budget Total" = 3 : 4}

The example produces the following report:

                                  Sales East
            Actual   Year to Date      Budget    Year to Date 
         Jan    Feb  Actual Total    Jan    Feb  Budget Total 
       ===== ====== ============= ====== ====== ============= 
400-10   562    560         1,122    580    580         1,702 
400-20   219    243           462    230    260           722 
400-30   432    469           901    440    490         1,391 

As a rule, in symmetric reports, if a calculated column name has fewer levels than the number of column dimensions, the previous member (to the left) of each of the column dimensions, above the top level supplied in the calculated column name, is attributed to the calculated column. If normal PYRAMIDHEADERS mode is in use, the centering of those higher-level column members shifts to the right to include the calculated column or columns. Column header members on the same level as calculated column names are not applied to the new calculated column or columns, and their centering does not shift.

If BLOCKHEADERS mode is in use; that is, if every member applying to a column is repeated above that column, the same rules apply, except that instead of shifting column header member centering, they are repeated in the appropriate higher levels of the calculated column name.

Asymmetric reports do not have groups of columns that share a member property. These reports still allow multiple-level column names up to the number of column levels defined, but member properties from preceding columns are not automatically shared and used for those levels that are undefined.

If there are fewer column header dimensions than the number of levels that you want, you can create multiline column labels. In this case, use TEXT, STARTHEADING, ENDHEADING, and other formatting commands to create a custom heading.

For the syntax and definitions of column calculation commands, see the Oracle Essbase Technical Reference.

Numbering Columns

If the number of regular (not calculated) columns varies in the report because multiple sections in the report have different numbers of columns, the column numbers used to identify the calculated columns shift accordingly, as illustrated:

  • If the first section of a report has 12 columns (including row name columns), and three calculated columns are declared, column numbers 0–11 are the regular columns, and columns 12–14 are the calculated columns.

  • If a second section of the report reduces the number of regular columns to six, then the regular columns are columns 0–5, and the same calculated columns are columns 6–8.

  • Similarly, if the number of regular columns is increased, the numbering of the calculated columns starts at a higher number.

In the example, CC1, CC2, and CC3 represent the names of three calculated column names. The column numbering for a report with two different sections with varying numbers of regular columns:

internal
col # s: 0    1      2      3     4     5     6     7   
            Jan    Feb    Mar   Apr   CC1   CC2   CC3 
            ===    ===    ===   ===   ===   ===   === 
   Sales      1      3      5     3    22    55    26 
   Expense    1      2      5     3    23    65    33 

                 same report- new section 
internal 
col # s: 0    1      2      3     4     5 
           Qtr1    YTD    CC1   CC2   CC3 
            ===    ===    ===   ===   === 
   Sales      2      9     22    57    36 
   Expense    4      8     56    45    33 

If you do not want the calculated columns in the second section, or if you need a different set of column calculation, use the command REMOVECOLCALCS to clear the old ones out. You can then define new column calculations.

This example assumes that all three column calculations had no references to regular columns other than columns 1 and 2. If CC3's calculation were = 1 + 3 + 6, when the second section of the report starts, an error occurs stating that the column calculation referred to a nonexistent column (6).

Totaling Rows

Row calculations create summary rows in a report. You can use summary rows to calculate the sum of data across a range of rows or to calculate an arithmetic expression composed of simple mathematical operators.

The following table summarizes row calculation commands:

Task

Report Command

Create a row and associate it with a row name or label. This process is similar to declaring a variable. You can also perform simple calculations with CALCULATE ROW. For more complex calculations, use SETROWOP. See also OFFROWCALCS and ONROWCALCS.

CALCULATE ROW

Temporarily disable row calculations in the report. See also CALCULATE ROW and ONROWCALCS.

OFFROWCALCS

Re-enable calculations after using OFFROWCALCS. See also CALCULATE ROW and OFFROWCALCS.

ONROWCALCS

Define complex calculations for the row specified in CALCULATE ROW. SETROWOP defines a calculation operator to be applied to all subsequent output data rows. You can display the calculation results in the newly created row with the PRINTROW command.

SETROWOP

Immediately display the row specified in CALCULATE ROW to the report.

PRINTROW

Reset the value of the calculated row to #MISSING. See also CLEARALLROWCALC.

CLEARROWCALC

Reset the value of all calculated rows after using the CLEARROWCALC command.

CLEARALLROWCALC

Create a calculated row with captured data. See also SAVEANDOUTPUT.

SAVEROW

Capture data and output the result after using the SAVEROW command.

SAVEANDOUTPUT

For the syntax and definitions of row calculation commands, see the Oracle Essbase Technical Reference.

Commands that designate columns must use valid data column numbers, as determined by the original order of the columns.

  • Precede and follow all operators in an expression with a single space.

  • Essbase does not support nested (parenthetical) expressions.

  • Essbase supports integer and floating-point constants in expressions as single entries or members of an array.

The CALCULATE ROW command can specify an operation (+, -, *, /, or OFF) as an equation consisting of constants, other calculated rows, and operators. Equations are evaluated at the time of declaration. Member names are not supported in expressions with the CALCULATE ROW command.

If you specify an operator, the operator applies to subsequent output rows and stores the result in the calculated row. Specifying an operator is useful for aggregating a series of rows to obtain a subtotal or total. To reset the operator, use SETROWOP. If the CALCULATE ROW command does not specify either an equation or an operator, the + operator is assumed.

The CALCULATE ROW command supports the standard mathematical operations. For syntax and parameter descriptions, see the Oracle Essbase Technical Reference.

This example is based on the Sample.Basic database.

{ CALC ROW "Total Sales" = "Sales..Group1"
     + "Sales..Group2" }

The example creates “Total Sales” based on two other calculated rows.

Changing How Data Is Displayed

Formatting commands customize how data displays in the final report:

Underlining

Use underlining as a visual aid to break up blocks of information in a report.

Task

Report Command

Set the default underline character to display in the report.

UNDERLINECHAR

Underline all characters that are not blank in the preceding row.

UCHARACTERS

Underline all the columns in the preceding row.

UCOLUMNS

Underline all the data columns for a row, while not underlining the row name columns.

UDATA

Underline all the row name columns in the preceding row while not underlining the data columns.

UNAME

Underline the row member names in a row whenever a member from the same dimension as the member in the command changes. Use the NOUNAMEONDIM command to turn off underlining for new rows.

UNAMEONDIMENSION

Suppressing Data Formatting

You can suppress data that you do not want to be displayed in the final report by using SUPPRESS commands.

Suppress

Report Command

Brackets around negative numbers. Use the BRACKETS command to re-enable brackets.

SUPBRACKETS

Commas in numbers greater than 999. Use the COMMAS command to re-enable commas.

SUPCOMMAS

Rows that have only zero data values. Use INCZEROROWS or INCEMPTYROWS to re-enable the display.

SUPZEROROWS

The European method for displaying numbers (2.000,01 whereas the non-European equivalent is 2,000.01). Use the EUROPEAN command to re-enable European number display.

SUPEUROPEAN

The automatic insertion of a page break whenever the number of lines on a page exceeds the current PAGELENGTH setting.

SUPFEED

Formats that produce output such as underlines and skips. Use INCFORMATS to re-enable the display.

SUPFORMATS

Text masks that were defined in the report using the MASK command. Use INCMASK to re-enable the display.

SUPMASK

See Suppressing Page, Column, and Row Formatting for descriptions of the SUPPRESS commands used to suppress formats.

Indenting

Use indenting to provide visual clues to row levels of the script.

Task

Report Command

Shift the first-row names column in column output order by a specified number of characters.

INDENT

Indent subsequent row members in the row names column based on the generation in the database outline. Use the NOINDENTGEN command to left-justify row member names based on generation name.

INDENTGEN

Inserting Custom Titles

Titles are user-generated and optional, in contrast to the automatically generated page and column headings and row names, which describe the data on the report page.

Titles repeat at the top of each report page and provide the following information about a report:

  • A date and time stamp

  • The user name of the person running the report

  • Page numbers

  • The name of the source database

  • Any other descriptive information

To add a title to the report, use the TEXT command, combined with any of the following:

  • Any predefined keywords that automatically display information in the report

  • A text string that you define

    Note:

    You can also use the TEXT command at the bottom of the report to provide summary information.

See the Oracle Essbase Technical Reference for the syntax and definitions of Report Writer commands.

Replacing Missing Text or Zeros with Labels

When you run a report, there are often many empty data cells where no data was applicable to the retrieval, or cells where the value is zero.

The report displays the default #MISSING label in the data cell when no data values are found.

*  To replace the #MISSING label with a text label, at the point in the script where you want to replace the #MISSING label with a text label, type:

{MISSINGTEXT ["text"]} 

    where text is the text string that to be displayed in the data cells.

    You can place the MISSINGTEXT command at any point in the report script; the command applies throughout the script.

    Note:

    You can also suppress #MISSING labels from appearing in the report. See Suppressing Data Formatting for descriptions of SUPPRESS commands used to suppress labels, or see the Oracle Essbase Technical Reference for the syntax and definitions of Report Writer commands.

    *  To replace zeros with a text label, at the point in the script where you want to replace zeros with a text label, type

    {ZEROTEXT ["text"]} 

      where text is the text string to be displayed in the data cells.

      Note:

      If a value is equal to #MISSING, any string inserted after that value using the AFTER command does not print. AFTER strings also do not print if you replace #MISSING with some other value (such as 0).

      Adding Blank Spaces

      Adding blank spaces in a report draws the reader to key information, such as totals.

      Task

      Report Command

      Add one or more blank lines in the final report.

      SKIP

      Add a blank line when a member from the same dimension as the specified member in the command changes on the next line in the report.

      Use the NOSKIPONDIMENSION command to turn off insertion of a new line.

      SKIPONDIMENSION

      Changing How Data Values Display

      You can use the following commands to change how data values display in the final report.

      Task

      Report Command

      Turn on the display of commas for numbers greater than 999 after commas have been suppressed with either a SUPCOMMAS or SUPALL command.

      COMMAS

      Turn on the display of brackets around negative numbers instead of negative signs, after using the SUPBRACKETS command earlier in the script.

      BRACKETS

      Include a percentage sign or other character after the data values.

      AFTER

      Include a dollar sign or other character before the data values.

      BEFORE

      Use the European method for displaying numbers where decimal points are used as the thousands separator character and commas separate the decimal portion of the number from the integer portion (2.000,01; the non-European equivalent is 2,000.01).

      EUROPEAN

      Overwrite text in each output row with a specified characters and position.

      MASK

      Selecting and Sorting Members

      The data that is displayed in the final report is based upon the members that you select and the order in which you display them. In addition, you can use conditional retrievals to further refine selecting and sorting members.

      Selecting Members

      Member selection commands are extraction commands that select ranges of members based on database outline relationships, such as sibling, generation, and level. Using member selection commands ensures that any changes to the outline are automatically reflected in the report, unless you change the member name on which the member selection command is based. Attribute dimensions can be included in member selection commands.

      Task

      Report Command

      Select members from the same dimension as the dimension member.

      ALLINSAMEDIM

      Include siblings of the specified member.

      ALLSIBLINGS

      Include ancestors of the specified member.

      ANCESTORS

      Select a base dimension member based on its attributes.

      ATTRIBUTE

      Select members in the level immediately below the specified member.

      CHILDREN

      Include descendants of the specified member to the report, excluding the dimension top.

      DESCENDANTS

      Select level 0 members; that is, the members at the bottom of the dimension.

      DIMBOTTOM

      Include the top member of the dimension.

      DIMTOP

      Include a member and its ancestors.

      IANCESTORS

      Select the specified member and members in the level immediately below it.

      ICHILDREN

      Include the specified member and its descendants.

      IDESCENDANTS

      Include the specified member and its parent.

      IPARENT

      Include members from the same dimension and generation as the specified member.

      OFSAMEGEN

      Include members from the same dimension and on the same level as the specified member.

      ONSAMELEVELAS

      Include the parent of the specified member to the report.

      PARENT

      Extract data for a specified date or for a time period before or after a specific date.

      TODATE

      Include base dimension members associated with the specified attribute dimension.

      WITHATTR

      Selecting Members by Using Generation and Level Names

      Generation and level name selection commands identify a specific level or generation of members based on either of the following items:

      • The default generation or level name in the outline

      • The user-defined generation or level name in the outline

      When you use generation and level names, changes to the outline are automatically reflected in the report. You can define your own generation and level names or use the default names provided by Essbase. See Generations and Levels.

      Using generation or level names whenever possible makes the report easier to maintain. Because you do not have to specify a member name in the report, you need not change the report if the member name is changed or deleted from the database outline.

      Note:

      Generation and level names are standalone commands. You cannot use them in place of member names in report extraction or formatting commands such as <DESCENDANTS or <CHILDREN.

      *  To use default level names, at the point in the script where you want to select a member by the default level name, use the format:

      Levn,dimensionName 

        where n is the level number.

        dimensionName is the name of the dimension from which you want to select the members.

        Note:

        Do not insert a space after the comma.

        For example, Lev1,Year selects all the level 1 members of the Year dimension.

        *  To use default generation names, at the point in the script where you want to select a member by the default generation name, use the format:

        Genn,dimensionName 

          where n is the generation number.

          dimensionName is the name of the dimension from which you want to select the members.

          Note:

          Do not insert a space after the comma.

          For example, Gen2,Year selects all the generation 2 members of the Year dimension.

          Note:

          These default generation and level names are not displayed in Outline Editor.

          Based on the Sample.Basic database, the following example uses the default generation name Gen2,Year to generate a report that includes the members Qtr1, Qtr2, Qtr3, and Qtr4 from the Year dimension.

          <PAGE(Product)
          <COLUMN(Year)
          <ROW (Measures)
          {OUTALTNAMES}
          Cola
          Gen2,Year
          Sales Profit
              !

          Resulting report:

                                   Cola Market Scenario
                             Qtr1     Qtr2     Qtr3     Qtr4
                           ======== ======== ======== ========   
          Sales              14,585   16,048   17,298   14,893
              Profit          5,096    5,892    6,583    5,206

          Selecting Duplicate Members

          The following Report Writer commands are available for use with duplicate member name outlines:

          Task

          Report Command

          Displays member names for any unique member names and a qualified name for any duplicate member names.

          REPQUALMBR

          Displays member names only for members of the dimension specified.

          REPMBR

          Displays member names followed by aliases.

          REPMBRALIAS

          Displays alias names followed by member names for members in the report output.

          REPALIAS

          Displays aliases followed by member names for members of the dimension specified.

          REPALIASMBR

          Displays a member identifier for duplicate member names.

          OUTPUTMEMBERKEY

          Selecting Dynamic Time Series Members

          You create and identify dynamic members in the database outline; they are members calculated only during user retrieval requests, such as generating a report script. The time dimension has a special Dynamic Time Series option. The Dynamic Time Series option has reserved the following generation names that you can define in the outline alias table:

          Generation Name

          Reserved Names

          Explanation

          History

          H-T-D

          History-to-Date

          Year

          Y-T-D

          Year-to-Date

          Season

          S-T-D

          Season-to-Date

          Period

          P-T-D

          Period-to-Date

          Quarter

          Q-T-D

          Quarter-to-Date

          Month

          M-T-D

          Month-to-Date

          Week

          W-T-D

          Week-to-Date

          Day

          D-T-D

          Day-to-Date

          See Applying Predefined Generation Names to Dynamic Time Series Members.

          Note:

          The database header message for the outline identifies the number of dynamic members that are enabled in the current outline.

          *  To select a Dynamic Time Series member, at the point in the script where you want to select a Dynamic Time Series member, use either:

            • <LATEST memberName

              where memberName is the name of the member in the time dimension.

              The <LATEST command, a global command, is applied to the entire report script and is an aggregation based on the lowest level member within the dimension.

            • reservedName(memberName)

              where reservedName is the reserved Dynamic Time Series generation name and the memberName is the name of the member in the time dimension.

              If you use this syntax to specify a Dynamic Time Series, the time series name is associated only to the member listed in the argument.

            When you run the report script, the members are dynamically updated and the information is incorporated into the final report.

            Note:

            You must enter the Dynamic Time Series string exactly as it is displayed in the database outline; you cannot create a string and incorporate it into the final report. You can create an alias table for the Dynamic Time Series members in the database outline and use the aliases instead of the predefined generation names.

            Selecting Members by Using Boolean Operators

            Boolean operators enable you to specify precise member combinations within a report—useful when dealing with large outlines. Use the AND, OR, and NOT Boolean operators, combined with extraction commands, to refine member selections within the report script.

            • AND, when all conditions must be met.

            • OR, when one condition of several must be met.

            • NOT, to choose the inverse of the selected condition.

            *  To create a Boolean expression using operators, at the point in the script where you want to use linking, enter the format:

            <LINK (extractionCommand [operator extractionCommand])

              where extractionCommand is the member selection command to retrieve data from, and operator is either the AND or OR operator.

              Note:

              Select members from the same dimension. All extraction command arguments must be enclosed in parentheses, as in the example above. NOT can be associated only with an extraction command and does not apply to the entire expression.

              You can use Boolean operators with member selection commands, such as UDA and wildcards. See the Oracle Essbase Technical Reference for a list of all valid extraction commands that can be used with the LINK command.

              Examples:

              <LINK ((<IDESCENDANTS("100") AND <UDA(Product,Sweet)) OR ONSAMELEVELAS "100"-10")

              selects sweet products from the “100” subtree, plus all products on the same level as “100-10.”

              <LINK ((<IDESCENDANTS("100") AND NOT <UDA (Product,Sweet)) OR ONSAMELEVELAS "100"-10")

              selects products that are not sweet from the “100” subtree, plus all products on the same level as “100-10.

              See the Oracle Essbase Technical Reference for additional examples of narrowing member selection criteria.

              Selecting Members by Using Substitution Variables

              Substitution variables act as global placeholders for information that changes regularly; you set the substitution variables on the server through Administration Services, MaxL, or ESSCMD, and assign a value to each variable. You can then change the value anytime, reducing manual changes to a report script. You must have the role of at least Database Manager to set substitution variables.

              For example, many reports are dependent on reporting periods; if you generate a report based on the current month, you must manually update the report script every month. With a substitution variable set on the server, such as CurMnth, you can change the assigned value each month to the appropriate time period. Essbase dynamically updates the information when you run the final report.

              See Using Substitution Variables for a comprehensive discussion about creating and changing substitution variables in the database outline. See the Oracle Essbase Technical Reference for information about the leading & character.

              You can set substitution variables at the following levels:

              • Server, providing access to the variable from all applications and databases on the server

              • Application, providing access to the variable from all databases within the application

              • Database, providing access to the specified database

              *  To use a substitution variable, at the point in the script where you want to use the variable, use the format:

              &variableName

                where variableName is the same as the substitution variable set on the server.

                For example,

                <ICHILDREN &CurQtr

                becomes

                <ICHILDREN Qtr1

                Note:

                The substitution variable must be accessible from the application and database against which you are running the report.

                Note:

                The variable name can be an alphanumeric combination whose maximum size is specified in Limits. You cannot use spaces or punctuation in the variable name.

                When you run the report script, Essbase replaces the variable name with the substitution value, and that information is incorporated into the final report.

                Selecting Members by Using Attributes

                Using attributes, you can select and report on data based on one or more characteristics of base members. You can group and analyze members of base dimensions according to their attributes. You can also perform crosstab reporting based on multiple attributes. Using the <ATTRIBUTE command, you can select all the base dimension members associated with an attribute. For example, you can query the Sample.Basic database on how many 12-ounce units of grape and orange juice were sold in New York during the first quarter.

                *  To select a member based on a specific attribute, at the point in the script where you want to select members based on a specific attribute, use the format:

                <ATTRIBUTE memberName

                  where memberName is the name of an attribute-dimension member; for example:

                  <ATTRIBUTE Bottle

                  returns all products packaged in bottles.

                  Attribute dimensions have members with the same name. For example, the attribute dimension Ounces and the attribute dimension Age each can have a member named 24. To ensure that a query returns correct results, specify the full attribute-dimension member name. The following format returns all products that are packaged in 24 oz. units:

                  <ATTRIBUTE Ounces_24

                  Attribute types can be text, numeric, date, and Boolean. See Understanding Attribute Types.

                  Selecting Members by Attribute Association

                  You can select all base dimension members associated with one or more attributes using the <WITHATTR command. For example, you can display all products associated with a member of the Pkg Type attribute dimension. At the point in the script where you want to select the members, enter the following syntax:

                  <WITHATTR (attributeDimensionName, "Operator", Value)

                  The following command returns all base dimension members that are associated with the attribute Small from the Population attribute dimension.

                  <WITHATTR (Population, "IN", Small)

                  The following command returns all base dimension members that are associated with the attribute 32 from the Ounces attribute dimension.

                  <WITHATTR (Ounces, "<", 32)

                  Note:

                  The <WITHATTR command can be used within the LINK command to refine member selections, as illustrated in the following example: <LINK ((<WITHATTR (Ounces, "<", 32) AND <WITHATTR ("Pkg Type", "=", Can))

                  Selecting Members by Date

                  You can extract attributes data for a specific date, for a period before a specific date, or for a period after a specific date using the <TODATE command. For example, you can extract information on all products that were introduced on December 10, 1996, before December 10, 1996, or after December 10, 1996. The <TODATE command must be used within the <WITHATTR command. For example, the following format returns data on all products that were introduced on December 10, 1996.

                  <WITHATTR ("Intro Date", "=", <TODATE ("mm-dd-yyyy", "12-10-1996")

                  The following format returns data on all products that were introduced before December 10, 1996.

                  <WITHATTR ("Intro Date", "<", <TODATE ("mm-dd-yyyy", "12-10-1996")

                  The following format returns data on all products that were introduced after December 10, 1996.

                  <WITHATTR ("Intro Date", ">", <TODATE ("mm-dd-yyyy", "12-10-1996")

                  Note:

                  The types of date format supported are mm-dd-yyyy or dd-mm-yyyy. The date must be between January 1, 1970 and January 1, 2038 (inclusive).

                  Selecting Members by Using UDAs

                  UDAs enable you to select and report on data based on a common characteristic. UDAs are useful when performing member selections from an outline with an unbalanced hierarchy (in which the members of a dimension do not have identical member levels). You can set UDAs on the server for characteristics such as color, size, gender, flavor, or any other common member characteristics. You must have Database Manager permissions to set UDAs on the server.

                  UDAs are different from attributes. UDAs are member labels that you create to extract data based on a particular characteristic, but you cannot use UDAs to group data, to perform crosstab reporting, or to retrieve data selectively. For data analysis, UDAs are not as powerful as attributes.

                  You can use the UDA command with Boolean operators to refine report queries. See Selecting Members by Using Boolean Operators for examples.

                  See Creating UDAs for information about creating and maintaining UDAs.

                  *  To select members based on a UDA, at the point in the script where you want to select members based on the UDA, use the format:

                  <UDA (dimensionName,"UDAstring")

                    where dimensionName is the dimension of the member that you select, and UDAstring is the UDA that is set on the server. The following example selects members of the Product dimension with the Sweet attribute.

                    <UDA (product,"Sweet")

                    When you run the report script, Essbase incorporates the UDA members into the final report.

                    Note:

                    You must type the UDA string exactly as it is displayed in the database outline; you cannot create a UDA string and incorporate it into the report script.

                    Selecting Members by Using Wildcards

                    You can use wildcards to select members, generation, or level names in a report script. If you use member names, Essbase searches the member and all descendants of that member. If you specify a generation or level name, Essbase searches only members of that generation or level.

                    Using wildcards reduces the member information needed for a script and simplifies script maintenance.

                    The following two types of wildcards are supported in Report Writer:

                    • Trailing asterisks (*) at the end of the string to search for common member properties

                    • Pattern-matching question marks (?) anywhere in the string to represent any single-character member property

                    *  To select members using a trailing wildcard, at the point in the script where you want to select members using a trailing wildcard, use the format:

                    <MATCH (memberName,"character*")

                      where memberName is the name of the member that you select, and character is the beginning character in the following member. Using the Sample.Basic database,

                      <MATCH (Year,"J*")

                      returns Jan, Jun, and Jul.

                      *  To select members using a pattern-matching wildcard, at the point in the script where you want to select members using a pattern-matching wildcard, use the format:

                      <MATCH (memberName,"???characters")

                        where memberName is the name of the member to select, and characters are the characters in the following member. Using the Sample.Basic database,

                        <MATCH (Product,"???-10")

                        returns 100-10, 200-10, 300-10, and 400-10.

                        Note:

                        In the Sample.Basic database example, three question marks represent the variable three characters in the string. If two question marks were used in the example, no matches were found. You can place question mark wildcards anywhere in the match string.

                        Suppressing Shared Members

                        In conjunction with the following items, you can suppress the display of later instances of shared members when you extract data for a report:

                        • Generation names

                        • Level names

                        • DIMBOTTOM command

                        • OFSAMEGEN command

                        • ONSAMELEVELAS command

                        Suppress shared members to eliminate unnecessary data duplication within the report.

                        *  To suppress shared members, at the point in the script from which you want to suppress a shared member, use:

                        <SUPSHARE 

                          <SUPSHARE suppresses the display of shared members for the duration of the report script. Use <SUPSHAREOFF to reset the display of shared members.

                          Selecting How Member Names are Displayed

                          Aliases make reports easier to read and help the reader focus on the data values rather than the meanings of member names. You can display members in a report by their aliases. For example, you can display page, column, and row names, such as Diet Cola or Caffeine Free Cola, rather than the corresponding member names 100-20 and 100-30.

                          Qualified member names help identify specific members in duplicate member databases. You can display the qualified member name when duplicate member names are encountered; for example, [State].[New York] and [City].[New York] uniquely identify the member name New York. For information about duplicate member names, see Creating and Working With Duplicate Member Outlines.

                          Task

                          Report Command

                          Display alias names for members of the specified dimension

                          <REPALIAS

                          Display alias names followed by member names

                          <REPALIASMBR

                          Display member names followed by alias names

                          <REPMBRALIAS

                          Display member names only for members of the dimension specified

                          <REPMBR

                          Display member names for unique member names and display qualified names for duplicate member names

                          <REPQUALMBR

                          Display member identifiers for duplicate member names in addition to member and alias name.

                          <OUTPUTMEMBERKEY

                          The first five commands in the table are format commands that can be applied to specific dimensions. They override one another. For example, when it encounters <REPALIASMBR Product, Report Writer displays the alias name in front of the member name. If <REPMBRALIAS Product is found later in the script, Report Writer then displays the member name in front of the alias name. For duplicate member outlines, the <OUTPUTMEMBERKEY command can be combined with any of the other commands in the table. The member identifier is included in the report output in addition to the member name, alias name, or both.

                          See the Oracle Essbase Technical Reference for command details.

                          Example Displaying a Member Name and Alias

                          You can display members in a report as a combination of the member name and its alias. Combining the member name and alias enables you to display more descriptive page, column, and row names, such as Diet Cola 100-20 or 100-30 Caffeine Free Cola.

                          *  To display member names and aliases, use the <REPALIASMBR command or REPMBRALIAS command in a report script.

                          This example uses <REPALIASMBR to display the alias name ahead of the member name:

                          <PAGE (Product, Measures)
                          <COLUMN (Scenario, Year)
                          Actual
                          <ICHILDREN Qtr1
                          <ROW (Market)
                          <IDESCENDANTS "300"
                          <REPALIASMBR Product
                              !

                            Resulting report:

                                             Dark Cream 300-10 Measures Actual 
                                                  Jan      Feb      Mar     Qtr1 
                                             ======== ======== ======== ======== 
                            Market                800      864      880    2,544 
                            
                                             Vanilla Cream 300-20 Measures Actual   
                                                  Jan      Feb      Mar     Qtr1 
                                             ======== ======== ======== ======== 
                            Market                220      231      239      690 
                            
                                             Diet Cream 300-30 Measures Actual 
                                                  Jan      Feb      Mar     Qtr1 
                                             ======== ======== ======== ======== 
                            Market                897      902      896    2,695 
                            
                                                Cream Soda 300 Measures Actual 
                                                  Jan      Feb      Mar     Qtr1 
                                             ======== ======== ======== ======== 
                            Market              1,917    1,997    2,015    5,929

                            Sorting Members

                            When you sort the members you include in a report, be aware that sorting commands affect members differently, depending on whether they are referenced by member selection commands or by static member definitions. Report Writer commands sort members by member name or data values.

                            Member selection commands such as <CHILDREN and <DESCENDANTS, select members in the order specified by the database outline. By default, a report that includes member selection commands displays members in their hierarchical database outline order. You can override this default by specifying a sort order with a sort command.

                            Because sort commands affect the order of the members selected by the member selection commands, they must precede any member selection commands to which they apply. If you specify a sort command, the sort order is preserved until another sort command overrides it.

                            Sort commands modify member selection commands, such as <CHILDREN and <DESCENDANTS. Sort commands do not perform final sorting of rows during formatting. Be careful when you place a sort command in the report script that you do not start the sort too soon, and that you override it to turn it off, if necessary, before the next selection command.

                            Sort commands have no effect on static member definitions.

                            Task

                            Report Command

                            Sort members alphabetically by the alias name of the member, if aliases are used in the report script.

                            SORTALTNAMES

                            Sort following members in ascending order starting with the lowest generation and moving toward the highest generation.

                            SORTASC

                            Sort following members in descending order starting with the highest generation and moving toward the lowest generation.

                            SORTDESC

                            Sort following members according to the generation of the member in the database outline.

                            SORTGEN

                            Sort following members according to the level of the member in the database outline.

                            SORTLEVEL

                            Sort members alphabetically by member name.

                            SORTMBRNAMES

                            Disable all previous sorting commands so that members added to the report follow the normal hierarchical order based on the database outline.

                            SORTNONE

                            For a list of sorting commands syntax and descriptions, see the Oracle Essbase Technical Reference.

                            Restricting and Ordering Data Values

                            Several Report Writer commands let you perform conditional retrieval and data sorting in reports.

                            Task

                            Report Command

                            Specify the number of rows to return. These rows must contain the top values of a specific data column.

                            TOP

                            Specify the number of rows to return. These rows must contain the lowest values of a specific data column.

                            BOTTOM

                            Specify the conditions the columns of a data row must satisfy before the row is returned.

                            RESTRICT

                            Specify the ordering of the rows of a report, based on the data values of data columns.

                            ORDERBY

                            For the syntax, definitions, and detailed examples of these commands, see the Oracle Essbase Technical Reference.

                            Configurable variables are used during conditional retrievals. For information about setting the Report Writer configurable variables, see Changing Buffer Size.

                            Understanding the Order of Operation

                            <RESTRICT, <ORDERBY, <TOP, and <BOTTOM can be displayed anywhere in the report script and in any order. When using these commands, place all global script formatting commands before a Page member or a Column member, or before a <PAGE command or <COLUMN command that expands into Page or Column members (for example, IDESCENDANTS, or ICHILDREN).

                            Essbase extracts data and applies restrictions and ordering in the following order:

                            1. Applies RESTRICT and any existing restrictive option such as SUPPMISSING, SUPZEROS, and SUPEMPTYROWS.

                            2. Applies TOP or BOTTOM, or both.

                            3. Applies ORDERBY.

                            Essbase then returns rows and displays output.

                            Using TOP, BOTTOM, and ORDERBY with Sorting Commands

                            <TOP, <BOTTOM, and <ORDERBY commands sort the output of a report by its data values. Essbase applies <TOP and <BOTTOM first, followed by <ORDERBY. If the report contains a sort command, such as <SORTMBRNAMES, which sorts members and not data, Essbase applies the sort command first, followed by <TOP and <BOTTOM, and then <ORDERBY. <ORDERBY is the final sort.

                            Using RESTRICT

                            The arguments of the <RESTRICT command let you specify qualifications for selecting rows. Essbase includes only qualified rows in the resulting report output.

                            <RESTRICT works only on the range of rows that you specify in a row member selection.

                            Essbase processes the restrictions from left to right, and does not allow grouping with parentheses in the list of arguments.

                            For example, the following example is not a valid syntax:

                            RESTRICT (... (@DATACOL(1) > 300 AND @DATACOL(2) < 600)...) 

                            Use only one <RESTRICT per report, as terminated by the ! command. If a report script contains more than one report, each <RESTRICT overwrites the one in the previous report. For example:

                            RESTRICT (@DATACOL(1) > @DATACOL(2) AND 800 < @DATACOL(3) 
                            OR @DATACOL(4) <> #MISSING)

                            This <RESTRICT command is equivalent in operation to the following syntax:

                            RESTRICT (((@DATACOL(1) > @DATACOL(2)) AND (800<@DATACOL(3))) OR (@DATACOL(4) <> #MISSING))

                            Using ORDERBY

                            The <ORDERBY command orders the output rows according to the data values in the specified columns. Using an optional direction argument to the ORDERBY command, you can specify either an ascending order using the ASC flag, or descending order using the DESC flag. You can specify different sorting directions in different columns of the same report. If no direction argument is used, ascending (ASC) is the default order.

                            To determine the set of rows to be ordered, specify the row grouping dimension in the command. The default row grouping is the innermost row dimension.

                            Only one <ORDERBY is allowed per report, as terminated by the ! command. If a report script contains more than one report, each <ORDERBY overwrites the one in the previous report.

                            Using ORDERBY with Formatting Commands

                            <ORDERBY command guidelines:

                            • Avoid using row formatting commands when you are using <ORDERBY in a report. Formatting commands scheduled for a given point in the report may show up unexpectedly because <ORDERBY shifted the row that contained the member with formatting.

                            • In general, avoid using row formatting commands, and place overall script formatting before column members or commands that expand the column members (such as “ICHILDREN column dimension, <column ..., column member”).

                            Using TOP and BOTTOM

                            The <TOP and <BOTTOM commands specify the qualified number of rows with the highest or lowest column values, respectively, within a row group to be returned in a report. If the row group member is not specified, the innermost row group dimension is the default row group.

                            You can use <TOP and <BOTTOM together in the same report, but only one <TOP and one <BOTTOM is allowed per report. In this case, the two commands should have the same data column as their argument in order to prevent confusion. The result of the <TOP and <BOTTOM command is sorted by the value of the data column specified in the command in descending order.

                            <TOP and <BOTTOM work only on the range of rows specified in row member selection.

                            Note:

                            If <TOP or <BOTTOM occurs with <ORDERBY, the ordering column of the <ORDERBY does not have to be the same as the data column of the <TOP or the <BOTTOM.

                            If any combination of the <ORDERBY, <TOP, or <BOTTOM commands exist together in a report script, the row group member (<rowGroupMember>) should be the same. This restriction removes any confusion about the sorting and ordering of rows within a row group.

                            Caution!

                            Essbase discards rows that contain #MISSING values in their sorting column from the set of extracted data rows before the applying the TOP or BOTTOM sort.

                            For example, this command returns two rows with the highest data values in col2 (Actual, Qtr2) per row group:

                            1- TOP (2, @DATACOL(2))

                            When you run this command against the Sample.Basic database, the row grouping is Product, which implies that for Florida, the report returns 100-10 and 100-30 product rows, and for Maine, the report returns 100-10, 100-40 product rows, and so on.

                                                        Actual              Budget
                                                   Qtr1       Qtr2       Qtr1    Qtr2   
                            Florida     100-10      570       670        570     650 
                                        100-20      235       345        321     432 
                                        100-30      655       555        455     865 
                                        100-40      342       342        432     234 
                            Maine       100-10      600       800        800     750 
                                        100-20      734       334        734     534 
                                        100-30      324       321        235     278 
                                        100-40      432       342        289     310 
                            New York    100-10     1010      1210       1110     910 
                                        100-20      960       760        650     870 
                                        100-30      324       550        432     321 
                                        100-40      880       980        880    1080 
                                        100-50      #MI       #MI        #MI     #MI 

                            This example returns rows with the highest data values in col2 (Actual, Qtr2) per report, because the row grouping is the “market.”

                            2- TOP("market", 3, @DATACOL(2))

                            Resulting rows:

                            New York    100-10     1010     1210     1110     910   
                                        100-40      880      980      880    1080 
                            Maine       100-10      600      800      800     750 

                            This example returns two rows with the lowest data values in col2 (Actual, Qtr2) per row group.

                            3- BOTTOM ("market", 2, @DATACOL(2))

                            Resulting rows:

                            Maine       100-20      734       334       734      534   
                                        100-30      324       321       235      278 

                            Note:

                            <TOP and <BOTTOM put an upper limit on the number of (qualified) rows returned after all restrictions are applied. This upper limit equals the number of rows in the <TOP plus the number of rows in the <BOTTOM commands.

                            Understanding How Other Report Configurations Affect TOP and BOTTOM

                            When using the <TOP and <BOTTOM commands, be aware that some other commands affect their operation. In particular, Essbase treats the <SUPPMISSING, <SUPZEROS, and <SUPEMPTYROWS options as restrictions and applies them to the extracted rows along with the <RESTRICT command restrictions. Essbase applies these optional restrictions to the data rows before processing the <TOP or <BOTTOM commands, and before applying an <ORDERBY command.

                            Using TOP and BOTTOM with Formatting Commands

                            Whenever a formatting command occurs in a report, it is appended to the member that follows it. For example, in this sequence, {UCOLUMNS}, the underline columns command is appended internally to the member that comes next. In Script 1, it is appended to the row member that can be described as “first child of market, assuming Florida.”

                            SCRIPT 1                 SCRIPT 2 
                            ....                     .... 
                            < ROW Market             {UCOL} 
                            {UCOL }                  < Florida    (row member)   
                            <ICHILDREN Market
                            < TOP ....               < BOTTOM .... 

                            Script 2, appends {UCOLUMNS} to the row member Florida. Essbase executes {UCOLUMNS} whenever it encounters a row that has row member Florida. If the TOP or BOTTOM command returns a row that does not contain Florida, the formatting commands appended to the rows are never executed.

                            Therefore, it is a good idea to place all general formatting commands before a <COLUMN command, or a command that expands into column members to guarantee that the formatting is executed. However, do not use formatting commands that work on rows, because these rows may never be picked up by the <TOP or <BOTTOM command. Also avoid using <SAVEROW and <CALCULATE ROW with the <TOP and <BOTTOM commands.

                            Converting Data to a Different Currency

                            If the database has a currency partition, you can calculate currency conversions in report scripts. Use the <CURRENCY command to set the output currency and currency type. Use the <CURHEADING command to display the currency conversion heading.

                            Note:

                            Currency conversion is not supported across transparent partitions.

                            For information about creating a currency conversion application, see Building Currency Conversion Applications and Performing Conversions.

                            For the syntax and definitions of Report Writer commands, see the Oracle Essbase Technical Reference.

                            Generating Reports Using the C, Visual Basic, and Grid APIs

                            Task

                            C API Function

                            Visual Basic API Function

                            C Grid API Function

                            Start sending a report specification to the active database.

                            ESSBEGINREPORT

                            ESBBEGINREPORT

                            ESSGBEGINREPORT

                            Mark the end of a report specification being sent to the active database.

                            ESSENDREPORT

                            ESBENDREPORT

                            N/A

                            Send a report specification to the active database as a single string.

                            ESSREPORT

                            ESBREPORT

                            N/A

                            Send a report specification to the active database from a file.

                            ESSREPORTFILE

                            ESBREPORTFILE

                            ESSGREPORTFILE

                            See the Oracle Essbase API Reference for descriptions and syntax.