Setting up reports in Creatio

Introduction

The [Report setup] section enables users to create reports using Creatio tools and configure these reports via the FastReport designer. You can learn more about the section and find the interface description (Fig. 1) in the "The "Report setup" section” article.

Fig. 1. – The report setup page

General algorithm of creating a report:

1. Install the FastReport Designer (you only perform it once).
2. Create a report in the “Report setup” section.
3. Specify the data sources for the report.
4. Create a report data provider that will implement processing of data logic.
5. Download the file with data sources and set it up in the FastReport Designer.
6. Upload the configured report template to Creatio.

Installing the FastReport Designer

To install the FastReport Designer, use the following link and download the zip archive.

Creating a new report

To create a new report:

1. Open the System Designer by clicking . In the [System setup] block, click the [Report setup] link.
2. Click [New report].
3. In the parameter setup area (Fig. 1, 2) specify the report title, the section for the report, the display parameters.

Specifying the report data sources

In the [Specify data sources for the report] block of the working area (Fig. 1, 4), specify the list of objects, their columns and connections that will be used to receive data. Specify the localizable strings if needed. Use the JSON format. Example of providing a data source:

{
    // Name of the data provider class.
    "ProviderName": "YourProviderName",
    // Table structure for the report template.
    "Schemas": {
        // Name of the database table or virtual tables, whose columns need to be added to the report.
        "TableName1": {
            // Name of the column that needs to be added to the report.
            "ColumnName1": {
                // Column data type.
                "DataValueType": DataValueType1
            },
            "ColumnName2": {
                "DataValueType": DataValueType2
            }
        },
        "TableName2": {
            "ColumnName1": {
                "DataValueType": DataValueType1
            },
            "ColumnName2": {
                "DataValueType": DataValueType2
            }
        },
        // Report localizable strings.
        "LocalizableStrings": {
            // Name of the report localizable string.
            "LocalizableString1": {
                // Data type of the localizable string.
                "DataValueType": 1
            },
            "LocalizableString2": {
                "DataValueType": 1
            }
        }
    }
}

Click [Apply] in the toolbar (Fig. 1, 1) to save the data.

Creating a report data provider

The report data provider is a custom class written in C#. To create the provider:

1. In the custom development package, create a [Source code] type schema.
2. Create a service class in the schema source code. Use the Terrasoft.Configuration namespace or any of its embedded namespaces. Mark the class with the [DefaultBinding] attribute containing the necessary parameters. The service class must be the inheritor of Terrasoft.Configuration.Reporting.FastReport.IFastReportDataSourceDataProvider.
3. Add the GetLocalizableStrings(UserConnection) method implementation to the class. The method implements localization of the report fields.
4. Add the ExtractFilterFromParameters(UserConnection, Guid, IReadOnlyDictionary) method implementation to the class. This method is responsible for adding the interface filters.
5. Add the GetData(UserConnection, IReadOnlyDictionary) method implementation to the class. This method must return a Task<ReportDataDictionary> type value. Describe the logic of receiving the report data in the method.
6. Publish the source code schema.

Example of implementing the report data processing logic:

namespace Terrasoft.Configuration
{
    using System.Collections.Generic;
    using System.Threading.Tasks;
    using Terrasoft.Configuration.Reporting.FastReport;
    using Terrasoft.Core;
    using Terrasoft.Core.Factories;
    
    // Name of the data provider class for the report, whose logic needs to be implemented.
    [DefaultBinding(typeof(IFastReportDataSourceDataProvider), Name = "YourProviderName")]
    public class YourProviderName : IFastReportDataSourceDataProvider
    {
        // The code for implementing the logic of getting data for the report.
        
        // Localization of the report strings.
        private IEnumerable<IReadOnlyDictionary<string, object>> GetLocalizableStrings(UserConnection userConnection) {
            var localizableStrings = _localizableStringNames.ToDictionary(
                x => x,
                x => (object)(new LocalizableString(userConnection.ResourceStorage, _resourceManagerName, $"LocalizableStrings.{x}.Value")).Value);
            return new[] { localizableStrings };
        }
        
        // Adding the interface filters.
        private IEntitySchemaQueryFilterItem ExtractFilterFromParameters(UserConnection userConnection, Guid entitySchemaUId,
                IReadOnlyDictionary<string, object> parameters) {
            var managerItem = userConnection.EntitySchemaManager.GetItemByUId(entitySchemaUId);
            return parameters.ExtractEsqFilterFromReportParameters(userConnection, managerItem.Name) ?? throw new Exception();
        }
        
        // Adding data to the report.
        public Task<ReportDataDictionary> GetData(UserConnection userConnection, IReadOnlyDictionary<string, object> parameters) {            
            
        }

    }
}

Setting up templates in the FastReport Designer

Download the file with data sources. Click the [Download file] button in the [Download file with data sources to design a report in the FastReport Designer] block (Fig. 1, 4) of the working area. The file must have the *.frx extension.

Double click the downloaded file to open it in FastReport and configure the template layout. Learn more about configuring the template in the FastReport documentation.

Uploading the configured template to Creatio

Click the [Upload template] button in the [Import a file with the report template] block (Fig. 1, 4) of the working area to upload the prepared template to Creatio. After you upload the template, you can generate a report in the section dashboard view or on a record page. You can specify this in the parameter setup area (Fig. 1, 2). The generated report will be saved in the pdf format.

Case description

Create a "Contact Data” base report that would display the following information about the contacts:

• [Full name];
• [Birthday];
• [Gender];
• [Account].

Source code

You can download the package with case implementation using the following link.

Case implementation algorithm

1. Set up the report display parameters

Specify the following values (Fig. 2) for the created report in the parameter setup area (Fig. 1, 2):

• [Report title] – "Contact Data”;
• [Section] – "Contacts”;
• [Show in section] – select the checkbox;
• [Show in section] – select the checkbox;
• [Show in the section analytics view]) – select the checkbox;
• [Filter page] – SimpleReportFilterPage.

Fig. 2. – Setting up the report display parameters

2. Specify the data sources

In the [Specify data sources for the report] block of the working area (Fig. 1, 4), add the code below:

{
    // Name of the data provider class.
    "ProviderName": "ContactDataProvider",
    "Schemas": {
        "ContactData": {
            "Full name": {"DataValueType": 1},
            "Birthday": {"DataValueType": 1},
            "Gender": {"DataValueType": 1},
            "Account": {"DataValueType": 1}
        },
        // Added for localization.
        "LocalizableStrings": {
            "ReportTitle": {"DataValueType": 1},
            "FullNameLabel": {"DataValueType": 1},
            "BirthdayLabel": {"DataValueType": 1},
            "GenderLabel": {"DataValueType": 1},
            "AccountLabel": {"DataValueType": 1}
        }
    }
}

Click [Apply] to save and apply the changes (Fig. 1, 1).

3. Create the report data provider

Go to the [Advanced settings] section -> [Configuration] -> Custom package -> the [Schemas] tab. Click [Add] -> [Source code]. Learn more about creating a schema of the [Source Code] type in the “Creating the [Source code] schema” article.

Specify the following parameters for the created object schema (Fig. 3):

• [Title] – "Contact Data";
• [Name] – "UsrContactDataSourceCode".

Fig. 3. – Setting up the [Source Code] type object schema

The complete source code of the module is available below:

namespace Terrasoft.Configuration
{
    using System;
    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using System.Linq;
    using System.Threading.Tasks;
    using Terrasoft.Common;
    using Terrasoft.Configuration.Reporting.FastReport;
    using Terrasoft.Core;
    using Terrasoft.Core.Entities;
    using Terrasoft.Core.Factories;
    using Terrasoft.Nui.ServiceModel.Extensions;
    using EntitySchema = Terrasoft.Core.Entities.EntitySchema;
    using EntitySchemaColumn = Terrasoft.Core.Entities.EntitySchemaColumn;

    // Name of the data provider class for the report, whose logic needs to be implemented.
    [DefaultBinding(typeof(IFastReportDataSourceDataProvider), Name = "ContactDataProvider")]
    public class ContactDataProvider : IFastReportDataSourceDataProvider
    {

        private Guid _entitySchemaUId = new Guid("16BE3651-8FE2-4159-8DD0-A803D4683DD3");
        // Name of the source code schema.
        private readonly string _resourceManagerName = "UsrContactDataSourceCode";
        private readonly string[] _localizableStringNames = new[] {
            "ReportTitle",
            "FullNameLabel",
            "BirthdayLabel",
            "GenderLabel",
            "AccountLabel"
        };

        // Populating the report columns.
        private IEnumerable<IReadOnlyDictionary<string, object>> GetContactData(
            UserConnection userConnection,
            Guid entitySchemaUId,
            IEntitySchemaQueryFilterItem filter) {
                // Getting the object schema.
                var entitySchema = userConnection.EntitySchemaManager.GetInstanceByUId(entitySchemaUId);
                // Creating the object of the EntitySchemaQuery class.
                EntitySchemaQuery query = new EntitySchemaQuery(entitySchema);
                // Adding columns to query.
                query.AddColumn("Name");
                query.AddColumn("BirthDate");
                var gender = query.AddColumn("Gender.Name");
                var account = query.AddColumn("Account.Name");
                // Adding the created filter.
                query.Filters.Add(filter);
                // Getting the collection of contacts.
                var contacts = query.GetEntityCollection(userConnection);
                var contactsCollection = new Collection<Dictionary<string, object>>();
                // Populating the report columns.
                foreach (var entity in contacts)
                {
                    contactsCollection.Add(new Dictionary<string, object> {
                        ["Full name"] = entity.GetTypedColumnValue<string>("Name"),
                        ["Birthday"] = entity.GetTypedColumnValue<string>("BirthDate"),
                        ["Gender"] = entity.GetTypedColumnValue<string>(gender.Name),
                        ["Account"] = entity.GetTypedColumnValue<string>(account.Name)
                    });
                }
                return contactsCollection;
        }
        
        // Localization of the report title.
        private IEnumerable<IReadOnlyDictionary<string, object>> GetLocalizableStrings(UserConnection userConnection) {
            var localizableStrings = _localizableStringNames.ToDictionary(
                x => x,
                x => (object)(new LocalizableString(userConnection.ResourceStorage, _resourceManagerName, $"LocalizableStrings.{x}.Value")).Value);
            return new[] { localizableStrings };
        }
        
        // Adding the interface filters.
        private IEntitySchemaQueryFilterItem ExtractFilterFromParameters(UserConnection userConnection, Guid entitySchemaUId,
                IReadOnlyDictionary<string, object> parameters) {
            var managerItem = userConnection.EntitySchemaManager.GetItemByUId(entitySchemaUId);
            return parameters.ExtractEsqFilterFromReportParameters(userConnection, managerItem.Name) ?? throw new Exception();
        } 

        // Getting data.
        public Task<ReportDataDictionary> GetData(UserConnection userConnection, IReadOnlyDictionary<string, object> parameters) {
            var filter = ExtractFilterFromParameters(userConnection, _entitySchemaUId, parameters);
            var result = new ReportDataDictionary {
                // Populating the report columns.
                ["ContactData"] = GetContactData(userConnection, _entitySchemaUId, filter),
                ["LocalizableStrings"] = GetLocalizableStrings(userConnection)
            };
            return Task.FromResult(result);
        }
    }
}

Populate the localizable strings of the report with the following values (table 1):

Table 1. Setting up the localizable strings

Learn more about working with localizable strings in the "Source code designer” article.

Publish the schema.

4. Download the template and set up its layout in FastReport

Download ContactData.frx. Click [Download file] in the [Download file with data sources to design a report in the FastReport Designer] block (Fig. 1, 4) of the working area.

To open the template in the Report Designer:

1. Run the Terrasoft.Reporting.FastReport.Designer.exe file from the zip archive (Fig. 4) and open the FastReport designer.

   Fig. 4. – The FastReport Designer file

   

2. Click [Open...] in the window that pops up (Fig. 5).

   Fig. 5. – The [Open...] button

   
   You can also open the report template from the [File] menu -> [Open...] or by pressing [Ctrl+O] key combination.

   Fig. 6. – The [File] menu with the [Open...] option

   

3. Navigate to the folder containing the downloaded report (usually, it is the “Downloads” folder), select the file with the template and click [Open] (Fig. 7).

   Fig. 7. – Opening the report template in FastReport

   

Set up the template layout (Fig. 8).

Fig. 8. – Setting up template layout in the FastReport Designer

5. Upload the configured report template to Creatio

To upload the ContactData.frx, file, click [Upload template] in the [Import a file with the report template] block of the section working area (Fig. 1, 4). Confirm that the template has been uploaded successfully.

As a result, the "Contact Data” report will be available on the contact page under the [Print] (Fig. 9).

Fig. 9. – The “Contact Data” report displayed on the record page of the [Contacts] section

You can also find the report on the [Contacts] sectin dashboard view, under the [Reports] button (Fig. 10).

Fig. 10. – The “Contact Data” report displayed in the [Contacts] section dashboard view

The report is generated as per the selected [Filtered records in list] option in the filtering page (Fig. 11). To upload the report, click [Create report]. To close the filtering page and cancel generating the report, click [Cancel].

Fig. 11. – The filtering page of the "Contact Data” report

The report looks as follows:

Fig. 12. – Example of the “Contact Data” report

Report by several records in a section

To include information from several section records to your report (Fig. 13):

1. Open the needed section.
2. Apply filters if needed.
3. Click [Actions] —> [Select multiple records].
4. Select the needed report in the [Print] button drop-down list.

Fig. 13. – Getting the “Contact Data” report from several records in the [Contacts] section

The report looks as follows (Fig. 14):

Fig. 14. – Example of the "Contact Data” report based on several section records

Multilingual interface elements in reports

The [Translations] section in the System Designer enables setting the values of interface elements for a multilingual report. To find the previously localized strings of the report, use the Configuration key (e.g., Configuration:UsrContactDataSourceCode). You can find a report field using the following key: Configuration:SchemaName:FieldName.Value (e.g., Configuration:UsrContactDataSourceCode:LocalizableStrings.ReportTitle.Value).

If the [Show on the section list review]) and [Show on the section record page] checkboxes are selected, translate the report title. You can find the report title using the following key: Configuration:SchemaName:Caption (e.g., Configuration:UsrContactDataSourceCode:Caption). If the [Show in the section analytics view] checkbox (Fig. 1, 2) is selected, translate the report title. You can find the report title using the following key: Data:SysModuleAnalyticsReport.Caption:FieldIdentificator (e.g., Data:SysModuleAnalyticsReport.Caption:d52e8b78-772b-77ee-3394-bdb3616d859a).

Learn more about working with the [Translarions] section in the "How to translate the interface and system elements in Creatio” article.

Transferring the package to another development environment

To transfer the package with the report to another environment, go to the [Configuration] section -> the [Data] tab and bind the data of the following elements:

• FastReportDataSource_ReportName – the source of the report data. To bind it, use the source Id from the [dbo.FastReportDataSource] database table.
• FastReportTemplate_ReportName – the report template. To bind it, use the template Id from the [dbo.FastReportTemplate] database table.
• SysModuleReport_ReportName – the report. To bind it, use the report Id from the [dbo.SysModuleReport] database table.