How to change the calculation for the "Closed" column in the [Forecasts] section.

Introduction

Use Creatio to plan your company’s sales turnover and analyze the targets. In the [Forecasts] section, you can generate forecasts using different units of measure registered in the system, and calculate the actual values. This enables to specify the time period for which you want to analyze the sales performance and monitor the overall performance of your department using the summary tables provided in the [Forecasts] section.

More information about the section can be found in the “[Forecasts] section” article.

Case description

Change the logic of calculation of the “Closed” column in the [Forecasts] section: the calculation should be based on invoices instead of sales.

Case implementation algorithm

1. Copy the source code of the forecast building module

To do this, search for the ForecastBuilder schema name in the [Configuration] section (Fig.1.1). Double click the found schema (Fig. 1.2) to open the module schema in the module designer ( “Module designer”).

Fig. 1. Search field in the [Configuration] section

Copy all content from the [Source code] area (Fig. 2) to a text file. .

Fig. 2. Copying the source code of the schema

2. Create a replacing forecast building module.

To do this, select a custom package in the [Configuration] section and execute the [Add] -> [Replacing Client Module] action on the [Schemas] tab (Fig. 3). The procedure for creating a replacing custom module is covered in the “Creating a custom client module schema”.

Fig. 3. Creating a replacing module

For the created module schema, set the “ForecastBuilder” as a [Parent object] (Fig.4). After that, all schema properties from the parent module will be applied automatically.

Fig. 4. Properties of the module schema

Add the source code of the parent schema (that was copied at the previous step) on the [Source Code] tab and save the schema.

3. Change the method of the forecast page opening

In the source code of the ForecastBuilder module schema, change the values of the valuePairs array in the openForecastPage() method of the Terrasoft.configuration.BaseForecastsViewModel to the values corresponding the schema of the [Invoice] object. The source code for the changes (previous values are commented out):

...
openForecastPage: function(moduleId, operation) {
    ...
    var valuePairs = [
        {
            name: "EntitySchemaUId",
            value: "bfb313dd-bb55-4e1b-8e42-3d346e0da7c5" //value: "ae46fb87-c02c-4ae8-ad31-a923cdd994cf"
        },
        {
            name: "EntitySchemaName",
            value: "Invoice" //value: "Opportunity"
        }
    ];
    ...
},
...

You can get the value of the EntitySchemaUId Id by executing following SQL query to the Creatio database (Fig. 5):

select lower(UId), Name from SysSchema
Where name = 'Invoice' 
and ExtendParent = 0

Fig. 5. Request result

Save the schema to apply changes.

5. Modify the tsp_RecalculateForecastFact stored procedure

The tsp_RecalculateForecastFact stored procedure recalculates the values of the “Closed” column for selected time period. Executing and applying changes in the stored procedures is described in the “How to: Modify a Stored Procedure (SQL Server Management Studio)” MSDN article.

To use invoices for calculation, make following modification to the procedure (previous values are commented out).

1. Change the value stored in the @CompletedId variable.

--SET @CompletedId = '{60D5310C-5BE6-DF11-971B-001D60E938C6}'
SET @CompletedId = '{698D39FD-52E6-DF11-971B-001D60E938C6}'

This variable stores the Id of the status of a paid invoice. You can get the value of the variable by executing following SQL query to the Creatio database (Fig. 6):

select Id, Name from InvoicePaymentStatus
where Name = 'Paid'

Fig. 6. Request result

2. Change the query which result is stored in the @MaxDueDate variable.

--SET @MaxDueDate = (SELECT Convert(Date, MAX(DueDate), 104) FROM Opportunity o WHERE o.StageId = @CompletedId)
SET @MaxDueDate = (SELECT Convert(Date, MAX(StartDate), 104) FROM Invoice o WHERE o.PaymentStatusId = @CompletedId)

The query searches for the newest paid invoice.

3. Change the subquery expression stored in the @SQLText variable. In this subquery the logic of calculating the “Closed” and “Pipeline” columns is implemented.

--Initial value
/*SET @SQLText = N'
    SELECT
    (SELECT SUM(ISNULL(fiv.[Value], 0))
    FROM [ForecastItemValue] fiv
    WHERE fiv.[ForecastItemId] = @P5
    AND fiv.[PeriodId] = @P6
    AND fiv.[ForecastIndicatorId] = @P7
    ) PlanAmount,
    (SELECT SUM(ISNULL(o.[Amount], 0)) 
    FROM [Opportunity] o
    WHERE o.[StageId] = @P1
    AND o.[DueDate] >= @P2 
    AND o.[DueDate] < @P3
    AND o.' + @ColumnName + N' = @P4
    ) FactAmount,
    (SELECT SUM(ISNULL(o.[Amount], 0) * ISNULL(o.[Probability], 0) / 100) 
    FROM [Opportunity] o
    INNER JOIN [OpportunityInStage] ois ON ois.[OpportunityId] = o.[Id]
    INNER JOIN [OpportunityStage] os ON os.[Id] = ois.[StageId]
    WHERE os.[End] = 1
    AND ois.[DueDate] >= @P2
    AND ois.[DueDate] < @P3
    AND ois.[Historical] = 0
    AND o.' + @ColumnName + N' = @P4
    ) PotentialAmount'*/
--New value    
SET @SQLText = N'
    SELECT
    (SELECT SUM(ISNULL(fiv.[Value], 0))
    FROM [ForecastItemValue] fiv
    WHERE fiv.[ForecastItemId] = @P5
    AND fiv.[PeriodId] = @P6
    AND fiv.[ForecastIndicatorId] = @P7
    ) PlanAmount,
    (SELECT SUM(ISNULL(o.[Amount], 0))
    FROM [Invoice] o
    WHERE o.[PaymentStatusId] = @P1
    AND o.[StartDate] >= @P2
    AND o.[StartDate] < @P3
    AND o.' + @ColumnName + N' = @P4
    ) FactAmount,
    (SELECT SUM(ISNULL(o.[Amount], 0))
    FROM [Invoice] o
    INNER JOIN [InvoicePaymentStatus] os ON os.[Id] = o.[PaymentStatusId]
    WHERE os.[FinalStatus] = 0
    AND o.[StartDate] >= @P2
    AND o.[StartDate] < @P3
    AND o.' + @ColumnName + N' = @P4
    ) PotentialAmount'

Run the SQL script (F5 key) to apply the changes.

As a result, the calculation of “Closed” and “Potential” columns will be based on invoices (Fig. 8) istead of sales (Fig. 7).

Fig. 7. Calculating the “Closed” columns by sales

Fig. 8. Calculating the “Closed” columns by invoices

The resulting source codes of the module and stored procedure can be downloaded from the link.