The RESTRICT command specifies the conditions that the row must satisfy before it becomes part of a result set.
Syntax
<RESTRICT (<column | value> <operator> <column | value>{<logicalOperator><column | value> <operator> <column | value>})
Parameter | Description |
---|---|
<column > | @DATACOL (<colnumber>) | @DATACOL (<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 |
<logicalOperator> | Report Writer processes logical operations from left to right without exception. Parentheses are not supported. The supported logical operators are AND and OR. |
Notes
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:
Any sorting command that sorts on member names (for example <SORTDESC or <SORTASC)
RESTRICT
TOP and BOTTOM
ORDERBY
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 buffers used for storing and sorting the extracted data. The following settings affect the way the RESTRICT, TOP, and BOTTOM commands work:
Retrieval Buffer Size (a database setting)
Retrieval Sort Buffer Size (a database setting)
NUMERICPRECISION (an essbase.cfg setting)
For more information on the database settings, see the Oracle Essbase Database Administrator's Guide.
Example
{ 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
See Also