Create Report Options for non-Dynamics Databases
Moderate - some database knowledge required
Overview
As a report designer, you want to give your report viewers the ability to specify report options by allowing them to select specific values from the database.
Using Report Options, you can create NL("Lookup") functions to return all the values that you want viewers to be able to select from - which you can then use to filter data just like you would in any other NL function.
Non-Dynamics Example
-
Suppose you made an Options sheet for a report that is returning orders. You might include customer name, City, State/Country, and Date like the following.
-
First, to tell Jet Reports which rows contain options, you must type Options in column A to the left of each filter option like the following:
-
Next, type Title in row 1 above the column that contains the titles of your options and Value above the column that contains the filters you are using in the report:
-
The functions for customer name, city, and state/country are pretty simple. They can be created like the following:
-
When viewers select a date, you may want to give them a note that they are actually filtering on ship date, not order date. You can do this by adding a Filter Field to your NL function called "Headers=" and typing the message you want in as the Filter argument like the following:
-
When the report is run, a Report Options windows will display:
-
You can type filter values into the Filter column, or if you click the arrow to the right of each filter, you will get a Filter Lookup window with the values pulled from the database by the NL("Lookup") formula. Notice that this Lookup window can be used to progressively build filters using the database values and the Insert Filter Operator buttons. When you click a value or an operator, it will be inserted into the Current Filter box at the bottom.
Notice that if you click the icon next to the Date, the header at the top of the Filter Lookup window gives the message that you typed into the Headers= Filter in the NL("Lookup") formula.
When we click the arrow next to the Company, the header at the top of the Report Options Lookup window gives the message that we typed into the Headers= Filter in the NL("Lookup") formula.
-
Finally, using this method to set report options, you can actually hide the Options sheet itself from viewers by adding +HideSheet to cell A1 like the following.
Displaying Values Not in the Database
It is possible to display a list of lookup values which are not in the database. We can do this by supplying an Excel array for the Table argument of the NL("Lookup") function:
=NL("Lookup",{"MyValue1","MyValue2","MyValue3"},"My Values")
=NL("Lookup",H5:H10,"My Values")
Note that a Field argument ("My Values" in this example) is still required when you specify an Excel array, so that there is a value in the title of the Lookup window.