How to Modify the Lookup of an Expression using Sage Intelligence

1 minute read time.

There may come a time when you want to choose parameters from a list that determines what data is run for the report. For example, you can select a warehouse from a list of warehouses that will send only the data for the chosen warehouse to the report. Without the parameter list, by default, this list is pulled directly from the source container of the report. This can ease the load on the database as well as save you valuable time.

For containers that access large data sets, its recommended that the SQL lookup type for all expressions be changed to Direct from Container or SQL Statement Defined. This will optimize lookups and minimize the load on the database server.

How to Modify the Lookup Location

  1. In the Connector, locate the expression for the parameter you wish to use.
  2. Set the Lookup Type property to the required lookup type and click Apply.

 

Lookup Types supported by Sage Intelligence:

  • Direct from Container (Default) – this option will cause lookups to be drawn directly from the source container. 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.
  • Customized SQL Statement
  • Customized SQL Statement (Code & Desc Pair)
  • Text File

For more detailed information on the lookup types please visit the Help files in Sage Intelligence.

Best Practices for Lookups

  • Ensure that lookups are working properly on all expressions in the container and not just the expressions intended to be used in the report as other users may create reports off the container. You use the Check/Test and Sample Data features as a first line test then Check Test All Expressions.
  • Where the functionality of the database allows for the use of the SQL distinct keyword, ensure that lookups display a distinct list of values.
  • Where necessary, set the lookup type to a Customized SQL Statement and customize the statement in order to achieve the preferred end result. The lookup of the expression does not always have to come from the table listed in the expression source; it can be set to obtain the value from a different table