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

Introducing Report Options with Database Lookup


Suppose you wanted to give your report viewers the option to select specific values from the database on the Options sheet.  Previously, you couldn't do this unless you manually typed the values onto the Options sheet.  Now you can use the Database Lookup feature to do that automatically and even eliminate the Options sheet entirely.

=NL("Lookup","Date","Period Start","Period Type","Month","Period Start","01/01/00..12/31/01")

=NL("Lookup","Date","Period End","Period Type","Month","Period End","01/01/00..12/31/01")

=NL("Lookup","Company","Name","Headers=","Only select 1 company at a time")

 

Dynamics GP Fabrikam Example

Suppose you are creating an Options sheet for a P&L Statement. ou might want filters for revenue accounts, cost of good accounts, expense accounts, years, and periods. You could set that up 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 sheet(s).

In order for Jet Reports to know where the data is in the database, you must write NL functions with "Lookup" as the What argument. These NL functions will simply return all the values that you want viewers to be able to select as filters, so you can filter data just like you would in any other NL function.

The first 3 functions for GL account filters are fairly simple. They can be created like the following.

The years can be retrieved from the GL11110, Account Summary Master View table like the following.

When viewers select a period, you may want to remind them that Period 0 contains the starting balance. 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.

Now when you select Jet | Report a Report Options window will pop up like the following.

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 on a value or an operator, it will be inserted into the Current Filter box at the bottom.

Notice that if you click the arrow next to the period filter, 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.

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.

 

 

Access Northwind 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 sheet(s).

In order for Jet Reports to know where the data is in the database, you must write NL functions with "Lookup" as the What argument. These NL functions will simply return all the values that you want viewers to be able to select as filters, so you can filter data just like you would in any other NL function.

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.

Now when you select Jet | Report a Report Options window will pop up like the following.

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 on a value or an operator, it will be inserted into the Current Filter box at the bottom.

Notice that if you click the arrow 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.

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 not in the database. You can do this by supplying a Excel array for the Field argument of the NL("Lookup") function like the following:

=NL("Lookup",{"MyValue1","MyValue2","MyValue3"},"My Values")

=NL("Lookup",H5:H10,"My Values")

Note that a Field argument is still required when you specify an Excel array so that there is a value in the title of the Lookup window.

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

Comments