Table Component Guide

The Table Component Guide gives you an overview of how to create tables with RazorFlow Dashboards for PHP. After reading this guide, you will be able to:

  1. Create tables from PHP Arrays.
  2. Change formatting of columns.
  3. Connect table to DataSource and display aggregated data.
  4. Link the table to other components in the dashboard.

Before reading this guide, please read the following documents:

  1. Installing RazorFlow Dashboards for PHP
  2. How to create Dashboards
  3. Add components to Dashboards
  4. Options in RazorFlow

Static Table

Hint

** Why use static tables? **

Static tables do not have the functionality that are present in tables powered by DataSources but they do have certain benefits:

  • Static tables can use PHP Arrays so it can work with some existing reporting code you’ve written.
  • Static tables can still be formatted, paginated and filtered by the user.

In this example, you create a simple static table.

  1. Create a new Dashboard and set the dashboard title

    Dashboard::setTitle("Fruit Nutrition Table");
    
  2. Create a TableComponent object and set the caption

    $table = new TableComponent();
    $table->setCaption("Fruit Nutrition");
    
  3. Add columns using TableComponent::addStaticColumn

    $table->addStaticColumn("Name");
    $table->addStaticColumn("Cost/kg");
    $table->addStaticColumn("Water Content");
    $table->addStaticColumn("Best Before");
    

    The argument passed to TableComponent::addStaticColumn is the name of the column.

  4. Add rows of data to the table using TableComponent::addStaticRow

    $table->addStaticRow (array("Apple", 2, 87, "2/3/2002"));
    $table->addStaticRow (array("Cherries", 4, 81, "2/3/2003"));
    $table->addStaticRow (array("Watermelon", 3, 92, "2/3/2002"));
    

    Each array passed to TableComponent::addStaticRow is a single row in the table. The array indices 0, 1, 2, 3 correspond to the columns “Name”, “Cost/kg”, “Water Content”, “Best Before”.

  5. Add this component to the Dashboard and render the Dashboard.

    Dashboard::addComponent($table);
    
    Dashboard::Render();
    

Complete working example of a static table

<?php
require "../razorflow.php";

Dashboard::setTitle("Table Component");

$table = new TableComponent();
$table->setCaption("Fruit Nutrition");
$table->addStaticColumn("Name");
$table->addStaticColumn("Cost/kg");
$table->addStaticColumn("Water Content");
$table->addStaticColumn("Best Before");
$table->addStaticRow (array("Apple", 2, 87, "2/3/2002"));
$table->addStaticRow (array("Cherries", 4, 81, "2/3/2003"));
$table->addStaticRow (array("Watermelon", 3, 92, "2/3/2002"));
Dashboard::addComponent($table);

Dashboard::Render();

Basic Formatting of Columns

Hint

Why format columns in a table?

Formatting columns in a table has the following benefits:

  • Some additional context (like units, currency, etc) is required for a table to fulfil it’s purpose.
  • You can make important columns stand out.

You can format the text inside columns to behave consistently, by making use of:

  • Number prefix and suffixes.
  • Formatting the time display

You will make the following changes to the previous example:

  • Format the currency of the first column by specifying appropriate symbol and setting the number of decimals.
  • Adding a percentage number suffix to the second column.
  • Format the “best before” column to show only the month and the year.

The steps taken to modify the table is:

  1. Modify the 2nd column

    $table->addArrayColumn("Cost/kg", array(
            'numberPrefix' => '$',
            'decimals' => 2
    ));
    

    The options being set for this column are:

    • 'numberPrefix'=>'$' - Show “$” before every number on the series labels.
    • 'decimals'=>2 - Show two points of decimal precision (like 32.00, 0.10, etc.)

    By passing these options, a number like 3.2 is displayed as “$3.20”.

  2. Modify the 3rd column

    $table->addArrayColumn("Water Content", array(
            'numberSuffix' => "%"
    ));
    

    The options being set for this column are:

    • 'numberSuffix'=>'%' - Since the water content is a percentage value, this option will display “%” after each number.
  3. Modify the 4th column

    $table->addArrayColumn("Best Before", array(
            'timeFormatString' => "M"
    ));
    

    The options being set for this column are:

    • timeFormatString=>'M' - ‘M’ is a shorthand notation for dates like “March 08”. But this can be customized to display data in any format required.

      To see more information, see Time Format Strings.

Complete working example of table with basic formatting.

<?php
require "../razorflow.php";

Dashboard::setTitle("Table Component");

$table = new TableComponent();
$table->setCaption("Fruit Nutrition");
$table->addStaticColumn("Name");
$table->addStaticColumn("Cost/kg", array(
	'numberPrefix' => '$',
	'decimals' => 2
));
$table->addStaticColumn("Water Content", array(
	'numberSuffix' => "%"
));
$table->addStaticColumn("Best Before", array(
	'timeFormatString' => "M"
));
$table->addStaticRow (array("Apple", 2, 87, "3/8/2002"));
$table->addStaticRow (array("Cherries", 4, 81, "6/2/2003"));
$table->addStaticRow (array("Watermelon", 3, 92, "3/25/2002"));
Dashboard::addComponent($table);

Dashboard::Render();

More Column Formatting

You can further apply other formatting to the text inside tables like:

  • Making text bold/italic
  • Alignment of text
  • Color of the text.

Advanced Formatting of columns

<?php
require "../razorflow.php";

Dashboard::setTitle("Table Component");

$table = new TableComponent();
$table->setCaption("Fruit Nutrition");
$table->addStaticColumn("Name", array(
	'textAlign' => 'center',
	'boldText' => true
));
$table->addStaticColumn("Cost/kg", array(
	'numberPrefix' => '$',
	'decimals' => 2,
	'textAlign' => 'right'
));

$table->addStaticColumn("Water Content", array(
	'numberSuffix' => "%",
	'textColor' => 'green',
	'italicText' => true,
	'boldText' => true
));
$table->addStaticColumn("Best Before", array(
	'timeFormatString' => "M"
));
$table->addStaticRow (array("Apple", 2, 87, "3/8/2002"));
$table->addStaticRow (array("Cherries", 4, 81, "6/2/2003"));
$table->addStaticRow (array("Watermelon", 3, 92, "3/25/2002"));
Dashboard::addComponent($table);

Dashboard::Render();

In this example, you will modify the code from the previous example. Particularly when columns are being added using TableComponent::addArrayColumn.

  1. Modify the first column

    $table->addArrayColumn("Name", array(
            'textAlign' => 'center',
            'boldText' => true
    ));
    

    The options being set for this column are:

    • 'textAlign'=>'center' - Center align the text.
    • 'boldText'`=>'true' - Make the text bold.
  2. Modify the second column

    $table->addArrayColumn("Cost/kg", array(
            'numberPrefix' => '$',
            'decimals' => 2,
            'textAlign' => 'right'
    ));
    

    The options being set for this column are:

    • 'numberPrefix'=> '$' - Display “$” before every value, indicating that it’s currency.
    • 'decimals'=>'2' - Show two points of decimals.
    • 'textAlign'=>'right' - Align the text to the right. This is the usual alignment for columns which show a measure of currency.
  3. Modify the third column

    $table->addArrayColumn("Water Content", array(
            'numberSuffix' => "%",
            'textColor' => 'green',
            'italicText' => true,
            'boldText' => true
    ));
    

    The options being set for this column are:

    • 'numberSuffix'=>'%' - Since the water content is a percentage value, this option will display “%” after each number.
    • 'textColor'=>'green' - Set a text color.
    • 'italicText'=>'true' - Display text as italics.
    • 'boldText'=>'true' - Display text as bold.

Summary: Formatting columns in table

To format the columns in your table, use the following options while adding columns using TableComponent::addArrayColumn or TableComponent::addColumn. Please see Options in RazorFlow for an introduction on how to use Options

Table Format Summary
Option Description
TableColumnOptions::$boldText Makes text bold
TableColumnOptions::$italicText Makes text italic
TableColumnOptions::$aggregate Whether to group by in an aggegrate
TableColumnOptions::$groupBy Whether to group by this column
TableColumnOptions::$aggregateFunction The aggregate type
TableColumnOptions::$primaryColumn Whether it is the primary column
TableColumnOptions::$displayAsRange Whether to display this column as ranged value
TableColumnOptions::$timeUnit If the value is being displayed as a time-based range, what kind of ranging should be used
TableColumnOptions::$sort Sort the column. Possible values - ASC and DESC
TableColumnOptions::$numberPrefix A string that is prefixed before every number
TableColumnOptions::$colorRange A ColorRange object to color the text
TableColumnOptions::$textColor Change the text color
TableColumnOptions::$textAlign Align the text inside the column
TableColumnOptions::$width Width of the column in pixels
TableColumnOptions::$name The name of the column
TableColumnOptions::$timeFormatString Format the time string. For instance, use ‘d’ for short date
TableColumnOptions::$type The type of data

Tables from a DataSource

You can also add Table Components which display data made available from a Database. To do this, you must link your table to a RazorFlow DataSource. Once you do that, each column in your Table Component can display data from a single column in that Database, or even an expression or aggregate of values in the columns.

Simple Table from DataSource

<?php
require "../razorflow.php";

Dashboard::setTitle("Table Component");

// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();

$table = new TableComponent();
$table->setCaption("Sales List");
$table->setDataSource($dataSource);
$table->addColumn("Sale ID", "InvoiceLineId", array(
	'width' => 81
));
$table->addColumn("Artist", "artist.Name", array(
	'width' => 80
));
$table->addColumn("Song Name", "track.Name");
$table->addColumn("Amount", "track.UnitPrice * Quantity", array(
	'numberPrefix' => '$',
	'width' => 60
));
$table->addColumn("Sale Date", "InvoiceDate");
Dashboard::addComponent($table);

Dashboard::Render();
  1. Create and configure a DataSource object. In this example, we will be using the RazorFlow Sample DataSource.

    $dataSource = RFUtil::getSampleDataSource();
    
  2. Create the TableComponent object, and set a caption

    $table = new TableComponent();
    $table->setCaption("Sales List");
    
  3. Use the TableComponent::addColumn function to add a column to the table:

    $table->addColumn("Sale ID", "InvoiceLineId", array('width' => 81));
    $table->addColumn("Artist", "artist.Name", array('width' => 80));
    $table->addColumn("Song Name", "track.Name");
    $table->addColumn("Amount", "track.UnitPrice * Quantity", array('numberPrefix' => '$', 'width' => 60));
    $table->addColumn("Sale Date", "InvoiceDate");
    

    The arguments to addColumn are:

    1. The title of the column (“Sale ID”, “Artist”, etc...)
    2. The SQL Expression to get the column data, for example:
      • InvoiceLineId - A column in the database
      • artist.Name - A column in the artist table.
      • track.UnitPrice*Quantity - A complex SQL Expression.
    3. An array of options passed with the shorthand notation:
      • 'width'=>81 - The width of the column in pixels
      • 'currency'=>'$' - Prefix numbers with the ‘$’ string, thus implying currency

Aggregating table records

In the previous example, each row in the table corresponded to a single row in the Database. However, sometimes it’s required that a row in the table is comprised of information aggregated across many rows. For example, you may wish to see the average amount earned from each artist.

For that, you’d do a SQL Query like this

SELECT artistName, AVG(saleAmount) FROM sales_table GROUP BY artistName;

Here, you are aggregating the expression saleAmount grouped by artistName, using the aggregation function AVG. You can do the same thing in the TableComponent. In this example, you will find out the total sales for each artist:

Aggregate a table

<?php
require "../razorflow.php";

Dashboard::setTitle("Table Component");

// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();

$table = new TableComponent();
$table->setCaption("Sales List");
$table->setDataSource($dataSource);
$table->addColumn("Artist", "artist.Name", array(
	'groupBy' => true
));
$table->addColumn("Amount", "track.UnitPrice * Quantity", array(
	'numberPrefix'=>'$',
	'width' => 100,
	'aggregate' => true
));
Dashboard::addComponent($table);

Dashboard::Render();
  1. First, create a new dashboard, and add a DataSource, and a TableComponent.

  2. Create a column for the artist. In this column, set the groupBy option to true. This flags that aggregated records should be grouped by this column’s value

    $table->addColumn("Artist", "artist.Name", array('groupBy' => true));
    
  3. Create a column for the total sales amount

    $table->addColumn("Amount", "track.UnitPrice * Quantity", array('numberPrefix' => '$', 'width' => 100, 'aggregate' => true));
    

    In this column, you’re specifying the title, and the expression like before. However, you’re passing three options:

    • 'numberPrefix'=>'$' - Sets the number prefix as ‘$’, signifying this column as currency.
    • 'width'=>100 - Set a width of 100px for this column
    • 'aggregate'=>true - This option enables aggregation on the column so this column becomes the expression being aggregated.

Sorting table records

In the previous example, the records are sorted by the artist name. Let’s say you are more interested in seeing which artists have had the highest level of sales. For that you need to sort the table in descending order of sales amounts.

Sort aggregated table

<?php
require "../razorflow.php";

Dashboard::setTitle("Table Component");

// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();

$table = new TableComponent();
$table->setCaption("Sales List");
$table->setDataSource($dataSource);
$table->addColumn("Artist", "artist.Name", array(
	'groupBy' => true
));
$table->addColumn("Amount", "track.UnitPrice * Quantity", array(
	'numberPrefix' => '$',
	'width' => 100,
	'aggregate' => true,
	'sort' => 'DESC'
));
Dashboard::addComponent($table);

Dashboard::Render();

For the previous table, while adding the “Amount” column, you simply have to set an extra option 'sort'=>'DESC':

$table->addColumn("Amount", "track.UnitPrice * Quantity", array('numberPrefix' => '$', 'width' => 100, 'aggregate' => true, 'sort' => 'DESC'));

Timestamp Ranges

Along with strings and numbers, you’ll be showing dates and times in your tables as well. If you add a column with a SQL Expression which corresponds to a DATETIME column in your database, the TableComponent will automatically detect it and display it as a neatly formatted date.

However, sometimes, you’re only interested in the month/year/etc of the column. In cases like these, you would consider using the timestamp as a Range.

Timestamp Ranges

<?php
require "../razorflow.php";

Dashboard::setTitle("Ranged Table Component");

// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();

$table = new TableComponent();
$table->setCaption("Sales List");
$table->setDataSource($dataSource);
$table->addColumn("Sale ID", "InvoiceLineId", array('width' => 50));
$table->addColumn("Artist", "artist.Name", array('width' => 80));
$table->addColumn("Song Name", "track.Name");
$table->addColumn("Amount", "track.UnitPrice * Quantity", array(
	'numberPrefix'=>'$',
	'width' => 60
));
$table->addColumn("Sale Date", "InvoiceDate", array(
	'displayAsRange' => 'time',
	'timeUnit' => 'year'
));
Dashboard::addComponent($table);

Dashboard::Render();
  1. First, create a Table Component like earlier

    $table = new TableComponent();
    $table->setCaption("Sales List");
    $table->setDataSource($dataSource);
    $table->addColumn("Sale ID", "InvoiceLineId", array('width' => 50));
    $table->addColumn("Artist", "artist.Name", array('width' => 80));
    $table->addColumn("Song Name", "track.Name");
    $table->addColumn("Amount", "track.UnitPrice * Quantity", array('currency' => true, 'width' => 60));
    
  2. Now, display the sale date as a yearly range

    $table->addColumn("Sale Date", "InvoiceDate", array('displayAsRange' => 'time', 'timeUnit' => 'year'));
    

    The arguments to this function are:

    • The column name - “Sale”
    • The SQL Expression which corresponds to a DATETIME column - InvoiceDate
    • Options:
      1. 'displayAsRange'=>'time' which tells the TableComponent that this should be displayed as a range.
      2. 'timeUnit'=>'year' show the time as a yearly range.

Aggregating By Timestamp Ranges

Once you have a timestamp column displayed as a range, you can aggregate other columns grouped by these values. For example, you can show how much was the total sales by month with this example:

Aggregated timestamp range table

<?php
require "../razorflow.php";

Dashboard::setTitle("Ranged Table Component");

// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();

$table = new TableComponent();
$table->setCaption("Sales List");
$table->setDataSource($dataSource);
$table->addColumn("Sale Date", "InvoiceDate", array(
	'displayAsRange' => 'time',
	'timeUnit' => 'month',
	'groupBy' => true,
	'sort' => 'ASC'
));
$table->addColumn("Amount", "track.UnitPrice * Quantity", array(
	'numberPrefix' => '$',
	'width' => 60,
	'aggregate'=> true
));
Dashboard::addComponent($table);

Dashboard::Render();
  1. Add the “Sale Date” column using addColumn like this

    $table->addColumn("Sale Date", "InvoiceDate", array(
            'displayAsRange' => 'time',
            'timeUnit' => 'month',
            'groupBy' => true,
            'sort' => 'ASC'
    ));
    

    Here, the arguments passed to addColumn are:

    • “Sale Date” - The title of the column
    • InvoiceDate - A SQL Expression which corresponds to a DATETIME column in the database.
    • A list of options:
      1. 'displayAsRange'=>'time' - Display the column ranged by time
      2. 'timeUnit'=>'month' - Show the dates ranged by month.
      3. 'groupBy'=>true - While aggregating other columns, group by this column.
      4. 'sort'=>'ASC' - Sort in ascending order of dates, so the rows are displayed in chronological order
  2. Add the “Amount” column using addColumn like this

    $table->addColumn("Amount", "track.UnitPrice * Quantity", array(
            'numberPrefix' => '$',
            'width' => 60,
            'aggregate'=> true
    ));
    

    This is similar to how you’ve created the column in the previous examples.

Note that you can also sort the table by using the “Amount” column by changing the code to pass the sort option to the “Amount” column

Sorting the aggregated timestamps

<?php
require "../razorflow.php";

Dashboard::setTitle("Ranged Table Component");

// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();

$table = new TableComponent();
$table->setCaption("Sales List");
$table->setDataSource($dataSource);
$table->addColumn("Sale Date", "InvoiceDate", array(
	'displayAsRange' => 'time',
	'timeUnit' => 'month',
	'groupBy' => true
));
$table->addColumn("Amount", "track.UnitPrice * Quantity", array(
	'numberPrefix'=>'$',
	'width' => 60,
	'aggregate'=> true,
	'sort' => 'DESC'
));
Dashboard::addComponent($table);

Dashboard::Render();

Start building interactive, mobile-friendly Dashboards in PHP. See DemosDownload