Manually Creating Report Options

Related Articles...

Overview

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.

Worksheet Tags

A set of worksheet tags are used to create a Report Options Window.

Here is a simple example:

Required Tags

Creating a Report Options window requires the use of three tags:

1. 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.

2. TITLE

   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.

3. VALUE

   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.

Optional Tags

There are three additional tags which can be used to add functionality to Report Options windows:

1. LOOKUP

   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.

2. TOOLTIP

   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.

3. VALID

   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.

Process

1. To create a Report Options window, begin by opening a blank Excel worksheet.

2. Type Auto+Hide+Hidesheet in cell A1

   

   (this will automatically hide the Options sheet when the report is run)

3. Add the tags for TITLE, VALUE, and (optionally) LOOKUP

   

4. Add OPTION for each row of filters you will need

   

5. Fill in the cells with the information you want for your Report Options

   

6. If defining a LOOKUP, you can can enter the function by hand or use the Jfx to define it

   =NL("Lookup",{"Week","Month","Quarter","Year"},"Period Type")

   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: