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.
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.
The field name returned by query.
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”.
The display format to use for dates, times, and numbers. Supports typical Excel-style notation (e.g. "##,###.00") as well as standard .NET abbreviations.
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.
Ascending or Descending (the default is Ascending).
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.
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.
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.
ClickSaveto create the Lookup. (This will save all changes that you have made.)
Edit an existing Lookup:
SelectEditfromManage Lookupswindow. This enables editing of all the grid rows and their associated details.
Make changes to the Lookup grid rows and associated details as
ClickSaveto save your changes.
Clone an existing Lookup:
Select the Lookup that you would like to clone and click onClone.
A new row will appear at the end of the grid. All of the values are identical to the original Lookup except theName, 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 newNamefor 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.
ClickSaveto save all your changes.
Delete an existing Lookup:
Select the Lookup that you want to remove.
Click onDelete. You will be prompted to continue. ClickYesto 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 aNewreport orEditan existing report. Refer toAdmin Tools > Reportsfor 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.
To obtain data for your reports (and lookups), Mercury must query your database(s). Depending on the Connection used for your report or lookup, there will be one or more of the following query-types available. (For more on Connections, see Admin > ...
Mercury reports (like any reports) rely on queries to retrieve data. Queries can take several forms. (Not all of these may be applicable to your database platform.) Stored Procedures Views Web Services (OData and similar) SQL Statements The query is ...
Custom Tables Occasionally you may find that your company’s database(s) are missing some information that would help your reporting and analysis. For example, perhaps you are doing multi-company reporting from your accounting platform, and the ...
Manage Reports The Manage Reports form lets you create and edit reports, assign User accesses, and perform other maintenance tasks. (Note: Mercury allows users who run the report to modify the layout and save as a Favorite, so you don't have to worry ...