Expressions

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 
                ->set( "{first_name}.' '.{last_name}" )
not 
                ->set(" '{first_name} {last_name}' ")


Run Demo


<?php

      require_once(__DIR__ .'/../vendor/autoload.php');
      

      \Reportico\Engine\Builder::build()
          ->properties([ "bootstrap_preloaded" => true])
          ->datasource()->database("mysql:host=localhost; dbname=DATABASE NAME")->user("USER")->password("PASSWORD")
          ->title     ("Employee List")
          ->description     ("Produces a list of our employees")
          ->sql       ("
                SELECT EmployeeID employee_id, LastName last_name, FirstName first_name, date(BirthDate) birth_date, Country
                FROM northwind_employees
                WHERE 1 = 1
                ORDER BY Country, LastName
                ")
          ->expression("fullname")
                ->set("{first_name}.' '.{last_name}")
          ->expression("age")
                ->set("date_diff(new DateTime(), new DateTime({birth_date}))->format('%y years %m months %d days')")
          ->column("first_name")->hide()
          ->column("last_name")->hide()
          ->column("fullname")->order(3)

          ->execute();
?>