Quick Start to Report Scripts

A Hyperion Essbase Application Manager report lets you retrieve formatted summaries from a database.

There are several ways that you can report on the data in your database:

• Use the Application Manager's Report Writer to create a report script and run your report, as explained in this chapter.
• Generate database reports through a spreadsheet. You can use report commands in a spreadsheet in Free-Form mode or in Template Retrieval mode. For information on reporting through your spreadsheet interface, see your Hyperion Essbase Spreadsheet Add-in User's Guide.
• Use Hyperion Essbase APIs. See Developing Report Scripts for more information about using report APIs or the online API Reference in the DOCS directory for syntax and technical descriptions.
• Use third-party reporting tools such as Crystal Info for Hyperion Essbase (sold separately from Hyperion Essbase).

Use Report Writer when you need to create large-scale reports consisting of many pages of multidimensional data. Reports of this scale often can exceed the capabilities of even the most robust spreadsheet. You can use Application Manager to create report scripts and run reports, or you can schedule report scripts to run in batch mode, using either the MaxL language interface or the ESSCMD command-line interface. See the online Technical Reference in the DOCS directory for information about MaxL or ESSCMD.

Report Writer commands let you define formatted reports, export data subsets from an Hyperion Essbase database, and produce free-form reports.

This chapter provides fundamental information about reports and report scripts, including:

• Creating a Simple Report Script
• Parts of Report Scripts and Reports
• Planning a Report
• Security and Multi-User Issues
• Creating and Editing Report Scripts
• Running Report Scripts
• Developing Free-Form Reports

For information about creating complex report scripts, see Developing Report Scripts.

Creating a Simple Report Script

When you combine report commands that include page, row, and column dimension declarations with selected members, you have all the elements of a simple report script.

The following step-by-step example of a report script specifies these elements, dimensions, and member selection commands. It includes comments, which document the behavior of the script, and the ! output command. This example is based on the Sample Basic database, which is supplied with your Hyperion Essbase OLAP Server installation.

1. Start Application Manager, and connect to your Hyperion Essbase server.
2. Select the Sample application and the Basic database, and click the Report Scripts button.

   If you do not have the Sample Basic database installed, contact your Hyperion Essbase administrator.

3. Click the New Button to open the Report Editor.
4. Type the following information, with the exception of the commented (//) lines, which are for your reference:

   // This is a simple report script example 
   // Define the dimensions to list on the current page, as below
   <PAGE (Market, Measures) 
   

   // Define the dimensions to list across the page, as below 
   <COLUMN (Year, Scenario) 
   

   // Define the dimensions to list down the page, as below 
   <ROW (Product) 
   

   // Select the members to include in the report 
   Sales 
   <ICHILDREN Market 
   Qtr1 Qtr2 
   Actual Budget Variance 
   <ICHILDREN Product 

   // Finish with a bang
       ! 
   

5. Choose File > Save, and type Myrept1 for the report script object name, and save it on the server (the default).
6. Choose Report > Run.

When you run this report against the Sample Basic database, the script produces the following report:

                                       East Sales 
                              Qtr1                       Qtr2 
                   Actual   Budget  Variance   Actual  Budget  Variance 
                 ======== ======== ======== ======== ======== ======== 

100                 9,211    6,500    2,711   10,069    6,900    3,169 
200                 6,542    3,700    2,842    6,697    3,700    2,997 
300                 6,483    4,500    1,983    6,956    5,200    1,756 
400                 4,725    2,800    1,925    4,956    3,200    1,756 
  Product          26,961   17,500    9,461   28,678   19,000    9,678 

                                       West Sales 
                              Qtr1                       Qtr2 
                   Actual   Budget  Variance   Actual  Budget  Variance 
                 ======== ======== ======== ======== ======== ======== 

100                 7,660    5,900    1,760    7,942    6,500    1,442
200                 8,278    6,100    2,178    8,524    6,200    2,324 
300                 8,599    6,800    1,799    9,583    7,600    1,983 
400                 8,403    5,200    3,203    8,888    6,300    2,588 
  Product          32,940   24,000    8,940   34,937   26,600    8,337 

                                      South Sales 
                              Qtr1                       Qtr2 
                   Actual   Budget  Variance   Actual  Budget  Variance 
                 ======== ======== ======== ======== ======== ======== 

100                 5,940    4,100    1,840    6,294    4,900    1,394 
200                 5,354    3,400    1,954    5,535    4,000    1,535 
300                 4,639    4,000      639    4,570    3,800      770 
400              #Missing #Missing #Missing #Missing #Missing #Missing 
  Product          15,933   11,500    4,433   16,399   12,700    3,699 

                                     Central Sales
                              Qtr1                       Qtr2
                   Actual   Budget  Variance   Actual  Budget  Variance 
                 ======== ======== ======== ======== ======== ======== 

100                 9,246    6,500    2,746    9,974    7,300    2,674 
200                 7,269    6,800      469    7,440    7,000      440 
300                10,405    6,200    4,205   10,784    6,800    3,984 
400                10,664    5,200    5,464   11,201    5,800    5,401 
  Product          37,584   24,700   12,884   39,399   26,900   12,499 

                                      Market Sales 
                              Qtr1                       Qtr2 
                   Actual   Budget  Variance   Actual  Budget  Variance 
                 ======== ======== ======== ======== ======== ======== 

100                32,057   23,000    9,057   34,279   25,600    8,679 
200                27,443   20,000    7,443   28,196   20,900    7,296 
300                30,126   21,500    8,626   31,893   23,400    8,493 
400                23,792   13,200   10,592   25,045   15,300    9,745 
  Product         113,418   77,700   35,718  119,413   85,200    34,21 

For information about Report Writer command syntax and definitions, see the online Technical Reference in the DOCS directory. If you are using the Report Editor, choose the Help menu item to consult the Application Manager Help.

To create report scripts in MaxL, use the export database statement. For example, export database sample.basic using report_file 'C:\Hyperion\Essbase\App\Sample\Basic\asym.rep' to data_file 'c:\month2.rpt';

To create report scripts in ESSCMD: Use the REPORTLINE command to execute a single report line. For example, REPORTLINE "<DESCENDANTS Year !" Use the REPORT command to execute one or more report lines. For example, REPORT <IDESCENDANTS Year <ICHILDREN Market ! See the online Technical Reference in the DOCS directory for information about this command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.

Parts of Report Scripts and Reports

The Hyperion Essbase Report Writer consists of three main components:

• Report Editor is an ASCII text editor that you use to write the report script. The Report Editor features a text editing window and customized menus. Saved report scripts have the file extension .REP.
• Report Extractor retrieves the data information from the Hyperion Essbase database when you run a report script.
• Report Viewer displays the complete report. Saved reports have the file extension.RPT.

  Figure 37-1: Report Writer Components

  

How the Report Extractor Retrieves Data

The Report Extractor processes the report script and retrieves data in the following order:

1. Composes the member list, based on all possible member combinations. For example, the following command retrieves member East and all of its descendants:

   <IDESCENDANTS East 
   

2. Applies member restrictions. For example, the following command refines the member selection:

   <LINK 
   

3. Orders the member output. For example, the following command determines the order in which members are sorted:

   <SORT 
   

4. Extracts data from the following areas:
   • Local regions
   • Partitioned regions
   • Dynamically calculated data
5. Restricts data. For example, the following command suppresses the display of all rows that contain only missing values:

   {SUPPRESSMISSINGROWS} 
   

6. Sorts data. For example, the following command returns rows with the highest values of a specified data column:

   <TOP 
   

7. Formats output. For example, the following command skips one or more lines in the final output report:

   {SKIP} 
   

The order in which the Report Extractor retrieves data is important when using complex extraction and formatting commands. For example, because the Extractor restricts data (step 5) before sorting data (step 6), if you place conditional retrieval commands in the wrong order, the report output results could be unexpected. Be aware of the data retrieval process when designing your report scripts.

Parts of a Report

Understanding the parts of a report is essential as you plan and design your own reports. A typical report is composed of the following parts:

• Page Headings--list dimensions represented on the current page. All data values on the page have the dimensions in the page heading as a common property.

  <PAGE (Market, Measures)
  

• Column Headings--list members across a page. You can define columns that report on data from more than one dimension, which results in nested column headings.

  <COLUMN (Year, Scenario)
  

• Row Headings--list members down a page. You can define a member list that includes rows from more than one level within a dimension or from more than one dimension. The rows are indented below the dimension name.

  <ROW (Product)
  

• Titles--contain user-defined text, date and time stamp, the user ID of the person running the report, page numbers, the name of the source database, or any other descriptive information. Titles are user-generated and optional, whereas page, column, and row headings are automatically generated, because they are necessary to clearly describe the data on the report page.

  { STARTHEADING 
  TEXT   1 "Prepared by:" 
        14 "*USERNAME" 
         C "The Electronics Club" 
        65 "*PAGESTRING" 
  TEXT  65 "*DATE" 
  SKIP 
  ENDHEADING }
  

• Data values--are the values contained in the database cells; they are the lookup results of member combinations or the results of calculations when the report is run through the Report Extractor. Each data value is the combination of the members in the page heading, column heading, and row name.

  All data values in a row share the properties of the row names of that row. A report can have zero or more row name dimensions, each of which produces column of row names, with the innermost row name column cycling the fastest.

  Figure 37-2: Elements of a Typical Report

  

Parts of a Report Script

A report script consists of a series of Hyperion Essbase Report Writer commands, terminated by the bang (!) report output command.

You can enter one or more report scripts in a report script file. A report script file is an ASCII text file that you create with the Hyperion Essbase Report Editor or any text editor.

To build a report script, enter commands that define the layout, member selection, and format in the Hyperion Essbase Report Editor.

The commands in Report Writer perform two functions: data extraction and formatting.

• Extraction commands--deal with the selection, orientation, grouping, and ordering of raw data extracted from the database. These commands begin with less than signs (<).
• Formatting commands--allow for customization of the report format and appearance, the creation of new columns, and calculation of columns and rows. These commands are generally contained within braces ({}), although some begin with less than signs (<).

Additionally, the bang character (!) terminates a series of commands and requests information from the database. You must terminate a report script with a bang character, or you can use several bang characters within the script. See Optimizing Your Reports for more information about the ! character.

See the online Technical Reference in the DOCS directory for detailed information about the various report commands that you can use.

Planning a Report

Report design is an important part of presenting your information. Designing a report is easy if you include the proper elements and arrange information in an attractive, easy-to-read layout.

1. Consider your reporting needs and the time it will take to generate the report.
2. Make a rough sketch of the report that includes:
   • Report layout
   • Number of columns
   • Members to include
   • Titles, if applicable
   • Format of the data values
3. Review the sketch; if you need to add additional data or formatting to the report, this is often apparent at this stage.
4. Determine ways to optimize the run time of the report. See Optimizing Your Reports for suggestions about optimizing your report script.

Security and Multi-User Issues

Because you run the Report Editor from the Application Manager menu, you must have access to the Application Manager in order to use the Report Editor to create or modify a report script. You can also use any text editor to create script files. If you use the Application Manager's Report Editor, it lets you create and modify report scripts stored on your desktop machine, as well as the Hyperion Essbase server. To modify report scripts stored on the server, you must have Application Designer or Database Designer access.

Hyperion Essbase supports concurrent, multi-user database access. As in most multi-user environments, Hyperion Essbase protects your critical data with a security system. Users can read or update data only if they have the correct privileges.

When you execute a report script, the Hyperion Essbase security system verifies that you have Read or higher access level to all data members specified in the report. In a filtering process identical to the one for retrieving members into a spreadsheet, Hyperion Essbase filters any member from the output for which you have insufficient privileges.

To users who are only reporting data, locks placed by other users are transparent. Even if a user has locked and is updating part of the data required by your report, the lock does not interfere with your report in any way. The data in the report reflects the data in the database at the time you run the report. Running the same report later reflects any changes made after your last report ran.

See Managing Security at Global and User Levels for more information about the Hyperion Essbase security system.

Creating and Editing Report Scripts

You can create your report script in the Report Editor or with any ASCII text editor. Once you create your script, you can choose to save it to either the Hyperion Essbase server or your desktop machine.

You can modify your script using text editing features that let you cut, copy, paste, find, and replace. You can also undo most commands and changes with the Edit > Undo command.

The Report Editor uses familiar text editing commands, such as Edit > Cut, Edit > Copy, and Edit > Paste. These commands are available from the Application Manager menu or through accelerator key (hot key) combinations.

Creating New Report Scripts

Before you can create a report script, you must connect to an Hyperion Essbase server and open a database.

1. Choose File > New > Report Script from the Application Manager menu.

   The Report Editor displays.

   Figure 37-3: New Report Editor Window

   

2. Begin typing your report script.

   The name of the report script is Untitled when it is first displayed. When you save the script, the Application Manager prompts you for a file name.

Saving Report Scripts

You can save a report script as:

• A file on your client machine.
• An object on the Hyperion Essbase server. If you want other users to have access to the report script, you need to save it on the Hyperion Essbase server. You can associate the script object with:
  • An application and all the databases within the application, which lets you run the script from any of the databases in the application.
  • A database, which lets you run the script from the specified database.

Report scripts have a.REP extension by default. If you run a report script from the Application Manager it must have a.REP extension.

1. Choose File > Save.
2. Choose to save the script on the Hyperion Essbase server or to your desktop machine.

1. Click the Server button in the Location box.
2. Type the name to give to the file in the Object Name box.
3. Choose the server where you want to save the script in the Server list box.
4. Choose the application where you want to save the script in the Application list box.
5. Choose the database where you want to save the script in the Database list box, or choose "(all dbs)" to make the report script available to all databases within the chosen application.
6. Choose Report Scripts in the List Files of Type box.
7. Click OK.

1. Type the file name in the Object Name box.
2. Choose the application where you want to save the script in the Application list box.
3. Choose the database where you want to save the script in the Database list box, or choose "(all dbs)" to make the report script available to all databases within the chosen application.
4. Choose Report Scripts in the List Files of Type box.
5. Click OK.

   By default, the file is saved to the \ESSBASE\CLIENT\SAMPLE directory on your desktop machine.

Opening Report Scripts from the Application Manager

1. From the Application Manager menu, choose File > Open.

   Figure 37-4: Open Server Object Dialog Box

   

2. From the Location group, select the storage location for the report script.
   • Click the Server option to search the Hyperion Essbase server.
   • Click the Client option to search your desktop machine. The dialog box title changes to Open Client Object.
3. From the List Objects of Type list box, choose Report Scripts.

   The Objects list box displays the names of all available report scripts in the selected application and database.

   Note:

   To choose another application to search, select it from the Application list box. To choose another database to search, select it from the Database list box.

4. You can also locate a locally stored report script through the Windows file system. Click the File System button in the Client Object dialog box.

   The Open Client File dialog box is displayed. The file name mask *.REP is displayed in the File Name text box and all report script files in the current directory are displayed in the list box.

5. To search other directories, use the Directories list box.
6. When the report script you want to open is displayed in the Objects list of the Open Server Object, Open Client Object, or Open Client File dialog box, you can:
   • Click the report script name. When the name is displayed in the Object Name text box, click OK.
   • Enter the name in the Object Name text box and click OK.
   • Double-click on the report name in the Objects list.

     The Application Manager starts the Report Editor and loads the selected report script.

Opening Report Scripts from an Application or Database Directory

You can open report scripts stored in applications and databases from the application directory.

1. Double-click the application name to open the application folder and select a database from the Databases list that contains a report script, or select "(all dbs)" from the Databases list if you want to open a report script stored at the application level.
2. Click the Report Scripts button.
3. From the Reports list, choose the report script you want to open.
4. Click Open to start the Report Editor and load the selected report script.

   Figure 37-5: Database with a Report Script

   

Finding Text

You can use the Find menu command to search for text in the Report Editor. Find locates all occurrences of the specified text in your report script.

1. Choose Edit > Find to open the Find dialog box.

   Figure 37-6: Find Dialog Box

   

2. Type the text you want to find in the "Find what" text box.
3. Use the Direction group box to choose the search direction.
   • Click the Down option button to search from the cursor position forward to the end of the report script.
   • Click the Up option button to search backward from the cursor position to the beginning of the report script.

     For example, Figure 37-6 shows a dialog box that finds the next occurrence of <CHILDREN "400" between the current cursor position and the end of the report script.

4. If you want the search to be case-sensitive, check the "Match case" check box. The search matches uppercase and lowercase letters exactly as they are displayed in the "Find what" text box.

   In the example, the "Match case" check box is checked, so the search finds <CHILDREN "400" but not <Children "400".

5. Click "Find Next."

   If the search finds the text you entered in the "Find what" text box, it highlights the text in the Report Editor.

6. To edit the selection, click Cancel to close the Find dialog box.
7. To search for other occurrences of the text, click Find Next again.

   When Application Manager reaches the end of the report script, or if it cannot find the selected text to replace, the following dialog box is displayed:

   Figure 37-7: Continue Find Dialog Box

   

   • Click Yes to continue searching the file.
   • Click No to close the dialog box and return to the Find dialog box.
8. When you are finished, click Cancel to close the Find dialog box.

Replacing Text

The Replace menu command replaces one or all instances of specified text with different text.

1. Choose Edit > Replace to open the Replace dialog box.

   Figure 37-8: Replace Dialog Box

   

2. Enter the text you want to find in the "Find what" text box.

   If you want the search to be case-sensitive, check the "Match case" check box. The search matches uppercase and lowercase letters exactly as they are displayed in the "Find what" text box.

   In the example, the "Match case" check box is checked, so the search finds Actual but not ACTUAL.

3. Enter the text in the "Replace with" box that will replace one or all instances of the contents of the "Find what" box.
4. Click "Find Next."

   If the search finds the text you entered in the "Find what" text box, it highlights the text in the Report Editor.

5. Click Replace to replace the highlighted text with the information in the "Replace with" box.
6. Click Replace All to replace all instances of the text in the "Find what" box with the information in the "Replace with" box.

   When the search reaches the end of the report script, or if it cannot find the selected text to replace, the following dialog box is displayed:

   Figure 37-9: Continue Find Dialog Box

   

   • Click Yes to continue the searching the file.
   • Click No to close the dialog box and return to the Replace dialog box.
7. When you are finished, click Cancel to close the Replace dialog box.

Cutting, Copying, and Pasting Text

Cutting, copying, and pasting are basic text editing features that you use to modify your report script. These features work with the desktop Clipboard, allowing you to take information from the Report Editor (by copying or cutting) and then bring it back into the Report Editor (by pasting). You can copy, cut, and paste between different scripts in the Report Editor window. For example, you can copy text from Script1, open Script2, and paste the text into Script2.

1. In the Report Editor window, select the text to copy.
2. Choose Edit > Copy.

   The text is still displayed in the Report Editor window, and a copy of the information is stored in the desktop Clipboard.

1. In the Report Editor window, select the text to cut.
2. Choose Edit > Cut.

   The text is removed from the Report Editor window, and is stored in the desktop Clipboard.

1. In the Report Editor window, place your cursor where you want the text to be displayed.
2. Choose Edit > Paste.

   The Paste command is disabled if there is nothing stored in the Clipboard.

Deleting Text

1. In the Report Editor window, select the text to delete.
2. Choose Edit > Delete, or use the Delete key on your keyboard.

Running Report Scripts

The Report menu in Report Editor features commands that you can use to choose a destination for the report, select a database against which to run the report script, and run the report script to generate a final report. The following sections describe these commands.

Choosing the Report Output

The Output Options menu command opens the Report Output Options dialog box that lets you choose three output destinations for your report:

• Window--displays your report in a window using the default system font.
• Printer--sends your report to the currently selected printer and lets you choose the font to use for the report. If Window is also selected, window output is displayed in the same font.
• File--sends your report to a.RPT file.

You can select as many output options as you like, but you must select at least one.

Sending the Report Output to a Window

1. Choose Report > Output Options from the Report Editor.

   Figure 37-10: Report Output Options Dialog Box with Window Check Box Selected

   

   The Window check box is selected as the default.

   If you also check the Printer check box, you can select a printer font for Hyperion Essbase to use as the screen font. See Sending Report Output to a Printer for details.

2. The Show Warnings check box is selected as the default. This option displays any warning messages about report processing in a window on the screen. If you want to turn off this display, click the check box.
3. To display your report in a window, click OK.

   The Report Output Options dialog box closes. You are ready to select a database and run the report.

Sending Report Output to a Printer

1. Choose Report > Output Options from the Report Editor menu.
2. Click the Window check box to deselect it if you do not want the report to be displayed in a window.
3. Check the Printer check box. Application Manager enables the Font button.

   Figure 37-11: Report Output Options Dialog Box with Printer Check Box Selected

   

4. Click the Font button to select a printer font for the report.

   The fonts, font styles, and font sizes available on your system are shown in the Font dialog box.

   Figure 37-12: Font Dialog Box

   

5. From the Font list, select the font type to use in the report.
6. If you want to change the style of the font, choose a different style from the Font Style list.
7. From the Size list, choose the point size of the font. A sample of the font is displayed in the Sample box.
8. When you have finished configuring font options, click OK.

   The Font dialog box closes, and you return to the Report Output Options dialog box.

9. In the Report Output Options dialog box, the Show Warnings check box is selected as the default. This option displays any warning messages about report processing in a window on the screen. If you want to turn off this display, click the check box.
10. Click OK to prepare the report for the printer.

    The Report Output Options dialog box closes. You are ready to select a database and run the report.

Sending Report Output to a File

1. Choose Report > Output Options from the Report Editor.
2. Click the Window check box to deselect it if you do not want the report to be displayed in a window.
3. Check the File check box.

   Application Manager enables the Find button.

   Figure 37-13: Report Output Options Dialog Box with File Check Box Selected

   

4. If you want to save the report to your desktop file system, click Find to choose a directory location.

   The file name mask for report output, *.RPT, is displayed in the "File name" text box. Any report scripts already in the current directory are displayed in the list box.

   Figure 37-14: Report Output File Dialog Box

   

5. In the "File name" text box, enter a name for the report script, while retaining the.RPT extension.

   Report script filesfollow the same naming conventions as DOS files.

6. In the Folders list box, select the directory location for the report file.
7. Click OK to save the name and location. The Report Output File dialog box closes and returns to the Report Options dialog box. The File text box displays the file name and directory location of the report script.
8. Click OK to prepare the report script.
9. The Show Warnings check box is selected as the default. This option displays any warning messages about report processing in a window on the screen. If you want to turn off this display, click the check box.

   The Report Output Options dialog box closes. You are ready to select a database and run the report.

   Note:

   You must check at least one output option to run a report, and you can check any combination of output options.

Choosing Report Databases

The Select Database dialog box lets you select a database against which to run your report script.

1. Choose Report > Database from the Report Editor.

   The Select Database dialog box is displayed.

   Figure 37-15: Select Database Dialog Box

   

2. Click the Server or Client option to specify the location of the database.
3. From the Server list box, select the server where the database resides.

   If there are no server names in the list, click the Connect button to log onto the server.

4. Select the application where the database resides from the Application list box.

   Application Manager lists the databases that reside on the selected server and in the selected application.

5. Select the database from the Database list.
6. Click OK.

When you run the report, it reports on the selected database.

Running the Report

The Report > Run menu command sends your report to the screen and any other output destination you have selected. See Choosing the Report Output to learn how to choose a report output.

To run the report script, choose Report > Run.

Running Multiple Report Script Files

You can use the RUNREPT command in ESSCMD to run one or more report script files. See the online Technical Reference in the DOCS directory for information about this command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.

Executing a Report String Without Creating a Report Script

	You can use the REPORTLINE command in ESSCMD to run a single report string. See the online Technical Reference in the DOCS directory for information about this command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.
	You can use the REPORT command in ESSCMD to run multiple report strings. See the online Technical Reference in the DOCS directory for information about this command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.

Developing Free-Form Reports

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

tion Manager's Report Editor window.

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

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

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

This example produces the following report:

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

You can use formatting commands to add specific formats to a free-form report. The rest of the report is automatically produced in a format similar to that of any other report. When PAGE, COLUMN, and ROW commands are omitted, Hyperion Essbase formats free-form reports according to the following rules:

1. The Report Extractor finds the last member or members of a single dimension defined in the report specification (before the report output operator !). This dimension becomes the ROW dimension for the report. All remaining selections become PAGE or COLUMN dimensions, as defined by rules 2 and 3.
2. The Report Extractor searches for any single-member selections. If a single member is found that does not satisfy rule 1, that dimension becomes a PAGE dimension.
3. The Report Extractor searches for all remaining dimension members that do not satisfy rules 1 or 2. If any remaining members are found, those dimensions become COLUMN dimensions. COLUMN dimensions are nested in the order of selection in the free-form script.
4. The Report Extractor searches the database outline for any dimensions not specified in the report specification. If any unspecified dimensions are found, they become PAGE dimensions (the default for single-member selections, as defined in rule 2).
5. A subsequent selection of one or more consecutive members from a given dimension overrides any previous selection for that dimension.

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

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

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

1. Because California, Oregon, Washington, Utah, Nevada, and West are listed last, the Report Extractor treats them as if ROW(Market) had been specified (according to rule 1).
2. Sales is a single-member selection from dimension Measures. The Report Extractor treats this member as if PAGE(Measures) had been specified (according to rule 2).
3. After searching the remaining members, the Report Extractor finds members of dimensions Year and Scenario, which it treats as COLUMN(Year, Scenario), according to rule 3.
4. The Report Extractor searches the Database Outline and finds that dimension Product is not specified in the report specification. Since Product is a single-member selection, the Report Extractor treats this member as if PAGE (Product) had been specified (according to rule 4).
5. Finally, the Report Extractor finds that Apr May Jun is from the same dimension as Jan Feb Mar and is displayed on a subsequent line of the script. The Report Extractor discards the first specification (Jan Feb Mar) and uses the second (Apr May Jun).

As a result, the report example produces the following report:

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

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