Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
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.
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.
For more information, see "Creating Scripts" in the Essbase Administration Services Online Help.
// 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 !
For more information, see "Saving Scripts" in the Essbase Administration Services Online Help.
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
The Report Writer consists of three main components:
.rep
..rpt
.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 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.
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:
<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.
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.
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:
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.
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.
This section describes the process for creating a report script.
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.
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.
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.
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 |
---|---|---|
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:
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:
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.
![]() |