Validate Report Options

Overview

When using Report Options, it is possible to validate the values entered when entering Report Mode or when the report is refreshed.  To do so, you can use the following optional worksheet column tags: VALID and MESSAGE.

1. Valid

   The column containing the Valid tag should contain a valid Excel function that returns TRUE or FALSE based on the value of the report option.

2. Message

   If, after switching to Report Mode and the Report Option values are entered, the Excel function in the Valid column returns FALSE, the Report Viewer will be presented with the message defined in the column containing the Message tag.

Example

When a report is run with these example report options, valid values for the "Year" option are greater than 2010, and valid values for the "State" option are "WA", "OR", or "ID".

After the Report Viewer acknowledges the message presented when the Option value is validated, the original Report Option value is restored to the Value column.  However, this value is restored as text This is important to remember when working with numeric values, and these values should be handed accordingly in the validation formula.

For example:  Rather than using the validation formula =IF(C3>10,TRUE,FALSE) to ensure that the Report Option value is greater than ten, you should ensure that the original value is treated as a numeric value within the formula: =IF(VALUE(C3)>10,TRUE,FALSE)