RESTRICT
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>})Parameters
- <column >
-
@DATACOLUMN (<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
-
- <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)
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 (@DATACOLUMN(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 reportWhich 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