Create Report Options for Dynamics Database Lookup

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.

Dynamics NAV Example

1. Suppose you have an Options sheet with filters for start date, end date, G/L account number, customer number, and company:

   

2. First, to tell the Jet Excel add-in which rows contain options, you must type Options in column A to the left of each filter option:

   

3. Next, in row 1, type Title above the column that contains the titles of your options and Value above the column that contains the filters you are using in your report:

   

4. To look up dates in NAV, you can use the Date table.

   Here, the NL(Lookup) functions for start date and end date use months as the Period Type and give viewers the option to select months between 2018 and 2019.

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

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

   You must create another column in your Options sheet with Lookup in row 1 and add the lookup functions to the appropriate option:

   

5. Let's also give the viewers the ability to select from the G/L account numbers and customer numbers:

   

6. Finally, when the viewers select a company, let's give them a note that they can only select one company at a time. We can do this by adding a Filter Field to our NL(Lookup) function called "Headers=" and typing the message we want as the Filter argument:

   

7. When the report is run, a Report Options window will appear:

   

   We can type filter values into the Value column, or if we click icon in the Lookup column , we will get a Report Options 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 Filter Operators buttons.

   

   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.

   

8. Finally, we can hide the entire Options sheet itself from viewers by adding +HideSheet to cell A1:

   

Displaying Values Not in the Database

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

Related Articles

• Report Options for non-Dynamics Databases