Reporting - getting started¶

Why use MFSQL Reporting?¶

It is simply to setup. It also provides flexibility to do very involved and complex reporting.

Changes in M-Files is refreshed incrementally, providing near real time data access

It includes access to the M-Files event log. Report on event logs combined with other M-Files data

It allows for interactive reporting. Use interject with MFSQL Connector to view data in excel and perform updates to M-Files directly from excel

Review the deployment plan for a step by step guide for deployment.

Read the whitepaper to get an overview the MFSQL Connector for reporting.

Best practices and things to think about¶

To get on the spot data or do a few reports, simply follow the step in Reporting : Quick start

For more advanced reporting and to explore the full capabilities of the Connector:

Adding class tables¶

spMFSetup_Reporting can we used at any time to create additional class tables. Note that this procedure will perform a number of operations and will take some time.

Use spMFCreateTable to create additional class tables without going through all the sub processes of the setup reporting routine. To create multiple class tables in one go use spMFCreateAllMFTables

Keep class tables up to date¶

The procedure spMFUpdateTable is useful in the initial stages of exploring the tables, unless the datasets are very large. In the case of larger than 30 000 objects in a table, we recommend to use spMFUpdateTableinBatches.

When scheduling regular updates for an individual class, use spMFUpdateMFilesToMFSQL. This can be used for both small and large tables are able to perform both incremental and full updates. It uses a process to determine only versions that have changed and will do the processing in batches. This allows for updates to run over for multiple hours in the case of large tables.

Use spMFUpdateAllncludedInAppTables in an agent to schedule the updating of all tables included in the app

DECLARE @ProcessBatch_ID INT;

EXEC dbo.spMFUpdateAllncludedInAppTables @UpdateMethod = 1,
    @RemoveDeleted = 1,
    @IsIncremental = 0,
    @ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
    @Debug = 0

Check the current status and result of updates with spMFClassTableStats

EXEC dbo.spMFClassTableStats
    @IncludeOutput = 0

Scheduling the updates¶

To schedule updates in predetermined intervals for all class tables use spMFUpdateAllncludedInAppTables. For more details on Automated update of records from M-Files

Using a separate reporting database¶

It is a good practice to use a separate database for reporting. This implies that the database with the procedures, views and tables designed to make the data reporting data available to the report designer such as PowerBI, Crystal or Interject, is separate from the MFSQL engine. It will simplify the access of the data for users, and provide an extra security layer for the core data in MFSQL Connector.

building complex views¶

MFSQL Connector tables are partly denormalised to simply the access to lables for lookups without having the resort to complex joins. Multi lookup values are listed as a delimited string. However, sometimes it may be required to refine the data further, and this would be accomplished with one or more of the following techniques:

To access data on a sub-object (such as the region of a customer) the Column with _ID suffix is joined to the objid column on the sub-object class table

Use /functions/fnMParseDelimitedstring to split the delimited string into single values

Use the listitem column from /functions/fnMParseDelimitedstring to join to the objid when the sub-object is a multi-select lookup.