Database Queries for Reports and Lookups

Database Queries for Reports and Lookups

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 > Connections.)

  1. Stored Procedure

  2. View

  3. Statement (SQL)

  4. Web Service Query (aka “Query") (used by Business Central connections)

  5. CodeUnit queries (used by Business Central connections)

When creating a report (see Admin > Reports) or a Lookup (see Admin > Lookups), you will:

  1. Tell Mercury the Connection to use.
  2. Choose a query-type.
  3. Select the specific query you need from a drop-down list.  (Or in the case of a Statement, you would enter or paste the query.)

In the New-Report form, the Query-Type is part of the “Report Source” specification that follows the Connection.

In the Lookup form, the Query-Type is part of the “Data Source” specification that follows the Connection.

If you don’t see the specific query you need in the drop-down list, you may have to choose a different query-type and look at the list again.  Whenever you select a query-type, the list is re-created. (This does not apply to Statement queries.)

Most queries are authored by database administrators or developers. If you don’t know what query is needed for a particular report, check with your IT department.

Stored Procedure

The most powerful query type – but also the most complex.  Procedures are miniature “programs'' that live inside your database and are typically authored by a database administrator or developer.

A stored procedure specifies the parameters it uses (if any).  Parameter values for stored procedures are required (cannot be blank) and will automatically appear in Mercury’s Parameters grid, both when a report definition is being created or edited by an admin-user (see Admin > Reports), or when the report is being run by a user.

A Lookup cannot use parameters.  Therefore, any stored procedure that includes parameters cannot be the basis for a lookup.


A View also lives inside your database but is simpler to create and is less powerful than a stored procedure.  Views do not explicitly specify parameters, but Mercury allows you to designate any field(s) in the View to be used as a “parameter” available to the end-user running the report.  But unlike the parameters of a stored procedure, View parameters are optional – at runtime, the user doesn’t have to supply a value.


A Statement query-type consists of one or more database query statements (using standard SQL syntax).  Unlike Procedures and Views, Statements don’t reside in your source database.  They’re stored as a part of Mercury’s report-definition and must be written or pasted directly into the field located to the right of the pencil icon.  Alternatively, click the pencil icon and Mercury provides a Statement Editor that makes it easier to enter, test and edit your query. If you’re familiar with SQL you can write queries yourself, or your IT developer can provide a query for you.

Statement Editor

Statement Queries entered directly into Mercury can be managed using the Statement Editor. 

  • Access the Statement Editor by clicking the pencil icon shown below.


  • Paste or Type a Statement Query into the frame on the left. 


  • Optional: click Format to apply typical formatting to your query – line breaks, indentation, and so on.

  • Optional: Test the query by clicking the Validate button.  This gives you a chance to find errors before you actually try to run the query and retrieve data.

    • If Mercury returns the result “Valid” then proceed to the next step.

    • If Mercury returns the result “Invalid”, review the Statement and make the necessary corrections.

  • Complete the testing by clicking the Execute button. Results will appear in the frame on the right.

Save time testing Queries that return large amounts of data with the built-in filter, Top:.  Just set the number of records you wish to have returned and click Execute.
  • Click OK to save your query and close the Editor window. 

Statement Parameters 

Even though Statement queries don’t reside inside your production database, you can still make use of the Parameters – but you do this yourself as part of the query. 

Parameters are prefixed with a special character that the database recognizes to mean “this is a parameter”.   After the prefix character, you can give the parameter any name you wish. 

Different database platforms may use different prefix characters.  You must know the proper character for your platform (more on this below). 

A common prefix character is the asperand symbol or at sign: “@” (the same way Mercury presents possible parameters to you if you’re using a View or Stored Procedure rather than a Statement).  If you’re working with a Statement Query and aren’t sure about the parameter prefix, try “@”. 

Here are a couple of example statement-queries that use parameters, based on SQL Server.  For these examples we assume the database contains a table or view called “PayablesTransactions”. 

The database (and Mercury) recognizes “@MinBalance” and “@DocDateFrom” as parameters and asks you to supply values in the Statement Editor if you want to test the query execution.  When the user runs the report, these items will appear in the “Enter Parameters” grid. 



As noted above, the parameter prefix character is dependent on the database platform you’re using.  As of this writing, these are the standard prefix characters: 

Database Platform
Parameter Prefix
Microsoft SQL Server
@ (at sign)
@ (at sign)
Actian / Zen
: (colon)
@ (at sign)
Text files (CSV, other)
@ (at sign)

Web Service Query (or simply “Query”)

This is a query that’s similar to a View, but which takes place via an internet (or intranet) web-service connection.  This type of query is available through platforms such as Business Central.  In Mercury this may be shown simply as a “Query”.


Also available through Business Central, a CodeUnit query is a special program that resides within your Business Central database and is typically created by a specialized developer.  You can think of it as a special Business Central version of a stored procedure.


SQL Server Connection – View, StoredProcedure, and Statement

Here’s a create-new-report screenshot that uses an SQL Server Connection (the connection is named “Wonderland” in this example).  The available query-types for this Connection are StoredProcedureView, and Statement.

Microsoft Dynamics 365 / Business Central – Query (i.e., web-service query) and CodeUnit

Here’s a create-new-report screenshot based on a Business Central connection (the connection is named “Mercury-D365-BC” in this example).  The available query-types for a BC connection are Web-service Query (“Query”) and CodeUnit.


As noted previously, the query-type (and the name of the query, if it’s not a Statement) is chosen by the author of the query – typically an IT person.

If you can’t find the query you’re looking for when creating a new report or a lookup:

  1. Double-check the Connection.  Available query-types (and specific queries) dependent on the connection being used.
  2. Make sure you have chosen the proper query-type.  Mercury only displays queries for one particular query-type at a time.  (This is done to narrow your list of choices, and to speed things up.)
  3. If your IT person has supplied you with a Statement, there is no predefined list of available queries.  You type in (or copy/paste) the content of a Statement query.  
  4. If you’re still having trouble, reach out to the author of the query (i.e., your IT department).
    • Related Articles

    • Lookups

      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 ...
    • Reports

      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 ...
    • Database Guidelines

      The Mercury Meta-database Mercury stores meta-data -- report definitions, user/report permissions, etc -- in its own SQL Server database in Microsoft’s secure Azure environment.  Each Mercury installation gets its own Mercury meta-db – it’s not ...
    • Running Table Reports

      Pivot reports and Table reports are run in exactly the same way.  The following examples may show pivot-reports, table-reports, or a mixture - it doesn’t matter. Reports are run in one of two ways. Running from scratch: select the report, specify the ...
    • Running Pivot Reports

      Pivot reports and Table reports are run in exactly the same way.  The following examples may show pivot-reports, table-reports, or a mixture - it doesn’t matter. Reports are run in one of two ways. From scratch: select the report, specify the ...