Developing Report Scripts

Once 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 some of the data values' display. Formatted data values are displayed in the report when you run the script, based on the combined extraction and report 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:

• Let you customize the format and appearance of a report and create report-time calculations. Formatting commands are generally enclosed inside left and right braces ({}), although there are several formatting commands that begin with the < character.
• Are either applied globally within the report script or are specific to a member.

This chapter provides information about creating complex report scripts, including:

• Syntax Guidelines
• 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

For fundamental information about reports and report scripts, see Quick Start to Report Scripts.

Syntax Guidelines

To build a report, you enter commands that define the layout, member selection, and format you want in the Application Manager's Report Editor. When you write a report script, follow these guidelines:

• Separate commands with at least one space, tab, or new line. Report processing is not affected by extra blank lines, spaces, or tabs.
• Enter commands in either 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 a single 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").
  • Names containing one or more numerals at the beginning of the name (for example, "100-Blue")
  • Names containing any of the following characters:

    *	(asterisks)
    @	(at signs)
    []	(brackets)
    :	(colons)
    ,	(commas)
    >{}	(braces)
    -	(dashes, hyphens, or minus signs)
    <	(less than signs)
    ()	(parentheses)
    +	(plus signs)
    /	(slashes)
    ;	(semicolons)

• 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}.
• Use // (double slash) 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 multi-line comments.

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.

For information about formatting the page, column, or row headings, see Formatting Page, Column, and Row Headings.

Creating Page, Column, and Row Headings

1. Type <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. For example,

   <PAGE (Measures, Market)
   

2. Press Enter.
3. Type <COLUMN(dimensionname), where dimensionname equals the name of each dimension to display across the page. For example,

   <COLUMN (Year)
   

   You can add dimension names to create nested column headings.

4. Press Enter.
5. Type <ROW(dimensionname), where dimensionname equals the name of each dimension to display down the page. For example,

   <ROW (Market)
   

6. Press Enter.

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:

For information about suppressing the display of headings in the report, see Suppressing Page, Column, and Row Formatting.

Creating Symmetric and Asymmetric Reports

Hyperion Essbase reports can contain symmetric or asymmetric column groups. Hyperion 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, Hyperion Essbase creates a symmetric report unless you select the same number of members for all column dimensions.

See Examples of Report Scripts for an example of an asymmetric report.

The Hyperion Essbase evaluation of symmetry versus asymmetry takes place prior to 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 Hyperion 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 only need to change 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 like 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 like those used in symmetric reports. An asymmetric report uses the <BLOCKHEADERS mode of column layout.

Formatting

Formatting commands define the format of data and labels in the final report. These commands are generally enclosed in left and right braces ({}).

The two types of formatting commands are global and member-specific commands.

• 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 a additional rows to skip lines, you must use the SKIP command again.

Formatting Report Pages

There are a number of formatting commands that you can use to design the look of your final report pages.

See Examples of Report Scripts for report formatting examples.

Setting the Page Length, Width, and Centering

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

Inserting Page Breaks

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

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 only two columns are set up, Actual and Budget. The{DECIMAL}command, however, refers to a column three, which does not yet exist. Hyperion 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).

Hyperion 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)

Accommodating Long Column and Row Names

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

There are a several ways to modify your columns to display the entire member name.

Suppressing Page, Column, and Row Formatting

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

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.

Formatting Reports with Tab Delimiters

You can place tabs between columns rather than spaces in your report scripts. This is useful when you want to export report output into another form.

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

When you save the report script, Hyperion Essbase automatically replaces the spaces with tabs. When you view the report in the Report Viewer, black squares indicate tab marks.

Figure 38-1: Tab-Delimited Report

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 Examples of Report Scripts.

Totaling Columns

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

The following table summarizes column calculation commands:

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 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 the standard mathematical operations. For syntax and parameter descriptions, see the online Technical Reference in the DOCS directory.

If you use the same name for more than one column, Hyperion 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 could, for instance, 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 two or more 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 do this, insert the tilde character (~) 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 not defined.

In cases where there are fewer column header dimensions than the number of levels that you want, you can create multi-line 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 online Technical Reference in the DOCS directory.

Numbering Columns

If the number of regular (non-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. For example:

• If the first section of a report has 12 columns (including row name columns), and 3 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 6, then the regular columns are columns 0-5, and the same calculated columns are columns 6-8.
• Likewise, 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 columns names. The column numbering for a report with two different sections with varying numbers of regular columns would be:

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 would occur 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:

For the syntax and definitions of row calculation commands, see the online Technical Reference in the DOCS directory.

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.
• Hyperion Essbase does not support nested (parenthetical) expressions.
• Hyperion 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.

If you specify an operator, it applies to subsequent output rows and stores the result in the calculated row. This 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 online Technical Reference in the DOCS directory.

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

You can use a variety of formatting commands to customize how data displays in your final report.

Underlining

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

Suppressing Data Formatting

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

See Suppressing Page, Column, and Row Formatting for information about suppressing the display of page, column, and row formats.

Indenting

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

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 such valuable information about a report as:

• A date and time stamp
• The user ID 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 predefined keywords that automatically display information in the report
• A text string that you define

See the online Technical Reference in the DOCS directory 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 any text string that you want to display in the data cells.

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

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 any text string that you want to display in the data cells.

Adding Blank Spaces

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

Changing How Data Values Display

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

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 the selection and sorting of 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 your report, unless you change the member name on which the member selection command is based. Attribute dimensions can be included in member selection commands.

Selecting Members Using Generation and Level Names

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

• 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 your report. You can either define your own generation and level names, or you can use the default names provided by Hyperion Essbase. For information on generations and levels, see Introducing Dynamic Dimension Building.

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

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 following format:

Levn,DimName 

where n is the level number.

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

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 following format:

Genn,DimName 

where n is the generation number.

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

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

The following example is based on the Sample Basic database. It 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
    !

The report script produces the following 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 Dynamic Time Series Members

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

See Creating Applications and Databases for information about creating and maintaining Dynamic Time Series generation names in the database 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 of the following formats:

<LATEST memberName 

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

The <LATEST command is a global command that 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.

Selecting Members Using Boolean Operators

Boolean operators let you specify precise member combinations within your report, which is particularly useful when dealing with large outlines. Use the AND, OR, and NOT Boolean operators, combined with extraction commands, to refine your member selections within the report script.

• Use the AND operator when all conditions must be met.
• Use the OR operator when one condition of several must be met.
• Use the NOT operator 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 following 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.

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

Examples:

<LINK ((<IDESCENDANTS("100") AND <UDA(Product,Sweet)) OR <LEV 
(product,0))

selects sweet products from the "100" sub-tree, plus all level 0 products.

<LINK ((<IDESCENDANTS("100") AND NOT <UDA (Product,Sweet)) OR 
<LEV (product,0))

selects non-sweet products from the "100" sub-tree, plus all level 0 products.

See Examples of Report Scripts for other examples of narrowing member selection criteria.

Selecting Members Using Substitution Variables

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

For example, many reports are dependent on reporting periods; if you generate a report based on the current month, you would have to 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. Hyperion Essbase dynamically updates the information when you run the final report.

See Creating Applications and Databases for information about creating and changing substitution variables in the database outline. See the online Technical Reference in the DOCS directory, 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:

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

At the point in the script where you want to use the variable, use the following format:

&variablename

where variablename is the same as the substitution variable set on the server. For example,

<ICHILDREN &CurQtr

becomes

<ICHILDREN Qtr1

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

Selecting Members 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 two or more 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 flavored juice and orange flavored 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 following format:

<ATTRIBUTE (mbrName)

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

<ATTRIBUTE (Bottle)

returns all products packaged in bottles.

There can be cases where attribute dimensions have members with the same name. For instance, the attribute dimension Ounces and the attribute dimension Age can each have a member named 24. To ensure that a query returns correct results, you must specify the full attribute dimension member name, as set in the Attribute Member Name dialog box in the Hyperion Essbase Application Manager. The following format:

<ATTRIBUTE (Ounces_24)

returns all products that are packaged in 24 oz. units.

Attribute types can be text, numeric, date, and Boolean. See Working with Attributes for more information.

Selecting Members Associated with a Specific Attribute

You can select all the 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 (AttrDimName, "Operator", Value)

For example:

<WITHATTR (Population, "IN", Small)

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

<WITHATTR (Ounces, "<", 32)

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

<LINK ((<WITHATTR (Ounces, "<", 32) AND <WITHATTR ("Pkg Type", 
"=", Can))

For more information on the syntax for the <ATTRIBUTE and <WITHATTR commands, see the online Technical Reference in the DOCS directory.

Selecting Members by a Specific 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:

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

returns data on all products that were introduced on December 10, 1996.

The following format:

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

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

The following format:

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

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

For more information on the syntax for the <TODATE command, refer to the Report Writer commands in the Quick Technical Reference.

Selecting Members Using User-Defined Attributes

A user-defined attribute (UDA) enables you to select and report on data based on a common characteristic. These attributes are particularly useful when performing member selections from an outline with an unbalanced hierarchy (a hierarchy where 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 Designer privileges 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. Hence, for data analysis, UDAs are not as powerful as attributes.

You can use the UDA command in conjunction with Boolean operators to refine report queries further. See Selecting Members Using Boolean Operators for more information.

See Creating Applications and Databases 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 following format:

<UDA (dimName,"UDAstring")

where dimName is the dimension of the member that you select, and UDAstring is the UDA that is set on the server. For example:

<UDA (product,"Sweet")

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

Selecting Members Using Wildcards

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

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

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

• Trailing asterisk (*) characters at the end of the string to search for common member properties
• Pattern-matching question mark (?) characters 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 following format:

<MATCH mbrName,"charName*")

where mbrName is the name of the member that you select, and charName 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 following format:

<MATCH (mbrName,"???charNames")

where mbrName is the name of the member to select, and charNames 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.

Selecting Members Using Static Member Names

Static member names are non-changing member names, such as Sales and COGS, that you enter directly into the report script. Although they are easy to establish, static member name definitions can be difficult to maintain if your database outline changes. You must change the member name in every script that is used with that database.

In general, report scripts are easier to maintain if you use generation and level names, or member selection commands, rather than static member names whenever possible. For example, when you remove a product, such as Widgets, from the dimension, the member selection command <children Product works, but a specific list referencing Widgets as a static member name generates the following error message:

"Unknown Member [Widgets]."

Static members tend to be particularly difficult to maintain when dealing with distributed OLAP databases, where both source and target databases must be consistent.

The following report script uses a static member called ProductGroups to select all the Level 1, Product member names from the Sample Basic database.

<PAGE(Year)
<COLUMN(Product)
<ROW (Measures)
Qtr1
ProductGroups
Sales Profit
    !

The report script produces the following report:

Qtr1 Market Scenario 100 200 300 400 Diet ======== ======== ======== ======== ======== Sales 25,048 26,627 23,997 20,148 25,731 Profit 7,048 6,721 5,929 5,005 7,017

When you run a report that includes static member definitions, the report displays members in order of their definition in the report script by member name. Sort commands have no effect on static member definitions. See Sorting Members for more information about the effects of sorting members.

Suppressing Shared Members

You can suppress the display of duplicate shared members when you extract data for your report. You can only suppress shared member display in conjunction with the following:

• Generation names
• Level names
• DIMBOTTOM command
• OFSAMEGEN command
• ONSAMELEVELAS command

Suppressing shared members is useful when you want to eliminate unnecessary duplication of data within the report.

To suppress shared members:

At the point in the script where you want to suppress a shared member, type

<SUPSHARE 

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

See Suppressing Page, Column, and Row Formatting for more information about suppressing data from your final report.

Selecting Alias Names for Members

Aliases make reports easier to read and help your 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.

For a complete listing of alias commands, see the online Technical Reference in the DOCS directory.

To Display a Member Name and Alias

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

To display a member name and alias, use the <OUTALTNAMES and <OUTALTMBR commands together in a report script, as shown in the following example:

<PAGE (Product, Measures)
<COLUMN (Scenario, Year)
{OUTALTNAMES}
<OUTALTMBR
Actual
<ICHILDREN Qtr1
<ROW (Market)
<IDESCENDANTS "300"
    !

The report script produces the following 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 your 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 either 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 any final sorting of rows during formatting. Be careful when you place a sort command in your 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.

For a list of sorting commands syntax and descriptions, see the online Technical Reference in the DOCS directory.

Restricting and Ordering Data Values

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

For the syntax and definitions of these commands, see the online Technical Reference in the DOCS directory. For detailed examples using these commands, see Examples of Report Scripts.

Configurable variables are used during conditional retrievals. For information about setting the Report Writer configurable variables, see Optimizing Your Reports.

Defining 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).

Hyperion 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 and/or BOTTOM.
3. Applies ORDERBY.

Hyperion Essbase then returns rows and displays output.

See Quick Start to Report Scripts for more information.

Using TOP, BOTTOM, and ORDERBY with Sorting Commands

<TOP, <BOTTOM, and <ORDERBY commands sort a report output by its data values. Hyperion 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, Hyperion Essbase applies the sort command first, followed by <TOP and <BOTTOM, and then <ORDERBY. <ORDERBY is the final sort that takes place.

Restricting Data Ranges

The arguments of the <RESTRICT command let you specify qualifications for selecting rows. Hyperion 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.

Hyperion 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)...) 

Only one <RESTRICT is allowed 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))

Ordering Data

The <ORDERBY command orders the output rows according to the data values in the specified columns. You can specify either ascending <ASC (the default) or descending <DESC.You can specify different sorting directions in different columns of the same report.

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

Follow these guidelines when using the <ORDERBY command in a report script:

• 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").

Specifying Rows to Return

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.

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:

Hyperion 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))

This command returns the following 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))

This command returns the following 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 is equal to the number of rows in the <TOP plus the number of rows in the <BOTTOM commands.

How Other Report Configurations Affect Row Specifications

When using the <TOP and <BOTTOM commands, be aware that some other commands affect their operation. In particular, Hyperion Essbase treats the <SUPPMISSING, <SUPZEROS, and <SUPEMPTYROWS options as restrictions and applies them to the extracted rows along with the <RESTRICT command restrictions. Hyperion 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. Hyperion 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.

Because of this, it is a good idea to place all general formatting commands before a <COLUMN command, or a command that expands into column members. This guarantees that the formatting is executed. However, you should 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 your 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.

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

For the syntax and definitions of Report Writer commands, see the online Technical Reference in the DOCS directory.

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

Use the following table to determine the report API calls that you can make:

See the online API Reference in the DOCS directory for syntax and descriptions of these API functions.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.