Defining Parameter Lookups

Defining Parameter Lookups

When a report has Parameters defined for it a user may use the lookup facility to pick parameters from a database list (see Running a Report with Parameters for more details). By default the parameter list is pulled directly from the Source Container of the report. Often this can be very time consuming and load heavily on the underlying database and often there is a more logical place to pull the list from.

An example of this might be where your report is pulling information from a large transactional table (for example, Sales) and a parameter is defined on the report against a Customer Account code. The Customer Account Code might also be defined in a smaller reference data table (for example, Customers) and it would make sense to then define this as the lookup table.

To do this locate the underlying expression for the Parameter in the Connector and set Lookup Type property to the appropriate lookup type. Sage Intelligence Reporting supports the following lookup types:

• Direct from Container (Default)

  This option will cause Lookups to be drawn directly from the Source Container. Note that this is inefficient if the source container contains more than one table however it will only bring back entries where they are contained in the container

• SQL Statement Defined

  This option allows you to insert a SQL statement which will be used to define the Lookup. This is useful where you want maximum control over the values that are returned in the Lookup. Using this option requires that you are familiar with SQL and with any SQL specifics of the Database system being accessed. Once you select this option and click the Apply button a new property named Lookup SQL SELECT Statement will appear and you can edit it to customise the lookup

• SQL Statement Defined (Code & Desc Pair)

  This option allows you to insert a SQL statement which returns a code and description pair for each data item to be returned in the Lookup. The Description is displayed in the Lookup but the Code of the selected Lookup item is used for the Parameter Value. This is useful where the parameter value required is not meaningful to the user. An example would be where Store Code is required as a Parameter Value, however users only know stores by the Store Name. By using a Code & Desc Pair Lookup, the Store Names will be displayed in the Lookup list but the Store Code will be passed through as the parameter value when a Store Name is selected from the list. Using this option requires that you are familiar with SQL and with any SQL specifics of the Database system being accessed. Once you select this option and click the Apply button a new property named Lookup SQL SELECT Statement will appear and you can edit it to customise the lookup

• Text File

This option allows you to specify a text file which will be used to define the Lookup. The text file must contain a single item on each line. Using Text Files can be useful when the list provided in a parameter lookup is very specific. Where a normal SQL lookup might bring back some items which are not relevant to a user then a Text File can be used to populate with just the relevant items. It should be noted that the text files that are used for Lookups lie outside of the Containers and are not bundled with a Report that is exported for redistribution. The text files would need to be implemented in the target system manually