An Essbase report lets you retrieve formatted summaries from a database.
There are several ways that you can report on the data in your database:
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 or Administration Services 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 Automating the Production Environment for information about ESSCMD. See also the 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 Essbase database, and produce free-form reports.
This chapter provides fundamental information about reports and report scripts, including:
For information about creating complex report scripts, see Developing Report Scripts.
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 OLAP Server installation.
To create a simple report script using Application Manager:
If you do not have the Sample Basic database installed, contact your Essbase administrator.
// 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 !
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 Technical Reference in the docs directory. If you are using the Report Editor, choose the Help menu item to consult the Essbase Application Manager Online Help.
See the Technical Reference in the docs directory for information about the MaxL statement and REPORTLINE and REPORT commands. See Automating the Production Environment for information about ESSCMD.
For information about using the Report Script Writer in Administration Services, see the Essbase Administration Services Online Help.
The Report Writer consists of three main components:
The Report Extractor processes the report script and retrieves data in the following order:
<IDESCENDANTS East
<LINK
<SORT
{SUPMISSINGROWS}
<TOP
{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.
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 (Market, Measures)
<COLUMN (Year, Scenario)
<ROW (Product)
{ STARTHEADING TEXT 1 "Prepared by:" 14 "*USERNAME" C "The Electronics Club" 65 "*PAGESTRING" TEXT 65 "*DATE" SKIP ENDHEADING }
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 476: Elements of a Typical Report
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 an ASCII text file that you create with the Report Editor or any text editor.
To build a report script, enter commands that define the layout, member selection, and format in the Report Editor.
The commands in Report Writer perform two functions, data extraction and formatting:
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 Reports and Other Types of Retrieval, for more information about the ! character.
See the Technical Reference in the docs directory for detailed information about the various report commands that you can use.
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.
Note: As you plan your report, minimize your use of numeric row names. To avoid ambiguity, give the rows names that describe their content.
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 OLAP Server. To modify report scripts stored on the server, you must have Application Designer or Database Designer access.
Essbase supports concurrent, multiple-user database access. As in most multiple-user environments, 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 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, 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 for Users and Applications for more information about the Essbase security system.
You can create your report script using the Report Editor in Application Manager, the Report Script Editor in Administration Services, or with any ASCII text editor. For more information about using the Report Script Editor in Administration Services, see Essbase Administration Services Online Help.
Once you create your script, you can choose to save it to either OLAP 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.
Before you can create a report script, you must connect to an OLAP Server and open a database.
To create a new report script:
Figure 477: New Report Editor Window
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.
You can save a report script as:
Report scripts have a .REP extension by default. If you run a report script from the Application Manager it must have a .REP extension.
To save the script on the OLAP Server:
To save the script on your desktop machine:
By default, the file is saved to the \ESSBASE\CLIENT\SAMPLE directory on your desktop machine.
To open a report script from the Application Manager:
Figure 478: Open Server Object Dialog Box
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.
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.
You can open report scripts stored in applications and databases from the application directory.
To open a report script from an application folder:
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.
To find text in a report script:
For example, Figure 480 shows a dialog box that finds the next occurrence of <CHILDREN "400" between the current cursor position and the end of the report script.
In the example, the Match case check box is checked, so the search finds <CHILDREN "400" but not <Children "400".
If the search finds the text you entered in the Find what text box, it highlights the text in the Report Editor.
When Application Manager reaches the end of the report script, or if it cannot find the selected text to replace, the dialog box shown in Figure 481 is displayed:
Figure 481: Continue Find Dialog Box
The Replace menu command replaces one or all instances of specified text with different text.
To replace text in a report script:
Figure 482: Replace Dialog 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.
If the search finds the text you entered in the "Find what" text box, it highlights the text in the Report Editor.
When the search reaches the end of the report script, or if it cannot find the selected text to replace, the dialog box shown in Figure 483 is displayed:
Figure 483: Continue Find Dialog Box
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.
The text is still displayed in the Report Editor window, and a copy of the information is stored in the desktop Clipboard.
The Paste command is disabled if there is nothing stored in the Clipboard.
The text is removed from the Report Editor window, and is stored in the desktop Clipboard.
The Paste command is disabled if there is nothing stored in the Clipboard.
To remove text from the Report Editor:
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.
The Output Options menu command opens the Report Output Options dialog box that lets you choose three output destinations for your report:
You can select as many output options as you like, but you must select at least one.
To send your report output to a window:
Figure 484: 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 Essbase to use as the screen font. See Sending Report Output to a Printer for details.
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.
To send your report output to a printer:
Application Manager enables the Font button as shown in Figure 485.
Figure 485: Report Output Options Dialog Box with Printer Check Box Selected
The fonts, font styles, and font sizes available on your system are shown in the Font dialog box.
The Font dialog box closes, and you return to the Report Output Options dialog box.
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.
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.
To send your report output to a file:
Application Manager enables the Find button.
Figure 487: Report Output Options Dialog Box with File Check Box Selected
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 488: Report Output File Dialog Box
Report script files follow the same naming conventions as DOS files.
The Report Output File dialog box closes and you return to the Report Options dialog box. The File text box displays the file name and directory location of the report script.
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.
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.
The Select Database dialog box lets you select a database against which to run your report script.
The Select Database dialog box is displayed.
Figure 489: Select Database Dialog Box
If there are no server names in the list, click the Connect button to log onto the server.
Application Manager lists the databases that reside on the selected server and in the selected application.
When you run the report, it reports on the selected database.
Note: If the report is already associated with a database or application other than the one you specify, the Select > Database command does not change that association but simply overrides the default database for this Report Editor session only.
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.
You can use the RUNREPT command in ESSCMD to run one or more report script files. See the Technical Reference in the docs directory for information about this command. See Automating the Production Environment for information about ESSCMD.
You can use the following ESSCMDs to run one or more report strings.
To run a single report string, use the REPORTLINE command. See the Technical Reference in the docs directory for information about this command. See Automating the Production Environment for information about ESSCMD.
To run multiple report strings, use the REPORT command. See the Technical Reference in the docs directory for information about this command. See Automating the Production Environment for information about ESSCMD.
Free-form reports are often easier to create than structured reports. The free-form reporting style is ideal for ad hoc reporting in the Application 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:
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, Essbase formats free-form reports according to the following rules:
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:
As a result, the report example produces the following report:
Note: You cannot use substitution variables in free-form mode.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |