Calculated columns

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 above image shows a ‘sales per product’ report generated by Smart Report Maker. 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.

Please note that using Smart Report Maker, you can have multiple calculated columns in any report. Additionally, you can get the subtotals and the grand totals of any calculated columns.

The calculated columns configuration file

In each generated report by Smart Report Maker, you should find a configuration file named “calculated_columns.php”. You can use this file for configuring any number of required calculated columns. You can find this file at:

/{Smart-Report-Maker-PATH}/SRM8/SRM/Reports8/rep{report-name}/calculated_columns.php

where: {Smart-Report-Maker-PATH}: The path in which you uploaded Smart Report Maker on your local or remote server.

{report-name}: Your generated report’s name.

The next step is to explain in a step-by-step approach how to use Smart Report Maker to create a ‘sales per product’ report with calculated columns

How to add calculated columns to your report using Smart Report Maker.

In this tutorial let’s assume the following:

  1. The details of the products are saved in a ‘products’ table. And, we need only the ‘ProductName’ column from this table.
  2. The details of orders are saved in the ‘oreder_details’ table. And, we need the ‘OrderId’, the ‘UnitPrice’, and the ‘Quantity’ columns from this table.
  3. We will have to create a calculated column, the ‘total_price’. This column is calculated by multiplying the unit price by the sold quantity of each raw.

Creating this report by Smart Report Maker is relatively easy and can be done in a few minutes. We should start by clicking the  ‘Create New Report’ button to lunch the PHP report builder wizard. then:

  • In the first step, we should enter the connection parameters of the MYSQL database. Once a connection is established we should select to create a report based on a ‘table’.
  • In the second step, we should select both the “Products” and “OrderDetails” tables. Once this is done the system should direct us to the “Relationships” tab in the same step.

  • In the next step, we should select the columns which we want to include in the report. kindly note that you will find only the database columns. Calculated columns will come later.
  • In the grouping step, we should select to group the report by the “Product_name” column, since we want to have a sales per product report.

  • In the subtotals step, we should choose the “SUM” function and choose to apply this function to the “Quantity” column.

  • Once we finish creating the report, we will see that we have a report similar to what we want except that it doesn’t have any calculated columns which require a very small configuration.
  • We should connect to our server via FTP and visit the generated report directory which should be found at:
    /{Smart-Report-Maker-PATH}/SRM8/SRM/Reports8/rep{your-report-name}/
    where: {Smart-Report-Maker-PATH}: The path in which you uploaded Smart Report Maker on your local or remote server.
  • 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.     
);
  • In 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_price"=>function($row){

       return $row["unitprice"] * $row["quantity"];
      }    
);
  • In the above code snippet, we define one calculated column “total_price” 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.
  • Similarly, if we want to add another calculated column in the same report, we should add the new column name and the function to calculate its value in the same array. For example, let’s say we want to add another calculated column for “total_price_after_vat”, we should add the column name and then link it to a function that multiplies the total_price (we should refer to it as $row[“total_price”]) by 1 + VAT percentage (i.e. multiply by 1.15 if VAT is 15%).

$calculated_columns = array(    
  "total_price"=>function($row){
       return $row["unitprice"] * $row["quantity"];
      }, 
 "total_price_after_vat"=>function($row){
       return $row["total_price"] * 1.15;
      }    
);

 

Summery

  • 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 Maker, 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 Maker. You can find this file at:/{Smart-Report-Maker-PATH}/SRM8/SRM/Reports8/rep{your-report-name}/calculated_columns.PHP where: {Smart-Report-Maker-PATH}: The path in which you uploaded Smart Report Maker on your local or remote server.

upgrade link