Custom Tables

Custom Tables

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 various charts-of-accounts are different for different companies.  It would be nice to have a “map” that translates everything into a consistent scheme. 


Or perhaps your database tracks the status of some operation (perhaps manufacturing or assembly), but uses only status codes (maybe just numbers like 1,2,3 or cryptic codes like “IP”,NO”, and “PEND”) – and you would like friendlier descriptions to be available. 


Mercury lets you maintain one or more “Custom Tables” for purposes like this.  You can maintain the data yourself and use the table(s) in your own queries. 


Mercury doesn’t offer a way for the user to create the custom-table. That's done externally. This is because we don't want Mercury to have that kind of access to your company’s database. Mercury cannot create or update anything – EXCEPT for data in a custom table. You should contact your database administrator if you would want to create a new custom table to appear inside Mercury.  Once created, you can maintain the data yourself.   
Your database administrator may wish to contact support@mercurybi.com for more information. 
There ARE tables in Mercury that describe the structure of the custom-table, so Mercury knows how to present it to the user -- column-headers and so forth. But Mercury doesn't offer you a way to populate these "description" tables either - your database administrator must do that at the same time the data-table itself is created. Populating the description tables is discussed at the end of the article - see the section below , titled For DBAs : Creating custom tables.
To access your custom tables: 

  • Select Tools > Show Custom Tables at the upper right of the main Mercury window.

  •  

  • A window similar to the one below is displayed.Click on the table that you want to open.

If you don't see all of your tables, click on on the corner of the list. The list will expand to show you the rest of the custom tables stored within Mercury.
All the records in the selected custom table will be shown. It will also unlock additional operations that you could do on the table. 
Adding New Records 
Click on Add on the Home tab of the ribbon on the custom table window.          
 

  • A new blank row will appear at the end of the grid indicated by once you start entering value in the new row. 

The number of columns will vary according to the definition of your table.

  • Click Save on the Home tab of the ribbon to save the newly entered record in your table.

  •  

Editing existing records 

Double click on the cell that you want to edit.                                                            

  • After making changes to the particular column, Click Save on the Home tab of the ribbon to save all your changes. 

Deleting records 

Click on the row that you want to delete.                                                               

  •  Click Delete on the Home tab of the ribbon. At the confirmation prompt, click Yes to delete the record; No to cancel and retain the group.

  •  

Resetting your table (abandoning unsaved changes) 


 

Click Reset on the Home tab of the ribbon to undo all unsaved changes. At the confirmation prompt, click Yes to undo the changes; No to retain the changes and keep working (your changes still have not been saved). 

Printing the records in the table: 

Refer to Printing Report for more information on customizing the print.


Quick printing the records: 

Click Quick Print button on the Home tab of the ribbon.                                     Select the printer on which your file will eventually be printed.

Click OK.

Exporting the data

Refer to Exporting Report Results for more information on exporting your data. The steps to export your customs are similar to those used to export report results. 


Importing records into your custom table 


 

Click Import on the Home tab of the ribbon. 
Choose the file that you want to import into your table.  (The supported file types may vary according to your version of Mercury.) 
At the confirmation prompt, click Yes to import the records; No to cancel import. 

Customizing the layout Using the Ribbon 

The features available for working with your custom table are similar to those available for working with Table-report results.  For details on any particular features, see The Table Report Ribbon. 


 

Auto Width - Automatically maintains column-width to fit everything in the window without horizontal scrolling.
Best Fit - Sizes all column widths to fit their contents. 
Group Panel - Exposes a panel that allows you to drag one or more column headers to group your report by. See Summarizing and Grouping for more information. 
Search Panel - Shows a search panel on the top right of the grid that allows searching for data within the grid. 
Filter Row - Click on Filter Row to expose a row at the start of the grid indicated by
  • Double click on the cell beneath the column that you would like to filter and enter the value that you would like to search for in the column.


  • All the rows with IRH in the CompanyDB column gets displayed. 


Creating Custom Tables - For DBA's
 
In order to use custom tables: 
You create the custom table(s) yourself.  You can create the table in any database you wish, assuming the Mercury database Connection can get to it.   See the remarks for the CustomTables.Connection field below. 

You add records to two “description” tables that reside in Mercury’s internal report definition database.  These two tables describe your custom-table so that Mercury knows how to present it to users.  (These tables already exist as part of your Mercury installation – but they are empty until you populate them.) 

Description Tables: CustomTables and CustomTableFields 
The two description tables are CustomTables and CustomTableFields.  The database schema in which they reside depends on whether you’re working with a multi-user version of Mercury, or the single-user/demo version. 
To determine the Mercury you’re using, click the “i”nfo icon in the top right of the screen: 
 

Multi-User Mercury 
A multi-user report definition database shows a server that’s in Microsoft’s Azure cloud.  You’ll need a query tool or data editor that can work with SQL Server databases. 
The SQL administrator login name for the database is shown in the info-box.  The password was provided when your report-definition was initially created.  If you have lost your password, please contact us at support@mercurybi.com -- we’ll need your license name, plus the server name shown in the info-box. 

 
The description tables are in the “dash” schema: dash.CustomTables and dash.CustomTableFields. 
 
Single-User / Demo Mercury 
In this case you’ll see a report-definition database in your local AppData folder.  
SQLite is the database used for the single-user Mercury, and you’ll need a query tool or editor that can work with SQLite databases. 
  
In this case the description tables are CustomTables and CustomTableFields (no schema name needed). 

Populating the Description Tables 
When you do your inserts (or updates), remember to quality the table-names with “dash.” if necessary. 
No fields can be Null, but you can use 0s for numerics and empty strings for character fields. 

CustomTables – insert one row for each custom data table you want. 

Field 

Datatype 

Required? 

Remark 

TableID 

int 

Y 

must be unique (not auto-assigned) 

SchemaName 

nvarchar 

Y 

schema where your custom table resides (can be blank if this doesn’t apply) 

TableName 

nvarchar 

Y 

the name of your custom table 

Connection 

nvarchar 

Y 

the Connection describing the data source (e.g. server and database) housing your custom table.  The credentials used by this connection must have appropriate access permission to the custom table (SELECT, INSERT, UPDATE, DELETE). 

This Connection must exist. 

See Admin > Connections for information on creating and using Connections. 

Inactive 

bit 

Y 

1 = inactive; 0 = active (controls whether Mercury presents the table to the user 

Created 

smalldatetime 

N 

date-created 

Modified 

smalldatetime 

N 

date-modified 

RowID 

int 

X 

auto-assigned -- don't populate this manually 

 
CustomTableFields – insert one row for each column in your custom data table. 

Field 

Datatype 

Required? 

Remark 

TableID 

int 

Y 

Foreign key referencing CustomTables 

FieldName 

nvarchar 

Y 

Column name in your custom data table 

Caption 

nvarchar 

N 

Column name shown to user in the UI 

DataType 

nvarchar 

N 

.NET-compatible data type 

See https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/builtin-types/built-in-types 

FieldLength 

int 

Y 

(for numerics, use 0.  For strings, use the max # of characters) 

VisibleIndex 

smallint 

Y 

Order in which field is presented to the user in the UI 

FormatString 

nvarchar 

N 

.NET-compatible format string for displaying numeric fields. 

EditMask 

nvarchar 

N 

Edit mask for input/edit. 

See https://docs.devexpress.com/WPF/6945/controls-and-libraries/data-editors/common-features/masked-input 

(Mercury uses DeveloperExpress components much of its UI.) 

UseEditMaskForDisplay 

bit 

N 

1=apply edit-mask when displaying data; 0=apply edit mask only during data entry 

Editor 

nvarchar 

N 

optional – a Mercury Lookup-list to associate with the field. 

See Tools > Lookups for details. 

IsKey 

bit 

Y 

Is this a key column in the table? 1=yes; 0=no 

AllowNulls 

bit 

Y 

Are nulls allowed? 1=yes; 0=no 

DefaultValue 

nvarchar 

N 

Field's default value 

Created 

smalldatetime 

N 

date this record was created 

Modified 

smalldatetime 

N 

date this record was modified 

RowID 

int 

X 

auto-assigned -- don't populate this manually 

  
Example DDL: Create custom table and populate description tables 
These SQL statements will create a custom data-table, and then add records to the two Mercury description tables.   
In this example, a database group called RPTSGRP is granted permission to work with the custom table.  The Connection credential used to access the custom table is assumed to be a member of this group. 
The data table is created on one of your servers – production or test, wherever you wish. 
This example assumes that you have a multi-company enterprise, with each company in its own database and its own accounting structure.  You’re using a custom data table to create a consistent structure for reporting and analysis purposes. 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
CREATE TABLE dbo.Custom_HarmonizeGPAccts( 
[CompanyDB] [varchar](40) NOT NULL, 
[AcctNo] [varchar](40) NOT NULL, 
[AcctName] [varchar](100) NOT NULL default(''), 
[HarmonizedAcctNo] [varchar](40) NOT null default(''), 
[HarmonizedAcctName] [varchar](100) NOT null default(''), 
 CONSTRAINT [PK_Custom_HarmonizeGPAccts] PRIMARY KEY CLUSTERED  
( 
[CompanyDB],[AcctNo] ASC 
) 
)  
GO 
GRANT SELECT,UPDATE,INSERT,DELETE ON dbo.Custom_HarmonizeGPAccts TO RPTSGRP 
GO 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
The corresponding description-table records are populated in Mercury’s report definition database.  See above for information on where that database resides and how to access it. 
Here’s the “Table” descriptor.  Only required fields are populated in this example. 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
insert dash.CustomTables 
( 
    TableID 
  , SchemaName 
  , TableName 
  , Connection 
  , Inactive 
) 
values 
(  101 -- TableID – int 
  , N'dbo'  -- SchemaName - nvarchar(128) 
  , N'Custom_HarmonizeGPAccts'            -- TableName - nvarchar(128) 
  , N'GP1'  -- Connection name 
  , 0 -- Inactive - bit 
    ) 
 
Insert a descriptor Field record for each field in your custom table.  Consistent with the structure of the custom data table, the Company-Database and Account-number are key fields. 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
insert dash.CustomTableFields 
( 
TableID 
, FieldName 
, Caption 
, DataType 
, FieldLength 
, VisibleIndex 
, UseEditMaskForDisplay 
, IsKey 
, AllowNulls 
, DefaultValue 
) 
values 
 (101,'CompanyDB',         'CompanyDB',          'System.String',40, 1, 0, 1, 0, '')  
,(101,'AcctNo',            'CoAcctNo',           'System.String',40, 2, 0, 1, 0, '')  
,(101,'AcctName',          'CoAcctName',         'System.String',100,3, 0, 0, 0, '')  
,(101,'HarmonizedAcctNo',  'HarmonizedAcctNo',   'System.String',40, 4, 0, 0, 0, '')  
,(101,'HarmonizedAcctName','HarmonizedAcctName', 'System.String',100,5, 0, 0, 0, '')  
 
GO 
  

    • Related Articles

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

    • Introduction to Pivot Reports

      Pito Salas, the father of pivot tables, noted that spreadsheets have patterns of data and rightly concluded that a tool that could help users recognize these patterns would be tremendously useful. His efforts from 1986 onwards culminated in 1991 with ...

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

    • Data Retrieval Guidelines

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

    • Microsoft Dynamics GP Actions

      By using Mercury "Actions" in a report based on a Dynamics GP database, you can: Open an underlying GP form associated with the data (a drillback) Open an attached document. Each of these features is discussed in detail below. For a general overview ...