Case study Cost allocation: Solution design

We will design data model according to the requirements.

Then we will process the data by a system of queries.

We can implement a special table as an optimization feature that will contain results of the cost distributions. This conversion will be launched by the solution Administrator manually with a button Recalculate on the page of the Dashboard Cost allocation. This button of type Script will execute function RefreshData() from the script block recalculate.

//#block recalculate
RefreshData();

This function will always insert into the table correct results that will be then used for displaying reports. If there are no changes in the input data, there is no reason to perform the recalculation at every report display. By using a table with results we will accelerate the report display.

Script block:

function RefreshData()
{
var m = OG.Model.GetByCode('cost_allocation');

var clTotalGroup = m.ClassDefs['total_group_materialized'];
var qTotalGroup = m.Queries['qtotal_group'];

OG.Sql.RunSql('delete from ' + clTotalGroup.DBTableName);

var colAmount = clTotalGroup.Columns['amount'];
var colCountry = clTotalGroup.Columns['country'];
var colPeriod = clTotalGroup.Columns['period'];
var colTeam = clTotalGroup.Columns['team'];

var colAmount2 = qTotalGroup.Columns['amount'];
var colCountry2 = qTotalGroup.Columns['country___country'];
var colPeriod2 = qTotalGroup.Columns['period___period'];
var colTeam2 = qTotalGroup.Columns['team___team'];

var sql = OG.TextUtils.Format(
'insert into {0} ({2},{3},{4},{5}, CreatedDataSourceId) select Amount,country___country,period___period,team___team, 1 from ({1}) d',
clTotalGroup.DBTableName, qTotalGroup.QuerySqlDB,
colAmount.DBColumnName, colCountry.DBColumnName, colPeriod.DBColumnName, colTeam.DBColumnName,
colAmount2.DBColumnName, colCountry2.DBColumnName, colPeriod2.DBColumnName, colTeam2.DBColumnName
);
OG.Sql.RunSql(sql);
}