Calculated columns are virtual columns that are not physically stored in your database table Instead, their values are calculated from one or more other columns.

The below image shows a ‘sales per product’ report generated by Smart Report Engine. The generated report is grouped by the product name, then for each product, the report shows its orders, unit price, and sold quantities in each order. These columns are physically stored in the database. On the other hand, the total price column is a calculated column that doesn’t exist in the database. It is calculated by multiplying the unit price by the sold quantity in each raw.

Using Smart Report Engine, you can have multiple calculated columns in any report. Additionally, you can get the subtotals and the grand totals of any calculated columns.

In this tutorial, we will use the Smart report engine to create a simple report with a calculated column. Let’s assume we have an “items” table. This MYSQL table store information about some items and their sales. The report we need should diplay info about each item along with the “total_sales” of each item However, the database table doesn’t have this information. it has however the “sold quantity” for each item in a column and the “unit price” of each item in another column. Therefore, to display the “total_sales”  we should add it as a calculated column where its value is estimated by multiplying the “unit price” by the “sold quantity” in each raw.

Doing this by Smart Report Engine is fairly easy whether in native PHP or Laravel. we should start by writing the code that should display the physical column only as follows:

1- Adding the physical columns

In a native PHP Project, the code will be something like the following:

2- Adding the calculated columns

• By running the code above, we should have a report similar to what we want except that it won’t have any calculated columns.
• We should connect to our server via FTP and visit the generated report directory which should be found at:
  /your-app-path/sre_reports/rep{your-report-name}/ (in case of native installation).
  /your-app-path/public/sre_reports/rep{your-report-name}/ (in case of Laravel installation).
  
  
• In the generated report directory from the previous step, we should find a “calculated_columns.php” file. By opening this file for editing with any text editor. we should find documentation on how to add calculated columns to this generated report, then the only uncommented code we should find is the “$calculated_columns” array as shown below.

$calculated_columns = array(    
  // add any calculated columns here.     
);

n the “$calculated_columns” array, we should define the calculated column we want to add to the report as well as the method to calculate it as shown in the code below.

$calculated_columns = array(    
  "total_sales"=>function($row){

       return $row["unitprice"] * $row["quantity"];
      }    
);

• In the above code snippet, we define one calculated column “total_sales” and linked it to the function that should be used to calculate its value.  Please note that the value of any calculated column is calculated by the function linked to it and which should accept only one parameter “$row” which represents any row in the report.  Therefore if we need to include any column in the calculation formula we should refer to that column as $row[“name_of_the_column”] For example, $row[“unit_price”].
• Once we save the calculated_column.php file changes and refresh the browser we should be able to see the calculated columns displayed in the report.

Summary

• Calculated columns are virtual columns that are not physically stored in your database table Instead, their values are calculated from one or more other columns.
• You can have multiple calculated columns in any report.
• You can get the subtotals and the grand totals of any calculated columns.
• In each generated report by Smart Report Engine, you should find a configuration file named “calculated_columns.php”. This file is made for configuring any number of calculated columns needed for any report generated by Smart Report Engine. You can find this file at: /your-app-path/sre_reports/rep{your-report-name}/