It often happens that databases hold data in the format that is convenient for storing but not for a user. To let data easily convert from one format to another the TOLAPCube supports calculated fields. A calculated field is the one whose value is comprised from other fields’ values of the same or some other related table. Calculated fields allow a number of typical for multidimensional Cube desing tasks to be performed.

1. Conversion of the data stored in the database to the user friendly format. For example, financial transactions can be stored in the database in the form of a code (that is every aspect of this information will be presented in figures). Using calculated fields we can decode any aspect of the information: for example, show the full names of months or states instead of numbers, or perform more complicated operations.
2. Computing the value for analysis from several fields. For example, the total price of the goods bought by a certain customer and the discounts he received may be held in separate cells. But for the purposes of analysis we will need them calculated together.
3. Avoiding of the redundant data in the database. A relational database is almost always designed in a normal form that allows not to store redundant data. However during analysis we may need to have the values from the related table placed into the fact table. This can be done with calculated fields.

The Types of Calculated Fields

RadarCube allows two types of calculated fields: auto-calculated and event-calculated fields. Event calculated fields can be calculated by any algorithm in the TOLAPCube.OnCalculateField event handler. Auto-calculated fields can be calculated without this event using the value of another field and the format mask.

The calculated fields play the main role when defining the calculated hierarchy or measure for the Cube and so they can be created when defining the structure of the Cube with the methods AddCalculatedColumn, AddCalculatedMeasure or AddCalculatedHierarchy. There are two parameters in each of these methods that can be omitted: SourceColumnName and SourceColumnFormat. If both these parameters are left empty then the event-calculated field, whose value must be calculated in the TOLAPCube.OnCalculateField event handler, will be created (see below). If the SourceColumnName parameter is passed to the method then the auto-calculated field is created. The value of this field is converted automatically from the field given as SourceColumnName parameter. If the source field type implements the IFormattable interface then the conversion is done by the IFormattable.ToString() method with the SourceColumnFormat parameter as the format of conversion. If the source field type doesn't implement IFormattable then the objects ToString() method is called instead. For example the following code adds the auto-calculated field "Year" in the "Orders" table whose values are calculated from the "OrderDate" field:

cube.AddCalculatedHierarchy("Time", d.Orders, typeof(string), "Year", "OrderDate", "yyyy");

The source field can be really located in another table, related to the current with "one-to-many" relation. If so the SourceColumnName may be prefixed with the source table name: <TableName>.<FieldName>.

TOLAPCube.OnCalculateField Event

The essencial part of the calculated fields is the TOLAPCube.OnCalculateField event handler. This event is fired every time a record is read from a source table. When the event is fired it means the following conditions have already been fulfilled:

1. All fields of the corresponding record have already been read and their values are ready to be used in calculation.
2. All auto-calculated fields are already computed and ready to be used in calculation.
3. All parent tables (Master) related to the current one (Detail) are already read and all their calculated fields are already computed.

As a singe parameter the TCalculateFieldArgs object is passed to the event handler. It provides access to any field of any table related to the current one (i.e. that is being read at the time the event is fired). Here are the main three properties of TCalculateFieldArgs class:

1. ActiveTable - the table being read at the moment the event is fired.
2. DataSet - the data schema. This property provides access to any table in the data schema and their relations. However, it necessary to keep in mind that by the moment the event is fired not all tables may have been read from the database yet. .
3. Row - the current record, whose calculated fields must be computed in the event. This is the most useful property in the event because the TDataRow class has many methods to assign/read the field values in the current or related record. Having the related parent records you can, in turn, use them to retrieve grand parent related records and so on.

These classes are described in details in related article, here we'll just give an example of the event handler.

private void cube_OnCalculateField(object Sender, TCalculateFieldArgs e)
{
    // Here we calculate two columns: "Employees"."Employee Name" and "Order Details"."Sales"
    if (e.ThisTable("Employees"))
    {
        e.Row["Employee Name"] = e.Row["FirstName"] + " " + e.Row["LastName"];
    }
    if (e.ThisTable("Order Details"))
    {
        // Convert all fields to Double in case they have different types
        e.Row["Sales"] = Convert.ToDouble(e.Row["Quantity"]) * Convert.ToDouble(e.Row["UnitPrice"]) * (1 - Convert.ToDouble(e.Row["Discount"]));
        // Also we may get any field from any record of the parent table.
        // For example assume we need the date of the order here:
        TDataRow OrdersRow = e.Row.GetMasterRow("Orders");
        if (OrdersRow != null)
        {
            // Here we never use that value but we coud have
            DateTime OrderDate = Convert.ToDateTime(OrdersRow["OrderDate"]);
        }

    }
}

In this example we calculate the "Employee Name" field in the "Employees" table and the "Sales" field in the "Order Details" table. Also in the "Order Details" table we get the master row from the related table "Orders" and fetch the "OrderDate" field from the related record. There is just one thing left to say about the TOLAPCube.OnCalculateField event. Though it is designed to give values to calculated fields, it can be used however to change the values of any other field, not necessarily a calculated one. In this regard the calculated fields are no different from all others. However this event is never fired for the tables that don't have even a single calculated row in them. So if you want to use this event to change the field values "on the fly", you have to provide at least one calculated field (for example with AddCalculatedField method).