HYBRIDANALYSISON HYBRIDANALYSISOFF |
ATTRIBUTE WITHATTR TODATE |
The & command prefaces a substitution variable in the report script.
&variableName
variableName | The name of the substitution variable set on the database. The variable name can be any combination of letters, numbers, and underscores, up to 80 characters in length. Spaces, punctuation, and other special characters are not supported in the variable name. |
Any string that begins with a leading & is treated as a substitution variable; Hyperion Essbase replaces these variables with their associated value names prior to the parsing of the report script. If you have member names that contain a leading &, they are recognized as substitution variables.
<ICHILDREN &CurQtr
becomes
<ICHILDREN Qtr1
if the substitution variable CurQtr has the value name "Qtr1".
The ! command tells Hyperion Essbase to output the instructions in the report script to the current line.
!
Each report script requires at least one ! command to produce output. Use multiple instances of the ! command to separate multiple report specifications in a report script.
Following !, the new report specification retains data format output commands from previous specifications unless you enter commands in the new report that turn them off. The new report specification does not retain data extraction command defaults.
If you omit ! at the end of the report script and run the report, the report processor does not report output or display an error message.
The AFTER command displays the first character of a designated character string following the data columns in the report.
{ AFTER char [columnList] }
char | Single character enclosed in quotation marks. |
columnList | Optional list of one or more column numbers, separated by spaces. If included, AFTER affects only these columns. If you do not specify columnList, all data columns are affected. |
This command displays only the first character of a string, even if more are specified. If you do not specify any columns in columnList, char is displayed after all data columns in the report.
The {AFTER "%"} command in the following report displays the percent sign after each data value.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) { AFTER "%" } <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,591% 2,476% 2,567% 3,035% 10,669% Compact_Disc 3,150% 3,021% 3,032% 3,974% 13,177% Audio 5,741% 5,497% 5,599% 7,009% 23,846% |
The <ALLINSAMEDIM command selects all the members from the same dimension as the specified dimension member for the report.
<ALLINSAMEDIM mbrName
mbrName | Single member representing a dimension. All members from this dimension are selected. |
<ALLINSAMEDIM Audio
Selects all the members from the dimension for the following report.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <ALLINSAMEDIM Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ======= ======= ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Audio 5,741 5,497 5,599 7,009 23,846 Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Camera 2,506 2,522 2,602 3,227 10,857 Visual 10,795 10,102 11,812 14,365 47,074 Product 16,536 15,599 17,411 21,374 70,920 |
ALLINSAMEDIM is not supported with HYBRIDANALYSISON.
The ALLSIBLINGS command adds all the siblings of the specified member to the report.
<ALLSIBLINGS mbrName
mbrName | Name of member whose siblings you want to add. |
Siblings are all the members that have the same parent as mbrName in the Database Outline.
<ALLSIBLINGS Stereo
Selects the siblings of the member Stereo for the following report.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW Product) <ALLSIBLINGS Stereo !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 |
The ANCESTORS command adds all the ancestors of the specified member to the report.
<ANCESTORS mbrName
mbrName | Name of member whose ancestors you want to add. |
<ANCESTORS Stereo
Adds Audio and Product to the following report since Audio is the parent to Stereo and Product is the parent to Audio.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <ANCESTORS Stereo !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ====== Audio 5,741 5,497 5,599 7,009 23,846 Product 16,536 15,599 17,411 21,374 70,920 |
The ASYM causes a report to be printed in an asymmetric format.
<ASYM
Hyperion Essbase prints an asymmetric report (with BLOCKHEADERS) only when all column dimensions include the same number of selected members and all members from each column dimension are on the same line. Otherwise, a symmetric report (with PYRAMIDHEADERS) is produced.
This command reverses a previously specified SYM command in an asymmetric report.
If the <SYM keyword is used, all report headers appear in a symmetric format, even if there are equal numbers of members in each row of the column header. In this case, the <ASYM keyword is used to turn off symmetric mode.
The following example is based on Sample Basic.
<PAGE (Measures, Market) South Sales <SYM <COLUMN (Scenario, Year) Actual Budget Jan Feb <ROW (Product) <IDESCENDANTS "100" ! <ASYM !
Which produces the following reports:
Sales Texas Actual Budget Jan Feb Jan Feb ======== ======== ======== ======== 100-10 452 465 560 580 100-20 190 190 230 230 100-30 #Missing #Missing #Missing #Missing 100 642 655 790 810 Sales Texas Actual Budget Jan Feb ======== ======== 100-10 452 580 100-20 190 230 100-30 #Missing #Missing 100 642 810 |
The <ATTRIBUTE command returns all base-dimension members that are associated with a specified attribute.
<ATTRIBUTE attMbrName
attrMbrName | The name of a member of an attribute dimension |
<ATTRIBUTE Red
returns all base-dimension members that are associated with the member red of the specified attribute dimension.
<ATTRIBUTE Large
<ATTRIBUTE
Caffeinated_True)
.<ATTRIBUTE
12_Ounces
).The BEFORE command displays a character string before the data columns in the report.
{ BEFORE "char" [ columnList ] }
char | A single character enclosed in quotation marks. |
columnList | Optional. List of the column numbers, separated by spaces, that you want char to precede. Without columnList, char is displayed before all columns in the report. |
Quotes without a character string clear the text displayed before data columns. For example, { BEFORE "" } turns off previously issued BEFORE commands.
The command { BEFORE "$" } displays the dollar sign before all the data values in the following report.
<PAGE Market, Accounts, Scenario) Chicago Sales Actual <COLUMN Year) <ICHILDREN Year <ROW (Product) { BEFORE "$" } <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo $2,591 $2,476 $2,567 $3,035 $10,669 Compact_Disc $3,150 $3,021 $3,032 $3,974 $13,177 Audio $5,741 $5,497 $5,599 $7,009 $23,846 |
The BLOCKHEADERS command displays all members that apply to a column as the column heading, in the style used by asymmetric reports.
{ BLOCKHEADERS }
The only format that can be used with asymmetric reports. Pyramid headers are the default for symmetric reports.
This command displays all column members above each column in asymmetric style, in contrast to the pyramid display that is the default for symmetric reports.
The following example is based on Sample Basic.
<PAGE Measures) Sales {WIDTH 7} {BLOCKHEADERS} <SYM <COLUMN (Scenario, Year, Market) Actual Budget Jan Feb East West <ROW (Market) <IDESCENDANTS "400" !
This example produces the following report:
Sales Actual Actual Actual Actual Budget Budget Budget Budget Jan Jan Feb Feb Jan Jan Feb Feb East West East West East West East West ====== ====== ====== ====== ====== ====== ====== ====== 400-10 562 1,115 560 1,122 580 740 580 740 400-20 219 1,032 243 1,065 230 690 260 700 400-30 432 625 469 618 440 410 490 400 400 1,213 2,772 1,272 2,805 1,250 1,840 1,330 1,840 |
The BOTTOM command returns rows with the lowest values of a specified data column.
<BOTTOM ([<rowgroupDimension>,]<rows>, <column>)
<rowgroupDimension> | Optional row grouping dimension that determines the rows to sort as a set. |
<rows> | Positive integer number of rows to be returned; must be greater than 0. |
<column> | @DATACOL (<colnumber>) | @DATACOLUMN (<colnumber>)
where <colnumber> is the target column number; must be between 1 and the maximum number of columns in the report. |
The row grouping dimension has a default of the inner row.
This command sorts the result set by the value of the specified data column in descending order.
Rows containing #MISSING values in the sort column are discarded from the result set before BOTTOM is applied.
You can use TOP and BOTTOM, ORDERBY and RESTRICT in the same report script, but you can use each command only once per report. If you repeat the same command in a second report in the same report script, the second command overwrites the first. Place global script formatting commands before a PAGE, COLUMN command or associated member (for example, <ICHILDREN or <IDESCENDANTS). Avoid using row formatting commands with BOTTOM.
If any of the ORDERBY, TOP, BOTTOM, or RESTRICT commands exist together in a report script, the row group dimension <rowgroupDimension> should be the same. This prevents confusion about the sorting and ordering of rows within a row group. Otherwise, an error is issued.
The ORDERBY, TOP, and BOTTOM commands sort a report output by its data values. The RESTRICT command restricts the number of valid rows for the report output. Their order of execution is:
This order of execution applies irrespective of the order in which the commands appear in the report script.
You can use configurable variables to specify the size of the internal
server buffers used for storing and sorting the extracted data. The ReptKByteBuf
,
ReptKByteSortBuf
,
and
NumericPrecision
variables affect the way the RESTRICT, TOP,
and BOTTOM commands work. For more information on configurable variables,
see the Database Administrator's Guide.
<Page (Market, Accounts, Scenario) Chicago Sales Actual <Bottom (5, @DataCol(4)) <Column(Year) <Ichildren Year <Row(Product) <Idescendants Product ! <Bottom (3, @DataCol(1)) {Indentgen 3} Boston Sales Actual <Ichildren Year <Idescendants Product !
Which produces the following report based on the Demo Basic sample database:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Camera 2,506 2,522 2,602 3,227 10,857 Stereo 2,591 2,476 2,567 3,035 10,669 -------------------------------------------------------------------------------- Boston Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Compact_Disc 3,290 3,034 3,132 3,571 13,027 Stereo 2,450 2,341 2,377 2,917 10,085 Camera 2,230 2,255 2,266 3,162 9,913 Boston Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Compact_Disc 3,290 3,034 3,132 3,571 13,027 Stereo 2,450 2,341 2,377 2,917 10,085 Camera 2,230 2,255 2,266 3,162 9,913 |
The following example uses the ORDERBY, TOP, BOTTOM, and RESTRICT functions:
<TOP ("Year", 10, @Datacol(2)) {Width 15} {Decimal 2} {OutAltNames} <BOTTOM ("Year", 5, @DataCol(2)) <OutMBrAlt <Column(Scenario) {SupBrackets} Actual Budget "Variance %" <RESTRICT (@DataCol(2) > 3000 and @DataCol(1) < 3500) <Row(Year, Product) <Idescendants Product <Children Year <OrderBy ( "Year",@DataCol(1), @DataCol(2) Desc) !
Which produces the following report based on the Sample Basic sample database:
Measures Market Actual Budget Variance % ============== ============== ============== Qtr2 100-20 Diet Cola 1,534.00 21,010.00 -38.15 100-20 Diet Cola 1,534.00 21,010.00 -38.15 300-30 Diet Cream 2,723.00 3,100.00 -12.16 300-30 Diet Cream 2,723.00 3,100.00 -12.16 Qtr4 300-30 Diet Cream 2,820.00 3,080.00 -8.44 300-30 Diet Cream 2,820.00 3,080.00 -8.44 200-20 Diet Root 2,834.00 3,790.00 -25.22 200-20 Diet Root 2,834.00 3,790.00 -25.22 Qtr1 200-20 Diet Root 2,963.00 3,600.00 -17.69 Qtr2 200-20 Diet Root 3,079.00 3,640.00 -15.41 200-20 Diet Root 3,079.00 3,640.00 -15.41 Qtr3 200-20 Diet Root 3,149.00 3,700.00 -14.89 200-20 Diet Root 3,149.00 3,700.00 -14.89 400-10 Grape 3,201.00 3,090.00 3.59 300-10 Dark Cream 3,355.00 3,730.00 -10.05 |
The BRACKETS command displays parentheses around negative numbers instead of negative signs.
{ BRACKETS }
Brackets are the default for negative numbers.
The BRACKETS command need only be used to cancel the effect of a previously issued SUPBRACKETS command. Brackets are used by this command to mean parentheses.
{BRACKETS} displays -43.243 as (43.243) in the report.
The CALCULATE COLUMN command creates a new report column, performs on-the-fly calculations, and displays the calculation results in the newly-created column.
{ CALCULATE COLUMN "newColumn" = expression }
"newColumn" | New column name enclosed by quotation marks. |
expression | Any valid column calculation expression.
The following mathematical operators are supported in column calculations: + Addition operator. - Subtraction operator. * Multiplication operator. %X%Y Evaluates X as a percentage of Y. / Division operator. :X:Y Performs a summation of data values from X to Y (inclusive). Must be the first operator if used with multiple operators. |
If an operation or equation is not specified, the default is + (add).
CALCULATE COLUMN adds up to 499 ad-hoc column calculations to a report (the maximum number of column calculations that can be defined at any one time).
Each new calculated column is appended to the right of the existing columns in the order in which it is created, and is given the next available column number.
See the ORDER command for more information on column numbering and ordering.
The following example is based on Sample Basic.
<PAGE (Measures, Market) Sales <SYM <COLUMN (Scenario, Year) Actual Budget Jan Feb {WIDTH 8 0} {WIDTH 7} {WIDTH 11 5 6} {CALCULATE COLUMN "Actual YTD" = 1 + 2} {CALCULATE COLUMN "Budget YTD" = 3 + 4} {ORDER 0 1 2 5 3 4 6} <ROW (Market) <CHILD "400" !
This example produces the following report:
Sales Market Actual Budget Jan Feb Actual Ytd Jan Feb Budget Ytd ====== ====== ========== ====== ====== ========== 400-10 2,839 2,879 5,718 2,320 2,350 4,670 400-20 2,562 2,596 5,158 2,040 2,050 4,090 400-30 1,233 1,261 2,494 990 1,030 2,020 |
The following samples demonstrate other column calculations that you can perform:
To calculate a new column named "1st Qtr" equal to the sum of the first
3 columns, use:
{CALCULATE COLUMN "1st Qtr" = 1 : 3}
To calculate a new column that is equal to column 12 taken as a percentage
of the value in column 12 of a calculated row called "Total Sales", use:
{CALCULATE COLUMN "% of Total" = 12 % "Total Sales" 12}
To calculate a new column equal to column 1 multiplied by the constant
35, use:
{CALCULATE COLUMN "Extended_Price" = 1 * 35.}
The following example calculates a new column, adds column 1 to column 3, divides the result by column 6, adds column 8, takes that result as a percentage of column 15,
multiplies that result by the constant number 100, subtracts the value
from the 3rd column of the calculated row "Tot_Row", and adds the result
to column 12.
{CALCULATE COLUMN "New_Col" = 1+3 / 6+8 % 15 * 100.-"Tot_Row" 3+12}
{CALCULATE COLUMN "New_Col" = 1+3 / 6+8 % 15 * 100.-"Tot_Row" 3+12}
OFFCOLCALCS
ONCOLCALCS
REMOVECOLCALCS
SETROWOP
The CALCULATE ROW command creates a named report row and performs on-the-fly calculations.
{ CALCULATE ROW "newRow" [ columnNo ] = expression}
OR
{ CALCULATE ROW "newRow" [ operator]}
"newRow" | Name of a new row, enclosed by quotation marks, that was declared with CALCROW, SAVEROW, or SAVEANDOUTPUT. |
columnNo | Optional. Designation of column numbers to which Hyperion Essbase selects and applies the expression. |
expression | Any valid row calculation expression. |
operator | Any valid mathematical operator. The following mathematical operators
are supported in row calculations:
+ Addition operator. - Subtraction operator. * Multiplication operator. % X % Y Evaluates X as a percentage of Y . / Division operator. OFF Turns off the row operator. |
If an operation or equation is not specified, the default is + (add).
This command creates a named report row and performs on the fly calculations. CALCULATE ROW creates the row and associates it with a row name or label. This is similar to declaring a variable. The CALCULATE ROW command can also specify an operation (+, _, *, /, or OFF) as an equation consisting of constants, other calculated rows, and operators.
Equations are evaluated at the time of declaration. If an operator is specified, subsequent output rows have the operator applied to them with the result stored in the calculated row.
This is useful for aggregating a series of rows to obtain a subtotal or total. The operator can be reset at any point with SETROWOP. If neither an equation nor an operator are specified in the CALC ROW command, the + operator is assumed.
SETROWOP defines a calculation operator to be applied to all subsequent output data rows. You can display the calculation results in the newly created row with the PRINTROW command.
The following samples demonstrate row calculations that you can perform:
To compute "Avg Sales" by dividing by the constant 2, use:
{ CALC ROW "Avg Sales" = "Total Sales" / 2 }
To multiply the first six data columns of the calculated row "Total
Sales" by the six factors and store the result in the calculated row "Factored
Sales", use:
{ CALC ROW "Factored Sales" = "Total Sales" * [1.0 1.3 1.9 2.3 3.0
3.7 ] }
To store five factors in the first five columns of "Factors", for use
in later calculated row computations and/or PRINTROW output, use:
{ CALC ROW "Factors" = [ 1.3 2.6 3.1 2.3 5 ] }
To store the value from the seventh column of "Total Sales", multiplied
by 1000, in every column of the calculated row "Ending Sales", use:
{ CALC ROW "Ending Sales" = "Total Sales" 7 * 1000 }
To set the value in column 7 of "Ending Sales" to the corresponding
value from the row "Total Sales", use:
{ CALC ROW "Ending Sales"7 = "Total Sales" }
"Total" refers to itself in this calculation and divides itself
by 1000:
{ CALC ROW "Total" = "Total" / 1000. }
To show a variety of operations used in one expression, use an expression
like this:
{ CALC ROW "xyz" = [ 11 12.3 -6 ] / 7 + "abc"2 % 4300. + 10 }
This expression divides the three values in the array by the constant 7 (if there are currently more than three data columns, the extra columns remains #Missing), adds the value from column 2 of "abc" to every column, and computes the resulting row's values as percentages of the constant 4300, and adds the constant 10 to all columns, storing the final result in "xyz". Note that if there are more than three data columns, the result in the extra columns is 10, since prior to the last operation, they were #Missing.
CLEARROWCALC
CLEARALLROWCALC
DUPLICATE
OFFCOLCALCS
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
OUTPUT
PRINTROW
REMOVECOLCALCS
RENAME
SAVEROW
SETROWOP
SUPOUTPUT
The CHILDREN command selects all members in the level immediately below the specified member as defined in the database outline.
<CHILDREN mbrName
mbrName | Dimension or member name of the parent |
This command does not select the specified member.
<CHILDREN Year
Which selects members Qtr1, Qtr2, Qtr3, and Qtr4, in that order (see the Notes for this command).
<CHILD Qtr1
Which selects members Jan, Feb, and Mar, in that order.
DESCENDANTS
ICHILDREN
IDESCENDANTS
The CLEARALLROWCALC command resets the value of all calculated rows
to #MISSING.
{ CLEARALLROWCALC }
CALCULATE ROW
CLEARROWCALC
OFFCOLCALCS
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
PRINTROW
REMOVECOLCALCS
SETROWOP
SUPOUTPUT
The CLEARROWCALC command resets the value of the row calculation
name to #MISSING
.
{ CLEARROWCALC name }
name | Name of a calculated row from a CALCULATE ROW command. |
CALCULATE ROW
CLEARALLROWCALC
OFFCOLCALCS
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
PRINTROW
REMOVECOLCALCS
RENAME
SAVEANDOUTPUT
SAVEROW
SETROWOP
SUPOUTPUT
The COLHEADING command turns on automatic display of the column header, and sets it to be output prior to display of the next non-suppressed output data row.
{ COLHEADING }
By default, page and column headers (together called the HEADING) are turned on. This means they are displayed prior to the first actual output row in a report, and are reset to display again whenever:
A specific COLHEADING, PAGEHEADING, or IMMHEADING dictates a new heading. Once they are reset to "display", they are output just prior to the new non-suppressed output row.
This command can be specified between the STARTHEADING and ENDHEADING commands to position the heading relative to other outputs defined in the custom heading.
When this command is used, the column members are displayed at the time the heading is generated, rather than immediately. Thus, if this command was issued at the start of the report script, it would still generate column headings only as part of the regular heading, and not as the first item on the page.
This command also displays column headings after they have been suppressed with either a SUPCOLHEADING, SUPHEADING, or SUPALL command.
The command COLHEADING displays the column heading members for a second time in the following report after displaying a blank line with the SKIP command.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <ICHILDREN Audio { SKIP COLHEADING } <ICHILDREN Visual !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Audio 5,741 5,497 5,599 7,009 23,846 Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Camera 2,506 2,522 2,602 3,227 10,857 Visual 10,795 10,102 11,812 14,365 47,074 |
HEADING
SUPCOLHEADING
IMMHEADING
SUPPAGEHEADING
PAGEHEADING
TEXT
The COLUMN dimensions command specifies the column or columns to display across the page.
<COLUMN ( dimList)
dimList | Dimension name or a comma-delimited list of dimensions |
This command defines the dimensions that are displayed as column members in the final report. Column members are displayed above the data columns in the report. Any member in the report specification from the dimension the member in the COLUMN command is a column member. The order of the members in the command determines the order of the column headers in the report. The first header line of column members are from the same dimension as the first member in the dimList. The second line members are from the dimension of the second member, and so on. You can have at most one member from each dimension in the dimList and cannot repeat a dimension.
Once you have identified the column dimensions using this command, any members from those dimensions that are a part of the report are defined as the data columns. If a member is not selected from a column dimension, then the highest member in that dimension is used.
<COLUMN (Year, Scenario)
Which creates a report with Year members at the head of each column. Nested below each Year member are columns headed by members of Scenario.
The COMMAS command displays commas for numbers greater than 999 after commas have been suppressed with either a SUPCOMMAS or SUPALL command.
{ COMMAS }
{ COMMAS } displays the number 1345 as 1,345 in the report.
BRACKETS
DECIMAL
SUPALL
SUPCOMMAS
The CURHEADING command enables the display of the currency conversion heading.
{ CURHEADING }
This command turns on the display of the currency conversion heading, if it was suppressed with SUPCURHEADING. The currency conversion heading is displayed along with each page heading as it is displayed.
See the example for the CURRENCY command.
IMMHEADING
CURRENCY
SUPCURHEADING
TEXT
The CURRENCY command performs on-the-fly currency conversions in a report.
<CURRENCY targetCurrency
targetCurrency | Currency and currency type to display in the report. Currency type
is optional. Up to four members (at most one from each currency database
dimension) in a cross-dimensional member (->)
For example: US$, or FFrancs->Actual->Jun99 |
This command converts the data values in the report to the targetCurrency and causes the currency heading to be displayed in the final report with the page heading. This does not convert the data in the database, only in the report.
If the <CURRENCY command is not used, the data is reported as it is currently stored in the database. Typically, the Database is set up with currency conversions performed, so no additional conversion is required. The <CURRENCY command is primarily for ad hoc currency translations.
<PAGE (Market, Measures, Scenario) Illinois Sales Budget <COLUMN (Year) <CHILDREN Qtr1 <CURRENCY US$ <ICHILDREN Colas !
This example produces the following report:
Currency: US$ Illinois Sales Budget Jan Feb Mar ======== ======== ======== 100-10 360 370 380 100-20 240 260 280 100-30 #Missing #Missing #Missing 100 600 630 660 |
The DECIMAL command determines the number of decimal places to display in the report.
{ DECIMAL decPlaces | VARIABLE [ columnN [columnN] ] }
decPlaces | Number of decimal places to display. Positive integer from 0 (the default) to 40. Specify either VARIABLE or decPlaces. |
VARIABLE | Allows the decimal to float; may switch to scientific notation (E+00 format) if necessary to display the significant digits of a number in the given column width. Specify either VARIABLE or decPlaces. |
columnN | Optional. List of columns, separated by spaces, to be affected by this command. If omitted, the command affects all data columns. |
Positive integer from 0 (the default) to 40.
If you specify columns in the DECIMAL command before designating them with a member selection, the column numbers apply to all selected columns with a number that is a multiple of the specified column number.
The total number of specified column numbers should not exceed the value of columnN.
{DECIMAL 2}
Displays the number 65.4365 as 65.44 in the final report.
BRACKETS
COMMAS
SUPBRACKETS
SUPCOMMAS
The DESCENDANTS command adds the descendants of the specified member to the report, excluding mbrName itself.
<DESCENDANTS mbrName
mbrName | Name of parent of descendants to include. |
Adding the descendants of the top of the dimension adds all the members in the dimension to the report, except the dimension top. The IDESCENDANTS command includes the specified member.
<DESCENDANTS Year
Which selects members Jan, Feb, Mar, Q1, Apr, May, June, Q2, Jul, Aug, Sep, Q3, Oct, Nov, Dec, Q4.
CHILDREN
ICHILDREN
IDESCENDANTS
The DIMBOTTOM command includes the members at the bottom of the dimension, those members with no children (level 0), to the report.
<DIMBOTTOM mbrName
mbrName | A member from the dimension for which you want to retrieve the bottom-level members. Whether you specify the dimension name or the member name, this command retrieves the level 0 members associated with the dimension to which the member belongs. |
This command extracts the members at the bottom of the dimension, those members with no children (level 0), to the report. mbrName is from the dimension whose bottom members you want to select.
The command <DIMBOTTOM Product adds all the members from the bottom of the Product dimension to the following report:
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <DIMBOTTOM Product !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Camera 2,506 2,522 2,602 3,227 10,857 |
The DIMEND command allows you to specify a dimension format that is processed after cycling through all the members in the dimension.
<DIMEND dimList
dimList | List of members, separated by commas, that represents the dimensions for which the format is intended. |
The dimension format is made up of one or more format commands to be
processed after cycling through all the members in the dimensions.
Any formatting commands in the report script encountered immediately
before the DIMEND command become formats for all dimensions listed in the
dimList
of the command.
When the report is produced, after processing all members from the specified
dimension(s) associated with the format, including the processing of any
groups of members from other dimensions which are nested inside the specified
dimension(s), the DIMEND format is then processed.
The UCOLUMNS format command underlines the columns in the report after every cycle through the Market dimension. In the report, you see the children of Qtr1 for East followed by the children of Qtr1 for the West. After West, before starting over with East again, the processing of UCOLUMNS displays the underlines in the report.
<PAGE (Accounts, Scenario) Sales Actual <COLUMN (Product) /* Applied after dimension processing*/ <ICHILDREN Audio <ROW (Market,Year) East West <CHILDREN Qtr1 { UCOLUMNS } <DIMEND(Market) /* Puts underline after Market */ !
This example produces the following report:
Sales Actual Stereo Compact_ Audio ======== ======== ======== East Jan 2,788 3,550 6,338 Feb 2,482 3,285 5,767 Mar 2,569 3,458 6,027 West Jan 4,102 4,886 8,988 Feb 3,723 4,647 8,370 Mar 3,808 4,788 8,596 =========== ============ ======== ======== ======== |
The DIMTOP command adds the top of the dimension for the member to the report.
<DIMTOP mbrName
mbrName | Single member from the dimension to designate. |
You can specify any member from the dimension with this command including the actual top of the dimension.
<DIMTOP Stereo
Adds the top of the Product dimension to the report.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <DIMTOP Stereo !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ======= ======= ======= ======= Product 16,536 15,599 17,411 21,374 70,920 |
The DUPLICATE command allows a member name to occur more than once in a dimension group selection.
<DUPLICATE mbrRange
mbrRange | Specifies a single member name or selection command.
|
This command is useful either (a) in a multi-section report when the same row name appears more than once in each section or (b) when the row must be captured (without printing) once at the top of each section for calculation purposes, and included again later in the section for output.
The following example is based on Sample Demo.
<PAGE (Market) East <COLUMN (Scenario, Year) Budget Actual Jan Jan { ORDER 2,0,1,3,4 WIDTH 12 0 1 NOINDENTGEN AFTER "%" 4 SKIPONDIM Product LMARGIN 10 } <ROW (Product, Accounts) { CALC ROW "Sales" OFF } { CALC COL "Actual~% Sales" = 2 % "Sales" 2 } <ICHILDREN Visual { SAVEROW } Sales Payroll Marketing Profit <DUPLICATE Sales !
This example produces the following report:
East Budget Actual Actual Jan Jan % Sales ======== ======== ======== 1,200 Television Payroll 1,236 25% 440 Marketing 365 9% 1,240 Profit 1,295 26% 4,800 Sales 5,244 100% 1,030 VCR Payroll 1,044 25% 150 Marketing 156 4% 1,466 Profit 1,417 35% 4,200 Sales 4,311 100% 1,195 Camera Payroll 1,167 42% 300 Marketing 288 11% 528 Profit 400 19% 2,850 Sales 2,656 100% 3,425 Visual Payroll 3,447 29% 890 Marketing 809 8% 3,234 Profit 3,112 27% 11,850 Sales 12,211 100% |
The ENDHEADING command ends the definition of the custom page heading that is displayed at the top of each page in the report.
{ ENDHEADING }
This command ends the definition of the custom page heading displayed at the top of each page in the report and in certain other situations. The STARTHEADING command begins the heading, and all commands encountered between the STARTHEADING and ENDHEADING are part of the heading definition.
See example for the STARTHEADING command.
HEADING
IMMHEADING
STARTHEADING
SUPHEADING
The EUROPEAN command enables non-US formatting of numbers by switching commas and decimal points in the final report data values.
{ EUROPEAN }
This command enables non-US formatting of numbers by switching commas
and decimal points in the final report data values.
Decimal points are used as the thousands separator character while commas
separate the decimal portion of the number from the integer portion.
The following example is based on Sample Demo.
This report displays an example of the { EUROPEAN } command for the
report based on Chicago followed by the { SUPEUROPEAN } command for the
Boston report.
<PAGE(Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) <CHILDREN Audio ! {EUROPEAN} Chicago Sales Actual <CHILDREN Year <CHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== Stereo 2,591 2,476 2,567 3,035 Compact_Disc 3,150 3,021 3,032 3,974 Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== Stereo 2.591 2.476 2.567 3.035 Compact_Disc 3.150 3.021 3.032 3.974 |
BRACKETS
COMMAS
DECIMAL
SUPBRACKETS
SUPCOMMAS
SUPEUROPEAN
The FEEDON command enables page breaks in a report when the number of output lines on a page is greater than the current PAGELENGTH setting.
{ FEEDON }
The defaults are FEEDON and PAGELENGTH of 66 lines.
This command enables page breaks (and, by default, a new page header) in a report when the number of output lines on a page is greater than the current PAGELENGTH setting. This command is used after a SUPFEED command has disabled the page break.
The FIXCOLUMNS command fixes the number of total columns in the final report regardless of how many actual columns are originally selected.
{ FIXCOLUMNS number }
number | Number of columns that you want to be displayed in your final report. |
This command fixes the number of total columns in the final report regardless of how many actual columns are originally selected. Only the first number of columns, which includes row name columns and data columns, are displayed.
This command is often used in conjunction with the ORDER command to select and reorder a specific subset of the requested columns, cutting off the unwanted excess columns.
The following examples are based on Sample Demo.
The command { FIXCOLUMNS 3 } causes only 3 columns, the row name column
and two data columns, to be displayed even though there are additional
columns for the data values of Qtr3, Qtr4 and Year.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) {FIXCOLUMNS 3} <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 ======== ======== Stereo 2,591 2,476 Compact_Disc 3,150 3,021 Audio 5,741 5,497 |
This example used FIXCOLUMNS and ORDER to create a non-symmetric report.
<PAGE (Market, Accounts) <COLUMN (Year, Scenario) <ROW (Product) { ORDER 0,1,3,5,6 FIXCOLUMNS 5 } Chicago Sales Jan Feb Mar Actual Budget <ICHILDREN Audio !
Chicago Sales Jan Feb Mar Mar Actual Actual Actual Budget ======== ======== ======== ======== Stereo 923 834 834 900 Compact_Disc 1,120 1,050 980 1,000 Audio 2,043 1,884 1,814 1,900 |
If the command { BLOCKHEADERS } had also been used, the output would be:
Chicago Sales Jan Feb Mar Mar Actual Actual Actual Budget ======== ======== ======== ======== Stereo 2,591 2,476 2,348 2,438 Compact_Disc 3,150 3,021 3,115 3,028 Audio 5,741 5,497 5,825 5,003 |
Note that without the FIXCOLUMNS, the column headers would have been:
Jan Feb Mar Actual Budget Actual Budget Actual Budget
The FORMATCOLUMNS command expands the number of data columns when processed.
{ FORMATCOLUMNS number }
number | Expected number of columns that are encountered for formatting purposes. |
This command expands the number of data columns when processed. Before any data column members are added, the report assumes only one data column. FORMATCOLUMNS and other commands that refer to column numbers automatically expand the number of data columns when processed. FORMATCOLUMNS formats the report page set-up for a predetermined number of data columns for text and heading set-up.
This command does not actually limit the number of output columns as FIXCOLUMNS does. For example, a TEXT command used to center text can be issued before the addition of members that define the data columns, so centering would be off unless FORMATCOLUMNS is used to indicate the expected number of columns.
{ FORMATCOLUMNS 10 } sets up an expected report size of 10 columns for formatting purposes.
The HYBRIDANALYSISON command enables Essbase to interpret Report Writer commands in the context of a Hybrid Analysis Relational Source.
<HYBRIDANALYSISON
If a database contains a Hybrid Analysis Relational Source, specifying the <HYBRIDANALYSISON command before Report Writer commands causes Essbase to determine if the Report Writer commands need to be extended to include one or more Hybrid Analysis members.
You can use the <HYBRIDANALYSISON command multiple times in a report, and you can place the command around members or expansions that do not include Hybrid Analysis members without affecting the report results. <HYBRIDANALYSISON applies to all subsequent member selections until its effect is cancelled by the <HYBRIDANALYSISOFF command.
Disabling Hybrid Analysis in Application Manager takes precedence over the <HYBRIDANALYSISON setting for any dimensions that are disabled and thus cancels any <HYBRIDANALYSISON command issued in Report Writer.
Assume that some members of the Product dimension are present in a Hybrid Analysis Relational Source and that no members of Scenario and Market are present in the Hybrid Analysis Relational Source. When the following report script is run, Hybrid Analysis members in the Product dimension are returned.
<DESC "Scenario" <HYBRIDANALYSISON <DESC "Product" <DESC "Market"
Assume that 100-10-1 is present in the Hybrid Analysis Relational Source. The following report script produces an error because 100-10-1 does not exist in the Essbase outline. If <HYBRIDANALYSISON were specified, 100-10-1 would be recognized in the report.
<DESC "Scenario" <DESC "100-10-1" <DESC "Market"
The HYBRIDANALYSISOFF command prevents Essbase from interpreting Report Writer commands in the context of a Hybrid Analysis Relational Source.
<HYBRIDANALYSISOFF
If a database contains a Hybrid Analysis Relational Source, specifying the <HYBRIDANALYSISOFF command before one or more Report Writer commands prevents Essbase from extending the report to include Hybrid Analysis members. You can use <HYBRIDANALYSISOFF multiple times in a report. <HYBRIDANALYSISOFF applies to all subsequent member selections until its effect is cancelled by the <HYBRIDANALYSISON command.
Assume that some members of the Product and Market dimensions are present in the Hybrid Analysis Relational Source and that no members of Scenario are present in the Hybrid Analysis Relational Source. When the following report script is run, Hybrid Analysis members in the Product dimension are returned; however, Hybrid Analysis members in the Market dimension are not returned because retrievals subsequent to the <HYBRIDANALYSISOFF command cannot include a dimension that has members present in the Hybrid Analysis Relational Source.
<DESC "Scenario" <HYBRIDANALYSISON <DESC "Product" <HYBRIDANALYSISOFF <DESC "Market"
The HEADING command displays the page heading: either the default heading or the heading as defined with the STARTHEADING and ENDHEADING commands.
{ HEADING }
The default heading includes the page member heading, the column member heading, and, if applicable, the currency heading.
This command displays the page heading: either the default heading or the heading as defined with the STARTHEADING and ENDHEADING commands. If the SUPHEADING command has been used to turn off the display of the heading, this command also turns it back on, printing it just before the next non-suppressed output row, and thereafter at the top of every new page (unless SUPHEADING is used again). The heading automatically adjusts to any change in column or page selection members and is generated prior to the next output data row without the need for a further HEADING command.
See the example for the STARTHEADING command for an example of a heading.
COLHEADING
ENDHEADING
IMMHEADING
PAGEHEADING
STARTHEADING
SUPHEADING
The IANCESTORS command adds a member and its ancestors to the report.
<IANCESTORS mbrName
mbrName | Single member whose ancestors you want to include. |
The ancestors of a member consists of its parent, that parent's parent, and so on, all the way to the top member of the dimension, including the specified member.
The ICHILDREN command selects the specified member and all members in the level immediately below it.
<ICHILDREN mbrName
mbrName | Dimension or member name of the parent |
This command selects a member and its children, as defined in the Database Outline. <
<ICHILDREN Year
Which selects members Qtr1, Qtr2, Qtr3, Qtr4, and Year, in that order (see the Notes for this command).
<ICHILDREN Qtr1
Which selects members Jan, Feb, Mar, and Qtr1, in that order (see the Notes for this command).
ANCESTORS
CHILDREN
DESCENDANTS
PARENT
The IDESCENDANTS command adds the specified member and its descendants to the report.
<IDESCENDANTS mbrName
mbrName | Name of single member and descendants to add to the report. |
Adding the descendants of the top of the dimension adds all the members in the dimension to the report, including the dimension top.
<IDESCENDANT Product
Adds all the members from the Product dimension to the report since all the members are descendants of the member Product which is the top of the dimension. Audio and Visual are the children of Product. Stereo and Compact_Disc are the children of Audio while Television, VCR, and Camera are the children of Visual.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <IDESCENDANTS Product !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Audio 5,741 5,497 5,599 7,009 23,846 Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Camera 2,506 2,522 2,602 3,227 10,857 Visual 10,795 10,102 11,812 14,365 47,074 Product 16,536 15,599 17,411 21,374 70,920 |
ANCESTORS
CHILDREN
DESCENDANTS
PARENT
The IMMHEADING command forces the immediate display of the heading without waiting for the next non-suppressed data row.
{IMMHEADING}
Under normal circumstances, the heading only appears when at least one
non-suppressed row is ready to be output on the current page. For this
reason, when any suppression commands are turned on (such as SUPMISSING
or SUPZEROS), and an entire page is suppressed, those page headers are
normally skipped entirely.
An occurrence of the IMMHEADING command prints the header immediately,
even if there is no current row to print. This command does not unsuppress
data, but simply prints its headings.
This command is useful for inserting special formatting between the heading and the first output record. This is usually impossible because the header does not print until it is ready to output data immediately, that is, after any formats associated with the row have been processed.
See the example for STARTHEADING for an example of a heading.
ENDHEADING
HEADING
STARTHEADING
SUPHEADING
The INCEMPTYROWS command displays empty rows of data, or rows that contain
only zeros or #MISSING
data values, in the final report.
{ INCEMPTYROWS }
This command displays empty rows of data, or rows that contain only
zeros or #MISSING
data values, in the final report. This command
is used to cancel the effects of SUPEMPTYROWS, SUPMISSINGROWS or SUPZEROROWS.
INCMISSINGROWS
INCZEROROWS
SUPALL
SUPEMPTYROWS
SUPMISSINGROWS
SUPZEROROWS
The INCFORMATS command controls the formats affected by the following commands: SUPMASK, SUPMISSING, and SUPZERO.
{ INCFORMATS }
Whenever the SUPMASK, SUPMISSING, or SUPZERO commands are used, by default SUPFORMATS is also set on, unless it has been specifically turned off.
INCFORMATS prints out the format associated with a particular data row
even when that row is suppressed.
This means that line formatting, TEXT and MASK commands, and headers
do not print unless their associated data rows print (or are not suppressed).
The INCMASK command re-includes (turns back on) the mask that has been suppressed by the command SUPMASK.
{ INCMASK }
The INCMISSINGROWS command displays missing rows of data, or rows that
contain all #MISSING
data values, in the final report.
{ INCMISSINGROWS }
This command displays missing rows of data, or rows that contain all
#MISSING
data values, in the final report. This command is used
after a SUPMISSINGROWS or SUPEMPTYROWS command has been used to remove
the missing rows from the final report.
INCEMPTYROWS
INCZEROROWS
SUPALL
SUPEMPTYROWS
SUPMISSINGROWS
SUPZEROROWS
The INCZEROROWS command includes rows that contain only data values of zero in the final report.
{ INCZEROROWS }
This command displays zero rows of data, or rows that contain only data values of zero, in the final report. This command is used after a SUPZEROROWS or SUPEMPTYROWS command has been used to remove the zero rows from the final report.
INCEMPTYROWS
INCMISSINGROWS
SUPALL
SUPEMPTYROWS
SUPMISSINGROWS
SUPZEROROWS
The INDENT command shifts the first row names column in column-output order by the specified number of characters.
{ INDENT [ offset ] }
offset | Optional. Number of spaces to indent column 0 from the left boundary
of the name column. Values:
|
Default (No value): Indents columns by 2.
When a member is indented, the width of the names column for that member
is decreased to offset the indent. This does not shift the remaining columns
in the report.
Once the indented names column has been declared, you can use the ORDER
command to moved it within the final output format or precede it with regular
or calculated columns.
Hierarchical relationships between row members are, by default, indicated
by indentation. Indentation only applies to a group of rows generated together,
such as when a single ! is used. If each consecutive row is generated independently,
using its own !, then no indentation occurs.
In the following example, the first report for Chicago shows the default indentation while the second report for Boston uses the { INDENT 10} command to shift the row names column 10 places to the right.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <ICHILDREN Audio ! { INDENT 10 } Boston Sales Actual <ICHILDREN Year <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Audio 5,741 5,497 5,599 7,009 23,846 Boston Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Stereo 2,450 2,341 2,377 2,917 10,085 Compact_~ 3,290 3,034 3,132 3,571 13,027 Audio 5,740 5,375 5,509 6,488 23,112 |
The INDENTGEN command indents subsequent row members in the row names column based on the generation in the database outline.
{ INDENTGEN [ offset ] }
offset | Optional number that determines the amount to indent each succeeding generation from the previous generation. Default: INDENTGEN -2. |
-2 is the default at the start of each report. {INDENTGEN}
This command indents row members in the row names column based on the
generation in the Database Outline. Generations are counted starting at
the top of the dimension.
The top of the dimension is the first generation of the dimension. The
children of the top are the second generation and so on. The
offset
determines how many characters each successive generation is indented.
A positive number places the first generation at the leftmost position
and indents each successive generation to the right. A negative number
places the last generation on the left.
By default, all generations in a row group are indented by -2 for each relative generation difference. A row group is the group of row members selected before a an exclamation point (!) is encountered. If every row is generated separately (a ! after every row member) all the "groups" are one row only, and thus are not indented because there is no relative generation difference.
The indentation is based on relative rather than absolute generation differences so that if a report is working with only the lower levels of a many-level tree, all the row names do not start heavily indented, wasting column space. If offset is not given, it does not have a default value of -2.
The following example shows the default generation indentation for the Chicago report followed by the {INDENTGEN 3} command in the Boston report.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <IDESCENDANTS Product ! { INDENTGEN 3 } Boston Sales Actual <ICHILDREN Year <IDESCENDANTS Product !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ======= ======= ======= ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Audio 5,741 5,497 5,599 7,009 23,846 Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Camera 2,506 2,522 2,602 3,227 10,857 Visual 10,795 10,102 11,812 14,365 47,074 Product 16,536 15,599 17,411 21,374 70,920 Boston Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ======= ======= ======= ======= Stereo 2,450 2,341 2,377 2,917 10,085 Compact_Disc 3,290 3,034 3,132 3,571 13,027 Audio 5,740 5,375 5,509 6,488 23,112 Television 4,197 3,757 4,740 5,000 17,694 VCR 3,645 3,663 4,201 4,509 16,018 Camera 2,230 2,255 2,266 3,162 9,913 Visual 10,072 9,675 11,207 12,671 43,625 Product 15,812 15,050 16,716 19,159 66,737 |
The IPARENT command adds the specified member and its parent to the report.
IPARENT mbrName
mbrName | A single member, which must not be the top member of the dimension. |
This command selects the current member and its parent, as defined in the database outline.
This example selects the member Jan and its parent member, Qtr1, in that order.
<IPARENT Jan
The LATEST command specifies a Dynamic Time Series member in a report script. You must create a Dynamic Time Series member in the database outline before you use it in a report script.
<LATEST mbrName
or
reservedName (mbrName)
reservedName | One of the following pre-defined generation names:
History-To-Date (H-T-D)
Year-To-Date (Y-T-D) Season-To-Date (S-T-D) Period-To-Date (P-T-D) Quarter-To-Date (Q-T-D) Month-To-Date (M-T-D) Week-To-Date (W-T-D) Day-To-Date (D-T-D) |
mbrName | The name of the level 0 member in the Time dimension. |
This command specifies a Dynamic Time Series tag, which has reserved generation names that are defined in the database outline alias table. If you use the < LATEST syntax, the command is applied globally in the report script. If you use the reservedName (mbrName) syntax, Hyperion Essbase applies the command only to the member listed in the syntax argument.
<LATEST Mayor
Q-T-D (May)
Database Administrator's Guide
The LINK command uses the AND, OR, and NOT> Boolean operators, combined with extraction commands, to refine member selections.
<LINK (extractionCommand [operator extractionCommand])
extractionCommand | Any of the following extraction commands or another AND/OR expression:
<ALLINSAMEDIM (member) <ALLSIBLINGS (member) <ANCESTORS (member) <CHILDREN (member) <DESCENDANTS (member) <DIMBOTTOM (member) <DIMTOP (member) <IANCESTORS (member) <ICHILDREN (member) <IDESCENDANT (member) <IPARENT (member) <MATCH (Dimension, match_string) <MEMBER (member) <OFSAMEGEN (member) <ONSAMELEVELAS (member) <PARENT (member) <UDA (Dimension, UDA_name) |
Operator | Any of the following Boolean operators:
|
This command produces member selections using Boolean operators to refine the search. NOT can only be associated with an extraction command, and does not apply to the entire expression. You must use NOT in conjunction with either the AND or OR operators.
The MEMBER extraction command is only used within a LINK expression; you can use the MEMBER selection to select a single member. Do not use the MEMBER command outside of a LINK expression.
<LINK (<UDA(product,Sweet) AND <LEV(product,0))
Selects all level 0 products that are sweet
<LINK ((<IDESCENDANTS("100") AND <UDA(product,Sweet)) OR <LEV(product, 0))
Selects sweet products from the "100" sub-tree plus all level 0 products
<LINK ((<IDESCENDANTS("100") AND NOT <UDA(product, Sweet)) OR <LEV(product, 0))
Selects non sweet products from the "100" sub-tree plus all level 0 products
ALLINSAMEDIM
ALLSIBLINGS
ANCESTORS
CHILDREN
DESCENDANTS
DIMBOTTOM
DIMTOP
IANCESTORS
ICHILDREN
IDESCENDANTS
IPARENT
MATCH
OFSAMEGEN
ONSAMELEVELAS
PARENT
UDA
The LMARGIN command sets the left margin for the report to marginSize characters.
{ LMARGIN [ marginSize ] }
marginSize | Optional numeric value: number of character spaces for left margin. |
If the LMARGIN command is not used, the default is 0. If marginSize is omitted, it assumes a default value of 0.
This command sets the left margin for the report to marginSize characters. In most cases the value of marginSize should be 2 or greater when printing on a laser printer.
{LMARGIN 10} sets the left margin to 10 characters.
The MASK command overwrites the text in each output row with the specified characters at the specified position.
{ MASK charPosition "replacement" [ charPosition "replacement" ] }
charPosition | Character position at which to start replacing text. |
"replacement" | New text, enclosed by quotation marks, with which to overwrite the original output. |
This command inserts text or other information into each subsequent output line of a report, overwriting any output in these positions. All nonblank characters in the text overwrite appears in the output line.
To create a mask of a blank character that overwrites output, enter ~ (the tilde character), rather than a blank space. The ~ is output as a blank space mask.
In addition to constant text, this command can use keywords to insert special strings into the report. These keywords begin with a "*" and must be entered. These are identical to the * keywords under the TEXT command, and are listed here for convenience. For a more complete discussion of * keywords, please turn to the TEXT command.
You may include multiple sets of positions and text in a single MASK command.
*APPNAME | The name of the application as set in the application definition. |
*ARBOR | The version information from the Hyperion Essbase Application Server. |
*COLHDRnumber1 number2 | The column heading members from the report, usually used with SUPCOLHEADING. |
*COLHDRFULL | The full column heading, along with underlines of the column headings and a 1-line skip. |
*CURRENCY | The currency conversion label that indicates to which currency the data values have been converted at report time with the CURRENCY command. |
*DATE | The date the report was generated. |
*DATETIME | The date, followed by the time, the report was generated. |
*DBNAME | The name of the data base within the application. |
*EDATE | The date in European (dd/mm/yy) format. |
*EDATETIME | The European format date (dd/mm/yy), followed by the time. |
*MACHINE | The network name for the machine that is running the Server. |
*PAGEHDRnumber | The page member heading for the report, usually used with SUPPAGEHEADING. |
*PAGENO | The page number for the current page. |
*PAGESTRING | The page number preceded by the text "Page:" |
*TIME | The time the report was generated. |
*TIMEDATE | The time followed by the date the report was generated. |
*TIMEEDATE | The time followed by the European format (dd/mm/yy) date. |
*USERNAME | The name of the user generating the report. |
The following example is based on Sample Basic.
<ROW (Year, Measures, Product, Market, Scenario) {SUPPAGEHEADING} {ROWREPEAT} {DECIMAL 2} {SUPCOMMAS} {MASK 3 "," 22 "," 40 "," 55 "," 74 ","} <CHILDREN Qtr1 Sales <CHILDREN Colas East Budget !
This example produces the following report:
Jan, Sales, 100-10, East, Budget, 1690.00 Jan, Sales, 100-20, East, Budget, 190.00 Jan, Sales, 100-30, East, Budget, 80.00 Feb, Sales, 100-10, East, Budget, 1640.00 Feb, Sales, 100-20, East, Budget, 190.00 Feb, Sales, 100-30, East, Budget, 90.00 Mar, Sales, 100-10, East, Budget, 1690.00 Mar, Sales, 100-20, East, Budget, 200.00 Mar, Sales, 100-30, East, Budget, 100.00 |
The MATCH command performs wildcard member selections.
<MATCH ("Member"|"Gen"|"Level","Pattern")
"Member" | Member name at the top of the member hierarchy you want to search. Hyperion Essbase searches the member name and its descendants. |
"Gen" | Default or user-defined name of the generation you want to search. |
"Level" | Default or user-defined name of the level you want to search. |
"Pattern" | The character pattern you want to search for, including a wildcard
character (* or ?).
|
This command performs wildcard member selection. Hyperion Essbase searches
for member names that match the pattern you specify, and returns the member
names it finds.
If you defined the members names in the database you are searching as
case-sensitive, the search is case-sensitive. Otherwise, the search is
not case-sensitive. To define database member names as case-sensitive,
choose Settings | Case Sensitive Members from the Application Manager Outline
Editor menu.
You can use more than one MATCH command in your report.
If Hyperion Essbase does not find any members that match the chosen character pattern, it returns no member names and continues with the other report commands in your report.
The following report is based on the Sample Basic database, and uses a * wildcard pattern search.
<PAGE (Measures, Market, Scenario) Sales East Actual <COLUMN (Year) <MATCH (Year, J*) <ROW (Product) lev1,Product !
Hyperion Essbase searches the Year dimension and finds 3 months beginning with the letter "J":Jan, Jun, and Jul. The report returns the following data:
Sales East Actual Jan Jun Jul ======== ======== ======== 100 2,105 2,625 2,735 200 1,853 2,071 1,992 300 1,609 1,795 1,926 400 1,213 1,404 1,395 Diet 620 712 778 |
The following report is based on the Sample Basic database, and uses a ? wildcard pattern search.
<PAGE (Measures, Market, Scenario) Sales East Actual <COLUMN (Year) <ROW (Product) <MATCH (Product, "???-10") !
Hyperion Essbase searches the Product dimension and finds all instances of products ending in "-10", and preceded by three characters. The report returns the following data:
Sales East Actual Year 100-10 23,205 200-10 8,145 300-10 13,302 400-10 6,898 |
The MISSINGTEXT command substitutes a text label for missing values.
{MISSINGTEXT [ "text" ] }
text | Optional text to use for missing values. |
This command replaces the word #MISSING
with text when
a missing data value is generated on a line in the report. If you do not
specify text, the default #MISSING
is restored.
{MISSINGTEXT "Not Applicable."}
SUPEMPTYROWS
SUPMISSINGROWS
SUPZEROROWS
TEXT
The NAMESCOL command determines the location of the row names columns in the report.
{ NAMESCOL [ columnList | CENTERED ] }
columnList | Optional list, separated by spaces, of the locations for each row name. List position corresponds to the number of the affected column. |
CENTERED (or C) | Key word that centers the column of row member names in the report.
Before using this parameter:
|
This command determines the placement of the row names columns in the report.
Make sure you use the NAMESCOL command after entering the column members in the report. You can get the same result with the ORDER command, but NAMESCOL is more convenient for moving just the names columns and when the number of data columns can vary.
The command { NAMESCOL c} places the row names column in the following report in the center of the report.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) { NAMESCOL c } <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ====== ====== ====== ====== ======== 2,591 2,476 Stereo 2,567 3,035 10,669 3,150 3,021 Compact_Disc 3,032 3,974 13,177 5,741 5,497 Audio 5,599 7,009 23,846 |
FIXCOLUMNS
FORMATCOLUMNS
NAMEWIDTH
ORDER
The NAMESON command turns on the display of column(s) of row member names.
{ NAMESON }
This command reverses the effect of a SUPALL or SUPNAMES command. These commands turn off the display of column(s) of row member names in the final report.
The NAMEWIDTH command determines the width of all row name columns in the report.
{ NAMEWIDTH [ width ] }
width | Optional. Specifies the total number of characters displayed for each column. |
If width is not given, then a default value of 17 is assumed.
This command determines the width of the column for all row member names in the report. Member names are truncated when necessary to fit in the column and the tilde character(~) signifies that there are letters not visible in the report. If each names column needs a different width, use the WIDTH command.
In the following example, the first report for Chicago displays the default width for the row names column while the { NAMEWIDTH 25 } command in the Boston report increases the width of the row names column to 25.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <CHILDREN Audio ! { NAMEWIDTH 25 } Boston Sales Actual <ICHILDREN Year <CHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Boston Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,450 2,341 2,377 2,917 10,085 Compact_Disc 3,290 3,034 3,132 3,571 13,027 |
The NEWPAGE command inserts a new page in the report regardless of how many lines have been generated for the current page.
{ NEWPAGE }
This command inserts a new page in the report regardless of how many lines have been generated for the current page. The report continues with a new page for the next row. A new heading is displayed at the top of the new page, assuming the page has at least one non-suppressed output data row, unless SUPHEADING is used.
The NOINDENTGEN command displays all row member names left-aligned in the row names column without indenting members based on generation in the database outline.
{ NOINDENTGEN }
By default, each generation is indented unless NOINDENTGEN is used.
This command displays all row member names left-justified in the row names column without indenting members based on generation in the Database Outline. Indenting generations is generally not useful if you sort member names alphabetically by name in a report.
The NOPAGEONDIMENSION command turns off insertion of a new page when the member in the report from the same dimension as member changes in a row of the report.
{ NOPAGEONDIMENSION mbrName }
mbrName | Single member whose dimension is part of the PAGEONDIMENSION declaration. |
This command turns off insertion of a new page when the member in the report from the same dimension as mbrName changes in a row of the report. It is needed only after the PAGEONDIMENSION command has been used.
{NOPAGEONDIMENSION Year} prevents a new page from being inserted when a member in the dimension Year changes, after PAGEONDIMENSION Year has been set.
NOSKIPONDIMENSION
PAGEONDIMENSION
SKIPONDIMENSION
The NOROWREPEAT command prevents row member names from being repeated on each line of the report if the row member name does not change on the next line. This is the default.
{ NOROWREPEAT }
NOROWREPEAT is the default; you need only use this command after using ROWREPEAT.
This command prevents row member names from being repeated on each line of the report if the row member name does not change on the next line. NOROWREPEAT is only used to cancel the effects of the ROWREPEAT command. The ROWREPEAT command causes all row member names to be displayed on every line of the report even if the names for some members are the same.
The following example is based on the Sample Demo database.
The following report is an example of the default behavior for row names
not repeating. The names only print when they change.
<PAGE (Market, Accounts) Chicago Sales <COLUMN (Scenario) Actual <ROW (Year, Product) { NOROWREPEAT } <ICHILDREN Qtr1 <ICHILDREN Audio! { ROWREPEAT } <ICHILDREN Qtr2 !
Which produces the following report:
Chicago Sales Actual Jan Stereo 923 Compact_Disc 1,120 Audio 2,043 Feb Stereo 834 Compact_Disc 1,050 Audio 1,884 Mar Stereo 834 Compact_Disc 980 Audio 1,814 Qtr1 Stereo 2,591 Compact_Disc 3,150 Audio 5,741 Chicago Sales Actual Apr Stereo 821 Apr Compact_Disc 985 Apr Audio 1,806 May Stereo 821 May Compact_Disc 1,014 May Audio 1,835 Jun Stereo 834 Jun Compact_Disc 1,022 Jun Audio 1,856 Qtr2 Stereo 2,476 Qtr2 Compact_Disc 3,021 Qtr2 Audio 5,497 |
The NOSKIPONDIMENSION command prevents insertion of a new line when a member from the same dimension as mbrName changes in a row of the report.
{ NOSKIPONDIMENSION mbrName }
mbrName | Single member that defines a dimension for which to halt line-skipping. |
This command turns off insertion of a new line when the member in the report from the same dimension as mbrName in the command changes in a row of the report.
This command is required only after the SKIPONDIMENSION command.
{NOSKIPONDIMENSION Year}
Prevents the insertion of a new line when a member in the dimension Year changes after an occurrence of SKIPONDIMENSION Year.
NOPAGEONDIMENSION
PAGEONDIMENSION
SKIPONDIMENSION
The NOUNAMEONDIM command turns off underlining for the new member name when the member in the report from the same dimension as the specified member changes in a row of the report.
{ NOUNAMEONDIM mbrName }
mbrName | Member whose dimension is part of the UNAMEONDIM declaration. |
This command turns off underlining for a new row when the member in the report from the same dimension as mbrName changes. It is needed only after the UNAMEONDIM command has been used.
NOPAGEONDIMENSION
NOSKIPONDIMENSION
PAGEONDIMENSION
SKIPONDIMENSION
UNAMEONDIMENSION
The OFFCOLCALCS command disables all column calculations within the report.
{ OFFCOLCALCS }
This command disables all column calculations within the report, for
example, those calculations set by CALCULATE COLUMN. The column(s) defined
for the calculation(s) display the value #MISSING
to indicate
no value was calculated for the column. This command temporarily turns
off the calculations but does not remove them.
See the example for the CALCULATE COLUMN command.
CALCULATE COLUMN
CLEARROWCALC
CLEARALLROWCALC
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
PRINTROW
REMOVECOLCALCS
SETROWOP
The OFFROWCALCS command temporarily disables all row calculations.
{ OFFROWCALCS }
This command temporarily disables all row calculations, for example, those calculations set by CALCULATE ROW. Subsequent rows of data do not contribute to a calculated row with an active SETROWOP until ONROWCALCS is issued. Disabling the calculations does not reset the values of the rows to zero. Instead, rows of data in the report after the command are ignored in the calculations.
See the examples for the CALCULATE ROW command.
CALCULATE ROW
CLEARROWCALC
CLEARALLROWCALC
OFFCOLCALCS
ONCOLCALCS
ONROWCALCS
PRINTROW
REMOVECOLCALCS
SETROWOP
The OFSAMEGEN command adds to the report the members from the same dimension and generation as the specified member.
<OFSAMEGEN mbrName
mbrName | Single member that designates the dimension and generation to retrieve. |
Generations are counted starting at the top of the dimension. The top of the dimension is generation 1; its children are generation 2. Each child's generation number is one greater than its parent's.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <OFSAMEGEN VCR !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Stereo 2,591 2,476 2,567 3,035 10,669 Compact_Disc 3,150 3,021 3,032 3,974 13,177 Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Camera 2,506 2,522 2,602 3,227 10,857 |
ALLINSAMEDIM
CHILDREN
DESCENDANTS
ONSAMELEVELAS
The ONCOLCALCS command reenables column calculations in the report after they have been disabled by OFFCOLCALCS.
{ ONCOLCALCS }
This command is required after the OFFCOLCALCS command, which disables column calculations.
See the example for the CALCULATE COLUMN command.
CALCULATE COLUMN
CLEARROWCALC
CLEARALLROWCALC
OFFCOLCALCS
OFFROWCALCS
ONROWCALCS
PRINTROW
REMOVECOLCALCS
SETROWOP
The ONROWCALCS command re-enables all row calculations after they have been disabled by OFFROWCALCS. Each subsequent row of data after using the command is calculated.
{ ONROWCALCS }
This command is required after the OFFROWCALCS command, which disables the row calculation(s).
See the example for the CALCULATE ROW command.
CALCULATE ROW
CLEARROWCALC
CLEARALLROWCALC
OFFCOLCALCS
ONCOLCALCS
REMOVECOLCALCS
The ONSAMELEVELAS command adds to the report all members on the same level as the specified member.
<ONSAMELEVELAS mbrName
mbrName | Single member that designates the dimension and generation to retrieve. |
Levels are counted up from the bottom of the dimension. Members in the database outline with no children are level 0; their parents are level 1, and so on. The level for a child is always 1 lower than its parent.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) <ONSAMELEVELAS Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======= ======= ======= ======== Audio 5,741 5,497 5,599 7,009 23,846 Video 10,795 10,102 11,812 14,365 47,074 |
ALLINSAMEDIM
CHILDREN
DESCENDANTS
OFSAMEGEN
The ORDER command specifies the order of columns in a report.
{ ORDER columnList }
columnList | Numeric designations of the columns to rearrange, separated by a space
between each column number. Each column number represents the initial positions of each column (from 0 to n where n is the last column, counting names, data, and calculated columns, respectively). The position of each number in the columnList represents the new order in which you want the columns to be displayed. |
Using the ORDER command without a columnList resets the column order to the default setting (i.e. 0, 1, 2, 3, 4, and so on).
This command determines the output order for columns in the report,
based on the original ordering of the columns.
Always refer to columns in any report command based on their original
position, not on the output position of the column after the ORDER command.
Make sure you specify all the report columns in the ORDER command unless
you use FIXCOLUMNS. ORDER simply moves the listed columns to locations
in the final report but does not shift the unlisted columns to make room
for the columns moved. If you have a five column report and you specify
the command {ORDER 2 3 4}, you see columns 2, 3 and 4 in the report followed
again by columns 3 and 4. If you really want a 3 column report, use {FIXCOLUMNS
3}.
Calculated data columns have column numbers which begin after the last regular data column. In other words, if each output data row had:
then columns 0 and 1 are the row name column numbers; 2, 3, and 4 are the regular data column numbers; and 5 and 6 are the calculated-data column members.
The following example is based on the Sample Basic database.
<PAGE (Measures, Market) Texas Sales {ORDER 0 1 4 2 5 3 6 BLOCKHEADERS} <COLUMN (Scenario, Year) Actual Budget Jan Feb Mar <ROW (Product) <DESCENDANTS "100" !
This script arranges the Jan, Feb, and Mar columns side-by-side.
Sales Texas Actual Budget Actual Budget Actual Budget Jan Jan Feb Feb Mar Mar ======== ======== ======== ======== ======== ======== 100-10 452 560 465 580 467 580 100-20 190 230 190 230 193 240 100-30 #Missing #Missing #Missing #Missing #Missing #Missing |
The <ORDERBY command orders the rows in a report according to data values in the specified columns.
<ORDERBY ( [<rowgroupDimension>,] <column> [direction>]{,<column> [<direction>]})
<Optional rowgroup Dimension> | Row grouping dimension that determines the rows to sort as a set. |
<column> | @DATACOL (<colnumber>) | @DATACOLUMN (<colnumber>)
where <colnumber> is the target column number; must be between 1 and the maximum number of columns in the report. |
<direction> | You can specify multiple columns with different sorting directions
where:
ASC is the ascending sort DESC is the descending sort |
The innermost row grouping is the default row group dimension.
Default direction is ascending.
You can use ORDERBY, TOP, BOTTOM, and RESTRICT in the same report script, but you can use each command only once per report. If you repeat the same command in a second report in the same report script, the second command overwrites the first. Place global script formatting commands, for example, SAVEROW, before a PAGE, COLUMN command or associated member (for example, <ICHILDREN or <IDESCENDANTS).
If any of the ORDERBY, TOP, BOTTOM, or RESTRICT commands exist together in a report script, the row group dimension <rowgroupDimension> should be the same. This restriction removes any confusion about the sorting and ordering of rows within a row group. Otherwise, an error is issued.
If TOP or BOTTOM commands exist in the same report with ORDERBY, the ordering column of ORDERBY need not be the same as that of TOP or BOTTOM.
The ORDERBY, TOP and BOTTOM commands sort a report output by its data values. The RESTRICT command restricts the number of valid rows for the report output. Their order of execution is:
This order of execution applies irrespective of the order in which the commands appear in the report script.
For an example that uses TOP, BOTTOM, ORDERBY, and RESTRICT together, see the entry for the BOTTOM command.
//Page dimension <PAGE("Measures") //Column dimensions <COLUMN("Scenario", "Year") //Row dimensions <ROW("Market", "Product") // Page Members "Sales" // Column Members "Scenario" "Jan" "Feb" "Mar" // Row Members "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" // Data sorting <ORDERBY ("Product", @DATACOL(1) ASC, @DATACOL(2) DESC, @DATACOL(3) ASC) ! // End of report
Which produces the following report based on the Sample Basic sample database:
Sales Scenario Jan Feb Mar ======== ======== ======== New York Diet #Missing #Missing #Missing 300 1 675 695 400 1 664 645 200-40 1 580 523 300-10 1 495 513 300-20 1 180 182 200-20 1 3 #Missing 200-30 1 3 #Missing 100-10 1 3 2 100-20 1 3 2 100-30 1 3 2 100 1 3 2 Product 1 3 2 200-10 1 3 63 200 1 3 586 300-30 1 #Missing #Missing 400-30 134 189 198 400-20 219 243 213 400-10 234 232 234 |
The OUTALT command sets the output alias to the database outline alias name, as defined in the current alias table.
<OUTALT
This command resets the definition of the alternate name for members in a report to the original alternate name.
This command is only needed after the commands OUTALTMBR or OUTMBRALT command have been used to redefine the alternate name. Issue the command { OUTALTNAMES } to display the alternate name instead of the member name.
The following example is based on the Sample Basic database.
<PAGE (Product, Measures) <COLUMN (Scenario, Year) {OUTALTNAMES} <OUTMBRALT Actual <CHILDREN Qtr1 <ROW Market) <IDESCENDANTS "300" <OUTALT <IDESCENDANTS "300" ! <OUTALT <IDESCENDANTS "300" !
This example produces the following report:
300-10 Measures Actual Jan Feb Mar ======== ======== ======== Market 800 864 880 Vanilla Cream Measures Actual Jan Feb Mar ======== ======== ======== Market 220 231 239 Diet Cream Measures Actual Jan Feb Mar ======== ======== ======== Market 897 902 896 Cream Soda Measures Actual Jan Feb Mar ======== ======== ======== Market 1,917 1,997 2,015 Dark Cream Measures Actual Jan Feb Mar ======== ======== ======== Market 800 864 880 Vanilla Cream Measures Actual Jan Feb Mar ======== ======== ======== Market 220 231 239 Diet Cream Measures Actual Jan Feb Mar ======== ======== ======== Market 897 902 896 Cream Soda Measures Actual Jan Feb Mar ======== ======== ======== Market 1,917 1,997 2,015 |
OUTALTMBR
OUTALTNAMES
OUTMBRALT
OUTMBRNAMES
The OUTALTMBR command sets the output alias to the database outline alias name (as defined in the current alias table) followed by the database outline member name.
<OUTALTMBR
Separate the alias and member name with a single space.
To produce reports that display the alternate name for a member, you
must also use the { OUTALTNAMES } command. If no alternate name exists,
only the member name is displayed.
The following example is based on Sample Basic.
<PAGE (Product, Measures) <COLUMN (Scenario, Year) {OUTALTNAMES} <OUTALTMBR Actual <CHILDREN Qtr1 <ROW (Market) <IDESCENDANTS "300" !
This example produces the following report:
300-10 Measures Actual Jan Feb Mar ======== ======== ======== Market 800 864 880 Vanilla Cream 300-20 Measures Actual Jan Feb Mar ======== ======== ======== Market 220 231 239 Diet Cream 300-30 Measures Actual Jan Feb Mar ======== ======== ======== Market 897 902 896 Cream Soda 300 Measures Actual Jan Feb Mar ======== ======== ======== Market 1,917 1,997 2,015 |
The OUTALTNAMES command displays alias names for members in a report.
{ OUTALTNAMES }
The member name is the default for reporting.
This command displays alternate names for members in the report instead of the actual member name. May be used in conjunction with OUTMBRNAME to switch between member names and alias names in report rows.
The following example is based on Sample Basic.
{WIDTH 15} //{OUTALTNAMES} If used (commented out), displays alias names for column headers <PAGE (Measures) Sales <COL (Year, Market, Scenario) Jan Feb Mar East Actual <ROW(Measures) {OUTALTNAMES} // These members display with aliases. <IDESCENDANTS "100" {OUTMBRNAMES} // These members display their member names as defined in the outline. <IDESCENDANTS "200" {OUTALTNAMES} // Switches back to alias names, as defined in the current alias table. <IDESCENDANTS "400" !
This example produces the following report:
Sales East Actual Jan Feb Mar ============== ============== ============== Cola 1,812 1,754 1,805 Diet Cola 200 206 214 Caffeine Free Cola 93 101 107 Colas 2,105 2,061 2,126 200-10 647 668 672 200-20 310 310 312 200-30 #Missing #Missing #Missing 200-40 896 988 923 200 1,853 1,966 1,907 Grape 562 560 560 Orange 219 243 213 Strawberry 432 469 477 Fruit Soda 1,213 1,272 1,250 |
OUTALT
OUTALTMBR
OUTMBRALT
OUTMBRNAMES
The OUTALTSELECT command selects an alias table in a report script.
<OUTALTSELECT AliasTableName
AliasTableName | The name of the selected alias table associated with the database outline. |
This command selects an alias table. The table remains in effect until another <OUTALTSELECT command executes. This lets you use different alias tables for different dimensions in a report script.
The following example is based on Sample Basic, using two different alias tables: Funny Names and Default.
<PAGE("Scenario") <COLUMN("Year", "Market") <ROW("Measures", "Product") <LINK( <CHILDREN("Qtr4")) <LINK( <CHILDREN("South")) <OUTALTSELECT "Long Names" {OUTALTNAMES}"100-10" "100-20" "100-30" <OUTALTSELECT Default {OUTALTNAMES} "200-10" "200-20" "200-30" !
Scenario Oct Nov Dec Texas Oklahoma Louisiana New Mexico Texas Oklahoma Louisiana New Mexico Texas Oklahoma Louisiana New Mexico ======== ======== ======== ======== ======== ======== ======== ======== ======== ======== ======== ======== Measures The Best Cola 185 74 47 23 174 71 44 20 188 66 46 27 SlimJim Cola 26 21 45 (26) 26 30 28 (8) 30 28 31 16 AntiShake Cola #Missing #Missing #Missing #Missing #Missing #Missing #Missing #Missing #Missing #Missing #Missing #Missing Old Fashioned 133 15 58 (6) 127 29 49 (7) 133 24 31 (5) Diet Root Beer 78 65 30 14 48 62 34 8 52 72 27 12 Sasparilla 55 76 1 (12) 59 89 1 (4) 47 121 (3) (15) |
OUTALTMBR
OUTALTNAMES
OUTMBRALT
OUTMBRNAMES
The OUTMBRALT command sets the output name to the Database Outline member name followed by the Outline alias, as defined in the current alias table.
<OUTMBRALT
This command defines the alternate name for members in the report as
the member name, followed by the alternate name.
The member name and alias are separated by a single space.
You must also use the { OUTALTNAMES } command to actually display the
new alternate name instead of the member names.
The following example is based on Sample Basic.
<PAGE (Product, Measures) <COLUMN (Scenario, Year) {OUTALTNAMES} <OUTMBRALT Actual <CHILDREN Qtr1 <ROW (Market) <IDESCENDANTS "300" !
This example produces the following report:
300-10 Measures Actual Jan Feb Mar ======== ======== ======== Market 800 864 880 300-20 Vanilla Cream Measures Actual Jan Feb Mar ======== ======== ======== Market 220 231 239 300-30 Diet Cream Measures Actual Jan Feb Mar ======== ======== ======== Market 897 902 896 300 Cream Soda Measures Actual Jan Feb Mar ======== ======== ======== Market 1,917 1,997 2,015 |
OUTALT
OUTALTMBR
OUTALTNAMES
OUTMBRNAMES
The OUTMBRNAMES command reverts to the default member name display after the OUTALTNAMES command has been used to display alternate names.
{ OUTMBRNAMES }
This command resets so that member names are displayed for all members in the report after the OUTALTNAMES command has been used to display alternate names. The member name is the default for reporting.
OUTALT
OUTALTMBR
OUTALTNAMES
OUTMBRALT
The OUTPUT command causes Hyperion Essbase to resume output, reversing the action of SUPOUTPUT.
{ OUTPUT }
This command causes Hyperion Essbase to resume output with the member specifications in effect when the OUTPUT command was issued. It will not "remember" where it was when the SUPOUTPUT command was issued. Further, any formatting commands that were issued in the interim will also be in effect. Thus, you can use the SUPOUTPUT command to suppress all output from a portion of the report script.
The PAGE defines which dimensions are displayed as page members in the final report.
<PAGE ( dimList )
dimList | Dimension name or a comma-delimited list of dimensions. |
This command specifies the dimension or dimensions to be used such that
each member or combination of members of these dimensions is an attribute
of all data cells on a page.
Page members are displayed at the top of the report above the column
members. Any member in the report specification from the same dimension
as a member in the PAGE command is a page member. Only one member
at a time from each page dimension is displayed in the page heading at
the top of each page.
Each time any member from one of the dimensions in the page heading
changes, it creates a new page heading. The order of the dimensions in
the PAGE command determines the order in which members occur in the page
heading. The member from the first dimension is displayed first, followed
by the second and so on.
On any single report page, the current page members are representative
of (are attributes of) all the data cells on the page.
<PAGE (Measures, Market)
Which creates a report based on member combinations of dimensions Measures and Market. The first page of the report lists all values for Sales, East; the next page lists all values for Sales, West; When all children of Market have been extracted, the report continues with Cost of Goods Sold, East followed by Cost of Goods Sold, West, and so on.
The PAGEHEADING command displays the page heading before the next data-output row.
{ PAGEHEADING }
The page heading is the default heading, which contains the current page members. By default, page and column headers (together called the HEADING) are turned on.
This command displays the page heading before the next data output row. Otherwise, a new page heading will occur only if the page or column members change, a page is generated (for example, page length is exceeded or a NEWPAGE command is issued), or a page header has not been done for this page and the first output row on the page is ready to print.
If PAGEHEADING is specified between the STARTHEADING and ENDHEADING commands, however, the page heading is displayed with the heading and not immediately. This command also permanently nullifies the effect of a previously issued SUPPAGEHEADING command.
The PAGEHEADING command in the following report inserts the page heading members in the report for a second time.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN (Year) <ROW (Product) Television VCR { SKIP PAGEHEADING SKIP } Compact_Disc Stereo !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Television 4,410 4,001 4,934 6,261 19,606 VCR 3,879 3,579 4,276 4,877 16,611 Chicago Sales Actual Compact_Disc 3,150 3,021 3,032 3,974 13,177 Stereo 2,591 2,476 2,567 3,035 10,669 |
COLHEADING
HEADING
PAGE
SUPALL
SUPCOLHEADING
SUPHEADING
SUPPAGEHEADING
TEXT
The PAGELENGTH command sets the maximum number of lines for one page in the report.
{ PAGELENGTH [ lines ] }
lines | Optional total number of output lines for the size of paper you are using. Because the Report Writer does not recognize any of the font characteristics of the output report, it operates based on lines rather than inches. |
The defaults are FEEDON and a PAGELENGTH of 66 lines, which normally translates to an 11-inch-long page. This value is assumed if lines is not given.
This command sets the maximum number of lines for one page in the report. After displaying the number of lines, a page break is inserted, followed by the heading. The page break is not inserted if a SUPFEED command has been used. The heading is displayed at the start of the new page unless SUPHEADING has been used.
If you are using legal size paper, the value should be 84 lines. If you are using A4 paper, the value should be 70 lines.
{ PAGELENGTH 50 } sets the maximum number of lines for one page to 50.
The PAGEONDIMENSION command performs a page break whenever a member from the same dimension as the specified member changes from one line in the report to the next.
{ PAGEONDIMENSION mbrName }
mbrName | Single member. If any member of the same dimension increments, a new page is started. |
This command performs a page break whenever a member from the same dimension as the member in the command changes from one line in the report to the next.
With the ROW command, you can display members from several dimensions in columns on the side of the report. At least one member changes from one of these dimensions for each row of the report.
PAGEONDIMENSION causes a new page to begin when the member from the selected dimension changes. A single report can have several PAGEONDIMENSION commands to page on different dimensions which change.
When combined with UNAMEONDIMENSION and SKIPONDIMENSION, UNAMEONDIMENSION is processed first followed by SKIPONDIMENSION and PAGEONDIMENSION in order.
The command { PAGEONDIMENSION Year } inserts a page break before displaying the members Qtr2, Qtr3, and Qtr4 in the following report below. On each new page, the heading members Chicago, Sales and Actual are displayed at the top of the page.
<PAGE (Market, Accounts) Chicago Sales Actual <COLUMN (Scenario) <CHILDREN Year <ROW (Year, Product) { PAGEONDIMENSION Year } <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Stereo 2,591 Compact_Disc 3,150 Audio 5,741 Chicago Sales Actual Qtr2 Stereo 2,476 Compact_Disc 3,021 Audio 5,497 Chicago Sales Actual Qtr3 Stereo 2,567 Compact_Disc 3,032 Audio 5,599 Chicago Sales Actual Qtr4 Stereo 3,035 Compact_Disc 3,974 Audio 7,009 |
NOPAGEONDIMENSION
NOSKIPONDIMENSION
SKIPONDIMENSION
The PARENT command adds the parent of the member to the report.
<PARENT mbrName
mbrName | Single member, which must not be the dimension (top) member. |
<PARENT Jan
adds Qtr1 to the report.
ANCESTORS
CHILDREN
DESCENDANTS
The PRINTROW command immediately displays the calculated rowName with its current values.
{ PRINTROW "rowName" }
"rowName" | Character string, enclosed by quotation marks, which designates a previously declared calculated row. When the command is issued, the designated row is printed immediately in the report. |
See the examples for the CALCULATE COLUMN command.
CALCULATE COLUMN
CLEARROWCALC
CLEARALLROWCALC
OFFCOLCALCS
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
REMOVECOLCALCS
RENAME
SAVEANDOUTPUT
SAVEROW
SETROWOP
The PYRAMIDHEADERS command displays column members in centered, pyramid-shaped levels above columns (the default style used by symmetric reports).
{PYRAMIDHEADERS}
Default for symmetric reports. Also resets the default column display following a BLOCKHEADERS command.
This command displays column members in centered, pyramid-shaped levels
over the columns in the report. Pyramid display of column members is the
default method for displaying column members.
Pyramid headers cannot be used with asymmetric reports unless the report
is extracted as a symmetric report and reordered or truncated to make it
asymmetric.
The following example is based on Sample Basic.
<PAGE (Measures, Market) Sales {WIDTH 7} { BLOCKHEADERS } <COLUMN (Scenario, Year) Actual Budget Jan Feb Mar <ROW (Market) <CHILD "200" ! {PYRAMIDHEADERS} <CHILD "300" !
This example produces the following report:
Sales Market Actual Actual Actual Budget Budget Budget Jan Feb Mar Jan Feb Mar ====== ====== ====== ====== ====== ====== 200-10 3,220 3,348 3,326 3,230 3,370 3,370 200-20 3,122 3,161 3,203 3,090 3,120 3,190 200-30 1,478 1,463 1,499 1,310 1,290 1,330 200-40 896 988 923 870 950 890 Sales Market Actual Budget Jan Feb Mar Jan Feb Mar ====== ====== ====== ====== ====== ====== 300-10 3,517 3,613 3,650 2,950 3,050 3,080 300-20 1,397 1,417 1,434 1,140 1,160 1,170 300-30 2,960 3,016 2,993 2,560 2,590 2,580 |
The QUOTEMBRNAMES command displays all the member names within quotation marks in the report script output when run through interfaces such as Essbase Application Manager, ESSCMD, and MaxL. Note that when the report script is run through the Essbase Spreadsheet Add-in or GRID API, the members are not returned within quotation marks.
<QUOTEMBRNAMES
QUOTEMBRNAMES can occur anywhere in a report script. This command is useful when using the Report Writer to export data intended for reloading a database without the use of a data load rule file.
<PAGE (Scenario) <COLUMN (Year) <ROW (Product, Market, Measures) <QUOTEMBRNAMES {ROWREPEAT} <ICHILDREN Year <DIMBOTTOM Product <DIMBOTTOM Market <CHILDREN Profit !
The following example is a portion of the report created by this report script:
"Scenario" "Qtr1" "Qtr2" "Qtr3" "Qtr4" "Year" ======== ======== ======== ======== ======== "100-10" "New York" "Sales" 1,998 2,358 2,612 1,972 8,940 "100-10" "Massachusetts" "Sales" 1,456 1,719 1,905 1,438 6,518 "100-10" "Florida" "Sales" 620 735 821 623 2,799 "100-10" "Connecticut" "Sales" 944 799 708 927 3,378 "100-10" "New Hampshire" "Sales" 353 413 459 345 1,570 "100-10" "California" "Sales" 1,998 2,358 2,612 1,972 8,940 "100-10" "Oregon" "Sales" 464 347 345 370 1,526 "100-10" "Washington" "Sales" 422 537 589 499 2,047 "100-10" "Utah" "Sales" 384 340 311 349 1,384 "100-10" "Nevada" "Sales" 225 242 259 239 965 "100-10" "Texas" "Sales" 1,384 1,500 1,791 1,535 6,210 "100-10" "Oklahoma" "Sales" 298 304 389 590 1,581 |
The REMOVECOLCALCS command removes all column calculation definitions from the report.
{ REMOVECOLCALCS }
This command removes all column calculation definitions from the report. The data values for any calculated columns are no longer calculated or displayed. This may be used if the limit of declared column calcs (50) is a problem. If the previous column calcs are no longer needed, they can be freed, creating room for up to 50 more.
CALCULATE COLUMN
CLEARROWCALC
CLEARALLROWCALC
OFFCOLCALCS
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
PRINTROW
SETROWOP
The RENAME command renames a member within the report.
{ RENAME "newMbrName" } mbrName
"newMbrName" | Valid member name, enclosed in quotation marks, to be used as the replacement name. |
mbrName | Name of the member that you want to rename temporarily. |
This command renames a member within the report. This is a way of creating a temporary alias that applies to a single member, and it applies only within the report. Note that when you assign a temporary name to a member name, you do not have to state the member name again before or on the following line after the RENAME command. However, if you do state the member name later in the report, but not immediately on the next line after the RENAME command, the temporary name will be reset to its original member name.
{RENAME "Video"} Visual
would rename the Visual member to "Video" in the report.
The RESTRICT command specifies the conditions that the row must satisfy before it becomes part of a result set.
<RESTRICT (<column | value> <operator> <column | value>{<logicalOperator><column | value> <operator> <column | value>})
<column > | @DATACOL (<colnumber>) | @DATACOLUMN (<colnumber>)
where <colnumber> is the target column number; must be between 1 and the maximum number of columns in the report. |
<value> | Cell data type (real number) | #MISSING |
<operator> | >, >= greater than, greater or equal
<, <= less than, less than or equal = equal !=, <> not equal |
<logical
Operator> |
Report Writer processes logical operations from left to right without exception. Parentheses are not supported. The supported logical operators are AND and OR. |
Restrictions set by this command are processed from left to right.
You can use only one RESTRICT command per report, with a maximum of
nine operators included in the command. RESTRICT persists to the end of
the report script unless overwritten. You can use RESTRICT, TOP, BOTTOM,
and ORDERBY in the same report script, but you can use each command only
once per report. If you repeat the same command in a second report in the
same report script, the second command overwrites the first. Place global
script formatting commands, for example, SAVEROW, before a PAGE, COLUMN
command or associated member (for example, <ICHILDREN or <IDESCENDANTS).
The RESTRICT command can appear anywhere in a script. If sorting commands,
including TOP, BOTTOM, or ORDERBY occur in the same report, the order of
execution is:
This order of execution applies irrespective of the order in which the commands appear in the report script.
For an example that uses TOP, BOTTOM, ORDERBY, and RESTRICT together, see the entry for the BOTTOM command.
You can use configurable variables to specify the size of the internal
server buffers used for storing and sorting the extracted data. The Application
Manager's Retrieval Buffer and Retrieval Sort Buffer, and the NumericPrecision
.CFG variable affect the way the RESTRICT, TOP, or BOTTOM commands work.
For more information on configurable variables, see the Database Administrator's
Guide.
{ StartHeading SupPageHeading Skip Text C "Annual Report" 70 "*PageString" Skip Endheading } // Display the rows where the value of column 3 is greater than 1,300 <RESTRICT (@DataCol(3) > +1300 ) // Page and column dimensions <Page (Accounts, Scenario) <Column (Scenario, Year) // Scenario members Actual Budget Scenario // Row dimensions <Row (Market, Product) // Market members <Ichildren Market // Product members <Idescendants Product ! // End report
Which produces the following report based on the Demo Basic sample database:
Annual Report Page: 1 Actual Budget Scenario ======== ======== ======== East Compact_Disc 13,612 13,616 13,612 Audio 13,438 14,551 13,438 Television 11,911 14,780 11,911 VCR 15,506 16,772 15,506 Camera 5,721 7,079 5,721 Visual 33,138 38,631 33,138 Product 46,576 53,182 46,576 West Compact_Disc 21,568 20,935 21,568 Audio 22,488 22,308 22,488 Television 10,688 13,535 10,688 VCR 19,706 17,782 19,706 Camera 9,957 12,397 9,957 Visual 40,351 43,714 40,351 Product 62,839 66,022 62,839 South Television 5,278 9,395 5,278 VCR 13,994 15,810 13,994 Camera 5,293 7,220 5,293 Visual 24,565 32,425 24,565 Product 24,565 32,425 24,565 Market Compact_Disc 35,180 34,551 35,180 Audio 35,926 36,859 35,926 Television 27,877 37,710 27,877 VCR 49,206 50,364 49,206 Camera 20,971 26,696 20,971 Visual 98,054 114,770 98,054 Product 133,980 151,629 133,980 |
The ROW command determines the row dimensions for a report, whose member names appear in the data rows of the report.
<ROW ( dimList )
dimList | Dimension name or a comma-delimited list of dimensions. |
This command determines the row dimensions for a report whose member names appear in the data rows of the report. The member(s) in the command determine which dimensions from the Database Outline are displayed in the rows.
dimList is a list of members or dimension members that specifies the order, from left to right, in which the row headers are listed unless subsequently moved by ORDER or NAMESCOL. Each dimension may be represented only once in dimList.
<ROW (Product)
Which creates a report with each member of Product
as a row in the report.
The ROWREPEAT command displays all applicable row members on each row of the report even if a member describing a row is the same as in the previous row.
{ ROWREPEAT }
Default is NOROWREPEAT.
This command returns the report to displaying members that change from one line to the next.
The following example is based on Demo Basic.
The command { ROWREPEAT } causes the row member names Qtr1 through Qtr4
to repeat for each line showing Compact_Disc in the report where the duplications
would normally be suppressed.
<PAGE Market, Accounts) Chicago Sales <COLUMN Scenario) Actual Budget <ROW Year, Product) {ROWREPEAT} <CHILDREN Year <CHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Budget ======== ======== Qtr1 Stereo 2,591 2,800 Qtr1 Compact_Disc 3,150 3,050 Qtr2 Stereo 2,476 2,700 Qtr2 Compact_Disc 3,021 3,050 Qtr3 Stereo 2,567 2,750 Qtr3 Compact_Disc 3,032 3,050 Qtr4 Stereo 3,035 3,300 Qtr4 Compact_Disc 3,974 3,950 |
The SAVEANDOUTPUT command adds rowMbr to the report and creates a new calculated row whose default name is rowMbr, but which may be renamed with an optional name enclosed in quotation marks.
{ SAVEANDOUTPUT [ "rowCalcName" ] } rowMbr !
"rowCalcName" | Optional. Name, enclosed by quotation marks, for the calculated data
row created by the SAVEROW command.
rowCalcName can be multi-part, separated by a tilde (~), as in the CALCULATE ROW and CALCULATE COLUMN syntax. |
rowMbr | Row member that determines the row name for the calculated data row. |
Creates a new calculated row whose default name is rowMbr, and
automatically stores the data associated with rowMbr. You can rename
with an optional name, rowCalcName, enclosed in quotes.
The calculation operator for that command is set by default to OFF.
This command creates a new calculated row. The command automatically
stores the data associated with rowMbr, and this data can be referenced
by CALC ROW, CALC COLUMN, PRINTROW, or any other command that can reference
a calculated row.
When this command is used, the calculation operator for that command
is set to OFF, so that its contents are not be affected unless the user
explicitly turns the operator back on.
SAVEANDOUTPUT both captures data and outputs the result, whereas SAVEROW
will capture the output but suppress it.
The following example is based on Demo Basic.
{ TEXT 18 "Expenses as % of Sales for January" } Jan Boston Audio Actual Budget { SAVEANDOUTPUT } Sales ! { CALCULATE COLUMN " Actual%" = 1 % "Sales" 1 CALCULATE COLUMN "Budget%" = 2 % "Sales" 2 } COGS Misc Payroll Marketing !
This example produces the following report:
Expenses as % of Sales for January Jan Boston Audio Actual Budget ======== ======== Sales 1,985 2,150 Jan Boston Audio Actual Budget Actual% Budget% ======== ======== ======== ======== Cost_of_Goods_Sold 941 1,007 47 47 Miscellaneous 4 0 0 0 Payroll 542 530 27 25 Marketing 134 130 7 6 |
CALCULATE COLUMN
CALCULATE ROW
CLEARROWCALC
CLEARALLROWCALC
OFFCOLCALCS
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
OUTPUT
PRINTROW
REMOVECOLCALCS
SAVEANDOUTPUT
SAVEROW
SUPOUTPUT
The SAVEROW command creates a new calculated row whose default name is rowMbr, but which may be renamed with an optional name enclosed in quotation marks.
{ SAVEROW ["newRowCalcName"] } rowMbr !
newRowCalcName | Optional. Name, enclosed in quotation marks, for the data row created by the SAVEROW command. The name can be multi-part, separated by a tilde (~), as in the CALCULATE ROW and CALCULATE COLUMN syntax. |
rowMbr | Default row member used to determine the row name for the calculated
data row.
rowMbr is the next member encountered after the { SAVEROW } command, so other intervening { } format commands or non-member-selecting < commands are allowed and do not affect which member is saved. |
Creates a new calculated row whose default name is rowMbr, and
automatically stores the data associated with rowMbr, but which
may be renamed with an optional name in quotes.
The calculation operator for that command is set by default to OFF.
This command creates a new calculated row. The command automatically
stores the data associated with rowMbr, and this data can be referenced
by any CALC ROW, CALC COLUMN, PRINTROW command, or any other that can reference
a calculated row.
When the command is used, the calculation operator for that command
is set to OFF, so that its contents are not affected unless the user explicitly
turns the operator back on. SAVEROW captures the data, but suppresses its
output.
The following example is based on Demo Basic.
{TEXT 18 "Expenses as % of Sales for January"} Jan Boston Audio Actual Budget {SAVEROW} Sales ! {CALCULATE COLUMN " Actual%" = 1 % "Sales" 1 CALCULATE COLUMN "Budget%" = 2 % "Sales" 2} COGS Misc Payroll Marketing Sales !
Which produces the following report:
Expenses as % of Sales for January Jan Boston Audio Actual Budget Actual% Budget% ======== ======== ======== ======== Cost_of_Goods_Sold 941 1,007 47 47 Miscellaneous 4 0 0 0 Payroll 542 530 27 25 Marketing 134 130 7 6 Sales 1,985 2,150 100 100 |
The SCALE command scales the data in the report by multiplying it by a numeric value.
{ SCALE factor [ columnList ] }
factor | Numeric value by which all output values are multiplied. The result is a scaled value. |
columnList | Optional. List of column numbers that this command affects. |
This command affects only the columns specified in the command or all columns if none are specified. Stored data is not affected by this command.
The command {SCALE .01} multiplies the data values in the second report by .01.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) <CHILDREN Audio ! {SCALE 2} Chicago Sales Actual <CHILDREN Year <CHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======= ====== ====== ====== Stereo 2,591 2,476 2,567 3,035 Compact_Disc 3,150 3,021 3,032 3,974 Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======= ====== ====== ====== Stereo 5,182 4,952 5,134 6,070 Compact_Disc 6,300 6,042 6,064 7,948 |
BRACKETS
COMMAS
DECIMAL
SUPBRACKETS
SUPCOMMAS
The SETCENTER command sets a new centerline position on the page.
{ SETCENTER charPosition }
charPosition | Integer representing a character position on your page. Character position is counted from the left edge of the page and is not affected by the left margin setting. |
This command sets a new centerline position on the page. Under normal circumstances, the center of the page is calculated based on the default page width and the left margin position until column members have been encountered, after which it defaults to the center of the data column area.
The SETCENTER command allows you to issue an arbitrary centerline position, which is then used for all centered text, including page headers. This can be helpful to center text before all the members defining the columns (and thus, the page width). It can also be used to reset the center in cases where the centering is not appealing when based on the exact center of the data columns.
The SETROWOP command defines on-the-fly calculations for a named row created with CALCULATE ROW.
{ SETROWOP "rowCalcName" [ operation ] }
rowCalcName | Named row, in double quotes, to which SETROWOP applies. |
operation | You can use any valid row calculation expression.
SETROWOP accepts the same mathematical operators as CALCULATE ROW. In addition, SETROWOP accepts the OFF operator, which turns off row operations for rows that follow. |
If an operation is not specified, the default is + (add).
This command determines the calculation for the calculated row specified in rowCalcName. The following table lists the operators you use for the operation in the command:
Operator | Operation |
+ | Addition |
- | Subtraction |
* | Multiplication |
/ | Division |
% | Percentages |
OFF | Turns off the calculation |
The addition operator, for example, will sum all the values in all rows output while the operation is on. The result in the calculated row may be printed with PRINTROW at any time. You may only use a single operator per calculated row. Before using the SETROWOP command, you must define the row name with the CALCULATE ROW command, or with SAVEROW or SAVEANDOUTPUT. Refer to the CALCULATE ROW command for more information on its ability to set the row operator.
See the examples in the CALCULATE ROW command.
CALCULATE ROW
CLEARROWCALC
CLEARALLROWCALC
OFFCOLCALCS
OFFROWCALCS
ONCOLCALCS
ONROWCALCS
OUTPUT
PRINTROW
REMOVECOLCALCS
SAVEANDOUTPUT
SAVEROW
SUPOUTPUT
The SINGLECOLUMN command displays a column heading when there is only one column member extracted in the report.
<SINGLECOLUMN
This formatting command displays a column heading when there is only one column member selected in the report.
<singlecolumn {suppagehead} <column(year) <row(measures) Profit Inventory Ratios Qtr1 !
This examples produces the following report:
Qtr1 ======== Profit 24,703 Inventory 117,405 Ratios 29,365 |
COLHEADING
PAGEHEADING
SUPCOLHEADING
SUPPAGEHEADING
The SKIP command skips one or more lines in the output report.
{SKIP n }
n | Positive integer representing the number of lines to skip. |
Single skip.
This command outputs a number of blank lines in the report or a single line if n is omitted from the command.
<PAGE (Measures, Market) Texas Sales <COLUMN (Scenario, Year) Actual Budget Jan Feb <ROW (Market) <DESCENDANTS "100" {SKIP 2} <DESCENDANTS "200" <DESCENDANTS "300" !
Which inserts two blank lines between the rows containing descendants of member 100 and descendants of members 200 and 300.
NEWPAGE
NOSKIPONDIMENSION
SKIPONDIMENSION
The SKIPONDIMENSION command inserts a blank line when a member from the same dimension as the specified member changes on the next line in the report.
{ SKIPONDIMENSION mbrName }
mbrName | Name of single member. When a member from this dimension changes during report processing, a blank line is inserted before the member change. |
This command outputs a blank line when a member from the same dimension as mbrName in the command changes on the next line in the report. With the ROW command, you can display members from several dimensions in columns on the side of the report. At least one member changes from one of these dimensions for each row of the report. The SKIPONDIMENSION displays a blank line before the member from the dimension changes. When combined with UNAMEONDIMENSION and/or PAGEONDIMENSION, UNAMEONDIMENSION is processed first followed by SKIPONDIMENSION and PAGEONDIMENSION in order.
The command {SKIPONDIMENSION Year} in the following report inserts a blank line before the row members Qtr2, Qtr3, and Qtr4 in the report.
<PAGE (Market, Accounts) Chicago Sales <COLUMN (Scenario) Actual <ROW (Year, Product) { SKIPONDIMENSION Year } <CHILDREN Year <ICHILDREN Audio !
Chicago Sales Actual Qtr1 Stereo 2,591 Compact_Disc 3,150 Audio 5,741 Qtr2 Stereo 2,476 Compact_Disc 3,021 Audio 5,497 Qtr3 Stereo 2,567 Compact_Disc 3,032 Audio 5,599 Qtr4 Stereo 3,035 Compact_Disc 3,974 Audio 7,009 |
NOPAGEONDIMENSION
NOSKIPONDIMENSION
PAGEONDIMENSION
The SORTALTNAMES command alphabetically sorts members by their alternate names within a member selection command (for example, <CHILDREN).
<SORTALTNAMES
This command sorts alphabetically all members added with a member command
(for example, <CHILDREN) by their alternate name. Members entered directly
in the report specification without a member command, calculated rows and
column names, or member commands encountered in the specification prior
to the SORTALTNAMES command, are not affected by the command.
This command must precede the selection commands, for example, CHILDREN
or DESCENDANTS. If no sorting commands are used, members are output in
hierarchical order based on the member outline. Any sort command remains
in effect until another sort command is issued.
The following example is based on Demo Basic.
The command <SORTALTNAMES sorts the members added to the report with
the <IDESCENDANTS Product command by the alternate name of each member.
The command {OUTALTNAMES} causes alternate member names to be displayed
in the report. {NOINDENTGEN} turns off hierarchical indenting so the row
names line up. Indented row names are not particularly useful when the
output is sorted on any criteria other than generation.
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== Audio 5,741 5,497 5,599 7,009 Camera 2,506 2,522 2,602 3,227 Compact_Disc 3,150 3,021 3,032 3,974 Product 16,536 15,599 17,411 21,374 Stereo 2,591 2,476 2,567 3,035 Television 4,410 4,001 4,934 6,261 VCR 3,879 3,579 4,276 4,877 Visual 10,795 10,102 11,812 14,365 Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======= ======= ======= ======= Audio 5,740 5,375 5,509 6,488 CD 3,290 3,034 3,132 3,571 Camera 2,230 2,255 2,266 3,162 Items 15,812 15,050 16,716 19,159 Media 10,072 9,675 11,207 12,671 Radio 2,450 2,341 2,377 2,917 TV 4,197 3,757 4,740 5,000 Video 3,645 3,663 4,201 4,509 |
ALLINSAMEDIM
CHILDREN
DESCENDANTS
SORTASC
SORTDESC
SORTGEN
SORTLEVEL
SORTMBRNAMES
SORTNONE
The SORTASC command specifies an ascending sort order.
<SORTASC
This command determines the order in which members are sorted in member
commands in the report specification. You use this command prior to the
other sort commands including SORTALTNAMES, SORTGEN, SORTLEVEL and SORTMBRNAMES.
With the SORTASC command, all following members selected are sorted into
ascending order starting with either the letter "a" or the lowest generation
and moving toward the letter "z" or the highest generation. Sorting in
ascending order is the default sort order and is only changed with the
SORTDESC command.
This command must precede the selection commands, or example, CHILDREN
or DESCENDANTS. If no sorting commands are used, members are output in
hierarchical order based on the member outline. Any sort command remains
in effect until reset by another sort command.
ALLINSAMEDIM
CHILDREN
DESCENDANTS
SORTALTNAMES
SORTDESC
SORTGEN
SORTLEVEL
SORTMBRNAMES
SORTNONE
The SORTDESC command specifies a descending, hierarchical sort order.
<SORTDESC
This command determines the order in which items are sorted in member
commands in the report specification. You use this command prior to the
other sort commands including SORTALTNAMES, SORTGEN, SORTLEVEL and SORTMBRNAMES.
With the SORTDESC command, all members are sorted in descending order starting
with either the letter "z" or the highest generation and moving toward
the letter "a" or the lowest generation.
This command must precede the selection commands, for example CHILDREN
or DESCENDANTS. If no sorting commands are used, members are output in
hierarchical order based on the member outline. Any sort command remains
in effect until another sort command is issued.
The following example is based on Sample Basic.
<PAGE (Market, Measures) Massachusetts Sales <COLUMN (Scenario, Year) Actual Budget Jan Feb Mar <ROW (Product) <SORTDESC <ICHILDREN Product !
This example produces the following report:
Massachusetts Sales Actual Budget Jan Feb Mar Jan Feb Mar ======== ======== ======== ======== ======== ======== Product 1,251 1,206 1,203 1,170 1,130 1,120 Diet #Missing #Missing #Missing #Missing #Missing #Missing 400 160 136 132 160 140 130 300 130 132 129 100 100 100 200 467 468 450 450 450 430 100 494 470 492 460 440 460 |
ALLINSAMEDIM
DESCENDANTS
SORTASC
SORTALTNAMES
SORTGEN
SORTLEVEL
SORTMBRNAMES
SORTNONE
The SORTGEN command sorts members by outline generation number.
<SORTGEN
This command sorts all members added with a member command, such as
<CHILDREN, according to the generation of the member in the Database
Outline. The top of the dimension in the Outline is generation 1 for the
dimension. The children of the top are generation 2, and so on. Each member's
generation is one higher than its parent. Members entered directly in the
report specification without using a member selection command, calculated
rows and column names, or member commands encountered in the specification
prior to the SORTGEN command, are not affected by the command.
This command must precede the selection commands, for example CHILDREN
or DESCENDANTS. If no sorting commands are used, members are output in
hierarchical order based on the member outline. Any sort command remains
in effect until another sort command is issued.
The following example is based on Sample Basic.
<PAGE (Product, Measures) East Sales <COLUMN (Scenario, Year) Actual Budget Jan Feb Mar <ROW (Market) <SORTGEN <IDESCENDANTS Market !
Which produces the following report:
Product Sales Actual Budget Jan Feb Mar Jan Feb Mar ======== ======== ======== ======== ======== ======== Market 31,538 32,069 32,213 29,480 30,000 30,200 East 6,780 6,920 6,921 6,180 6,350 6,360 West 10,436 10,564 10,674 9,460 9,530 9,640 South 3,976 4,082 4,055 3,870 3,970 3,990 Central 10,346 10,503 10,563 9,970 10,150 10,210 New York 2,479 2,625 2,601 2,300 2,450 2,440 Massachusetts 1,251 1,206 1,203 1,170 1,130 1,120 Florida 1,321 1,383 1,428 1,170 1,250 1,290 Connecticut 1,197 1,157 1,118 1,080 1,040 1,000 New Hampshire 532 549 571 460 480 510 California 3,602 3,699 3,755 3,450 3,490 3,570 Oregon 1,741 1,667 1,650 1,590 1,530 1,500 Washington 1,605 1,629 1,601 1,420 1,450 1,440 Utah 1,388 1,397 1,424 1,320 1,320 1,350 Nevada 2,100 2,172 2,244 1,680 1,740 1,780 Texas 1,455 1,544 1,506 1,490 1,580 1,560 Oklahoma 980 980 1,001 920 920 940 Louisiana 978 980 948 900 910 900 New Mexico 563 578 600 560 560 590 Illinois 2,538 2,653 2,697 2,580 2,690 2,740 Ohio 1,471 1,411 1,390 1,470 1,410 1,380 Wisconsin 1,341 1,363 1,369 1,280 1,330 1,330 Missouri 1,009 1,014 1,039 960 980 1,000 Iowa 2,029 2,042 2,104 1,810 1,800 1,860 Colorado 1,958 2,020 1,964 1,870 1,940 1,900 |
ALLINSAMEDIM
CHILDREN
DESCENDANTS
SORTASC
SORTALTNAMES
SORTDESC
SORTLEVEL
SORTMBRNAMES
SORTNONE
he SORTLEVEL command sorts members by outline level number.
<SORTLEVEL
This command sorts all members added with a member selection command,
such as <CHILDREN, according to the level of the member in the Database
Outline. The lowest level (level 0) members in the Outline are the members
which do not have any children.
Each member is 1 level higher than the highest level of its children.
Members entered directly in the report specification without using a member
selection command, calculated rows and column names, or member commands
encountered in the specification prior to the SORTLEVEL command, are not
affected by the command.
This command must precede the selection commands, for example CHILDREN
or DESCENDANTS.
The following example is based on Sample Basic.
<PAGE (Product, Measures) East Sales <COLUMN (Scenario, Year) Actual Budget Jan Feb Mar <ROW (Market) <SORTLEVEL <IDESCENDANTS Market !
This example produces the following report:
Product Sales Actual Budget Jan Feb Mar Jan Feb Mar ======== ======== ======== ======== ======== ======== New York 2,479 2,625 2,601 2,300 2,450 2,440 Massachusetts 1,251 1,206 1,203 1,170 1,130 1,120 Florida 1,321 1,383 1,428 1,170 1,250 1,290 Connecticut 1,197 1,157 1,118 1,080 1,040 1,000 New Hampshire 532 549 571 460 480 510 California 3,602 3,699 3,755 3,450 3,490 3,570 Oregon 1,741 1,667 1,650 1,590 1,530 1,500 Washington 1,605 1,629 1,601 1,420 1,450 1,440 Utah 1,388 1,397 1,424 1,320 1,320 1,350 Nevada 2,100 2,172 2,244 1,680 1,740 1,780 Texas 1,455 1,544 1,506 1,490 1,580 1,560 Oklahoma 980 980 1,001 920 920 940 Louisiana 978 980 948 900 910 900 New Mexico 563 578 600 560 560 590 Illinois 2,538 2,653 2,697 2,580 2,690 2,740 Ohio 1,471 1,411 1,390 1,470 1,410 1,380 Wisconsin 1,341 1,363 1,369 1,280 1,330 1,330 Missouri 1,009 1,014 1,039 960 980 1,000 Iowa 2,029 2,042 2,104 1,810 1,800 1,860 Colorado 1,958 2,020 1,964 1,870 1,940 1,900 East 6,780 6,920 6,921 6,180 6,350 6,360 West 10,436 10,564 10,674 9,460 9,530 9,640 South 3,976 4,082 4,055 3,870 3,970 3,990 Central 10,346 10,503 10,563 9,970 10,150 10,210 Market 31,538 32,069 32,213 29,480 30,000 30,200 |
ALLINSAMEDIM
CHILDREN
DESCENDANTS
SORTASC
SORTALTNAMES
SORTDESC
SORTGEN
SORTMBRNAMES
SORTNONE
The SORTMBRNAMES command sorts members alphabetically by name.
<SORTMBRNAMES
This command sorts all members added with a member selection command,
such as <CHILDREN alphabetically by member name when the members are
added to the report. Members entered directly in the report specification
without a member selection command, calculated rows and column names, or
member commands encountered in the specification prior to the SORTMBRNAMES
command, are not affected by the command.
This command must precede the selection commands. Any sort command remains
in effect until another sort command is issued.
The following example is based on Sample Basic.
<PAGE (Product, Measures) Sales <COLUMN (Scenario, Year) Actual Budget Jan Feb Mar <ROW (Market) <SORTMBRNAMES <IDESCENDANTS South !
This example produces the following report:
Product Sales Actual Budget Jan Feb Mar Jan Feb Mar ======== ======== ======== ======== ======== ======== Louisiana 978 980 948 900 910 900 New Mexico 563 578 600 560 560 590 Oklahoma 980 980 1,001 920 920 940 South 3,976 4,082 4,055 3,870 3,970 3,990 Texas 1,455 1,544 1,506 1,490 1,580 1,560 |
The SORTNONE command disables all previous sorting commands.
<SORTNONE
This command disables all previous sorting commands so that members added to the report with member selection commands are added following the normal hierarchical order based on the database outline.
ALLINSAMEDIM
DESCENDANTS
SORTALTNAMES
SORTDESC
SORTGEN
SORTLEVEL
SORTMBRNAMES
The SPARSE command tells Hyperion Essbase to use the sparse data extraction
method, which optimizes performance when a high proportion of the reported
data rows are #MISSING
. This data extraction method is different
from the regular method. Hyperion Essbase cannot use the sparse data retrieval
optimization method on Dynamic Calc or Dynamic Calc And Store members.
<SPARSE
If you have at least one sparse row dimension in your report, Hyperion
Essbase uses the sparse data extraction method in two cases:
Case 1: You use SUPMISSINGROWS in your report script to suppress #MISSING
values, and Hyperion Essbase estimates that a very high proportion
of the requested data rows are #MISSING
. In this case, Hyperion
Essbase implicitly uses the sparse method to optimize performance.
Case 2: You explicitly use the SPARSE command in your report script.
This forces Hyperion Essbase to use the sparse method. If you use the SPARSE
command in a report, and you have not used SUPMISSINGROWS, Hyperion Essbase
automatically turns on SUPMISSINGROWS for the report containing SPARSE.
Hyperion Essbase also turns on SUPMISSINGROWS for all following reports
in your report script, unless you specify INCMISSINGROWS in a subsequent
report.
Note: If your report does not contain at least one sparse row dimension, Hyperion Essbase cannot use the sparse method, and reverts to the regular method. Hyperion Essbase displays a message to tell you that it cannot use the sparse method.
When Hyperion Essbase uses the sparse method, it displays the following message: "Report Writer Sparse Extractor method will be executed."
If you have at least one sparse row dimension in your report, the report
is very large, and a very high proportion of the reported data rows are
#MISSING
, you may want to use the SPARSE command to force Hyperion
Essbase to use the sparse data extraction method. You can then assess if
this improves your report script performance.
If your report requests a small number of cells (#MISSING
and
non-missing), the sparse data extraction method is less efficient than
the regular method. In this case, Hyperion Essbase uses the regular method,
unless you have at least one sparse row dimension in your report, and you
explicitly use the SPARSE command.
SPARSE method: When Hyperion Essbase uses the sparse data extraction method, Hyperion Essbase first selects the row member combinations you have requested in your report script. Hyperion Essbase looks at only the non-missing data blocks for these row member combinations. If your database is very sparse, this method is very efficient.
Regular method: By contrast, when Hyperion Essbase uses the regular
data extraction method, it cycles through every possible member combination
requested by the report script. It then reports only those rows that are
not #MISSING
.
For example, suppose that only 1 in 10,000 data cells exist in a database.
The remaining cells are #MISSING
. On this database, you run a
report script that requests 100% of the data,
and uses SUPMISSINGROWS to
suppress the #MISSING
values.
If Hyperion Essbase uses the regular method of data extraction, Hyperion
Essbase cycles through all the requested member combinations.
If Hyperion Essbase uses the sparse extraction method, Hyperion Essbase
looks only at the non-missing data blocks for the row member combinations
requested. As this database is very sparse (only 1 in 10,000 data cells
exist), the number of existing data blocks is probably low. The sparse
method produces the report much faster.
Note: The sparse extraction method cannot be used if the report
contains attribute dimensions.
The STARTHEADING command starts the definition of the page heading in place of the default heading, which is displayed at the top of each page in the report or immediately following a HEADING command.
{ STARTHEADING }
Replaces default heading.
This command starts the definition of the page heading in place of the default heading, which is displayed at the top of each page in the report or immediately following a HEADING command. The ENDHEADING command signifies the end of the heading; all commands encountered between the STARTHEADING and ENDHEADING are part of the heading definition. Unless SUPHEADING is used outside the STARTHEADING / ENDHEADING group, the commands within the STARTHEADING/ENDHEADING group are re-executed at the start of each new page.
By default, new pages are started whenever a page member changes, the makeup of column headings change, the page length is exceeded and SUPFEED has not been used, the NEWPAGE command is issued, the HEADING command is issued, or the PAGEONDIMENSION command causes a page break. A custom heading will automatically include the default page header and column headers unless they are specifically suppressed with SUPPAGEHEADING and/or SUPCOLHEADING in the custom heading definition.
Note that headings (whether the default page and column headings or a custom heading created with ENDHEADING do not get output right at the start of a new page. They are delayed until the next non-suppressed output data row is encountered, and even then the heading is output only after the data row's format { } commands have been processed. This avoids blank pages with nothing but headers on them but it can make it a bit awkward to put out a TEXT (or other format which produces output) between the heading and the first output data row.
The following report provides a complete example of how to define a heading for a report. All the commands within the STARTHEADING and ENDHEADING commands are executed at the top of each page. The TEXT commands display information about the person who prepared the report, the date the report was generated, and other title information about the content of the report.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) { STARTHEADING TEXT 2 "Prepared by:" 14 "*USERNAME" C "The Electronics Club" 60 "*PAGESTRING" TEXT C "Quarterly Sales by City" 60 "*DATE" SUPPAGEHEADING TEXT 2 "*PAGEHDR" SKIP ENDHEADING} <IDESCENDANTS Product !
This example produces the following report:
Prepared by: Bob The Electronics Club Page: 1 Quarterly Sales by City 05/13/92 Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======= ======= ======= ======= Stereo 2,591 2,476 2,567 3,035 Compact_Disc 3,150 3,021 3,032 3,974 Audio 5,741 5,497 5,599 7,009 Television 4,410 4,001 4,934 6,261 VCR 3,879 3,579 4,276 4,877 Camera 2,506 2,522 2,602 3,227 Visual 10,795 10,102 11,812 14,365 Product 16,536 15,599 17,411 21,374 |
ENDHEADING
HEADING
IMMHEADING
SUPCOLHEADING
SUPHEADING
SUPPAGEHEADING
The SUPALL command suppresses the display of the page and column headings, all member names, page breaks, commas, and brackets in the final report.
{ SUPALL }
This command suppresses the display of the page and column headings, all member names, page breaks, commas and brackets in the final report. With this command, you see the data of the report and any text displayed as the result of the TEXT command. This command is equivalent to SUPHEADING, SUPPAGEHEADING, SUPCOLHEADING, SUPNAMES, SUPBRACKETS, SUPFEED, and SUPCOMMAS.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) <ICHILDREN Audio ! { SUPALL } Boston Sales Actual <CHILDREN Year <ICHILDREN Audio !
This example produces the following report. Note: the last three rows show the totals for Boston, without headings.
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ====== ====== ====== ====== Stereo 2,591 2,476 2,567 3,035 Compact_Disc 3,150 3,021 3,032 3,974 Audio 5,741 5,497 5,599 7,009 2450 2341 2377 2917 3290 3034 3132 3571 5740 5375 5509 6488 |
SUPBRACKETS
SUPCOLHEADING
SUPCOMMAS
SUPCURHEADING
SUPEMPTYROWS
SUPEUROPEAN
SUPFEED
SUPHEADING
SUPMISSINGROWS
SUPNAMES
SUPPAGEHEADING
SUPZEROROWS
The SUPBRACKETS command suppresses the display of parentheses around negative numbers.
{ SUPBRACKETS }
This command suppresses the display of parenthesis around negative numbers. The negative sign,(-) indicates negative numbers in the report.
{SUPBRACKETS}
displays (34.43) as -34.43.
COMMAS
DECIMAL
SUPALL
SUPBRACKETS
SUPCOMMAS
The SUPCOLHEADING command suppresses the display of the default column headings in the report.
{ SUPCOLHEADING }
This command suppresses the display of the default column headings in the report. Unless a custom heading is defined you will see only the page heading members at the top of the page and row members on the left side of each row. The keyword >*COLHDR with the TEXT command is not affected by SUPCOLHEADING and may still be used to generate automatic column headings where desired.
<PAGE (Market, Accounts, Scenario) { SUPCOLHEADING } Boston Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) <ICHILDREN Audio !
This example produces the following report:
Boston Sales Actual Stereo 2,450 2,341 2,377 2,917 Compact_Disc 3,290 3,034 3,132 3,571 Audio 5,740 5,375 5,509 6,488 |
COLHEADING
NAMESON
PAGEHEADING
SUPNAMES
SUPPAGEHEADING
The SUPCOMMAS command suppresses the display of commas in numbers greater than 999.
{ SUPCOMMAS }
The display of commas is the default if SUPCOMMAS is not used.
This command suppresses the display of commas in numbers greater than 999.
{SUPCOMMAS} displays the number 12,234,534.23 as 12234534.23.
BRACKETS
COMMAS
DECIMAL
SUPBRACKETS
The SUPCURHEADING command suppresses the display of currency information when you use the CURRENCY command to convert the data values in your report to a specified currency.
{ SUPCURHEADING }
This command suppresses the display of currency information when you use the CURRENCY command to convert the data values in your report to a specified currency. The keyword *CURRENCY with the TEXT command is not affected by SUPCURHEADING and may be used after SUPCURHEADING to create custom currency heading and placement.
The SUPEMPTYROWS command suppresses the display of rows that have only
0 or #MISSING
values in the row.
{ SUPEMPTYROWS }
This command suppresses the display of zero rows, for example, rows
that have only 0 or missing values in the row, in the final report. The
report will contain only rows which have at least one data value which
is neither #MISSING
nor zero.
{SUPEMPTYROWS} would suppress the display of the following row in a report:
Qtr1 Actual 0 #Missing 0 0 #Missing
INCEMPTYROWS
INCMISSINGROWS
INCZEROROWS
SUPMISSINGROWS
SUPZEROROWS
The SUPEUROPEAN command disables the European method for displaying numbers.
{ SUPEUROPEAN }
Non-European is the default.
In European mode, commas separate the decimal and whole number portion of a data value while decimal points are used for the thousands separator character. Non-European number display uses commas to separate thousands and the decimal point to separate decimals.
SUPEUROPEAN need only be used after a EUROPEAN command.
See the example for EUROPEAN.
The SUPFEED command suppresses the automatic insertion of a page break whenever the number of lines on a page exceeds the current PAGELENGTH setting.
{ SUPFEED }
Default when performing ad-hoc reports into a spreadsheet.
This command suppresses the automatic insertion of a page break whenever the number of lines on a page exceeds the current PAGELENGTH setting. The command FEEDON re-enables page breaks. The default page length is 66 lines unless reset with the PAGELENGTH command.
The SUPFORMATS command is used to suppress formats that produce output such as underlines and skips.
{ SUPFORMATS }
Set to "ON" by default when the SUPMASK, SUPMISSING, or SUPZERO commands are used.
This command is used to suppress formats which produce output such as underlines and skips. The SUPFORMATS command is used in those instances where you need to suppress formats which produce output, such as underlines, skips, etc., because the data row with which the formats are associated is automatically (and therefore unpredictably) suppressed due to commands such as SUPMISSING. Otherwise, a page could be filled with "orphan" underlines and no data. If you want to retain formatting in this case, you would need to specifically turn the formats on by using the INCFORMATS command.
The SUPHEADING command suppresses the display of the default heading (page header and column headers) or custom header, if defined, at the top of each page.
{ SUPHEADING }
Suppresses the display of the default heading.
This command suppresses the display of the page header and column headers, or custom header, if defined, at the top of each page. A custom heading is defined with the STARTHEADING and ENDHEADING commands. The HEADING command cancels the effect of the SUPHEADING command in addition to displaying the heading immediately prior to the next non-suppressed data row to be output. By default, new pages are started either when a page member changes, the makeup of column headings change, the page length is exceeded and SUPFEED has not been used, the NEWPAGE command is issued, the HEADING command is issued, or the PAGEONDIMENSION command causes a page break.
See the example for the STARTHEADING command.
ENDHEADING
HEADING
IMMHEADING
STARTHEADING
The SUPMASK command suppresses the display of a text mask.
{ SUPMASK }
This command suppresses the display of a text mask. Text masks are defined using the MASK command. The MASK command cancels the effect of the SUPMASK command, in addition to defining a new mask. While SUPMASK is in effect, a mask text string may still be output using the TEXT command's *MASK option.
The SUPMISSINGROWS command suppresses the display of all rows that contain
only #MISSING
values.
{ SUPMISSINGROWS }
<Sym <Column (Scenario, Year) Actual Budget Jan Dec <Top ("Measures", 5, @DataCol(4)) <Row (Measures, Market, Product) {SupMissingRows} <Idescendants Profit <Ichildren Market <Idescendants Product !
This example produces the following report:
Actual Budget Jan Dec Jan Dec ======== ======== ======== ======== Sales Market Product 31,538 33,342 29,480 30,820 Margin Market Product 17,378 18,435 16,850 17,360 COGS Market Product 14,160 14,907 12,630 13,460 Sales Central Product 10,346 10,662 9,970 10,310 West Product 10,436 11,116 9,460 10,200 |
INCEMPTYROWS
INCMISSINGROWS
INCZEROROWS
SUPEMPTYROWS
SUPZEROROWS
The SUPNAMES command suppresses the display of row member names in the final report.
{ SUPNAMES }
This command suppresses the display of row member names in the final report. You still see the page and column member names at the top of the report and the data values in the rows. The NAMESON command re-enables the display of row member names in the report.
The following example is based on Demo Basic.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) <ICHILDREN Audio ! { SUPNAMES } Boston Sales Actual <CHILDREN Year <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== Stereo 2,591 2,476 2,567 3,035 Compact_Disc 3,150 3,021 3,032 3,974 Audio 5,741 5,497 5,599 7,009 Boston Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== 2,450 2,341 2,377 2,917 3,290 3,034 3,132 3,571 5,740 5,375 5,509 6,488 |
COLHEADING
NAMESON
PAGEHEADING
SUPCOLHEADING
SUPPAGEHEADING
The SUPOUTPUT command suppresses all output while continuing to process all operations, such as calculations, format settings, and so forth. Use the OUTPUT command to resume output.
{ SUPOUTPUT }
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) <ICHILDREN Audio Stereo Compact_Disc {Supout} VCR TELEVISION {OUTPUT} Audio ! { SUPNAMES } Boston Sales Actual <CHILDREN Year <ICHILDREN Audio !
Which produces the same report as in SUPNAMES.
The SUPPAGEHEADING command suppresses the display of the page member heading whenever a heading is generated.
{ SUPPAGEHEADING }
This command does not suppress column headings and row members in the
report.
To reinstate page headings in the heading, use the PAGEHEADING command.
The keyword *PAGEHDR with the TEXT command may be used after a SUPPAGEHEADING
to produce a custom page member heading. *PAGEHDR with the TEXT is not
affected by SUPCOLHEADING.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <CHILDREN Year <ROW (Product) <ICHILDREN Audio ! { SUPPAGEHEADING } Boston Sales Actual <CHILDREN Year <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 ======= ====== ====== ====== Stereo 2,591 2,476 2,567 3,035 Compact_Disc 3,150 3,021 3,032 3,974 Audio 5,741 5,497 5,599 7,009 Qtr1 Qtr2 Qtr3 Qtr4 ======= ====== ====== ====== Stereo 2,450 2,341 2,377 2,917 Compact_Disc 3,290 3,034 3,132 3,571 Audio 5,740 5,375 5,509 6,488 |
COLHEADING
HEADING
IMMHEADING
NAMESON
PAGEHEADING
SUPCOLHEADING
SUPNAMES
TEXT
The SUPSHARE command suppresses the display of duplicate shared members when you use generation or level names to extract data for your report.
<SUPSHARE
This command suppresses the display of duplicate shared members only when you extract data for your report using:
SUPSHARE suppresses the display for the duration of the Report script, which can contain one or more reports. Use the SUPSHAREOFF command to reinstate the display of shared members.
Default: SUPSHAREOFF.
The Sample Basic database has a shared level of diet drinks. The shared members are 100-20 (Diet Cola), 200-20 (Diet Root Beer), and 300-30 (Diet Cream). All are level 0 members on the Product dimension. The following report:
{SUPMISSINGROWS} <SUPSHARE <PAGE (Measures, Market, Scenario) Sales West Actual <COLUMN (Year) <IDESCENDANTS Qtr1 <ROW (Product) lev0,Product !
returns the following data. The shared members appear only once in the data.
Sales West Actual Jan Feb Mar Qtr1 ====== ====== ====== ====== 100-10 1,174 1,146 1,173 3,493 100-20 700 726 727 2,153 100-30 465 426 413 1,304 200-10 667 705 707 2,079 200-20 1,203 1,209 1,209 3,621 200-30 853 845 880 2,578 300-10 1,102 1,127 1,133 3,362 300-20 523 546 566 1,635 300-30 977 1,029 1,040 3,046 400-10 1,115 1,122 1,107 3,344 400-20 1,032 1,065 1,100 3,197 400-30 625 618 619 1,862 |
The SUPSHAREOFF command reinstates the display of duplicate shared members after they have been suppressed using the SUPSHARE command.
<SUPSHAREOFF
This command reinstates the display of duplicate shared members after you have suppressed their display using the SUPSHARE command. You can suppress and reinstate duplicate shared member display only when you extract data for your report using:
Default: SUPSHAREOFF.
The Sample Basic database has a shared level of diet drinks. The shared members are 100-20 (Diet Cola), 200-20 (Diet Root Beer), and 300-30 (Diet Cream). All are level 0 members on the Product dimension. The following report:
{SUPMISSINGROWS} <SUPSHAREOFF <PAGE (Measures, Market, Scenario) Sales West Actual <COLUMN (Year) <IDESCENDANTS Qtr1 <ROW (Product) lev0,Product !
returns the following data. The example assumes that you have used SUPSHARE in a previous report in the report script. The SUPSHAREOFF command reinstates the duplicate shared member display so that the shared members appear twice in the report.
Sales West Actual Jan Feb Mar Qtr1 ====== ====== ====== ====== 100-10 1,174 1,146 1,173 3,493 100-20 700 726 727 2,153 100-30 465 426 413 1,304 200-10 667 705 707 2,079 200-20 1,203 1,209 1,209 3,621 200-30 853 845 880 2,578 300-10 1,102 1,127 1,133 3,362 300-20 523 546 566 1,635 300-30 977 1,029 1,040 3,046 400-10 1,115 1,122 1,107 3,344 400-20 1,032 1,065 1,100 3,197 400-30 625 618 619 1,862 100-20 700 726 727 2,153 200-20 1,203 1,209 1,209 3,621 300-30 977 1,029 1,040 3,046 |
The SUPZEROROWS command suppresses the display of rows that have only 0 values in the row in the report.
{ SUPZEROROWS }
{SUPZEROROWS} would not display the following row in the report:
Qtr1 Actual 0 0 0 0
but would display the following row:
Qtr1 Actual 0 #Missing 0 0
INCEMPTYROWS
INCZEROROWS
SUPEMPTYROWS
SUPMISSINGROWS
The SYM command forces a symmetric report, regardless of the data selection. Use SYM to change the symmetry of a report that Hyperion Essbase would create as an asymmetric report.
<SYM
Hyperion Essbase prints a symmetric report (with PYRAMIDHEADERS) when column dimensions do not include the same number of selected members or the members for each column dimension are not on the same line.
This command is used to set the report type as symmetric. Under default conditions (For example, when neither the ASYM nor SYM commands have been used), Hyperion Essbase will print an asymmetric report (with BLOCKHEADERS) when all column dimensions include the same number of selected members and all the members for each column dimension are on the same line. Otherwise, a symmetric report (with PYRAMIDHEADERS) is produced. If the <SYM keyword is used, then all report headers will appear in a symmetric format, even if there are equal numbers of members in each row of the column header. A symmetric report will also result if at least one of the column member lists is broken out onto more than one line.
When the <SYM keyword is used, the report will always be generated as a symmetric report, even with equal numbers of members selected in each column dimension. This is especially useful when you want to create a symmetric report without having to repeatedly type the lower-level members of symmetric/asymmetric reports. For a more detailed explanation see the <ASYM command. To turn off symmetric-only mode, use the <ASYM command.
The following example is based on Sample Basic.
<PAGE (Measures, Market) Texas Sales <SYM <COLUMN (Scenario, Year) Actual Budget Jan Feb <ROW (Market) <IDESCENDANTS "100" !
This example produces the following report:
Sales Texas Actual Budget Jan Feb Jan Feb ======== ======== ======== ======== 100-10 452 465 560 580 100-20 190 190 230 230 100-30 #Missing #Missing #Missing #Missing 100 642 655 790 810 |
The TABDELIMIT command places tabs rather than spaces between columns.
{ TABDELIMIT }
This command is useful when you want to turn report output into a more compressed form for export. TABDELIMIT can occur anywhere in a report script.
<PAGE (Scenario) <COLUMN (Year) <ROW (Product, Market, Measures) {Tabdelimit} {ROWREPEAT} <ICHILDREN Year <DIMBOTTOM Product <DIMBOTTOM Market <CHILD Profit !
This example produces the following report.
Only the first part of the
report is shown here:
Scenario Qtr1 Qtr2 Qtr3 Qtr4 Year 100-10 New York Margin 1,199 1,416 1,568 1,184 5,367 100-10 New York Total Expenses 433 488 518 430 1,869 100-10 Massachusetts Margin 1,237 1,533 1,741 1,224 5,735 100-10 Massachusetts Total Expenses 164 155 149 162 630 100-10 Florida Margin 372 442 494 375 1,683 100-10 Florida Total Expenses 174 192 200 175 741 100-10 Connecticut Margin 567 481 425 557 2,030 100-10 Connecticut Total Expenses 217 197 184 215 813 100-10 New Hampshire Margin 213 249 276 209 947 100-10 New Hampshire Total Expenses 139 149 155 137 580 100-10 California Margin 1,199 1,416 1,568 1,184 5,367 100-10 California Total Expenses 433 488 517 431 1,869 100-10 Oregon Margin 270 203 202 216 891 100-10 Oregon Total Expenses 193 183 176 180 732 |
The following is the same report without TABDELIMIT:
<PAGE (Scenario) <COLUMN (Year) <ROW (Product, Market, Measures) {ROWREPEAT} <ICHILDREN Year <DIMBOTTOM Product <DIMBOTTOM Market <CHILD Profit !
Without TABDELIMIT, the report looks like this.
Only the first few lines
of the report are shown here:
Scenario Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== 100-10 New York Margin 1,199 1,416 1,568 1,184 5,367 100-10 New York Total Expenses 433 488 518 430 1,869 |
The TEXT command adds descriptive text to a report.
{TEXT charPosition "text " [ charPosition "text" ... ]}
charPosition | Character position on the line to start text. Successive charPositions
need not be in ascending order.
The only effect the order has is that if the positions of two text strings cause an overlap, the last overwrites the first. "Last" is determined by left-right order in the TEXT statement, not by charPosition. |
text | Text to add to the report. |
This command inserts text or other information on a new line in the report. You specify the character position to begin the text at along with the text you want to display. The command can accept multiple sets of positions and text.
In addition to text, this command can insert special information based on keywords into the report as well. These keywords begin with a "*" and must be entered exactly. For example, you can display the current date and time in the report, the page number of the current page, or even user information such as the user name and application.
The following list presents the keywords along with the information which is displayed in the report.
TEXT 25 "*CALC
2""TotSales"}
would display the column 2 value of the calculated row
"TotSales" starting at character position 25, using the current column
format settings in effect for column 2.
number1number2
: Displays the column heading members
from the current default heading. You can indicate which rows of the column
header members you want to display and even which members in the row following
the keyword.
Number1
selects the row of column members
and number2
selects the member within the row. If you specify
just *COLHDR
or *COLHDR
with number1
,
the column heading members can not be combined with any other text on the
same line. Furthermore, the position of the text is ignored (the header
line will automatically be lined up with the existing data column setup),
unless you specify both number1
and number2
.
For example, *COLHDR
2 would display the second row of column
heading members in normal position over the data columns. *COLHDR
2
5 would display the 5th column member from the second row of column heading
members. This command is usually used with SUPHEADING
or SUPCOLHEADING
.
Using both number1 and number2,
TEXT 25 "*COLHDR 2 3"
would display the third member of the column heading range from the second row of column members starting in position 25.
Generally all column heading rows after the first level in symmetric reports have repeating groups of the same range of members.
The number2 specified refers to the member in the basic group of repeating members. For example, if Qtr1 Qtr2 and Qtr3 are the basic group which repeats in the second level column heading, the value for number2 can range from 1 to 3. Just because the group repeats 2 or 3 times does not mean that number2 can range up to 6 or 9. In this example, any number2 higher than 3 would be interpreted as trying to access a calculated column header.
Calculated column headers may also be accessed by the *COLHDR option. If a report has, for example, 3 calculated columns, the number2 which is used to access any particular level of the calculated column name depends on the number of members in the primary column header group for that heading level. In the previous example, where the second column heading line contained three members (Qtr1, Qtr2, and Qtr3), the second-level calculated column headings would be accessed with number2 set to 4, 5, or 6 (assuming only one row names column). Again, it does not matter how many times Qtr1, Qtr2, and Qtr3 may have been repeated on the column heading line-there are still only three members of the primary column header group.
For example, if the first calculated column defined is "YTD~PCT~TOTAL",
then the second level header "PCT" could be printed with TEXT 10 "*COLHDR
2 4"
assuming once again that the primary column heading group on level 2 had three members and only one row name dimension. Refer to ORDER for more information about column numbering.
The ORDER command does not affect the parameters for selecting the headers. The Number2 value is based on the original column order without regard to any reordering or truncation of columns with ORDER or FIXCOLUMNS.
TEXT C *PAGEHDR
2
would display only the second page member from the page heading
members from the current default page heading. It is usually used with
SUPHEADING or SUPPAGEHEADING.The following example is based on Demo Basic.
{TEXT 27 "Golden State Bottling Division" }
Which adds the text Golden State Bottling Division 27 spaces from the left margin of the report.
The following report lists several Examples of the TEXT command.
The first set of TEXT commands is defined in the custom heading of the report which is displayed at the top of every page. The command { TEXT 2 "*DATETIME" C "Annual Report" 65 "*PAGESTRING" SKIP } displays the date and time starting at character position 2 of the first line of the heading, centers the text "Annual Report" in the middle of the line, and displays the text "Page" followed by the actual page number starting at character position 65 of the first line.
The second line of the heading is defined by the command { TEXT 2 "City: " 12 "*PAGEHDR 1" } which displays the text "City:" starting a character position 2 and then displays the first page member for the page in the report. As per the first member in the PAGE command, these members are always from the Market dimension.
The command { TEXT 2 "Account: " 12 "*PAGEHDR 2" SKIP } for the third line of heading displays the text "City" at character position 2 followed by the page heading member from the Accounts dimension.
The TEXT commands at the end of the report display summary information about the report. The command { TEXT 2 "Prepared by: " 18 "*USERNAME" } displays the text "Prepared by:" at character position 2 followed by the name of the user who generated the report at character position 18.
For the next line, the command { TEXT 2 "Server Version: " 18 "*ARBOR" } displays the text "Server Version:" at character position 2 followed by the version information for the Hyperion Essbase Application Server.
The third line uses the command { TEXT 2 "Application: " 18 "*APPNAME" } to display the text "Application:" at character position 2 followed by the application name.
The final line uses the command { TEXT 2 "Database: " 18 "*DBNAME" } to display the text "Database:" at character position 2 followed by the database name.
{ STARTHEADING SUPPAGEHEADING TEXT 2 "*DATETIME" C "Annual Report" 65 "*PAGESTRING" SKIP TEXT 2 "City: " 12 "*PAGEHDR 1" TEXT 2 "Account: " 12 "*PAGEHDR 2" SKIP ENDHEADING } <PAGE (Market, Accounts) Chicago Sales <COLUMN (Scenario, Year) Actual <CHILDREN Year <ROW Audio { SKIP 2 "Prepared by: " 18 "*USERNAME" } { TEXT 2 "Server Version: " 18 "*ARBOR" } { TEXT 2 "Application: " 18 "*APPNAME" } { TEXT 2 "Database: " 18 "*DBNAME" } !
09/15/95 14:14:59 Annual Report Page: 1 City: Chicago Account: Sales Qtr1 Qtr2 Qtr3 Qtr4 ======== ======== ======== ======== Stereo 2,591 2,476 2,567 3,035 Compact_Disc 3,150 3,021 3,032 3,974 Audio 5,741 5,497 5,599 7,009 Prepared by : Admin Server Version: Gemini Alpha - 9/6/95 [Fri Sep 15 14:14:59 1995] Application: Demo Database: Basic |
The remaining examples of the TEXT command are based on the following report heading:
Chicago Sales Actual Budget Qtr1 Qtr2 Qtr3 Qtr1 Qtr2 Qtr3 ======= ======= ======= ======= ======== ======== |
{ TEXT 10 "*COLHDR 2" }
would produce the following line:
Qtr1 Qtr2 Qtr3 Qtr1 Qtr2 Qtr3 { TEXT 10 "*COLHDR 2 3" }
would produce the following text at position 10:
Qtr3 { TEXT 10 "*COLHDR 1 2" }
would produce the following text at position 10:
Budget { TEXT 10 "*COLHDRFULL" }
would produce the following lines of text regardless of the charPosition:
Actual Budget Qtr1 Qtr2 Qtr3 Qtr1 Qtr2 Qtr3 ======= ======= ======= ======= ======== ======== |
The <TODATE() command converts date strings to numbers that can be used to extract data output for a specific time period. <TODATE converts date strings into the number of seconds elapsed since midnight, January 1, 1970.
<TODATE (formatString, dateString)
formatString | The format of the date string, either "mm-dd-yyyy" or "dd-mm-yyyy". |
dateString | The date string. |
<TODATE("dd-mm-yyyy","15-10-1999")
The TOP command returns rows with the highest values of a specified data column.
<TOP ([<rowgroupDimension>,] <rows>, <column>)
<rowgroupDimension> | Optional. Row grouping dimension that determines the rows to sort as a set. The default is the inner row. |
<rows> | Positive integer that specifies the number of rows to be returned; must be greater than 0. |
<column> | @DATACOL (<colnumber>) | @DATACOLUMN (<colnumber>)
where <colnumber> is the target column number; must be between 1 and the maximum number of columns in the report. |
This command sorts the result set by the value of the specified data
column in descending order.
Rows containing #MISSING
values in the sort column are discarded
from the result set before TOP is applied.
You can use TOP and BOTTOM, ORDERBY and RESTRICT in the same report
script, but you can use each command only once per report. If you repeat
the same command in a second report in the same report script, the second
command overwrites the first. Place global script formatting commands before
a PAGE, COLUMN command or associated member (for example, <ICHILDREN or
<IDESCENDANTS). Avoid using row formatting commands with TOP.
If any of the ORDERBY, TOP, BOTTOM, or RESTRICT commands exist together
in a report script, the row group dimension
<rowgroupDimension>
should be the same. This prevents confusion about the sorting and ordering
of rows within a row group. Otherwise, an error is issued.
The ORDERBY, TOP, and BOTTOM commands sort a report output by its data values. The RESTRICT command restricts the number of valid rows for the report output. Their order of execution is:
This order of execution applies regardless of the order in which the commands appear in the report script.
For an example that uses TOP, BOTTOM, ORDERBY, and RESTRICT together, see the entry for the BOTTOM command.
You can use configurable variables to specify the size of the internal
server buffers used for storing and sorting the extracted data. The ReptKByteBuf
,
ReptKByteSortBuf
, and
NumericPrecision
variables affect
the way the RESTRICT, TOP, or BOTTOM commands work. For more information
on configurable variables, see the Database Administrator's Guide.
<Sym <Column (Scenario, Year) Actual Budget Jan Dec <Top ("Measures", 5, @DataCol(4)) <Row (Measures, Market, Product) {SupMissingRows} <Idescendants Profit <Ichildren Market <Idescendants Product !
Which produces the following report based on the Sample Basic sample database:
Actual Budget Jan Dec Jan Dec ====== ======== ======== ======== Sales Market Product 31,538 33,342 31,538 30,820 Margin Market Product 17,378 18,435 17,378 17,360 COGS Market Product 14,160 14,907 14,160 13,460 Sales Central Product 10,346 10,662 10,346 10,310 West Product 10,436 11,116 10,436 10,200 |
The UCHARACTERS command underlines all non-blank characters in the preceding row in the report.
{ UCHARACTERS ["char"] }
"char" | Optional. Single character, enclosed in quotation marks, used as the underline character. |
Default underline character "=" is used.
To underline names cleanly, the UCHARACTERS command treats a single space between two non-space characters as a character to underline. For example, in the name Sales_Revenue, the underscore is changed to a space on output, UCHARACTERS changes the space to "_".
The following example is based on Demo Basic.
{UCHARACTERS} underlines all the characters in the previous (Television)
row.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) Television { UCHARACTERS } VCR Compact_Disc !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======== ======== ======== ======== ======== Television 4,410 4,001 4,934 6,261 19,606 ========== ===== ===== ===== ===== ====== VCR 3,879 3,579 4,276 4,877 16,611 Compact_Disc 3,150 3,021 3,032 3,974 13,177 |
UCOLUMNS
UDATA
UNDERLINECHAR
UNDERSCORECHAR
The UCOLUMNS command underlines all the columns, including both names and data, in the preceding row in the report.
{ UCOLUMNS ["char"] }
"char" | Optional. Single character, enclosed in quotation marks, that creates an underline character. |
Default underline character "=" is used.
The width of the underline is based on the column width. If char is provided, then it is used as the underline character. Otherwise the default underline character is used.
The command {UCOLUMNS} in the following report underlines all the columns in the previous row which is the Television row.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) Television { UCOLUMNS } VCR Compact_Disc !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Television 4,410 4,001 4,934 6,261 19,606 ============ ======= ====== ====== ====== ======= VCR 3,879 3,579 4,276 4,877 16,611 Compact_Disc 3,150 3,021 3,032 3,974 13,177 |
UCHARACTERS
UDATA
UNDERLINECHAR
The UDA command selects members based on a common attribute, defined as a user-defined attribute (UDA) on the server.
<UDA (dimName, udaStr)
dimName | Name of the dimension associated with the udaStr. |
udaStr | Name of the user-defined attribute as it appears in the database outline. |
This command selects and reports on data based on a common member attribute. Before using a UDA command in a report, you must define the attribute on the server.
This example selects products that are sweet:
<UDA (product, "Sweet")
This example uses the UDA command within a LINK statement to select level 0 products that are sweet:
<LINK(<UDA(product, "Sweet") AND <LEV(product, 0))
The { UDATA } command underlines all the data columns for a row in a report, while not underlining the row name columns.
{ UDATA ["char"] }
"char" | Optional. Single character, enclosed in quotation marks, used as the underline character. |
Default underline character "=" is used.
The width of the underline is based on the column width. If char is provided, then it is used as the underline character. Otherwise the default character is used.
The command {UDATA} in the following report underlines all the data in the previous row which is the Television row.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) Television { UDATA } VCR Compact_Disc !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Television 4,410 4,001 4,934 6,261 19,606 ======= ====== ====== ====== ======= VCR 3,879 3,579 4,276 4,877 16,611 Compact_Disc 3,150 3,021 3,032 3,974 13,177 |
The UNAME command underlines all the row name columns in the preceding row while not underlining the data columns.
{ UNAME ["char"] }
"char" | Optional. Single character, enclosed in quotation marks, used as the underline character. |
Default underline character "=" is used.
If char is provided, then it will be used as the underline character. Otherwise the default character is used.
The command { UNAME } in the following report underlines the row member names in the previous row which is the Television row.
<PAGE (Market, Accounts, Scenario) Chicago Sales Actual <COLUMN (Year) <ICHILDREN Year <ROW (Product) Television { UNAME } VCR Compact_Disc !
This example produces the following report:
Chicago Sales Actual Qtr1 Qtr2 Qtr3 Qtr4 Year ======= ====== ====== ====== ======= Television 4,410 4,001 4,934 6,261 19,606 ============ VCR 3,879 3,579 4,276 4,877 16,611 Compact_Disc 3,150 3,021 3,032 3,974 13,177 |
The UNAMEONDIMENSION command underlines the row member names in a row whenever a member from the same dimension as the specified member changes.
{ UNAMEONDIMENSION mbrName }
mbrName | Single member representing a dimension. When a new member from this dimension is output, an underline appears under all row names in the previous line. |
This command underlines the row member names in a row whenever a member from the same dimension as the member in the command changes.
With the ROW command, you can display members from several dimensions in columns on the side of the report. At least one member changes from one of these dimensions for each row of the report.
A single report can have several UNAMEONDIMENSION commands to underline row member names, based on different dimensions which change.
When combined with UNAMEONDIMENSION and PAGEONDIMENSION, UNAMEONDIMENSION is processed first, followed by SKIPONDIMENSION and PAGEONDIMENSION in order.
The following example is based on Demo Basic.
<PAGE (Market, Accounts) Chicago Sales <COLUMN (Scenario) Actual <ROW (Year, Product) { UNAMEONDIMENSION Year } <ICHILDREN Year <ICHILDREN Audio !
This example produces the following report:
Chicago Sales Actual Qtr1 Stereo 2,591 Compact_Disc 3,150 Audio 5,741 ================ ================ Qtr2 Stereo 2,476 Compact_Disc 3,021 Audio 5,497 ================ ================ Qtr3 Stereo 2,567 Compact_Disc 3,032 Audio 5,599 ================ ================ Qtr4 Stereo 3,035 Compact_Disc 3,974 Audio 7,009 ================ ================ Year Stereo 10,669 Compact_Disc 13,177 Audio 23,846 |
NOPAGEONDIMENSION
NOSKIPONDIMENSION
PAGEONDIMENSION
SKIPONDIMENSION
The { UNDERLINECHAR } command sets the default underline character displayed when underlining any information in a report.
{ UNDERLINECHAR ["character"] }
"character" | Single character, enclosed in quotation marks, for the new underline character. |
Default underline character "=" is used.
You can use any graphic character that you can generate in the text editor used to define the report.
In some editing tools, you can generate a graphic underline by holding the ALT key down while typing 196 on the numeric keypad and then releasing the ALT key. For a double graphic underline, type 205. You must use a font with these graphic characters if the report is to print correctly.
{UNDERLINECHAR "-"} sets the character used when underlining to a single dash.
The UNDERSCORECHAR command replaces the _ (underscore) character in a member name with another character.
{ UNDERSCORECHAR "char"}
"char" | Single character, enclosed in quotation marks, that displays in place of underscore. |
This command sets the character used to replace the underscore character, "_", in member names in the report.
Reports generated with this command may not be suitable for reloading into the database as report format files. The member names may no longer match the structure outline or alternate names if the underscores are replaced with spaces.
{UNDERSCORECHAR " "}
Which replaces all underscores with spaces.(for example, member name New_York would appear as New York in the final report.)
The WIDTH command specifies the width of columns in a report.
{ WIDTH number [ column1 [ column2 [ columnN ] ] ] }
number | New column width in characters. |
column1 column2 columnN | Optional. Numbers designating the columns to resize, separated by spaces. Values: between 0 and 161, where 0 is the first row-name column. If column-numbers are not specified, all columns are resized to the width indicated by number. |
If the WIDTH command is not used, all columns are wide enough to fit the widest data value.
If the WIDTH command is followed by number with no column selections, number sets the width for all data columns in the report. Otherwise, the width is set for each data column listed in the command.
Column numbers are assigned starting at 0 for the first row-name column, incrementing by one for each row-name column, data column, and calculated column, in that order.
The tilde character (~) follows member names or values that must be truncated to fit in the column to indicate part of the name or value is not displayed.
If possible, space from adjacent columns is used to avoid truncating. The widths of names columns widths may be adjusted if their column numbers (0,1,...) are specifically included in the command. Alternatively, the NAMEWIDTH command may be used.
The following example is based on Sample Basic.
<PAGE (Measures, Market) Illinois Sales <SYM {WIDTH 7} {WIDTH 20 0} <COLUMN (Scenario, Year) Actual Budget Scenario Jan Feb Mar <DESCENDANTS "100" !
Which resizes all data columns to a WIDTH of seven and the row name label column (column 0) to a WIDTH of 20.
Sales Illinois Actual Budget Scenario Jan Feb Mar Jan Feb Mar Jan Feb Mar ====== ====== ====== ====== ====== ====== ====== ====== ====== 100-10 345 354 367 360 370 380 345 354 367 100-20 234 254 267 240 260 280 234 254 267 100-30 #Missi #Missi #Missi #Missi #Missi #Missi #Missi #Missi #Missi |
The <WITHATTR command specifies the characteristics of a base-dimension member that match the specified values in a report script. You must create attribute dimensions in the database outline and associate them with a base dimension before you use it in a report script.
<WITHATTR (dimName, "operator", value)
dimName | Single attribute dimension name. | ||||||||||||||
"operator" | Operator specification, which must be enclosed in double quotes (""). The supported operators are:
|
||||||||||||||
value | Value that, in combination with the operator, defines the condition that must be met. The value can be an attribute member specification, a constant, or a date-format function (for example, <TODATE>). |
These operators may behave differently depending on the attribute type with which you use them. See Notes for more information.
This command specifies two or more attribute dimension tags, which are associated with a base dimension that is defined in the database outline. If you use the <WITHATTR syntax, the command is applied only to a specific query in the report script.
The following table shows examples, based on the Sample Basic database, for each type of operator:
> | <WITHATTR(Population,">","18000000") | Returns New York, California, and Texas |
>= | <WITHATTR(Population,">=",10000000) where 10,000,000 is not a numeric attribute member, but a constant | Returns New York, Florida, California, Texas, Illinois, and Ohio |
< | <WITHATTR(Ounces,"<","16") | Returns Cola, Diet Cola, Old Fashioned, Sasparilla, and Diet Cream |
<= | <WITHATTR("Intro Date","<=",
<TODATE("mm-dd-yyyy","04-01-1996")) |
Returns Cola, Diet Cola, Caffeine Free Cola, and Old Fashioned |
= = | <WITHATTR("Pkg Type","= =",Can) | Returns Cola, Diet Cola, and Diet Cream |
<> or != | <WITHATTR(Caffeinated,"<>",True) | Returns Caffeine Free Cola, Sasparilla, Birch Beer, Grape, Orange, Strawberry |
IN | <WITHATTR("Population","IN",Medium) | Returns Massachusetts, Florida, Illinois, and Ohio |
The ZEROTEXT command replaces zero data values with a text string if a zero data value is output.
{ ZEROTEXT [ "text" ] }
text | Optional. Text string, in quotation marks, to use in place of the 0 value. |
If you do not specify text, the default 0 is restored.
All data values less than .00000000000001 and greater than -.00000000000001 are treated as 0, as well as all data values that would be displayed as 0, regardless of their true value.
{ZEROTEXT "-"} changes a 0 value to -.
Copyright 1991-2002 Hyperion Solutions Corporation. All rights reserved.