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.

View

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.

Statement

A Statement query-type consists of one or more database query statements (using standard SQL syntax).  But unlike Procedures and Views, Statement queries don’t reside in your database.  They’re stored as part of Mercury’s report-definition.  If you’re familiar with SQL you can write queries yourself, or your IT developer can provide a query for you to paste into Mercury.


If you create a report or a lookup that uses a Statement query, you enter (or paste) the query directly into Mercury – there is no drop-down list of Statement queries. 


If your query is complicated, you may want to edit it in a separate window – this gives you more room and also allows you to test the query.  Click the “pencil” icon as shown below to open the query-editor window.

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”.

CodeUnit

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.

Examples

SQL Server Connection – View, StoredProcedureand 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.

Troubleshooting

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