Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Manually creating a Report Options Window


Overview

This article describes how to manually create a Report Options window for use in Jet Professional. 

Help Topics

Report Options Tool

For information about using the automated Report Options design tool available from the Jet ribbon...

...see Using Report Options in the Jet Excel add-in.

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.

 



 

Worksheet Tags

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

 

Tag Meaning/Usage
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

Tag Meaning/Usage
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.

 


 

Process

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:

 

Title—Value—Lookup


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.

Optional Tags

 

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

 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments