Styling
Expressions allow you to call php syntax and call functions to create new columns and modify existing column values.For example you may wish to make a format change to a column such as round a number or capitalise a string.
Or you may wish to generate a hyperlink based on the value of a column.
Or you may wish to make a value go red if it exceeds a certain value.
You use expressions if you want to create a running total or aggregation on an existing column in order to display at the bottom of the section.
Assign the result of an expression to a report column
Usage:
\Reportico\Engine\Builder\build()
->expression(column :Name of column (existing or new) to assign an expression to)
->set(expression :Assignment value can include {} botation)
->if(condition :If the passed condition (as a PHP expression) is true the expression is set)
->else(expression :Set to this expression (as a PHP expression) if the condition results is not true)
->imageurl(
url :The url thats points to an image (the url can contain report column values using the {} notation.,
width :(Optional) The width in pixels of the displayed image, default is actual image width
height :(Optional) The height in pixels of the displayed image, default is actual image height)
->section(section [CELL|ALLCELLS|COLUMNHEADERS|ROW|PAGE|BODY|GROUPHEADERLABEL|GROUPHEADERVALUE|GROUPTRAILER])
->style(CSS style :A string containing CSS styles to apply to the column cell or section)
->drilldownToUrl(drillDownUrl :The url to drilldown to)
->drilldownToReport(
drillDownProject :The name of the project to drilldown to,
drillDownReport :The name of the report (without the xml extension) to drill down to)
->where(parameters :An array mapping target report criteria items with the columns in the report row pass in. So the keys
are target report items and the values are the column names to pass to those criteria items)
->link(
label :The label to display for the url link. This can include the actuial values of columns using the {} notation
,
url :The URL to go to when clicked. This can pass columns columns through using the {} notation)
->skip()
->sum(
sum column :The column to sum on,
group column :(Optional) The column to base the group sum on. When this value changes the sum is reset to zero.)
->avg(
average column :The column to average on,
group column :(Optional) The column to base the group average on. When this value changes the average is reset to zero.
)
->min(
average column :The column to calculate the minimum from on,
group column :(Optional) The column to base the group minimum on. When this value changes the minimum is reset)
->max(
average column :The column to calculate the maximum from on,
group column :(Optional) The column to base the group maximum on. When this value changes the maximum is reset)
->old(average column :The column to take the previous value from)
In this example, we have added two assignments.
The first concatenates the first and second name together into a new full name column, and then uses the column() method to hide the first and second name from the report.
The second uses the PHP in built date_diff function to calculate the age of the employee by taking the birth date away from the current date.
Note that in both cases we are deriving these expressions based on columns selected in the query and so they are fed into the expression()->set() method using the {} notation.
The expressions passed have to be in valid PHP and be careful to ensure that passed in columns are not in quotes. So in the concatenation exaple we use
Run Demo
The first concatenates the first and second name together into a new full name column, and then uses the column() method to hide the first and second name from the report.
The second uses the PHP in built date_diff function to calculate the age of the employee by taking the birth date away from the current date.
Note that in both cases we are deriving these expressions based on columns selected in the query and so they are fed into the expression()->set() method using the {} notation.
The expressions passed have to be in valid PHP and be careful to ensure that passed in columns are not in quotes. So in the concatenation exaple we use
->set( "{first_name}.' '.{last_name}" )
not
->set(" '{first_name} {last_name}' ")
Run Demo
<?php
require_once(__DIR__ .'/../vendor/autoload.php');
error_reporting(E_ALL);
\Reportico\Engine\Builder::build()
->properties([ "bootstrap_preloaded" => true])
->datasource()->database("mysql:host=localhost; dbname=DATABASE NAME")->user("USER")->password("PASSWORD")
->title ("Product Stock")
->description ("Produces a list of our employees")
->sql ("
SELECT ProductID id, ProductName product, UnitsInStock in_stock, ReorderLevel reorder_level, UnitsOnOrder on_order, companyname Company, country, categoryname category
FROM northwind_products
join northwind_suppliers on northwind_products.supplierid = northwind_suppliers.supplierid
join northwind_categories on northwind_products.categoryid = northwind_categories.categoryid
WHERE 1 = 1
ORDER BY categoryname
")
->expression("total_stock")->sum("in_stock","category")
->group("category")
->throwPageBefore()
->trailer("total_stock")->below("in_stock")->label("Total")
->customTrailer("Total in stock for category {category} is {total_stock}", "border: solid 4px #22D; background-color: #222; color: #fff; right: 0px; margin-left: auto; width: 50%; padding: 10px;")
->header("category")
->column("in_stock")->justify("right")
->column("on_order")->justify("right")
->column("reorder_level")->justify("right")
// Conditional styling - if the reorder level < in_stock then set in_stock cell to red
->expression("in_stock")
->style("background-color: #dd5555")
->if("{in_stock} < {reorder_level}")
// Conditional styling - if the reorder level < in_stock then set ALLCELLS in row to have light ref background
->expression()
->section("ALLCELLS")
->style("background-color: #ffcccc")
->if("{in_stock} < {reorder_level}")
// Section styling - Set borders around the column headers and set the color blue
->expression()
->section("COLUMNHEADERS")
->style("border: 2px solid #888888; color:#0000ff" )
// Section styling - Set dotted lines
->expression()
->section("ROW")
->style("border: 2px solid #888888; border-style: dotted" )
// Section styling - Set the background color or the table to a lightish blue
->expression()
->section("PAGE")
->style("border: 2px solid #888888; background-color:#dff" )
// Section styling - Set the color of each page to a different blue
->expression()
->section("BODY")
->style("background-color: #Aff;" )
->execute();