Smart Report Engine supports conditional formatting, which allows changing the appearance of a cell-based on its value. In this tutorial, we will create a report based on a single table and apply different conditional formatting rules in it. Let’s assume we have an “items” table in a MYSQL DB. This table stores data about some items of a sunglasses store.

Report Options

In this report we want to:

  1. Show only the following columns:
    “id,” “product_name,” “country,” “units_in_stock,” “price.”
  2. Applying the following conditional formatting rules:
    1. Units in stock less than or equal ten should be colored in red (The default color for conditional formatting function)
    2. Price between $10 and $100 should be colored in green.
  3. The style of the report should be “grey.”
  4. The layout of the report should be “stepped.”

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
use SRE\Engine\CustomEngine;
use SRE\Engine\ReportOptions;
require_once "../sre_bootstrap.php";
$report = new ReportOptions(SRE_PUBLIC_REPORT);
$report->select_tables(array("items"))
       ->select_fields(array("id", "country", "list_price", "units_in_stock","product_name"))
       ->set_grouping(array("country"))
       ->conditional_format_less("units_in_stock", 10, true)
       ->conditional_format_between("list_price", 0, 100,"#014421")
       ->set_layout("AlignedLeft")
       ->set_style_name("grey"); 
$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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
namespace App\Services;
 
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", "country", "list_price", "units_in_stock","product_name"))
              ->set_grouping(array("country"))
              ->conditional_format_less("units_in_stock", 10, true)
              ->conditional_format_between("list_price", 0, 100,"#014421")
              ->set_layout("AlignedLeft")
              ->set_style_name("grey"); 
       $engine = new CustomEngine($report);
       $report_path = $engine->create_report();
       return $report_path;
    }
}

Step by step code explanation

In the above code, we set the options we want for this report using the “$report” object, which is an instance of the “ReportOptions” Class.

Setting the Report as a “Public_Report”

A public report can be accessed freely without any integration with a login system. To set this report as public, we pass the “SRE_PUBLIC_REPORT”  flag to the constructor of the “ReportOptions” class.

Setting a certain MYSQL table as the data source of the report

In the above example, we want to select a table with the name “items” as the data source of the report. So, We pass an array with only the “items” table to the “select_tables” method

Showing only some selected columns in the report

In the above example, we want to show only specific columns in the report. So, we pass an array of the selected columns to the “select_fields” method.

Conditional formatting rules

In the above example, we apply two conditional formatting rules; we do this by calling two “conditional_format” methods, which are “conditional_format_less” and “conditional_format_between.”

We pass to the  “conditional_format_less” method, the column name “units_in_stock,” the filter value, which is 10 in this example,  then “True” because we want to apply the rule if the value of the “Units_in_stock” cell is less than or equal 10. Because we didn’t pass any color, if the rule matches, the cell will be colored in red (the default color in all conditional formatting methods).

We pass to the “conditional_format_between” method, the column name “list_price,” the minimum and maximum filter value, then the color we want to use if the rule matches.

Selecting a different style

In the above example, we want to use the “grey” style for our report. For doing this, we pass the “grey” parameter to the “set_style_name” method.

Selecting a layout

In the above example, we want to select the  “AlignLeft” layout for our report. For doing this, we pass the “AlignLeft” parameter to the “set_layout” method.

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

Display Report Next Tutorial