Reports

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 about creating a perfect report.) 

Select  Reports  from the  Admin Tools  ribbon at the top of the application.



A window similar to the following is displayed:



Create a new report

  1. Select  New  from the Manage Reports toolbar.

  1. A new screen will appear that allows you to enter details about the report.  Required fields are identified with the icon.

    Refer to the table below for information on each field.  

Field Name

Description

Report Name

A unique name for the report

Active

Checked - status is active.  The report is available to run and will appear in the report-list of any user who has been given permission.  (See the Report Properties - Users section of this article for more information.)


Unchecked - status is inactive.  The report is not available to any User, regardless of the permissions.  It can be accessed by Administrators in the Manage Reports List.  Inactive report names appear in italics with the row shaded.

Description

A brief description.  Users can search for reports based on descriptions, so taking the time to create a helpful description is worth it.

Display In / Report Type

The type of report.  Available choices are:

  • Pivot

  • Table

  • SSRS

  • Crystal

  • SSRS

Category

The Category is a descriptive classification of the report that appears in the main report-list of the end-users.  Categories are handy for sorting, grouping, and filtering the main report list that users see.  


Choose an existing category or create a new one by entering a name in the field.

Connection

Choose the “Connection”, which specifies the database that will be used for the report.

You can create a new connection for the report using the  button.  This will open the Manage Connections window.

The Connection specifies the “database-user”, which could be the same as Windows user, or could use a database-specific username and password. (Mercury encrypts this). In either case, the user must have permission the report’s query.

Report Source

The report source specifies the database query that retrieves data for the report.  It is composed of a query type and query name.


Possible query types are Stored Procedure, View, Statement and CodeUnit.  The ones available for the report you’re creating depend on the underlying database.  (For example, some databases don’t support Stored Procedures.) 


Queries are typically authored by IT, so you may need your IT department or database administrator to tell you the query-type and query-name that was designed to provide the data you want.


Choose the query-type from the drop-down list.

Then choose the specific query (stored procedure name or view name) from the second drop-down list.  (You can also enter it directly.)


If your query-type is Statement, that means you need to supply the query – you can enter it yourself if you know the query language, or you can get it from IT.


For more information on Database Queries see the article, Data Base Queries for Reports and Lookups.
  1. After you have provided the type and name of the Report Source click on  Validate. Mercury evaluates the report source’s query to determine the data fields it supplies. 
           
  1. Enter additional Report Properties.  The exact settings depend on the type of report you are creating: Pivot, Table, Crystal, or SSRS.
Mercury will page through the Report Properties required by each Report Type.  Enter the details on each page and click  Next  until the end.  (If you want to make a change you can click  Back.)  Click  Finish  to complete the report.  Don’t worry if you forget something – you can edit the report as often as you wish.  See the section on Report Properties for more information.

Edit an existing report

  1. Select the report that you want to edit by clicking on the row and then clicking the Edit icon from the Manage Reports toolbar.  Quick Access: You can also right-click the Report name from the main window’s Report List and choose Edit from the Manage Reports Toolbar.


This opens the report in the Edit Report window with all its details and properties. 

  1. Change the report properties and/or details as needed.  Please review the section on Report Properties for more information.

Some properties cannot be changed: Report-type, Report Source and Connection.  Changing any of these requires creating a new report.  

If the report has any Favorites (saved by any user) that include layouts, these layouts will be discarded in order to edit the report fields. (The Favorite’s saved Parameter values, if any, are preserved – only the layout will be lost.)  Mercury warns you with a banner in the Fields tab and requires that you  Enable Editing  to continue.  See the Pivot Reports Information>Favorites or Table Reports Information > Favorites articles for more on favorites.


Clone an existing report

  1. Select the report that you would like to duplicate by clicking anywhere on the row and click Clone.  Mercury will automatically rename and save the cloned report with a default prefix, “CLONED_FROM_”.
           
  1. The Edit Report window will open.  All the details and the properties of the selected report will have been copied to the cloned report. 
      
  1. Make any changes desired.
  2. Click  Save  to keep your changes or  Cancel  to discard them. (The cloned report still exists, even if you cancel your changes.)

Delete an existing report

  1. Select the report that you want to remove from the reports list by clicking anywhere on the row.
           
  1. Click  Delete  on the Manage Reports Toolbar.  Mercury will prompt you to confirm the deletion in order to continue.  Click  Yes  to complete the process.
           

Automatic Color Coding of Reports

Mercury can apply a unique color to reports in the Main Report List of each User.  Click on Generate Legend Colors to access a drop-down with two options.


  1. For all reports - randomly generates background color codes for all reports.  This will overwrite existing coding.
  2. For reports with no color - randomly generates background color codes for the reports without one.

Selecting multiple reports

Some operations (deleting and exporting) can be done for multiple reports at once.

  1. Click  Show Selector  on the Manage Reports Toolbar to reveal a checkbox selector column in the Manage Reports list.  You can then select multiple reports by checking boxes.  The row of selected a Report will turn from white to a shaded blue.
           
  1. When the checkboxes are shown,  Show Selector  changes to  Hide Selector.  Click to hide the checkbox selector column in the report list.  Any checked selection(s) remain highlighted.
  1. Alternatively, without using the selector, you can press and hold  CTRL  and click on the reports that you want to select.

Search for reports

  1. Click the  Search  icon on the Manage Reports Toolbar to show a search box above the report list.  Enter keywords for the report you want to search and press  Enter.
  2. Alternatively, press CTRL + F to display the search box.
  3. Click on the Close (X) button next to the search box to hide the search panel.
           
      

Export and Import reports

Mercury offers two kinds of exports.  

  1. Export the report definition – for subsequent import into a different installation of Mercury, or for backup purposes.  
  2. Export the results from a Pivot or Table Report canvas.  See Pivot Reports > Exporting Report Results or Table Reports > Exporting Report Results for more information.

To Export Report Definitions:

  1. Select one or more reports.  To select more than one report, use the “selector” feature described above.
  2. Click  Export Reports  on the Manage Reports Toolbar, and identify the name and location of the export file.

To Import:

You may wish to import reports that have either been exported from another installation of Mercury, downloaded from the Mercury website, or supplied by Mercury support.
  1. Click  Import Reports  on the Manage Reports Toolbar and choose the report file you wish to import.
          

Mercury will import the entire file.  Any reports you don't want will have to be deleted after the import is complete.


Report Properties

Depending on the report-type, there will be between two and six associated detail sections.  When creating a New report, these are presented one after the other, in sequence.  When editing an existing report, they are all shown at once as a set of tabs underneath the main report area.

Property

Pivot

Table

Crystal

SSRS

Parameters

🗸

🗸

🗸

🗸

Fields

🗸

🗸



Relations


🗸



Summaries


🗸



Actions


🗸



Users

🗸

🗸

🗸

🗸


Parameters

The parameters tab determines how the user running the report will be prompted for parameter values used by a data query.  This tab may vary somewhat based on the nature of the query (a stored procedure vs a view or a statement).  If the query is a stored procedure, parameters are determined by the author.  Otherwise, the Mercury user creating or editing the report can choose any report field(s) to be used as parameters by checking the box.


This tab is available for all the report types.

Refer to the table below for more information on each column.  Each item represents a parameter that is used by the Database Query to supply data for the report.

Column Name

Description

Parameter Name

The name of the parameter used by the Database Query (read-only).

Prompt

The label or caption that is presented to the user when entering parameter-values as part of running a report

Order

The order of the parameters in the parameter-entry grid presented to the end-user as part of running a report

Editor

A Lookup-list invoked for parameter-entry when the end-user runs the report

Data Type

The underlying datatype of the parameter (read-only)

Length

The size of the parameter (read-only)

Editor Filter

Filters the Lookup-list results available to the end-user when entering parameter values as part of running the report

Example: If you set the value of this column to Fabrikam, the Lookup-list presented to the end-user will only display records associated with the value Fabrikam.

Parameter Style

Applies to Crystal-type reports (read-only).



Fields

The Fields Tab contains information about each field supplied by the report's data-source and allows you to create new ones.
The exact set of columns in the grid is dependent upon the type of report you’re creating or editing (pivot vs table).


Tool-buttons Above the Fields Grid

  1. Table

  2. Pivot

  3. Add Unbound Field or Sparkline

  4. Delete Unbound Field or Sparkline

  5. Hide all FilterArea Fields

  6. Up and Down Arrows

Table and Pivot

Report field properties vary depending on whether you're working on a Pivot report or a Table report.  Even though you chose a default layout when you created the report, the User is able to switch between them when running the report. You can configure a layout for either or both types of reports. 

Unbound Field

An "Unbound Field" is also known as a "Calculated Field".  It is a new Field created using Fields from the original data source.  Because it isn’t part of the original data source, it’s not tied or "bound" to a data-source field.


When you create an Unbound Field, you provide Mercury with a calculation, a formula that defines how the field’s value is determined.

Add an Unbound field

  1. Click on  Add Unbound Field or Sparkline  at the top of the Fields tab.   A new field will appear in the fields grid.  The default name of this field is "New Unbound'' and the row is highlighted in light green.  Replace this with a new name.
           
  1. Enter a caption for the new field, the name used when the field is shown on the report.  This can be the same as the field name.
  1. You must also provide at least a data type and a formula.  See the article on Unbound Fields in Pivot Reports Information > Calculated Fields or Table Reports Information > Calculated Fields for more detail.
  2. Make any other changes you wish, just like you would do with any report field.
Be sure to save the report when you’re done creating or editing it!
You can edit your Unbound Field later – change its name, caption, even the formula.  You can change other attributes too.

Delete an Unbound field

  1. Select the field (click anywhere in the field’s row)
  1. Click  Delete Unbound Field or Sparkline

This button is enabled only when an Unbound field is selected.  Be sure to save the report when you’re done creating or editing it!

Sparkline

A Sparkline is a small, generalized chart that provides a condensed visual representation of the data from which it is drawn on.  Sparklines can also be added from the Canvas and preserved as a Favorite version of the Report. For Direction on how to add Sparklines from the Report Canvas, see the Sparklines article.

Add a Sparkline

  1. Click  Add Unbound Field or Sparkline.  A new field will appear in the fields grid.  The default name of this field is "New Unbound'', and the row is highlighted in light green.  Replace this with a new name.
  2. Enter a caption for the new field, the name used when the field is shown on the report.  This can be the same as the field name.  
  3. Click into the Fields cell of the Sparkline detail and then click on the 3 dots icon ( … ) to open the Sparkline Edit box.
                      
  1. Re-enter the Column Caption Name that was used for the Caption detail.
  2. Choose a Sparkline type.
  3. Add the desired fields from the Available area to the Selected area.
  4. Click  OK  to complete the sparkline.

Delete a Sparkline

  1. Select the sparkline by clicking anywhere in the row.
  2. Click  Delete Unbound Field or Sparkline
  3. Save  your changes.

Hide All Filter Area Fields



Hiding all FilterArea Fields is a feature available with Pivot Reports.  It provides more room for the report results on the Canvas by removing all the Fields for which the Area Type is designated as Filter.  This overrides the field's Visible setting.


Hidden Fields are still available for use in the filter from the Pivot Ribbon’s Field List button on the Canvas. For more information, see Customizing Field Rows and Columns Using the Field List.

Up/Down Arrows: Changing a Field’s Position

To change the order in which a field appears on the report:

Select the field that you wish to move by clicking anywhere in the row.

Use the  Up-Arrow / Down-Arrow  buttons above the Fields grid, located to the right of the  Delete Unbound Field or Sparkline  button.


Click on to shift the field one position towards the right.

Click on to shift the field one position to the left

These controls have the effect of changing the field’s visible order in the Layout.  This can also be achieved by editing the Visible Order Field Detail directly.  See below for more information on this and other columns in the Fields grid.


Field Details

The following Table describes the Fields Details and identifies which are required for each Report Type.

Column Name

Pivot Report

Table Report

Description

Field Name

🗸

🗸

The name of the field as returned by the report's data source.  (The stored procedure/view/statement)

Caption

🗸

🗸

The heading name of the field as it should appear in the report.

Area

🗸


The area in which the field appears: Row, Column, Data, or Filter.

See Introduction to Pivot Reports for more on areas.

TableNum


🗸

Appears only when the data source includes more than one table of results.

This is the table # in the data-source to which the field belongs.  Table-numbers range from 1 to the number of result-sets returned by the stored procedure.  There is normally no reason to change this value.

Visible Order

🗸

🗸

The order in which the field appears on the report.

Visible

🗸

🗸

Determines whether the field is visible on the report.

Sparkline


🗸

The theme used for Sparkline charts: Line, Column

Unbound Data Type


🗸

For Unbound fields (calculated fields), the datatype of the calculated results.

Formula


🗸

For Unbound fields (calculated fields), the expression that defines the calculation.  See the article on Unbound Fields in Pivot Reports Information > Calculated Fields or Table Reports Information > Calculated Fields for further information.

Binding

🗸


The expression that defines the calculation. See the article on Unbound Fields in Pivot Reports Information > Calculated Fields or Table Reports Information > Calculated Fields for further information.

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.

Typical examples:

  • n0 - numeric with 000s separators, no decimal places.

  • n2 - numeric with 000s separators, 2 decimal places (common for $ amounts)

  • f0 - numeric without 000s separators, no decimal places

Special formats:

  • Action - the field will present an "Action" button instead of data.  See Actions for more information.

  • Image - the field contains an image.

  • Image, #### - an Image rendered at a specific size

Summary Type

🗸


The summary function used when aggregating a data-area field. (Sum, Average, etc.)

Group Method

🗸


The custom grouping method (if any) used to group the field's values.  See General Tools > Groups for more on custom groups.

Sort Field Name


🗸

Specifies an alternate field to be used for sorting purposes.

Example: You might want to sort your sales TerritoryName values based on the corresponding your sales TerritoryNumber.

Sort Method

🗸

🗸

The custom method (if any) used to sort the field.  See Article General Tools > Sorts for more information.

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 normally Ascending)

Show Values

🗸


Determines whether the field's values are shown.

Show Totals

🗸


Determines whether the field's total is shown on the report.

Show Grand Totals

🗸


Determines whether a field's grand total is shown on the report.

Allow Filter

🗸

🗸

Determines whether the end-user can filter report results based on the values in the field.

If enabled, hovering over a Pivot Field Icon or Table Field Name in the report results shows a "filter" symbol to the user, enabling control over which of the field's values are to be included in the report.

Chart Axis

🗸

🗸

The role played by the field when used on a chart, i.e.  whether the field’s values define a chart axis or serve as the values to be plotted.

  • X - the x-axis – in a 2-d chart, typically the horizontal axis

  • Y - the y-axis – in a 2-d chart, typically the vertical axis

  • Z - the z-axis – in a 3-d chart, this is the axis that rises out of the page

  • V - designates the field as the “value” field to be charted on the x, y, (and possibly z) axes.


Relations

When a data retrieval query returns multiple distinct result-sets (tables), Mercury numbers each to identify which one a field belongs.  How the tables relate is defined in the Relations tab where the association between two of the tables is represented on each row.  This Property applies only to Table-reports as Pivot-reports use just the first table.

Relationships are composed of:

  1. a master table, also called a “Parent”
  2. a detail table, also called a “Child”
  3. a set of fields that are common to both tables, which link a Parent record with its associated Child record(s)

A Parent record can have 0, 1, or many Children.  A Child record has exactly one Parent record.


The example below shows a report that includes three tables arranged in three levels of hierarchy.  Table#1 is the parent of Table#2.  In turn, Table#2 serves as the parent of Table#3.  This results in a three-level report.



Column Name

Description

Relation Name

A name that you (and Mercury) use to identify the relationship between the two tables.  This name is shown on the canvas when the report is run.  You can make up any name you wish.


Consult with the person that created the database query (your IT Developer or DBA) if you need help understanding how the tables relate to each other.

Parent

The Table Num of the parent (master) table in the relationship being defined.  (See the Fields section of this article for more information on the Table Num value.)

 

Note that the same parent-table can have multiple child-tables.  Here’s a more complicated example than the one above.  There are four tables with 3 levels of hierarchy.  Table #2 is the child of #1 and the parent of both tables #3 and #4.

   

Child

The TableNum of the detail in the relationship being defined.  Note that Mercury doesn’t allow Table #1 to be a child table; it can only be a parent.

Key Fields

One or more fields that the parent and child have in common.  These fields are used to match each Parent record with its associated child record(s).  The key fields must be named the same in both tables.

When you specify the Parent and Child table #s, Mercury examines the field names that are common and presents them to you in the Key Fields drop-down list.  By default, all the fields are used as Key (linking) fields, but you can choose to omit some.  If you have any doubts about the key fields you need, check with your IT developer or DBA.


In the original example, the Key Fields dropdown for the “Applications” relation looks like this:



Tables 2 and 3 have four field names in common, and they’re all used to link a Child in table #3 to its Parent in table #2.

Summary

The Summary tab allows you to specify which fields you want to be summarized, and how.  It is available for Table-reports only.  (Note: users can also summarize fields on the Canvas.  See the article about Table Reports for more information.)

Add Summary


  1. Click  Add Summary  located above the Summary grid.  A new row will appear in the grid highlighted in blue with icons identifying all the required fields.
  2. Select the Field cell and use the drop down to pick the data field to be summarized.
  3. Select The Summary cell and use the drop down to choose a function.
  4. Select Summary Level and choose between the Total (Report) or Group levels to apply the summarization.
  5. Select Display Format and enter the presentation format for data.  See numeric formatting in the table below for more information.
  6. The Display Column will automatically default to the same value used for the Field detail.  Select the cell to pick an alternative if necessary.
  7. Select Display Area and choose between Header and Footer to position the Summary in the report layout.

Refer to the table below for more information on each column.

Column Name

Description

Field

The field on which to apply the summary function.

Level

The Table-Num to which the Field belongs.  (read-only)

Summary Type

The summary function to apply.

  • Sum - Totals the values in the Field.  Used for numeric Fields only.

  • Min - The minimum value in the Field.

  • Max - Finds the maximum value in the Field.

  • Count - Gives the total number of records in the Field.

  • Average - Totals all the underlying values in the Field area and divides it by the quantity of values.  Used for numeric Fields only.

  • None - No summary.  (This is useful for testing; you can deactivate the summary without deleting it from the list.)

Summary Level

The scope of the summary.

  • Total - Includes all the items in the table (always appears in the report footer)

  • Group - Summarizes data within each Group.

Display Format

The presentation format of the data in the summary.

Here are a few examples.

  • n0 - numeric with 000s separators, no decimal places.

  • n2 - numeric with 000s separators, 2 decimal places (common for $ amounts)

  • f0 - numeric without 000s separators, no decimal places

See this Microsoft article for information on standard format strings.

Display Column

The column under which the summary is displayed.  Normally you want this to be the same as the summary-column itself, but it doesn't have to be.

Back Color

The column under which the summary is displayed. 

Display Area

The options for placement.  Applies to Group summaries only.

  • Header - Displays the summary at the beginning of each Group section.

  • Footer - Displays summary at the end of each Group section.


Delete Summary

  1. Select the summary by clicking anywhere in the summary row.
  2. Click the  Delete Summary  button located above the Summary grid.
  3. Save  your changes.

Actions

An Action provides a way, from within a Mercury report, to “do something” based on a particular data row or cell.  Actions are available only in Table-reports.

An Action might consist of opening an external document attached to your data – say, a spec sheet on one of your products, or a sales invoice.  Or an Action might involve invoking an underlying application and opening one of its forms – perhaps a customer card or an accounting transaction.

Because systems store information in diverse ways, actions are related to the underlying database involved.  For example, one system might store external documents on a network file system, another in the cloud, and a third might embed document content in the database itself.

Typically, special programming is needed to support a given action for a particular database.  Therefore, the set of available actions is pre-defined in Mercury.

Actions are currently available for the following system.  See the reference for detailed information.

Microsoft Dynamics GP

See Microsoft Dynamics GP Actions


If Mercury does not yet offer any actions relating to your database and/or underlying application, please contact us.  We'll be happy to discuss implementing the Actions you need.

Users

The Users tab controls settings for report access and the default report-list background-color.  Note: Users can change their own report-list colors through the main Mercury window.



The Users tab is available for all report types.

Refer to the table below for more information on each of the columns.  All items are read-only except Color and the Allow checkbox.

Column Name

Description

User ID

The Mercury internal ID associated with the user (read only).

User Name

The user's name.  (read only)

Login Name

The Windows login name associated with the user (read only).

Group ID

The Mercury user-group to which the user belongs.  (See the Admin Tools article, Users for more information on users and user-groups.)

Color

The default background color in the report-list shown to the user.

Allow

When checked, the user can run the report.  Otherwise, the report does not even appear in the user's main Mercury report list.  You must also designate yours as a User to view the report in the main list.

If the report itself is inactive, then the report is not available to any users, regardless of the setting in this tab. See the description of a report's "Active" setting above for more information.


    • Related Articles

    • Introduction to Table Reports

      A Table report presents data in a simple tabular layout, similar to an Excel worksheet. Unlike Pivot reports, Table reports do not allow you to have multiple levels of row or column breakouts, or exchange rows and columns. But you can still do a lot: ...

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

    • Printing Pivot Reports

      You can print a Mercury report in two different ways: Print-Preview - displays the report in a "Print Preview" window, so that you can see what it looks like and change your page layout before printing. Quick-Print  - immediately sends the report to ...

    • Printing Table Reports

      You can print a Mercury report in two different ways: Print-Preview - displays the report in a "Print Preview" window, so that you can see what it looks like and change your page layout before printing. Quick-Print - immediately sends the report to ...