Using Lookups in GP Reports Viewer to make parameter selection more user friendly

One of the critical elements in reporting is parameter selection. During report creation it is essential to make sure that the proper parameters are added to the report to correctly filter on the data the users want to see. However, it doesn’t just stop there. To ensure user friendly reports it is also very important to make the parameter selection easy for users during report generation.

One way GP Reports Viewer aids in this task is by allowing for three different options for parameter lookups: Dynamics GP Lookups, Custom Static Lookups and Custom Dynamic Lookups. I will go over these three options below in more detail.

Dynamics GP Lookups

Out of the box, GP Reports Viewer includes default lookups for a number of common parameters including Customer ID, Vendor ID and Item Number:

Choosing one of these will point to the default Dynamics GP lookup window for each option, with all the standard GP functionality enabled. The idea here is that when users are printing reports, they have a familiar look and feel for selecting their report parameters. For example, if you have a report with Customer ID as the parameter, you can choose the Customer ID Lookup when setting up your report in GP Reports Viewer. The users, when printing the report, simply click on the looking glass icon:

And are then presented with a window they already know for selecting a customer:

One new addition GP Reports Viewer created for this is the Calendar Lookup, choosing this will result in the following pop up when users click the looking glass icon:

Custom Static Lookups

Sometimes, a parameter is a Yes/No choice, or something else requiring a short list of static options. For situations like this, you can create a Custom Static Lookup in GP Reports Viewer. Steps to do this:

1. Navigate to GP | Tools | GP Reports Viewer | Lookups.
2. The Lookup ID will automatically populate with the next available number.
3. Type in a Lookup Name. This is what you will see when assigning lookups to parameters on reports and what the users will see as the name of the lookup window.
4. Choose a Lookup Series – this is simply for categorizing lookups, all lookups will be available on all reports.
5. Select Static for Lookup Type.
6. Enter column Display Headings and Types for your lookup – only the first one is required, you can leave the others blank.
7. Enter Values for your columns, below is a lookup with only one column and a Yes/No option: 
8. If you click Test Lookup you will be prompted to Save and then shown the lookup you have just created:

Custom Dynamic Lookups

Now for the really cool option – with GP Reports Viewer you can create your own custom lookups that can be based on a SQL table, view or stored procedure. You can even point to data in a different database, it does not have to be the one that users are logged into.

This option is often useful for setting up parameter choices where the data may change. Since this option is so robust, I would like to show two different examples of this. One is going to point directly to data in a table, another one will use a SQL view that is created specifically for this lookup.

Example – using data directly from a table

This example will create a parameter lookup with a list of departments in GP. Department in this case is the third segment of the chart of accounts and it is assumed that the department names are populated in the GL already.

1. Navigate to GP | Tools | GP Reports Viewer | Lookups.
2. The Lookup ID will automatically populate with the next available number.
3. Type in a Lookup Name. This is what you will see when assigning lookups to parameters on reports and what the users will see as the name of the lookup window.
4. Choose a Lookup Series – this is simply for categorizing lookups, all lookups will be available on all reports.
5. Select Dynamic for Lookup Type. 
6. Under Database select Current Company Database.
7. Query Type will default to Select statement. (If you’re curious, the other option there is Stored procedure.)
8. For Table type in GL40200. (This is the Segment Description Master table.) 
9. You must have at least one column defined in your lookup and you can have up to three columns.  In this case, it makes sense to have two columns: department number and name. Below is what this would look like. Note that I deleted the Display Heading for Column 3, so that it simply shows up blank: 
10. We also need to restrict this to only look at the third segment, so add SGMTNUMB = 3 to the SQL Where Clause field: 
11. Click Test Lookup, choose Yes to save and you can now preview your lookup: 

Lookups that have more than one column will have a drop down option enabled to allow users to search by any of the available columns: 

Example – using a SQL view

Another common example where you might want to have a custom lookup is a list of all SOP invoices. There is a Dynamics GP lookup for SOP Numbers that is available in GP Reports Viewer, but if your report only has invoices, users may find it easier to have a shorter list to look through. I don’t think it is necessary to go through all the steps in detail, as they will be almost identical to the steps above, so I will simply show you what the setup looks like: 

The results of this lookup are as follows:

Below is the code for the SQL view I used in this lookup:

CREATE VIEW view_Sales_Invoices
AS
SELECT SOPNUMBE, CUSTNAME, DOCDATE
FROM SOP10100
WHERE VOIDSTTS = 0 AND SOPTYPE = 3
UNION
SELECT SOPNUMBE, CUSTNAME, DOCDATE
FROM SOP30200
WHERE VOIDSTTS = 0 AND SOPTYPE = 3
GO
GRANT SELECT ON view_Sales_Invoices TO DYNGRP

Now that you can create any lookups you need for your reports, you can make generating reports more user friendly by making sure each parameter on your GP Reports Viewer reports has a lookup.

For more information about GP Reports Viewer, please take a look at the GP Reports Viewer demo video.

Disclaimer: GP Reports Viewer is an add-on product for Dynamics GP that is created and sold by my company, Flexible Solutions. I may be slightly biased, but I think it is the best thing since sliced bread.