Sage Intelligence gives you the ability to make your Lookups more descriptive, giving you a better understanding of the values you are selecting and helping you to filter the right information. This is done by using a code and description pair as the Lookup Type in the Connector.
In this tip we’ll show you how to select this option and enter the SQL syntax for it. We’ll then add a parameter that makes use of it and show the difference in the values presented to you.
Parameters lets you select values that you’d like to filter a report. The selection that’s presented to you in the Report Manager is called a Lookup and Sage Intelligence allows you to define what this is. Depending on how it is setup, it may be difficult for you to know exactly what data will be available in your report, for example, if you are filtering by a product ID.
I’m going to use a basic example. I want to view my customer information in Microsoft®Excel® and be able to filter the report by customer ID at run time. I have added the Customers table to the Connector along with a number of Expressions including Customer ID and Customer Name.
I’m going to select my Customer ID Expression. In order to enable Lookups for an expression, the Allow Lookups checkbox needs to be ticked. You’ll notice that the default lookup type is Assumed from Base Table with the associated SQL SELECT statement.
Note: It is important to remember that Lookups are not provided if the Container Type is a SQL Query.
I’m now going to change the Lookup Type to a Code Description Pair. In order to use it, I need to change the SQL SELECT statement to include a field from my table that will serve as the description for the Lookup. This will be my Customer Name and it is quite easy to do. I just need to add the Name field to the SELECT part of the statement, after the Customer ID and separated by a comma.
Before selecting Code Description Pair, it’s a good idea to copy the existing SQL statement as it will be removed when the Lookup Type is changed. You can then paste it back after the change and add the description field from there. Remember to click Apply to save your work.
I have now created a standard report that uses the Customers Container and have added a parameter to the report which says the Customer ID is in…, and left the Parameter field blank.
You’ll see that when I run out the report, I’m requested to enter a value for the ID. If I click on the ellipses button, the Lookup is presented as a code description pair and makes the values I want to choose easier to identify.
Stay tuned as in an upcoming tip, we’ll show you how to use the Code Description Pair in conjunction with a Pass Through Variable.
