Jet Reports
Report Options
Introduction
When Creating a report with Jet, Report Options are created to allow us to select specific filters to narrow down the report to show just the information you want to see. Report Options are mainly created for Jet’s particular users (Viewers). This gives them the ability to apply some specific filter to the report without changing the report itself.
Report Options show as a pop-out window when you try to run a Report (built with report Report Options) just as shown below.
Procedure.
Step 1 Creating Report Options
The Report Options Function on Excel Ribbon Tools is the key to creating Report options.
When Click on the Report Options tab, the Window pop-out empty like this
You can then Fill in the spaces as shown below
Title = Start Date
Value = 1/1/2022
Tooltip = Select a start date of your choice.
For the look-up, I’ll show you how to use that later. use the same procedure to fill in the End date also, and your result should look like this.
Once this is done, a new sheet named Options is created automatically.
Step 2 Tags For creating a Report Options page
We have a required and optional tag to create our report options
Required tags
Creating a Report Options window requires the use of three tags which include,
Option, Titles, Values.
Optional tags
Two additional tags can be used to add functionality to Report Options windows which are,
Lookup and Tooltip.
Required tags
Option
The Option must always be in column A, any row that has OPTION in column A has become an option in the report options.
Titles
This creates a title for the options. it is always in the columns next to “Option”
Values
You can create value for your option here.
Procedure.
Step 1 Creating Report Options
The Report Options Function on Excel Ribbon Tools is the key to creating Report options.
When Click on the Report Options tab, the Window pop-out empty like this
You can then Fill in the spaces as shown below
Title = Start Date
Value = 1/1/2022
Tooltip = Select a start date of your choice.
For the look-up, I’ll show you how to use that later. use the same procedure to fill in the End date also, and your result should look like this.
Once this is done, a new sheet named Options is created automatically.
Step 2 Tags For creating a Report Options page
We have a required and optional tag to create our report options
Required tags
Creating a Report Options window requires the use of three tags which include,
Option, Titles, Values.
Optional tags
Two additional tags can be used to add functionality to Report Options windows which are,
Lookup and Tooltip.
Required tags
Option
The Option must always be in column A, any row that has OPTION in column A has become an option in the report options.
Titles
This creates a title for the options. it is always in the columns next to “Option”
Values
You can create value for your option here.
Optional tags
Lookup
This allows users to select a value or set of values from a drop-down list, this can be done using the Jet function “NL(lookup)“.
Tooltip
This provides you with useful information when you hover over an option in the Report Options window.
Step 3 How to use your NL(Lookup)
NL("Lookup") function is to simply pull a list of values from the database.
For example, if a list of customer numbers ("No.") from the "Customer" table is desired then the function would look something like this:
Step 4 Linking your report Option to your report.
Since we created our report options, I’ll show you how to link the Report Options page to your report. we will link this report option to the “Customer Sales Report“ we’ve created.
As we already know, the report option helps us narrow down our report. now, will filter our report with the date range and customer no from the report options. The filter will be applied on “D5“ and “H5“.
customer no. the filter will be applied to NL(rows). “D5“ while the Date range will be applied to “H5“.
When we applied this filter, we will be able to run our report for a particular date and also for customer no. we only want the report to show.
The amount here is our main concern, we want the amount to display concerning time. so, we apply our date filter here.
Click on the Jet formula you use to create the Amount, when the report wizard pops out, scroll down to the filter. under the filer field, search for the date and select the posting date as shown below.
Click on the next box on the right side “Filter“. Once you do that, go to Nested Jet Function and click on the drop-down. On the drop-down, click on the NP function.
Once you click on the NP, a new window will pop out where we fill in the required information.
On “What”- fill in “Date filter“
On the start date, click the insert report option at the top. This will open the report options where you can now select the start date. select that and then click add.
do the same for the End date.
When you’re done adding the date range, click back and then ok.
Adding customer No. as a filter to NL(rows) D5.
Click on the NL function to get access to the report wizard. then fill in as shown below.
To fill in the filter box here, just click on the “Insert Report Options“ and select No.
Step 5 Running our report.
Anytime you click on the run icon, the report option pops out for you to select or fill in how you want to apply your filter to the report.
As shown here, I run the report for (First January) 1/1/2022 to 12/1/2022 (First December) and I also want the report to show for just some particular customers, so I select from the lookup just 4 No.
when I run my report, it will display for that particular period and just the 4 customers.
