When building a report using the Jet Excel add-in, you can create a Report Options window which allows you to choose specific filters to narrow down the information you want to view. This is typically created to give the End User (viewer) the ability to customize what they see without having to change the report, itself. It shows as a pop-up window when running a report.
Note: This article describes how to manually create a Report Options window. For information about using the automated Report Options design tool see Using Report Options in the Jet Excel add-in.
A set of worksheet tags are used to create a Report Options Window.
Here is a simple example:
Creating a Report Options window requires the use of three tags:
This tag must appear in column A. Each row that has OPTION in column A becomes a user-selectable option in the Report Options window.
This tag must appear in row 1. The intersection of the OPTION row and the TITLE column creates the title for the option. In the example above, cell B3 contains Start Date, which can be seen in the sample report options window.
This tag must appear on row 1. The intersection of the OPTION row and the VALUE column creates the value for the option. In the example above, Cell C3 contains the value 1/1/2018. When the user opens the Report Options window, they can specify a new value. This value is then stored in cell C3. This cell can be referenced in other cells to create the report which the user desires.
There are three additional tags which can be used to add functionality to Report Options windows:
If used, this tag must appear in row 1. This allows users to select a value or set of values from a drop-down list. The values in the drop-down list may be directly from a database or from Excel. The NL(Lookup) function is used to define the values shown in the drop-down list.
If used, this tag must appear in row 1. This provides the user with useful information when they hover over an option in the Report Option window.
If used, this tag must appear on row 1. This provides a validation check on the data that the user provided. An Excel function containing the validation logic can to return a value of “TRUE” or “FALSE”. A result of “FALSE” will provide the user a message that their entered value is not valid and will force the user to input a valid value.
To create a Report Options window, begin by opening a blank Excel worksheet.
Type Auto+Hide+Hidesheet in cell A1
Add the tags for TITLE, VALUE, and (optionally) LOOKUP
Add OPTION for each row of filters you will need
Fill in the cells with the information you want for your Report Options
If defining a LOOKUP, you can can enter the function by hand or use the Jfx to define it
The word Lookup will appear in the cell (in this case, cell D5).
For more information on the NL(Lookup) function, see Using NL(Lookup) .You may notice the Lookup column does not have anything in it for the Start Date and End Date options. This is because the values 1/1/2018 and 3/31/2018 are hard-coded and may be changed manually to whatever date you desire when the report is run.
When the user runs the report and clicks the Lookup button for Period Type, they will be prompted with the list as defined in the NL(Lookup) function:
Using the ToolTip and Valid Optional Tags
There are a couple of other fields that can be added to the Report Options window:
Tooltip allows you to create a text note...
which is displayed when the user hovers over the field for which the note is defined...
Valid can be used when you want to place a limit on the values entered for an option.
Using the Excel IF() function...
...any value entered by the user will be checked against the TRUE/FALSE function you provide in the Valid column.
For more training on how to create Report Options, see Jet Reports Videos - Creating a Grouping Report