This chapter includes report scripts that demonstrate report procedures and formats that are most frequently required in business settings. If you examine the techniques in these scripts and the resulting output, you can adapt them for use in your own reports.
The samples use both the Demo Basic and Sample Basic databases provided with your Hyperion Essbase server. Each sample identifies the database used. The scripts for these examples are available in your \ESSBASE\APP\DEMO\BASIC directory or your \ESSBASE\APP\SAMPLE\BASIC directory.
The sample reports in this chapter demonstrate the following techniques:
For fundamental information about reports and report scripts, see Quick Start to Report Scripts. For detailed information about using Report Writer commands to write reports and reports scripts, see Developing Report Scripts. For the syntax and usage of each Report Writer command, see the Technical Reference in the docs directory.
This sample report contains data for Actual Sales. Each report page shows a different Product. The report lists products on the same page until the maximum page length is reached. To place each Product on a separate page, you must use the PAGEONDIMENSION format command, as shown in Sample 2.
Because none of the cities in South sell Stereo or Compact_Disc, the data values indicate #MISSING. You can represent missing values by suppressing the row or substituting a replacement text string, such as N/A. See Sample 2: Handling Missing Values for an example of substituting page breaks and labels for missing values.
Use the following script to create Sample 1:
<PAGE (Accounts, Scenario, Product) Sales Actual <IDESCENDANTS Audio <COLUMN (Year) <CHILDREN Year <ROW(Market) <ICHILDREN Market !
The ! report output command is required to generate the report.
Because the IDESCENDANTS selection command is used for Audio, the report selects all three members. Only a single member is selected from the other page dimensions, Sales and Actual. As a result, the script creates three report pages. They display as one long report page unless you use the PAGEONDIMENSION format command, as shown in Sample 2.
This report script, ACTSALES.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This report has the same layout and member selection as Sample 1, and shows you how to use page breaks and labels for missing values.
Sales Actual Stereo Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 7,839 7,933 7,673 10,044 West 11,633 11,191 11,299 14,018 South N/A N/A N/A N/A Market 19,472 19,124 18,972 24,062 |
Sales Actual Audio Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== East 18,132 17,635 17,638 21,836 West 25,954 25,207 25,627 31,265 South N/A N/A N/A N/A Market 44,086 42,842 43,265 53,101 |
Use the following script to create Sample 2:
<PAGE (Accounts, Scenario, Product) Sales Actual <IDESCENDANTS Product { PAGEONDIMENSION Product } { MISSINGTEXT "N/A" } <COLUMN (Year) <CHILDREN Year <ROW(Market) <ICHILDREN Market !
The PAGEONDIMENSION format command creates a page break whenever a member from the specified dimension changes. Because the report selects eight Product members, this creates an eight-page report.
The MISSINGTEXT format command substitutes any strings enclosed within double quotes into the #MISSING string. To suppress missing values, use the SUPMISSINGROWS command.
You can also combine format commands within one set of braces:
{ PAGEONDIMENSION Product MISSINGTEXT "N/A" }
This report script, MISS_LBL.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
Each page produced by this report sample contains Sales information for a given Market. The report has two groups of columns across the page. The Actual and Budget members are the nested column group below Year members.
Note that the Actual and Budget members are on the same line in the report. You can put multiple commands on one line, but report commands are easier to read if they are spread out.
Use the following script to create Sample 3:
<PAGE (Accounts, Market) Sales <ICHILDREN Market { PAGEONDIMENSION Market } { SUPMISSINGROWS } <COLUMN (Year, Scenario) <ICHILDREN Qtr1 Actual Budget <ROW(Product) <IDESCENDANTS Product !
The report selects four Markets because the <ICHILDREN command is applied to Market. Only Sales is selected from the other page dimension, so the report has four pages.
For the South, all the rows of Product data are not displayed. Recall that the cities in the South do not sell every Product. The report uses the SUPMISSINGROWS format command to suppress the output of any member rows with all missing values.
This report script, COLGROUP.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
Each page of this report contains Sales information for a given Market. The report page contains members for both Product and Year as groups of rows down the page. This script creates a four-page report because the page dimensions and their member selections are the same as in Sample 3. The row and column layout is switched because the row and column dimensions are different. This section shows a representative part of the output.
Use the following script to create Sample 4:
<PAGE (Accounts, Market) Sales <ICHILDREN Market { PAGEONDIMENSION Market } { SUPMISSINGROWS } <COLUMN (Scenario) <CHILDREN Scenario <ROW(Product3, Year) <ICHILDREN Year <IDESCENDANTS Product !
This report script, ROWGROUP.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
Each page represents a different combination of Product, Market, and Budget data. The total number of pages is determined by the number of Market and Product members. This section shows a representative part of the output.
Some data values have four decimal places. The number of decimal places, by default, is output to the true number of decimal values of the data cell. Sample 6: Formatting Different Combinations of Data uses the DECIMAL format command to define a specific number of places.
The member selection commands select three Product members and fourteen Market members. This produces a 42-page report. The number of report pages is determined by multiplying the number of members selected from each page dimension.
Use the following script to create Sample 5:
<PAGE (Scenario, Product, Market) Budget <ICHILDREN Product <IDESCENDANTS Market { PAGEONDIMENSION Product } // New page at each new Product { PAGEONDIMENSION Market } // New page at each new Market <COLUMN (Year) <ICHILDREN Year <ROW(Accounts) <DESCENDANTS Accounts !
This report script, COMBO1.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This report uses the same layout and member selection as Sample 5, and adds more formatting in the report body. Note the use of line formatting.
Use the following script to create Sample 6:
<PAGE (Scenario, Product, Market) { PAGEONDIMENSION Product PAGEONDIMENSION Market } Budget <ICHILDREN Product <IDESCENDANTS Market <COLUMN (Year) <ICHILDREN Year <ROW(Accounts) { SUPBRACKETS DECIMAL 0 } Sales Cost_of_Goods_Sold { UDATA "-" } //line formatting command Margin { SKIP } Marketing Payroll Miscellaneous { UDATA "-" } //line formatting command Total_Expenses { SKIP } Profit { UDATA DECIMAL 2 } //line formatting command Profit_% Margin_% !
Format commands apply to members that follow the commands. The report begins each new page with the formats in place at the end of the previous report page. For example, if a report page ends with two decimal places, the following page begins with two decimal places. This report demonstrates the use of several important format commands:
This report script, COMBO2.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This report outputs members in the middle of a page and uses aliases or alternate names. The default row member indentation is turned off.
Use the following script to create Sample 7:
<PAGE (Product, Market) { PAGEONDIMENSION Product } { PAGEONDIMENSION Market } <IDESCENDANTS Product { DECIMAL 0 } <SYM <COLUMN (Year, Scenario) Qtr4 Year Actual Budget <ROW(Accounts) { SUPBRACKETS OUTALTNAMES NOINDENTGEN ORDER 1,2,0,3,4 } Sales Cost_of_Goods_Sold { UDATA "-" } Margin { SKIP } Marketing Payroll Miscellaneous { UDATA "-" } Total_Expenses { SKIP } Profit { UDATA DECIMAL 2 } Profit_% Margin_% !
The SYM command forces the report to output symmetric column groups. The default is to display two columns: one for Qtr4 Actual and one for Year Budget. Because the report calls for Actual and Budget under both Qtr4 and Year, the SYM command is required. Alternatively, repeat the Actual and Budget names under Qtr4 and Year.
The OUTALTNAMES format command causes the report to use aliases or alternate names instead of member names.
The NOINDENTGEN format command causes row members to not be indented. By default, members are indented two spaces for each level.
The ORDER command moves specified output columns to new locations. The row name is considered column 0.
The FIXCOLUMNS format command restricts the number of output columns. Reports often require both ORDER and FIXCOLUMNS. You can use ORDER to remove unwanted columns, and FIXCOLUMNS to stop these columns from displaying after the report columns.
This report script, MIDDLE.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This report displays custom headings and percent sign (%) characters after each data value. This section shows a representative part of the output.
Use the following script to create Sample 8:
<PAGE (Accounts, Scenario, Product) { PAGEONDIMENSION Product } // New page when Product changes Profit_% Actual <IDESCENDANTS Product <COLUMN (Year) Jan Feb Mar Apr May Jun <ROW(Market) { STARTHEADING TEXT 1 "Prepared by:" 14 "*USERNAME" C "The Electronics Club" 65 "*PAGESTRING" TEXT 65 "*DATE" SKIP ENDHEADING } { Decimal 2 AFTER "%" SUPBRACKETS } // Place % at end and // suppress bracket <IDESCENDANTS Market !
Each data value in the report has a percent sign, %. This label is defined with the AFTER "%" format command. You can specify any character within quotation marks.
This report has custom headings at the top of each page. All format commands specified between the STARTHEADING and ENDHEADING format commands are displayed at the top of each report page.
TEXT format commands define text labels. The report generator provides dynamic text with *options. See the Technical Reference in the docs directory for a full list of the available options. This report uses the following options:
This report script, HEADING1.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This report builds on Sample 8 by adding custom page headings. By default, page dimension members are output at the top center of a report page. This section shows a representative part of the output.
Use the following script to create Sample 9:
<PAGE (Accounts, Scenario, Product) <IDESCENDANTS Product <SORTLEVEL { PAGEONDIMENSION Product } { STARTHEADING TEXT 1 "Prepared by:" 14 "*USERNAME" C "The Electronics Club" 65 "*PAGESTRING" SUPPAGEHEADING UNDERLINECHAR " " TEXT C "Actual Profit by Product" 65 "*DATE" TEXT 1 "Product:" 10 "*PAGEHDR 3" SKIP ENDHEADING } Profit_% Actual <COLUMN (Year) Jan Feb Mar Apr May Jun <ROW(Market) { DECIMAL 2 AFTER "%" SUPBRACKETS UNDERSCORECHAR " " } { INDENTGEN 1 } <IDESCENDANTS Market !
The SUPPAGEHEADING format command suppresses the default page headings from output.
The *PAGEHDR command customizes the location of page member labels. The Sample 9 script uses page heading number 3, Product because this is the third page dimension.
You may have also noticed that member names do not have underscores. The UNDERSCORECHAR format command blanks out underscores.
Another difference is the underlining of column headings. The UNDERLINECHAR format command causes the underlining to character to change to the character in quotes.
The report rows are also sorted according to their levels in the database outline. Sort commands, such as SORTLEVEL, do not affect individual members selected in reports. Instead, these commands work in conjunction with member selection commands.
Note: You can use only one sort command in a report.
Sample 9 reverses the indentation of levels from previous reports. The INDENTGEN command indents members to the specified number of characters.
This report script, HEADING2.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
Column calculation formulas manipulate the column value of a particular row or a constant. In this report sample, each % column represents the quarterly values as a percent of Sales for the respective quarter. In addition, the Avg column represents an average value for the two quarters.
Use the following script to create Sample 10:
// This report performs column calculations based on values in a // report row. <PAGE (Scenario, Product, Market) Actual <COLUMN (Year) Qtr1 Qtr2 { DECIMAL 2 3 4 } { NAMEWIDTH 22 WIDTH 7 3 4 } { ORDER 0 1 3 2 4 5 } <ROW (Accounts) { SAVEROW } Sales ! { CALCULATE COLUMN "%" = 1 % "Sales" 1 } { CALCULATE COLUMN "% " = 2 % "Sales" 2 } { CALCULATE COLUMN "Avg" = 1 + 2 / 2. } <DESCENDANTS Accounts !
Note: You can include comments in the report by preceding the text with //. The Report Extractor ignores everything that follows the double slash. You can use comments to explain report processing.
The SAVEROW command reserves space for a row member that the CALCULATE COLUMN command calculates. In this case, the calculation affects SALES. The ! is required after the member name.
The CALCULATE COLUMN command allows column numbers, row names, or constants in formulas. You can read the first calculation this way: "% equals column 1 as a percent of Sales in column 1."
Each calculated column label must be unique. Note how the second calculated column label has a blank space after the % sign.
To specify a constant, define a number followed by a period. You can use a constant in either a column or row calculation. The last column calculation takes the sum of columns 1 and 2 and divides by the value 2. This formula is interpreted as (1+2)/2, not 1 + (2/2.).
As noted in Sample 7, the ORDER command arranges columns in the specified order. By default, calculated columns are added to the end of existing columns retrieved from the database. In this example, columns 0-2 are automatically retrieved, based on selected members. Columns 3-5 are the calculated columns. The ORDER command applies to both retrieved and calculated columns.
This report script, COLCALC1.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This sample report has two different page layouts on the same page.
Use the following script to create Sample 11:
<PAGE (Year, Accounts, Scenario) <COLUMN (Market) <ICHILDREN Market <ROW(Product) <IDESCENDANTS Product Actual { DECIMAL 2 WIDTH 10 SUPBRACKETS AFTER "%" } Profit_% ! <PAGE (Accounts, Scenario, Product) Actual Sales Product <COLUMN(Year) <ICHILDREN Year <ROW(Market) { DECIMAL 0 After " " BEFORE "$" } <IDESCENDANTS Market !
In a single report, you can select multiple dimension layouts and members. To define a multiple layout report, define reports as you normally would. Separate the commands with exclamation marks as shown above. Whenever the column, row, or page dimensions change between ! output commands, new headings are automatically generated to match the new layout.
The BEFORE format command places a character in front of data values. The AFTER format command turns off the percent signs from the first report layout.
This report script, 2LAYOUTS.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This sample creates a report with a member name in each column. This format is required when you export Hyperion Essbase data to another product, such as an SQL database, with a flat file.
Use the following script to create Sample 12:
<PAGE(Scenario) <COLUMN(Year) <ROW (Market, Product, Accounts) <CHILDREN East <DESCENDANTS Product { DECIMAL 1 WIDTH 9 SUPBRACKETS SUPCOMMA MISSINGTEXT " " UNDERSCORECHAR " " SUPHEADING NOINDENTGEN SUPFEED ROWREPEAT Budget Jan Feb <DESCENDANTS Accounts !
The ROWREPEAT command produces rows of data that have the member names repeat for each row dimension.
The SUPFEED command suppresses page feeds. A page feed automatically occurs when the report output reaches the default page length of 66 rows, unless you enter the PAGELENGTH command to change this setting. When a large flat file is created, you can use this command to prevent page breaks (blank rows) from being displayed in the report every time output reaches a logical page length.
This report script, FLAT2SQL.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
Asymmetric columns make up this report. Typically, a report contains symmetric columns. That is, when multiple dimensions are displayed across the page as column groups, each level of nested columns has the same number of members nested below. Because Actual has only one nested column, Jan, and Budget has three nested columns, this report is considered asymmetric.
Some rows in the report use names other than the member names from the database. In addition to allowing aliases, as in Sample 7, you can rename a row name in the reporter.
Use the following script to create Sample 13:
<PAGE (Product, Market) <COLUMN (Scenario, Year) Actual Budget Budget Budget Jan Jan Feb Mar <ROW (Accounts) { RENAME "Revenue" } Sales { RENAME "Cost of Goods" } Cost_of_Goods_Sold { RENAME "Gross Margin" } Margin { SKIP UNDERSCORECHAR " " } <ICHILDREN Total_Expenses { SKIP } Profit !
To create an asymmetric report, you must specify the member name of each column. Because the report output has two column groupings, Scenario and Year, you must specify a member from each dimension for each column. If you do not specify each column member, the resulting report format is symmetric.
The RENAME command redefines a member name when the report is output. This is useful when you do not want to use an aliases table.
This report script, ASYMM.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This section contains two examples of CALCULATE COLUMN scripts and the reports they produce. CALCULATE COLUMN supports standard mathematical operations.
Use the following script to create Sample 14-A:
<PAGE(Market) East <COLUMN (Scenario, Year) Actual Budget Jan Feb Mar { CALCULATE COLUMN "Qtr1" = 2 : 4 CALCULATE COLUMN "Q1" = 5 : 7 CALCULATE COLUMN "Var~Q1" = 8 - 9 ORDER 2,3,4,8,0,1,5,6,7,9 WIDTH 7 WIDTH 10 0 1 } <ROW (Product, Accounts) <ICHILDREN Visual <CHILDREN Accounts !
This report script, COLCALC2.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
The following sample has two regular columns defined in asymmetric mode. For more information on asymmetric columns, see Sample 13: Creating Asymmetric Columns.
Use the following script to create Sample 14-B:
<PAGE(Market) East <COLUMN(Scenario, Year) Budget Actual Jan Jan { ORDER 2,0,1,3,4 WIDTH 12 0 1 NOINDENTGEN AFTER "%" 4 SKIPONDIMENSION Product LMARGIN 10 } <ROW(Product, Accounts) { CALCULATE ROW "Sales" OFF } { CALCULATE COLUMN "Actual~% Sales" = 2 % "Sales" 2 } <ICHILDREN Visual { SAVEROW } Sales Payroll Marketing Profit <DUPLICATE Sales !
This report script, COLCALC3.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
The sample reports in this section demonstrate CALCULATE ROW scripts and the reports they produce.
This sample report demonstrates the basic form of the CALCULATE ROW command.
Audio Actual Sales Jan Feb Mar ======== ======== ======== Boston 1,985 1,801 1,954 New_York 2,310 2,082 2,259 Chicago 2,043 1,884 1,814 Total Sales 6,338 5,767 6,027 Avg Sales 2,113 1,922 2,009 |
Use the following script to create Sample 15-A:
Audio Actual Sales Jan Feb Mar { CALCULATE ROW "Total Sales" } //create new calculated row Boston New_York Chicago { SKIP CALCULATE ROW "Avg Sales" = "Total Sales" /3 PRINTROW "Total Sales" PRINTROW "Avg Sales" } !
This report script, ROWCALC1.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This sample report is a simple summary of information in a North/South grouping, which is not part of the database outline. When relationships that you need for reporting are missing in the database outline, often the best solution is to use calculated rows (or columns).
Use the following script to create Sample 15-B:
// Declare Calculated Rows to Sum Southern and Northern Cities { CALCULATE ROW "Total Southern" OFF // initially, set operation to OFF CALCULATE ROW "Total Northern" OFF } <PAGE(Product,Scenario,Accounts) { RENAME "" } Product // all products, so blank out // the Product Label Budget Payroll <COLUMN(Year) Jan Feb Mar <ROW(Market) // Northern Cities { SETROWOP "Total Northern" + // Accumulate for Northern SKIP 3 IMMHEADING // Put out heading now so text // will go after it Text 0 "Northern Cities" UCHARACTERS } New_York Boston Chicago San_Francisco Seattle //Southern Cities { SETROWOP "Total Southern" + } // Accumulate for Southern { SETROWOP "Total Northern" OFF } // Stop Accumulation for Northern { SKIP Text 0 "Southern Cities" UCHARACTERS } Denver Los_Angeles Dallas Phoenix Houston { SKIP PRINTROW "Total Northern" // output calculated rows PRINTROW "Total Southern" } !
This report script, ROWCALC2.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
This report sample restricts columns during calculation to average rows that contain partly numbers and percentages. The report must calculate the total regional average percentages using previously calculated rows that contain the total sales for the region. Also, the report must compute (for averaging) a count of regions. The number of regions is set as a constant in the database outline. If this number changes, the report definition must be modified. If a count of regions is not computed, a hard-to-notice error could result.
Use the following script to create Sample 15-C:
{ // Declare some of the Calculated Rows to be used CALCULATE ROW "Avg~Budget" OFF CALCULATE ROW "Avg~Actual" OFF CALCULATE ROW "Tot Sales~Budget" OFF CALCULATE ROW "Tot Sales~Actual" OFF } // We need the values of Market->Visual->Qtr1->Sales->Actual and // Market ->Visual->Qtr1->Sales ->Budget to compute some // percentages at the bottom, so get them now Market <CHILDREN Visual Qtr1 Sales { SAVEROW "Actual Sales" } Actual // stores into first 3 // data columns { SAVEROW "Budget Sales" } Budget // of these rows, which // are cols 1-3 // change to columns 2-4 when we // specify 2 row dimensions in // next section // Since this is an example, not a formal report, we'll // type out the values for Actual Sales and Budget Sales here so // you can check the numbers: { SKIP 2 TEXT 0 "Actual Total Sales for the 3 Video Products in Qtr1:" 55 "*CALC" "Actual Sales" TEXT 0 "Budget Total Sales for the 3 Video Products in Qtr1:" 55 "*CALC" "Budget Sales" UCHARACTERS SKIP 5 } ! // Now we can do the main report { AFTER "%" 3,5,7 DECI 2 3,5,7 ZEROTEXT "--" MISSING "--" WIDTH 10 0 1 } <PAGE(Year) Qtr1 <COLUMN(Product,Accounts) <CHILDREN Visual Profit // split these 2 accounts onto // 2 lines to prevent default Profit_% // to asymmetric mode // because both column // dimensions have the same # of // members selected. Could have // used <SYM instead. <ROW(Market,Scenario) <ONSAMELEVELAS New_York { SETROWOP "Avg~Actual" OFF SETROWOP "Avg~Budget" + CALCULATE ROW "Count" = "Count" + 1. } Budget { SETROWOP "Avg~Budget" OFF SETROWOP "Avg~Actual" + } >{ SKIP } Actual { UCOLUMNS SKIP 2 } { // at this point, Avg~Budget and Avg~Actual ARE NOT YET // AVERAGES--they are the SUM of the Profit rows of each type. // Before converting them to averages, the report computes // Profit as a % of total sales for each type. Since we only // have 1 value for "Budget Sales" and "Actual Sales", // for each of the three visual products in those // rows, the report restricts the reference to those rows to // columns 2-4 while computing the percentage columns 3, 5, and 7, // based on profits in columns 2, 4 and 6 // calculate the percentages for Budget CALCULATE ROW "Avg~Budget" 3 = "Avg~Budget" 2 % "Budget Sales" 2 CALCULATE ROW "Avg~Budget" 5 = "Avg~Budget" 4 % "Budget Sales" 3 CALCULATE ROW "Avg~Budget" 7 = "Avg~Budget" 6 % "Budget Sales" 4 // now calculate the averages CALCULATE ROW "Avg~Budget" 2 = "Avg~Budget" / "Count" CALCULATE ROW "Avg~Budget" 4 = "Avg~Budget" / "Count" CALCULATE ROW "Avg~Budget" 6 = "Avg~Budget" / "Count" // calculate the percentages for Actual CALCULATE ROW "Avg~Actual" 3 = "Avg~Actual" 2 % "Actual Sales" 2 CALCULATE ROW "Avg~Actual" 5 = "Avg~Actual" 4 % "Actual Sales" 3 CALCULATE ROW "Avg~Actual" 7 = "Avg~Actual" 6 % "Actual Sales" 4 // now calculate the averages CALCULATE ROW "Avg~Actual" 2 = "Avg~Actual" / "Count" CALCULATE ROW "Avg~Actual" 4 = "Avg~Actual" / "Count" CALCULATE ROW "Avg~Actual" 6 = "Avg~Actual" / "Count" TEXT C "Total Regions Averages" PRINTROW "Avg~Budget" PRINTROW "Avg~Actual" } !
This report script, ROWAVG.REP, is available in your \ESSBASE\APP\DEMO\ BASIC directory.
The following two reports demonstrate the use of TOP and BOTTOM conditional retrieval commands in a report script. For more information, see Developing Report Scripts.
This sample report demonstrates the basic use of the BOTTOM command. The report is based on the Sample Basic database.
Use the following script to create Sample 16-A:
<Sym <Column (Scenario, Year) Actual Budget Jan Dec <Row (Market, Product) <ICHILDREN Market <ICHILDREN Product <Bottom (3, @DataColumn(3)) !
The BOTTOM command specifies that only the three lowest data values are returned for each row grouping, based on the target data values specified in column three (Budget, Jan). Notice that no row dimension is selected here, so the report output defaults to the innermost row.
This report script, BOTTOM.REP, is available in your \ESSBASE\APP\SAMPLE\ BASIC directory.
This sample report fragment demonstrates the basic use of the TOP command. The report is based on the Sample Basic database.
Use the report script TOP.REP, reproduced here, to create Sample 16-B:
<Sym //Supress shared members from displaying <Supshare <Column (Scenario, Year) Actual Budget Jan Dec <Row (Market, Product) <Desc Market //Use bottom level of products <DimBottom Product <Top (10, @DataColumn(3)) !
The TOP command specifies that only the ten highest data values are returned for each row grouping, based on the target data values specified in column three (Budget, Jan).
This report script, TOP.REP, is available in your \ESSBASE\APP\SAMPLE\ BASIC directory.
The following report demonstrates the use of the RESTRICT conditional retrieval command in a report script. For more information, see Developing Report Scripts.
Use the following script to create Sample 17:
<Sym <Column (Scenario, Year) Actual Budget Jan Dec <Row (Market, Product) <Ichildren Market <Ichildren Product <Restrict (@DataCol(3) < $300.00 ) !
The RESTRICT command specifies that only data values that are less than $300.00 are returned for each row grouping, based on the target data values specified in column three (Budget, Jan). Notice that no row dimension is selected here, so the report output defaults to the innermost row.
This report script, RESTRICT.REP, is available in your \ESSBASE\APP\ SAMPLE\BASIC directory.
The following report demonstrates the use of the ORDERBY conditional retrieval command in a report script. For more information, see Developing Report Scripts.
Use the following script to create Sample 18:
<Page ("Measures") <Column ("Scenario", "Year") <Row ("Market", "Product") "Sales" "Scenario" "Jan" "Feb" "Mar" "Apr" "New York" "Product" "100" "100-10" "100-20" "100-30" "200" "200-10" "200-20" "200-30" "200-40" "300" "300-10" "300-20" "300-30" "400" "400-10" "400-20" "400-30" "Diet" "100-20" "200-20" "300-30" <ORDERBY ("Product", @DATACOL(1) ASC, @DATACOL(2) DESC, @DATACOL(3) ASC @DataCol (4) DESC) !
The ORDERBY command is based only on data in the data columns. If the SUPPRESSMISSING command is not used in the report, #MISSING is considered to be the lowest data value. ORDERBY compares data values in the following order:
If two data values are the same, the sort proceeds to the next column to determine the order.
In the following subset of Sample 18, for Product 200-10, the data values in COL1 and COL2 are both 61; the data in COL1 should be in ascending order, the data in COL2 should be in descending order. The two values are compared, and as they are the same, COL2 and COL3 are compared. Therefore, even though COL2 is supposed to be in descending order, the comparison for the row 400-30 was determined by the values in COL3, which is in ascending order.
COL 1 COL 2 COL 3 COL 4 ===== ===== 200-10 61 61 63 66 400-30 134 189 198 198 300-20 180 180 182 189 |
The report script for Sample 18, ORDERBY.REP, is available in your \ESSBASE\APP\SAMPLE\ BASIC directory.
The following report demonstrates the use of the LINK command to narrow the members returned in a selection in a report script. For more information, see Developing Report Scripts.
Use the following script to create Sample 19:
<Page (Market) <Column (Year) Qtr1 Qtr2 <Row (Product) <Link (<UDA (product, naturally-flavored) OR <LEV (product, 0)) !
The LINK command uses the AND, OR, and NOT Boolean operators to refine your search. In the preceding example, the product with the "naturally-flavored" user-defined attribute (UDA), as well as all Level 0 products, are returned in the search.
Be careful how you group operators in the LINK expression. Hyperion Essbase evaluates operators from left to right. Use parentheses to group the expressions. For example, A OR B AND C is the same as ((A OR B) AND C). In the first expression, Hyperion Essbase evaluates the expression from left to right, evaluating A OR B before evaluating AND C. In the second expression, Hyperion Essbase evaluates the subexpression in parentheses (A OR B) before the whole expression, producing the same result. However, if you use (A OR (B AND C)), Hyperion Essbase evaluates the subexpression in parentheses (B AND C) before the whole expression, producing a different result.
This report script, LINK.REP, is available in your \ESSBASE\APP\SAMPLE\ BASIC directory.
This sample report uses members of attribute dimensions to view data on base dimensions that are associated with those attribute dimensions.
Use the following script to create Sample 20:
{WIDTH 12} <Page (Measures, Scenario, Caffeinated, Year, Market) Profit Actual Caffeinated_True Qtr1 East <Column (Ounces) <ICHILDREN Ounces <Row ("Pkg Type") <ICHILDREN "Pkg Type" !
The report output reflects data on Quarter 1 profits for caffeinated products by all their available sizes and package types. The data values indicate #MISSING when there is no data for a specific size in a specific package type. Because attributes are defined only on sparse dimensions, there are several #MISSING values in the sample report. You can represent missing values by suppressing the row or substituting a replacement text string, such as N/A. See Sample 2: Handling Missing Values for an example of substituting page breaks and labels for missing values.
This report script, ATTR.REP, is available in your \ESSBASE\APP\SAMPLE\ BASIC directory.
This sample report uses the WITHATTR command to view information based on the attributes of the members of a base dimension.
Use the following script to create Sample 21:
{WIDTH 12} <Page (Measures, Scenario, Year, Market) Profit Actual Qtr1 East <Column ("Pkg Type") <ICHILDREN "Pkg Type" <Row (Product) <WITHATTR(Caffeinated,"<>",True) <IDESCENDANTS Product !
The report output reflects data on Quarter 1 profits for caffeinated products by their package types. The data values indicate #MISSING when there is no data for a specific package type. Because attributes are defined only on sparse dimensions, there are several #MISSING values in the sample report.
This report script, WITHATTR.REP, is available in your \ESSBASE\APP\SAMPLE\ BASIC directory.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |