How to create macros for a custom report in Word

Introduction

The [Report setup] section enables users to create MS Word reports using Creatio tools and configure them using the Creatio MS Word Report Designer plug-in. Learn more about creating and setting up MS Word reports in the "Set up MS Word reports" article.

The general procedure of creating an MS Word report using custom macros is as follows:

1. Install the MS Word Report Designer plug-in. This is a one-time procedure. Learn more in the "Installing Creatio plug-in for MS Word" article.
2. Add a new report record in the [Report setup] section.
3. Set up the report display parameters
4. Implement custom macros.
5. Set up the report data fields and tables.
6. Set up the report template layout in the Creatio MS Word Report Designer plug-in and upload the template to Creatio.

Working with macros

Use macros to set up extra data output options for an MS Word report. You can use basic macros and create custom macros. Use custom macros to implement custom MS Word reports. A macro for setting up an MS Word report is a class implementing the IExpressionConverter interface (see the ExpressionConverterHelper schema of the NUI package).

To make a custom macro callable from the report template, mark the macro with the ExpressionConverterAttribute attribute containing the name of the macro. For example:

[ExpressionConverterAttribute("CurrentUser")]

The Evaluate(object value, string arguments = "") interface method must be implemented in the class. The method accepts an MS Word report template field value as an argument and returns the string type value that will be inserted instead of this field in the ready MS Word report.

The general procedure of creating a custom MS Word report macro is as follows:

1. Create a new report in the [Report setup] section.
2. Set up the report display parameters.
3. Set up the report data fields and tables.
4. Add the [Id] column to the list of report columns that will be the incoming parameter for the custom macro.
5. Add a schema of the [Source Code] type with a class implementing the IExpressionConverter interface to the custom package. The class must be marked by the ExpressionConverterAttribute attribute with the name of the macro. Implement the Evaluate(object value, string arguments = "") method in it.
6. Publish the [Source Code] type object schema.
7. Add a tag with the name of the custom macro in the [#MacrosName#] format to the [Id] column when setting up column fields.

Case description

Create an "Account Summary" report for the [Accounts] section edit page to display the following information about the account:

• [Name].
• [Type].
• [Primary contact].
• [Additional info]. The annual revenue should be displayed for [Customer] accounts and the number of employees for [Partner] accounts.

The report must contain information about the date of creation and the name of the employee who created it.

Source code

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

Case implementation algorithm

1. Create a new report

To do this:

1. Open the System Designer by clicking . In the [System setup] block, click the [Report setup] link.
2. Click [New report] –> [MS Word] (Fig. 1).

Fig. 1. The [Report setup] section list

2. Set up the report display parameters

Set the following values (Fig. 3) in the parameter setup area (Fig. 2, 2):

• [Report title] – "Account Summary".
• [Section] – "Accounts”.
• [Show in the section list view].
• [Show in the section record page].

Fig. 2. MS Word report setup page

Fig. 3. – Setting up the report display parameters

3. Implement custom macros

Go to the [Advanced settings] section –> [Configuration] –> Custom package –> the [Schemas] tab. Click [Add] –> [Source code] (Fig. 4). Learn more about creating a schema of the [Source Code] type in the "Creating the [Source code] schema" article.

Fig. 4. Adding the [Source Code] schema

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

• [Title] – "AccountInfoByTypeConverter".
• [Name] – "UsrAccountInfoByTypeConverter".

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

Implement a macro class for receiving additional information depending on the account type. The complete source code of the module is available below:

namespace Terrasoft.Configuration
{
    using System;
    using System.CodeDom.Compiler;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Runtime.Serialization;
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Web;
    using Terrasoft.Common;
    using Terrasoft.Core;
    using Terrasoft.Core.DB;
    using Terrasoft.Core.Entities;
    using Terrasoft.Core.Packages;
    using Terrasoft.Core.Factories;

    // An attribute with the [AccountInfoByType] macro name.
    [ExpressionConverterAttribute("AccountInfoByType")]
    // The class should implement the IExpressionConverter interface.
    class AccountInfoByTypeConverter : IExpressionConverter
    {
        private UserConnection _userConnection;
        private string _customerAdditional;
        private string _partnerAdditional;
        // Calling localizable string values
        private void SetResources() {
            string sourceCodeName = "UsrAccountInfoByTypeConverter";
            _customerAdditional = new LocalizableString(_userConnection.ResourceStorage, sourceCodeName,
                "LocalizableStrings.CustomerAdditional.Value");
            _partnerAdditional =  new LocalizableString(_userConnection.ResourceStorage, sourceCodeName,
                "LocalizableStrings.PartnerAdditional.Value");
        }
        // Implementing the Evaluate method of the IExpressionConverter interface.
        public string Evaluate(object value, string arguments = "")
        {
            try
            {
                _userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
                Guid accountId = new Guid(value.ToString());
                return getAccountInfo(accountId);
            }
            catch (Exception err)
            {
                return err.Message;
            }
        }
        // The method for receiving additional information depending on the account type.
        // As the Id input parameter of the account.
        private string getAccountInfo(Guid accountId)
        {
            SetResources();
            try
            {
                // Creating an EntitySchemaQuery class instance with the [Account] root schema.
                EntitySchemaQuery esq = new EntitySchemaQuery(_userConnection.EntitySchemaManager, "Account");
                // Adding the [Name] column from the [Type] lookup field.
                var columnType = esq.AddColumn("Type.Name").Name;
                // Adding the [Name] column from the [EmployeesNumber] lookup field.
                var columnNumber = esq.AddColumn("EmployeesNumber.Name").Name;
                // Adding the [Name] column from the [AnnualRevenue] lookup field.
                var columnRevenue = esq.AddColumn("AnnualRevenue.Name").Name;
                // The records are filtered by the account Id.
                var accountFilter = esq.CreateFilterWithParameters(
                    FilterComparisonType.Equal,
                    "Id",
                    accountId
                );
                esq.Filters.Add(accountFilter);
                // Retrieving an entity collection.
                EntityCollection entities = esq.GetEntityCollection(_userConnection);
                // If the collection is not empty, the method will return the corresponding
                // data depending on the account
                if (entities.Count > 0)
                {
                    Entity entity = entities[0];
                    var type = entity.GetTypedColumnValue<string>(columnType);
                    switch (type)
                    {
                        case "Customer":
                            return String.Format(_customerAdditional, entity.GetTypedColumnValue<string>(columnRevenue));
                        case "Partner":
                            return String.Format(_partnerAdditional, entity.GetTypedColumnValue<string>(columnNumber));
                        default:
                            return String.Empty;
                    }
                }
                return String.Empty;
            }
            catch (Exception err)
            {
                throw err;
            }
        }
    }
}

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.

After making changes, save and publish the schema.

Go to the [Advanced settings] section –> [Configuration] –> Custom package –> the [Schemas] tab. Click [Add] –> [Source code] (Fig. 4). 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. 6):

• [Title] – "CurrentDateConverter".
• [Name] – "UsrCurrentDateConverter".

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

Implement a macro class for retrieving the current date. The complete source code of the module is available below:

namespace Terrasoft.Configuration
{
    using System;
    using System.CodeDom.Compiler;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Runtime.Serialization;
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Web;
    using Terrasoft.Common;
    using Terrasoft.Core;
    using Terrasoft.Core.DB;
    using Terrasoft.Core.Entities;
    using Terrasoft.Core.Packages;
    using Terrasoft.Core.Factories;

    // An attribute with the [CurrentDate] macro name.
    [ExpressionConverterAttribute("CurrentDate")]
    // The class should implement the IExpressionConverter interface.
    class CurrentDateConverter : IExpressionConverter
    {
        private UserConnection _userConnection;

        // Implementing the Evaluate method of the IExpressionConverter interface.
        public string Evaluate(object value, string arguments = "")
        {
            try
            {
                _userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
                // The method returns the current date.
                return _userConnection.CurrentUser.GetCurrentDateTime().Date.ToString("dd MMM yyyy");
            }
            catch (Exception err)
            {
                return err.Message;
            }
        }
    }
}

After making changes, save and publish the schema.

Go to the [Advanced settings] section –> [Configuration] –> Custom package –> the [Schemas] tab. Click [Add] –> [Source code] (Fig. 4). 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. 7):

• [Title] – "CurrentUserConverter"
• [Name] – "UsrCurrentUserConverter".

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

Implement a macro class for retrieving the current user. The complete source code of the module is available below:

namespace Terrasoft.Configuration
{
    using System;
    using System.CodeDom.Compiler;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Runtime.Serialization;
    using System.ServiceModel;
    using System.ServiceModel.Web;
    using System.ServiceModel.Activation;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Web;
    using Terrasoft.Common;
    using Terrasoft.Core;
    using Terrasoft.Core.DB;
    using Terrasoft.Core.Entities;
    using Terrasoft.Core.Packages;
    using Terrasoft.Core.Factories;

    // An attribute with the [CurrentUser] macro name.
    [ExpressionConverterAttribute("CurrentUser")]
    // The class should implement the IExpressionConverter interface.
    class CurrentUserConverter : IExpressionConverter
    {
        private UserConnection _userConnection;
        // Implementing the Evaluate method of the IExpressionConverter interface.
        public string Evaluate(object value, string arguments = "")
        {
            try
            {
                _userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
                // The method returns the contact of the current user.
                return _userConnection.CurrentUser.ContactName;
            }
            catch (Exception err)
            {
                return err.Message;
            }
        }
    }
}

After making changes, save and publish the schema.

4. Set up the report fields

In the [Set up report data] block of the section working area (Fig. 2, 5), set up the fields to display in the report. To do this, click and select the [Id] column in the drop-down [Column] list (Fig. 8). The current [Id] column will later be used in the custom macro to retrieve the current date.

Fig. 8. Selecting a column

Click [Select].

Use the same procedure to add [Id] (the column will later be used in the custom macro for retrieving the current user), [Name], [Type], [Primary contact], and [Id] (the column will later be used in the custom macro for receiving additional information depending on the account type) to the column template.

The list of columns after this step is presented below (Fig. 9).

Fig. 9. The list of added columns

5. Attach custom macro tags to the column names

Change the property of the [Id] column of an [Account] object. To do this, take the following steps;

1. In the [Set up report data] block of the section working area (Fig. 2, 5), double-click the title of the [Id] column or click in the column title bar.
2. Change the [Id] value of the [Title] field to [Id[#CurrentDate#]] (Fig. 10). [#CurrentDate#] is a the custom macro tag for retrieving the current date.

   Fig. 10. Setting up a column property

   

   Click [Save].

Use the same procedure to add more custom macro tags to the names of other [Id] columns.

• [#CurrentUser#] – for receiving the current user.

• [#AccountInfoByType#] – for receiving additional information depending on the account type.

The list of columns after adding custom macro tags is presented below (Fig. 11).

Fig. 11. The list of added columns with macros

Click [Save].

6. Set up the report template layout and upload the template to Creatio

To set up the template:

1. Open any MS Word file.

2. Click [Connect] on the Creatio plug-in toolbar (Fig. 12).

   Fig. 12. The [Connect] button

   

3. Enter the username and password of the Creatio user. Click next to the [Server] field (Fig. 13).

   Fig. 13. Authentication

   

4. Click [New]. Enter the server parameters (Fig. 14).

   Fig. 14. Server settings

   

   Click [OK].

5. Click [Select report] on the Creatio plug-in toolbar (Fig. 15).

   Fig. 15. The [Select report] button

   

6. Select the "Account Summary" report and click [OK] (Fig. 16).

   Fig. 16. Selecting a report

   

   The report setup window looks as follows (Fig. 17):

   Fig. 17. Setting up the report

   

7. Set up the template layout. Learn more about setting up a report template in the "Design report layout via the Creatio MS Word plug-in" article.
   After the setup, the report looks as follows (Fig. 18):

   Fig. 18. Setting up a report template

   

8. Click [Save to Creatio] to load the configured report template in Creatio (Fig. 19).

   Fig. 19. The [Save to Creatio] button

   

As a result, the "Account Summary" report will be available on the contact page under [Print] (Fig. 20).

Fig. 20. – Displaying the "Account Summary" report on a record page of the [Accounts] section

A report for accounts of the [Customer] type looks as follows (Fig. 21).

Fig. 21. A sample "Account Summary" report for [Customer] accounts

A report for accounts of the [Partner] type looks as follows (Fig. 22).

Fig. 22. A sample "Account Summary" report for [Partner] accounts

Transferring the package to another development environment

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

• SysModuleReport_ReportName – the report. To bind it, use the report Id from the [dbo.SysModuleReport] database table.
• SysModuleReportTable_ReportName – the tabular component of the report. To bind it, use the report Id from the [dbo.SysModuleReportTable] database table.