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 expressionis 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 :(OptionalThe width in pixels of the displayed image, default is actual image width
        height 
:(OptionalThe 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 extensionto drill down to)
    ->
where(parameters :An array mapping target report criteria items with the columns in the report row pass inSo 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 linkThis can include the actuial values of columns using the {} notation
        
,
        
url :The URL to go to when clickedThis can pass columns columns through using the {} notation)
    ->
skip()
    ->
sum(
        
sum column :The column to sum on,
        
group column :(OptionalThe column to base the group sum onWhen this value changes the sum is reset to zero.)
    ->
avg(
        
average column :The column to average on,
        
group column :(OptionalThe column to base the group average onWhen this value changes the average is reset to zero.
        )
    ->
min(
        
average column :The column to calculate the minimum from on,
        
group column :(OptionalThe column to base the group minimum onWhen this value changes the minimum is reset)
    ->
max(
        
average column :The column to calculate the maximum from on,
        
group column :(OptionalThe column to base the group maximum onWhen 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
                ->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();