This article describes how to manually create a Report Options window for use in Jet Professional.
For information about using the automated Report Options design tool available from the Jet ribbon...
When building a report using Jet, 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 compromising the integrity of the data. It shows as a Pop-up when running a report.
A set of worksheet tags are used to create a Report Options Window.
The following table exhibits a simple example:
Creating a Report Options window requires the use of three tags
|Option||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|
|Title||This tag must appear in Excel’s row 1. The intersection of the Options 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.|
|Value||This tag must appear in Excel’s row 1. The intersection of the Options row and the Value column includes the Value for the option. In the example above, Cell C3 contains the value (1/1/2014). 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 the user desires.|
There are three additional tags which can be used to add functionality to Report Options windows
|Lookup||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. This optional tag must appear in Excel’s row 1.|
|Tooltip||This provides the user with useful information when they hover over an option in the Report Option window. This optional tag must appear in Excel’s row 1.|
|Valid|| 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. This optional tag must appear in Excel’s row 1.
To create a Report Options window, begin by opening a blank Excel worksheet
Always start at the uppermost left corner (Cell A1)
Type in Auto+Hide+Hidesheet to hide the Options worksheet once the report is run.
Next, add in the following headers:
Add Option for each row of filters you need
Finally, fill in the cells with the data you want to filter
The word “Lookup” will appear in the cell (in this case, D5). Use the JFx to create the query.
For more information on the NL(Lookup) function, see Using NL(Lookup) .
You may notice the values “1/1/2014” and “3/31/2014” do not have anything in the Lookup cells. This is because these are hard-coded and may be changed manually to whatever date you desire when the report is run.
There are a few other fields that can be added to the Report Options window
Enter “Tooltip” and “Valid” to the headers
“Tooltip” allows you to create a text note that hovers over fields in the Report Options window.
“Valid” can be used when you want to limit parameters in the filter fields. Similar to the “IF” function, it checks to see if a condition you specify is true or false.
For more training on how to create Report Options, see Jet Professional Videos, “Creating a Grouping Report”, “Grouping Report 5- Adding Report Options” .