A Lookup (or Lookup-list) is a list of values from which the user chooses when running a report -- the result is used as the value of a report parameter.  Lookups can be single-select or multi-select.

Lookups are created and managed via Tools > Admin Tools > Lookups (which you're reading about now).  A lookup is associated with a report parameter for use at run-time via Admin Tools > Reports.  The same lookup can be used in multiple report parameters.

The example below shows a multi-select Lookup list in use. The corresponding report parameter is a list of Customer Numbers. The user can select one or more items.                                                                                     

Mercury passes the set of selected items to the report’s stored procedure as a comma-delimited list of values.

Lookup-lists are populated based on stored procedures that retrieve the appropriate data from your database(s).

There are three steps involved in setting up a Lookup.

1. Create the Lookup, which is discussed below.
   
2. In the applicable report definition(s), associate the Lookup with a report parameter. See Admin Tools > Reports for more information. You can associate the same Lookup with more than one report.
3. Use the Lookup - This happens automatically when running a report with an associated Lookup, and is illustrated above. See Pivot Reports > Running Pivot Reports or Table Reports > Running Table Reports for more information.

A Lookup-list includes the following:

1. A Display Member  - field that specifies the data shown to the user in the Parameter entry area.
   
2. A Value Member - a field that specifies the data used by the report’s query.  This is often an ID# or Account# of some kind.

It’s possible for the Display and Value fields to be the same field.

• Other field(s) as desired, which provide additional context for the user and can be used to sort and search the list.                   

Managing Lookups

To open the Manage Lookups window:

1. Click on the Lookups icon on the Admin Tools ribbon menu.                                                                                                              
   

• A window similar to the following is displayed:                                                                                                                                     

Create a new Lookup:

• Select New from the Manage Lookups window.                                                                                                                                   
  

• A new row will appear in the grid marked in *.                                                                                                                                      
  

  Adding a new row makes the *entire grid* editable. In addition to your new Lookup, you can also edit any or all existing Lookups as you see fit. And you can add as many new rows as you wish.

  The grid columns are:

  • Name - A name that you assign. Lookup names must be unique. When you have entered a value here, additional entry fields are presented below the grid.
  • Description - A short description
  • Allow Multi Select - Allows selecting multiple items
  • Data Source - The stored procedure that provides the Lookup data. This is a read-only field in the grid.
    
  • Preview - Available when your Lookup definition is complete; it will execute the Lookup so that you can see the results and make sure it works as desired.
    

  Below the main grid, additional fields appear that must be completed.

  • Select a Connection to a server / database (see Admin Tools > Connections for more information)
    

    
    

  • Choose the Data Source - View/StoredProcedure/Statement/CodeUnit. These Database Query options depend on the connection type and may need to be provided by your IT Department or Database Administrator. For more information see the article, Data Base Queries for Reports and Lookups.
  • Enter the name of the stored procedure providing the data. If it’s a Statement, then you enter (or paste) the query into Mercury yourself.
  • Click Get Fields. This will populate a grid with the fields returned by the stored procedure. Change items as necessary.
    

  • 
    

    Field Name	The field name returned by query.
    Caption	The field name as you want it to appear in the Lookup list when it is displayed to the user. In the above example, the lazy user left the caption as “Cmpnynam”!  A better caption might have been “Company Name”.
    Format String	The display format to use for dates, times, and numbers. Supports typical Excel-style notation (e.g. "##,###.00") as well as standard .NET abbreviations. See this Microsoft article for information on standard format strings.
    Sort Index	The precedence of the field when used for sorting, relative to other sort-fields.  Sort-index is meaningful only when the report is sorted by multiple fields.
    Sort Order	Ascending or Descending (the default is Ascending).
    Display Member	The field whose values are shown in the report’s Parameters grid after the user has selected the desired item(s) The Lookup must have exactly one Display Member.
    Value Member	The "key" field whose values will be passed to the report’s stored procedure when the user runs the report. The Lookup must have exactly one Value Member. The Value Member and the Display Member can be the same field.
    Visible	Determines whether the field is shown in the list when it is displayed to the user at runtime. Depending on the results of your query, you may not need all the fields to be displayed in the list shown to the user running the report.

  • Click Save to create the Lookup. (This will save all changes that you have made.)

    
    

Edit an existing Lookup:

• Select Edit from Manage Lookups window. This enables editing of all the grid rows and their associated details.
  

  
  

• Make changes to the Lookup grid rows and associated details as
• Click Save to save your changes.

Clone an existing Lookup:

• Select the Lookup that you would like to clone and click on Clone.                                                                                                   
  

• A new row will appear at the end of the grid. All of the values are identical to the original Lookup except the Name, which is blank.

  
  

The entire grid is now in edit-mode, so you can change other Lookups or add New ones as you wish.

• Provide a new Name for the Lookup.
• Change other values in the Lookup’s grid row or detail area as desired.
• Since the entire window is available for editing, you can also make changes to other Lookups as desired, including adding new ones or making new clones.
• Click Save to save all your changes.

Delete an existing Lookup:

• Select the Lookup that you want to remove.                                                                                                                                         

• Click on Delete. You will be prompted to continue. Click Yes to delete the Lookup.

Undo unsaved changes:

• Reset cancels your pending changes.  If you confirm, all your edits since the most recent "Save" will be discarded.
  
  
  

Assigning a Lookup to a report

To make a Lookup available to an end-user running a report, the Lookup must be associated with the appropriate report parameter.

Assign a Lookup to a report:

• Click on the Reports icon in the Admin Tools ribbon menu.                                                                                                                 
  
• Create a New report or Edit an existing report. Refer to Admin Tools > Reports for more information on creating or editing a report.
• On the report’s Parameters tab, use the Lookup column to choose the Lookup you want to use for a particular parameter.
  For more information about creating and editing reports, see Admin Tools > Reports.                                                                     
  
• Click Save.