How to add a look-up to a pass-through variable

Report Manager filters and parameters let you summarize your report data in creative ways and can allow your reports to run faster. They however only act on the WHERE clause in the SQL that is executed for the report. Pass-through variables give you more flexibility by allowing other parts of your SQL to be affected at run time and can also be used to filter multiple reports by only setting one parameter.

When entering parameters at runtime it helps to have a list of available options to select on hand. These are referred to as look-ups.

Lookups are enabled by default for Data Field and SQL expressions that are added to a container but aren’t enabled by default for Pass-through expressions.

In this tip we are going to show you how to add a look-up to a pass-through expression.

Consider this scenario, you want to create a report that compares the cities your sales reps live in with the locations of your customers so that you can pair your sales reps with customers in their residential area. This will free up time for your sales reps and lower your expenses.

Using the Bellwether Garden Supply demo data in Sage 50 U.S., for example, I have added the Employee and Address containers to the Auto connection. I have also added the pass-through variable City Var to the Employee table and we will now create a look-up for it.

1. In the properties of the City Var pass-through expression, check the Allow Lookups

2. Then click Apply. The Lookup Type list will appear.

3. For the Lookup Type, select SQL Statement Defined Multiselect.

4. After you click Apply, the Lookup SQL SELECT Statement Multiselect field will appear.

5. Go to the City expression and copy the Lookup SQL SELECT Statement from it.

6. Go back to the pass-through expression and paste the SQL code into the Lookup SQL SELECT Statement Multiselect Click Apply to save your changes.

You have now finished setting up the lookup for your pass-through variable. To create your report you would do the following in the Report Manager:

1. Add two new standard reports, one that makes use of the Employee container and one that makes use of the Address container.
2. To the Employee report, add a parameter which sets the City Var variable to an empty field. Then add a filter to the report which sets the City field to the pass-through variable, i.e. @CityVar@. Make sure that you use the Is In comparison method as it allows the report to be filtered by more than one item.
3. Add a filter to the Address report which also sets the City field to @CityVar@ and also uses the Is In comparison method.
4. Create a Union report which includes the Employee and Address reports as sub reports.
5. Set the output sheet of the Address sub-report to sheet 3.
6. Make sure that the Employee sub-report is the last report in the list of sub-reports, i.e. ensuring that it will execute first when the union report is run.

Now, when you run your union report you will be given the option to lookup the available cities. The selection you make will be applied to both sub-reports in your union report and note that you can select more than one city at a time.