Understanding Report Script Basics Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Understanding Report Script Basics


An Analytic Services report enables you retrieve formatted summaries from an Analytic Services database. This chapter provides fundamental information about working with report scripts created by Report Writer. This chapter contains the following sections:

For a comprehensive discussion of 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 the Analytic Server installation.

  1. Create a report script.

    For more information, see "Creating Scripts" in the Essbase Administration Services Online Help.

  2. Type the following information in the report, 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 
        ! 
    

  3. Save the report script.

    For more information, see "Saving Scripts" in the Essbase Administration Services Online Help.

  4. Execute the report script.

    For information, see "Executing Report Scripts" in the Essbase Administration Services Online Help.

When you execute 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 
 

Understanding How Report Writer Works

The Report Writer consists of three main components:

Report Extractor

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:

  5. Restricts data. For example, the following command suppresses the display of all rows that contain only missing values:
    {SUPMISSINGROWS} 
    

  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 Report Extractor restricts data (step 5) before sorting data (step 6), if you place conditional retrieval commands in the wrong order, the report output results can be unexpected. Be aware of the data retrieval process when designing report scripts.

Parts of a Report

Understanding the parts of a report is essential as you plan and design your own reports.

Figure 206: Elements of a Typical Report

A typical report is composed of the following parts:

Parts of a Report Script

A report script consists of a series of 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 a text file that you create with Report Script Editor or any text editor.

To build a report script, enter or select commands that define the layout, member selection, and format in Report Script Editor. The different elements of a script are color-coded to aid in readability. You can enable syntax auto-completion to help build scripts quickly.

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

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

Planning Reports

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

To plan a report, perform the following tasks:

  1. Consider the reporting needs and the time required to generate the report.

  2. Make a rough sketch of the report. Be sure to include the following items:

  3. Review the sketch; if you need to add additional data or formatting to the report, it is often apparent at this stage.

  4. Determine ways to optimize the run time of the report.

    See Optimizing Reports and Other Types of Retrieval for a comprehensive discussion of how to optimize a report script.

Note: As you plan the report, minimize use of numeric row names. To avoid ambiguity, give the rows names that describe their content.

Considering Security and Multiple-User Issues

You must use Essbase Administration Services in order to use Report Script Editor to create or modify a report script. You can also use any text editor to create script files. If you use Report Script Editor, it lets you create and modify report scripts stored on your desktop machine, as well as the Analytic Server. To modify report scripts stored on the server, you must have Application Designer or Database Designer access.

Analytic Services supports concurrent, multiple-user database access. As in most multiple-user environments, Analytic Services protects critical data with a security system. Users can read or update data only if they have the correct permissions.

When you execute a report script, the Analytic Services 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, Analytic Services filters any member from the output for which you have insufficient permissions.

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 the report, the lock does not interfere with the 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 the last report ran.

See Managing Security for Users and Applications for a comprehensive discussion of the Analytic Services security system.

Reviewing the Process for Creating Report Scripts

This section describes the process for creating a report script.

  1. Create the report script. See Creating Report Scripts.

  2. Check the report script syntax. See "Checking Script Syntax" in the Essbase Administration Services Online Help.

  3. Save the report script. See Saving Report Scripts.

  4. Run the report script. See Executing Report Scripts.

  5. If desired, save the report. See "Saving Reports" in the Essbase Administration Services Online Help.

Creating Report Scripts

You can report on the data in a database using any of the following methods:

For more information about creating and editing report scripts in Essbase Administration Services, see "About Report Script Editor" in Essbase Administration Services Online Help.

Saving Report Scripts

You can save a report script in the following locations:

Report scripts have a .rep extension by default. If you run a report script from Essbase Administration Services, the script must have a .rep extension.

To save a report script using Report Script Editor, see "Saving Report Scripts" in Essbase Administration Services Online Help.

Executing Report Scripts

When you execute a report script using Essbase Administration Services, you can send the results to the Report Viewer window, to a printer, and/or to a file. From the Report Viewer window, you can print, save, and copy the report.

Using Essbase Administration Services, you can execute a report in the background so that you can continue working as the report processes. You can then check the status of the background process to see when the report has completed.

For more information, see "Executing Report Scripts" in Essbase Administration Services Online Help.

Copying Report Scripts

You can copy report scripts to applications and databases on any Analytic Server, according to your permissions. You can also copy scripts across servers as part of application migration.

To copy a report script, use any of the following methods:


Tool
Topic
Location

Administration Services

Copying Scripts

Essbase Administration Services Online Help

MaxL

alter object

Technical Reference

ESSCMD

COPYOBJECT

Technical Reference



Developing Free-Form Reports

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

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

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

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

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, Analytic Services 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 
 

Note: You cannot use substitution variables in free-form mode.



Hyperion Solutions Corporation link