Before we begin, if you haven’t already done so, you might want to read the primary usage example. This post will help you understand how to start using Smart Report Engine.

Using Smart Report Engine, you can include subtotals and grand totals in your reports to calculate the sum, average, minimum, maximum, and count of any column(s) for each group of records and for all records of your report.
In this tutorial, we will use Smart Report Engine to create a report with subtotals and grand totals. Let’s assume we have an “items” table in an MYSQL DB. This table has valuable information about the available items in a sunglasses store, and we want to make a professional report that shows the sold quantity and stock of each item in each country.

Report Options

In this report we want to:

1. Show only the following columns: “id”, “product_code”, “product_name”, “Photo”, “price”, “sold_quantity”, “stock”, “country”
2. Group the report per country.
3. show the sum of “sold_quantity” and “stock” per each country.

We will start by showing the code in a native PHP project and then in a Laravel project.

1- Case of a native PHP Project

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

use SRE\Engine\CustomEngine;
use SRE\Engine\ReportOptions;

require_once "sre_bootstrap.php"; //Make sure use the correct relative path to this file.
$report = new ReportOptions(SRE_PUBLIC_REPORT);
$report->select_tables(array("items"))
        ->select_fields(array(
            "id", "product_code", "product_name", "Photo", "price", "reorder_level",
            "Units_In_Stock", "Country"))
        ->set_grouping("country")
        ->sub_totals("country", SRE_SUM, arry(
                        "reorder_level", "Units_In_Stock"));
$engine = new CustomEngine($report);
$report_path = $engine->create_report();
if ($report_path) {
    echo "Report created successfully! To visit your report please <a href='" . $report_path . "' /> click here </a> ";
}

2- Case of Laravel Project

We will put the report generation logic in a service. It will be something like the following:

use Sre\SmartReportingEngine\src\Engine\Constants;
use Sre\SmartReportingEngine\src\Engine\CustomEngine;
use Sre\SmartReportingEngine\src\Engine\ReportOptions;
 
class ReportService {
 
    public function generatePublicReport() {
 
        $report = new ReportOptions(Constants::SRE_PUBLIC_REPORT);
        $report->select_tables(array("items"))
               ->select_fields(array(
            "id", "product_code", "product_name", "Photo", "price", "reorder_level",
            "Units_In_Stock", "Country"))
               ->set_grouping("country")
               ->sub_totals("country", Constants::SRE_SUM, arry(
                        "reorder_level", "Units_In_Stock"));
        $engine = new CustomEngine($report);
        $report_path = $engine->create_report();
        return $report_path;
    }
}

In the above code, we first set the needed options for this report using a “$report” object, which is an instance of the “ReportOptions” Class. Following are the report options defined in the example:

• Setting the Report as a “Public_Report”
  A public report can be accessed freely without any integration with a login system. We pass the “SRE_PUBLIC_REPORT”  flag to the constructor of the “ReportOptions” class to create a public report.
• Setting a specific MYSQL table as the data source of the report.
  In the above example, we select a table with the name “items” as the data source of the report. To do this, we pass an array with only the “items” table to the “select_tables” method.
• Select some columns of the selected table to show in the report.
  In the above example, we want to show only specific columns in the report. For this reason, we pass an array of the selected columns to the “select_fields” method.
• Grouping the report by “country”
  In the above example, we select the “country” column for grouping the report. We do this by passing an array with only the “country” column to the “set_grouping” method.
• Defining the subtotals and grand totals options
  In the above example, we want to show the sum of “sold_quantity” and “stock” for each country in the report and for all countries at the end of the report. For this reason, we use the sub_total method and pass to it the following parameters:
  • The column by which the report is grouped. In this tutorial, “Country” is the $group_by_column.
  • The subtotal function. In this tutorial, we use the “SRE_SUM” to get the totals of “sold_quantity” and “stock”. Other supported functions are SRE_AVERGAE, SRE_COUNT, SRE_MAX, and SRE_MIN.
  • An array of the columns on which you want to apply the subtotals function. In this tutorial, the affected columns are “sold_quantity” and “Stock”.

Then after defining all the options for this report via the “ReportOptions” object, we will pass this object to the constructor of the “CustomEngine” class. By doing this, we create a new instance of the “CustomEngine” class; then, we used this new instance to call the “create_report” method to generate the report and return its URL.

Summary

Using the sub_total function of Smart Report Engine, you can include subtotals and grand totals in your reports to calculate the sum, average, minimum, maximum, and count of any column(s) for each group of records and for all records of your report.