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

• Click OK to save your query and close the 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, 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 StoredProcedure, View, 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).