1. Scripting Reference for Oracle Essbase
  2. Report Writer
  3. Report Writer Command List
  4. RESTRICT

RESTRICT

The Essbase Report Writer 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:

  1. Any sorting command that sorts on member names (for example <SORTDESC or <SORTASC)

  2. RESTRICT

  3. TOP and BOTTOM

  4. 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 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
TOP
BOTTOM
ORDERBY