Creating a New Data Set

M-Files Reporting Data Services enable data exporting from M-Files to external databases (SQL Server). The administrator can specify manually which data will be exported, or export data on a scheduled basis from M-Files for reporting or other purposes. The M-Files Reporting Data Services functionality is installed in conjunction with M-Files Server. Once you have exported a data set, you can use it to generate a report.

Start by specifying a data set that you want to export for reporting. Do the following steps to create a new data set:

  1. Open M-Files Admin.
  2. In the left-side tree view, expand a connection to M-Files Server.
  3. In the left-side tree view, expand Document Vaults, and then expand a vault.
  4. Still in the left-side tree view, select Reporting and Data Export and then click New Data Set... on the task area.
    Result:The Data Set Properties dialog is opened.
  5. In the Name field, enter a name for the new data set.
  6. In the Data to export section, click Add Objects... to select the objects to be exported.
    Result:The Add Objects to Data Set dialog is opened.
  7. Use the Objects to export drop-down menu to select the objects to be exported on the basis of the object type.
    You can click Filter Objects... to refine the selected objects by specifying property conditions that the objects must meet to be exported. For more information on filtering objects by properties, see Property-Based Conditions.
  8. Optional: Select the object that you just added to the Data to export section and click Add Property... to add for the selected object type a property that you want to be exported.
    1. Use the Property to export drop-down menu to select the property to be exported.
    2. Go to the History and Old Values tab.
      On the History and Old Values tab you can define whether you want to export the change history and previous values of the selected property.
      Result:The History and Old Values tab is opened.
    3. Check the Export the change history of this property check box if you want to export the change history of the selected property.
      The change history adds an entry of each change in the property value to the data set. This makes it possible to answer questions such as "What was the total value of deals closed in August?" by exporting the change history of the property value Closed and pinpointing the objects for which the value of said property changed from No to Yes during August.
    4. Check the Export the old values of this property, using sampling check box if you want to export previous values of the selected property and use the Sample at drop-down menu to define the frequency of the sampling (daily, weekly, monthly, or yearly).
    5. Select either Full history, Fixed-length period, or Starting from date option to define the history length for the change history and old values of the selected property.
      If the frequency of sampling is high (for instance daily), it is recommended to restrict the length of the history period to avoid expanding the database unnecessarily and to increase the speed of the export function.
    6. Click OK to close the Property to Export dialog.
  9. In the OLE DB connection string field, enter the connection string for connecting to the target database or click Define... to define the connection string.

    If you are using Microsoft SQL Server as the target database, it is advised to use Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) as the data provider, and to use either Simple or Bulk-logged as the Recovery model setting in the target database. This will significantly improve the speed and performance of exporting data sets.

    For more information on database connection strings, see Connections to External Databases for Object Types.

  10. Go to the Advanced tab.
    Result:The Advanced tab is opened.
  11. In the Use the identity of the following user when reading data drop-down menu, select the user whose identity you want to use for transferring data from M-Files to the reporting services.
    The most suitable user is a "regular" user without any extended rights. If you use, for example, your own user identity that has a system administrator role, data that you do not want to expose to all users could end up in the reports displayed by the client software.
    If the metadata structure of the vault is translated into multiple languages, the metadata language of the exported data set depends on the vault language settings of the user whose identity is used for reading data.
    If you have the Electronic Signatures and Advanced Logging module in use, you must select (M-Files Server) as the user.
  12. Optional: Check the Export data on a scheduled basis option check box and click Schedule... if you want to export this data set on a scheduled basis.
    It is recommended to schedule the data export to be performed once a day/week/month. The reports are updated at the same time. If you do not select scheduling, the data will not be updated after export. You can also create a separate update link in the reporting services so that the user can update the report in the client software whenever desired. For more information, write to our customer support at [email protected].
  13. Optional: On the Advanced tab, define an alias for the data set.
    When you have automatic aliases in use and you write a name on the General tab, the Aliases field on the Advanced tab is automatically filled in. The alias has the format <predefined prefix>.<name>. Define automatic aliases for your vault in the configurations editor.
  14. Optional: Click Export Now to export the data set right away.
    You can click Status of Exporting... to view the status of the exporting process when the exporting is in progress.
  15. Click OK to finish creating the new data set.
The data set that you have just defined is added to the Reporting and Data Export list. The data set is exported either automatically on a scheduled basis or manually, depending on the settings that you have provided.
Now that you have created a data set, you can use it to create a report (see Creation of Reports and the Required Software) and then create a report object in M-Files to read the report (see Creating a Report Object for a Report).