You can define any of the following options in the key range parameter to customize your EvDRE data.
You can use the following methods for entering EvDRE options:
You can enter the appropriate keywords in the Options cell of the KeyRange.
The Options cell (which can also be named OptionRange), may contain an EvRNG function pointing to a range of cells listing valid options. This range is the default, if you select the Allow options box in EvDRE Builder. The first column in the range must contain a valid option keyword. The second column activates the corresponding option with a Y value, or with a numeric value.
A list of options is below with a description of each; the keywords are NOT case sensitive.
Option |
Description |
---|---|
ApplyColumnAbove |
Applies the colors and formulas in the Before/AfterRange to the column. |
AutofitCol |
Automatically adjusts the size of the columns containing the EvDRE ranges to fit the content after refreshing data. |
Bottom n |
Shows only the specified number (n) of the lowest values in the entire data range. |
DumpDataCache |
The content of the data cache is written in the log file EvDre_log.txt. |
ExpandOnly |
Disables the refresh action and performs only an expansion, when requested. The system does not retrieve data from the database. |
GroupExpansion |
When you expand a report using the Groups dimension, which is used to identify consolidation sequences. |
HideColKeys and HideRowKeys |
Hides the corresponding key ranges. |
NoRefresh |
Prevents the system from refreshing data from the database |
NoSend |
Prevents the system from sending data to the database. |
PctInput |
Enforces a different percentage of input data to trigger SQL queries (default is 20%) |
QueryEngine |
Manual (or blank for Automatic) |
QueryType |
NEXJ,TUPLE (or blank for Automatic) |
QueryViewName |
Use a user-defined view for querying SQL data |
ShowComments |
Add an Excel comment in any DataRange cell with a formula, if the value retrieved from the database differs from the one displayed by the formula. |
ShowNullAsZero |
All empty cells in the data range are filled with zeros. |
SortCol |
Sorts a given column. |
SQLOnly |
Forces the query engine to only issue SQL queries |
SumParent |
Inserts new rows with subtotals. |
SuppressDataRow and SuppressDataCol |
Performs a suppression on the defined data range directly in Excel. |
SuppressNodata |
Prevent the suppression of zero values. Only missing (no data) values are suppressed. Otherwise, both zeros and missing data are suppressed. |
Top n |
Shows only the specified number (n) of highest values in the entire data range. |
To define options, follow these steps:
Open an EvDRE report or input schedule.
In the Options cell of the Key range parameter table, enter the desired options.
When you enter more than one, use a comma as the delimiter. For example, you can enter SumParent, AutoFitCol, NoSend.
Choose the Expand All button and save the report or input schedule.
Note
The first column in the range must contain a valid option keyword. The second column activates the corresponding option with a Yes value, or with a numeric value, where appropriate.