Create MS Word report using custom macros

Advanced

Case. 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 scr_Settings_button.png. In the System setup block, click the Report setup link.
  2. Click New report —>MS Word.
scr_ReportSetup_startpage.png

2. Set up the report display parameters 

Set the following values in the parameter setup area (2):

  • Report title – "Account Summary".
  • Section – "Accounts”.
  • Show in the section list view.
  • Show in the section record page.
MS Word report setup page
scr_PrintForm.png
Setting up the report display parameters
scr_Parameters_example.png

3. Implement custom macros 

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 Create the Source code schema" article.

scr_SourceCode.png

Specify the following parameters for the created object schema:

  • Title – "AccountInfoByTypeConverter".
  • Name – "UsrAccountInfoByTypeConverter".
scr_AccountInfoByTypeConverter_settings.png

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(columnType);
                    switch (type)
                    {
                        case "Customer":
                            return String.Format(_customerAdditional, entity.GetTypedColumnValue(columnRevenue));
                        case "Partner":
                            return String.Format(_partnerAdditional, entity.GetTypedColumnValue(columnNumber));
                        default:
                            return String.Empty;
                    }
                }
                return String.Empty;
            }
            catch (Exception err)
            {
                throw err;
            }
        }
    }
}

Populate the localizable strings of the report with the following values:

Setting up the localizable strings
Name English (United States) Russian (Russia)
PartnerAdditional Number of employees {0} persons Number of employees {0} people
CustomerAdditional Annual turnover {0} Annual revenue {0}

After making changes, save and publish the schema.

Go to the Advanced settings section –> Configuration –> Custom package –> the Schemas tab. Click Add —> Source Code.

Specify the following parameters for the created object schema:

  • Title – "CurrentDateConverter".
  • Name – "UsrCurrentDateConverter".
scr_CurrentDateConverter_settings.png

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.

Specify the following parameters for the created object schema:

  • Title – "CurrentUserConverter"
  • Name – "UsrCurrentUserConverter".
scr_CurrentUserConverter_settings.png

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 (5), set up the fields to display in the report. To do this, click scr_AddButton.png and select the Id column in the drop-down Column list. The current Id column will later be used in the custom macro to retrieve the current date.

Attention. Use the Id column as an input parameter for a custom macro.

scr_Column_Id.png

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.

scr_ColumnList.png

5. Attach custom macro tags to the column names 

Attention. First, publish the Source Code type schema that implements a custom macro must. Then, add the name of the macro to the template layout. If you refresh the page in Creatio, the macro will not be printed.

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 (5), double-click the title of the Id column or click scr_EditButton.png in the column title bar.
  2. Change the [Id] value of the Title field to [Id[#CurrentDate#]]. [#CurrentDate#] is a the custom macro tag for retrieving the current date.
    scr_CurrentDate_macros.png

    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.

scr_ColumnListWithMacros.png

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.

    scr_ConnectButton.png

  3. Enter the username and password of the Creatio user. Click scr_PlusButton.png next to the Server field.

    scr_Authorization.png

  4. Click New. Enter the server parameters.

    scr_ServerSettings.png
    Click OK.

  5. Click Select report on the Creatio plug-in toolbar.

    scr_SelectReportButton.png

  6. Select the "Account summary" and click OK.

    scr_ChooseReport.png
    The report setup window looks as follows:
    scr_Report.png

  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:

    scr_CustomizedReport.png

  8. Click Save to Creatio to load the configured report template in Creatio.

    scr_SaveToCreatioButton.png

As a result, the "Account Summary" report will be available on the contact page under Print.

scr_Print_button.png

A report for accounts of the Customer type looks as follows.

scr_Report_Customer.png

A report for accounts of the Partner type looks as follows.

scr_Report_Partner.png