KPIComponent

The KPI or the “Key Performance Indicator” - in the broadest sense, is simply a number which changes with time. So when a dashboard is loaded, the KPI value is calculated and displayed. However, a well-defined and chosen KPI component can be a fantastic and quick way of gauging the performance of particular aspects of your organization.

A great example of a well chosen KPI is the speedometer in your car. It satisfies the following requirements:

  1. Knowing the value of the KPI is extremely important.
  2. It is large and is quickly readable.
  3. Historic data is not as important. You don’t worry too much how fast you were going ten minutes ago as much as your current speed.
  4. Making the speedometer a gauge can make the process of scanning the KPI even quicker. Because you’re only looking at the visual proximity of the gauge’s needle.

Static KPI

The first KPI Dashboard that we are going to build is going to contain a KPI with a static value

$kpi = new KPIComponent();
$kpi->setCaption('First KPI');
$kpi->setCurrentValue(42);

Dashboard::addComponent($kpi);

You are creating a KPI Component object $kpi which is now the Component Object. Then you call setCaption to set thecaption for the KPI. Then you use the KpiComponent::setCurrentValue to set the current value to a PHP number 42.

First KPI

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

Dashboard::setTitle ("KPI Component");

$kpi = new KPIComponent();
$kpi->setCaption('First KPI');
$kpi->setStaticCurrentValue(42);

Dashboard::addComponent($kpi);

Dashboard::Render();

Setting previous values

If you have some previous values of the KPI available, you can show it by setting the “Past Values” of the KPI Component using the KPIComponent::setPastValues method:

$kpi->setPastValues(array(12, 2, 55, 23, 12));

KpiComponent::setPastValues accepts an array of PHP numbers and displays the history in a spark line chart.

Add previous values

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

Dashboard::setTitle ("KPI Component");

$kpi = new KPIComponent();
$kpi->setCaption('First KPI');
$kpi->setStaticCurrentValue(42);
$kpi->setStaticPastValues(array(12, 2, 55, 23, 12));

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

Counting number of distinct items

Now, you’ll be creating your first KPI Component which is driven by a DataSource. You’ll often have to write KPIs to count the number of distinct items in a Database. For example in our RazorTunes Sample Database, if you had to count - say - the number of artists in the table

Previously, you’d have written a SQL Query like

SELECT COUNT(DISTINCT(artist.ArtistId)) as result FROM Table;

But in the KPI Component, it’s as easy as calling the KPIComponent::countDistinct function with the value expression.:

$artistCount->countDistinct("artist.ArtistId");

Here’s the full example on how to use the countDistinct function. .. rftest:

:path: dbkpi1
:correction: ../../
:title: Counting number of distinct items

Single Value from a Database

A KPI Component can show you a single value from a Database. For this, you need to connect to a DataSource and use the KPIComponent::setValueExpression method. For example, let’s say you want to get the value of the latest sale amount made in the sales table. You would normally write a SQL Query like this

SELECT amount FROM Sales ORDER BY saleTime DESC LIMIT 1;

Here, amount is the Value Expression - the value expression of a KPI is a SQL Expression, which can be a simple column, or a more complex SQL Expression involving multiple columns. The value expression is use to determine the value.

This would take all the Sales records, sort in reverse chronological order, and take only the first row from the Database. You can produce the identical effect by using KPIComponent::setValueExpression to set how the values are determined, and then use KPIComponent::sortOn to sort in decreasing order of the time

$lastSaleAmount->setValueExpression("invoiceline.InvoiceId");
$lastSaleAmount->sortOn("invoiceline.InvoiceId", "DESC");

Note that it’s not necessary to use sortOn every time you call setValueExpression but it has been used here becuse it’s required.

Absolute value of a table record

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

Dashboard::setTitle("KPI Component");

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

$lastSaleAmount = new KPIComponent();
$lastSaleAmount->setCaption("Last Sale Id");
$lastSaleAmount->setDataSource($dataSource);
$lastSaleAmount->setValueExpression("invoiceline.InvoiceId");
$lastSaleAmount->sortOn("invoiceline.InvoiceId", "DESC");

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

Aggregated values of a table record

If you want to perform aggregation - where values of multiple records are grouped together and collated - you can do that with KPIComponent::setValueExpression as well.

For example, if you want to find out the total sales of the latest invoice, you might write a SQL Query like

SELECT SUM(amount) FROM Sales GROUP BY (invoiceID) ORDER BY saleTime DESC LIMIT 1;

You can build a KPI around this concept, and this can be accomplished by varying parameters to setValueExpression like this

$totalSales->setValueExpression("track.UnitPrice * Quantity", array(
        'aggregate' => true,
        'aggregateFunction' => 'SUM',
        'groupBy' => 'invoiceline.InvoiceId',
        'numberPrefix' => '$'
));

In this code, you are calling setValueExpression with the value expression, and an array of options :

  1. 'aggregate'=>true - A flag telling the system to aggregate values.
  2. 'aggregateFunction'=>'SUM' - an option to specify the aggregation function, and determine how the records will be aggregated.
  3. 'groupBy'=>'invoiceline.InvoiceId' - specify by what column the records will be grouped.
  4. 'numberPrefix'=>'$' - Display a '$' before each number because the number is monetary currency.

The full example to set up and configure a KPI with an aggregated value is given below:

Aggregated values of table record

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

Dashboard::setTitle("KPI Component");

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

$totalSales = new KPIComponent();
$totalSales->setCaption("Total Sales");
$totalSales->setDataSource($dataSource);
$totalSales->setValueExpression("track.UnitPrice * Quantity", array(
	'aggregate' => true,
	'aggregateFunction' => 'SUM',
	'groupBy'=>'invoiceline.InvoiceId',
	'numberPrefix' => '$'
));
Dashboard::addComponent($totalSales);

Dashboard::Render();

Setting a timestamp expression

It is often required to have a KPI that changes with time. It useful to not only show the current value of the KPI, but also to show the previous few values. The KPI Component can effectively display the previous few values by using a “Spark Line” - which is a very small chart which can be used to identify the trend.

To do this, you simply need to call an extra function KPIComponent::setTimestampExpression with a column in your DataSource which represents a DATETIME field. For instance, if you were trying to build a dashboard for the latest few sales, you’d write a query like

SELECT Invoice.Total FROM Invoice ORDER BY Invoice.InvoiceDate DESC LIMIT 15;

You can achieve a similar effect by using the KPIComponent::setTimestampExpression and the KPIComponent::setValueExpression

$kpi->setValueExpression("Invoice.Total", array(
        'numberPrefix' => '$'
));
$kpi->setTimestampExpression("Invoice.InvoiceDate");

This will show the last 15 records. You can always change the number of records it will show by changing the KPIComponentOptions::$limitTo parameter like this

$kpi->setOption('limitTo', 15);

An example on how to use this is given below:

Setting a timestamp expression

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


Dashboard::setTitle("KPI Component");
// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();
$dataSource->setSQLSource("Invoice");

$kpi = new KPIComponent();
$kpi->setCaption("Latest Sale");
$kpi->setDataSource($dataSource);
$kpi->setValueExpression("Invoice.Total", array(
	'numberPrefix' => '$'
));
$kpi->setTimestampExpression("Invoice.InvoiceDate");
Dashboard::addComponent($kpi);

Dashboard::Render();

Aggregating by time ranges

A Key Performance Indicator might also be required to show how well a value did in a particular duration of time (say a month, year, etc). For that you can use the KPI Component to aggregate the values within a range of time.

For example, if you wanted to show a similar KPI and show how much was the total sale in the last month, and the months before, you can do it like this

$kpi->setValueExpression("Invoice.Total", array(
        'aggregate' => true,
        'currency' => true
));
$kpi->setTimestampExpression("Invoice.InvoiceDate", array(
        'timeUnit' => 'month'
));

Again, you’re specifying an aggregate option while calling setValueExpression but instead of setting a groupBy as you did earlier, you are setting the timestamp expression.

The values are automatically grouped according to the time ranges. In this example, it is being grouped monthly (which is set by the timeUnit option).

Aggregating by time

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

Dashboard::setTitle("KPI Component");

// Get the sample DataSource
$dataSource = RFUtil::getSampleDataSource();
$dataSource->setSQLSource("Invoice");

$kpi = new KPIComponent();
$kpi->setCaption("Monthly sales");
$kpi->setDataSource($dataSource);
$kpi->setValueExpression("Invoice.Total", array(
	'aggregate' => true,
	'numberPrefix'=>'$'
));
$kpi->setTimestampExpression("Invoice.InvoiceDate", array(
	'timeUnit' => 'month'
));
Dashboard::addComponent($kpi);

Dashboard::Render();

In case you may need to aggregate by time, and want to show only the latest result (in this example - you want to show only the current month’s records), you can do so by setting the showLatestOnly option

$kpi->setOption('showLatestOnly', true);

Show value from a complex SQL Query

If your KPI Value is derived from a more complex SQL Query, then you can use KPIComponent::setValueFromSQLQuery

$kpi->setValueFromSQLQuery("SELECT my_kpi_value FROM my_sql_table WHERE (conditions) LIMIT 1");

This query is evaluated and the result is used to display the value of the KPI. However, there are certain things you need to keep in mind while using this function:

  1. The Query will be executed as provided, and will not be modified. This might have some security considerations
  2. KPIs with values from SQL Query cannot be filtered, or have a drill down from another component.
  3. If you do not add a “LIMIT” Clause to the Query, and there are too many rows, PHP might run out of memory
  4. The first column of the first row will be taken as the KPI Value. If you wish to override this, please set the KPIValueFromQueryOptions::$valueColumn option.

Here’s a SQL Query that shows the average sales for the top 5 countries with the most sales

"SELECT AVG(country_sales) as avg_sales FROM
(SELECT SUM(total) as country_sales, BillingCountry FROM invoice
        GROUP BY BillingCountry ORDER BY country_sales DESC LIMIT 5)
AS subquery;"

The PHP Code to set the KPI with this value is

$totalSales->setValueFromSQLQuery("SELECT AVG(country_sales) as avg_sales FROM
        (SELECT SUM(total) as country_sales, BillingCountry FROM invoice
                GROUP BY BillingCountry ORDER BY country_sales DESC LIMIT 5)
        AS subquery;", array(
        'valueColumn' => 'avg_sales'
));

Here’s a full working example showing setValueFromSQLQuery in action:

Aggregating by time

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

Dashboard::setTitle("KPI Component");

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

$totalSales = new KPIComponent();
$totalSales->setCaption("Average Sales in Top 5 Countries");
$totalSales->setDataSource($dataSource);
$totalSales->setValueFromSQLQuery("SELECT AVG(country_sales) as avg_sales FROM 
	(SELECT SUM(total) as country_sales, BillingCountry FROM invoice 
		GROUP BY BillingCountry ORDER BY country_sales DESC LIMIT 5)
    AS subquery;", array(
    	'valueColumn' => 'avg_sales'
));

Dashboard::addComponent($totalSales);

Dashboard::Render();

Summary: Formatting KPI Component

To format the KPI component, use the following options. Please see Options in RazorFlow for an introduction on how to use Options

KPI Component Format Summary
Option Description
KPIComponentOptions::$showLatestOnly Should the KPI component show only the latest value?
KPIComponentOptions::$lowerLimit If the KPI is a guage, this value sets the lower limit of the guage
KPIComponentOptions::$upperLimit If the KPI is a guage, this value sets the upper limit of the guage
KPIComponentOptions::$numberDecimals The number of decimals to show
KPIComponentOptions::$numberPrefix A string that is prefixed before every number
KPIComponentOptions::$colorRanges Object which will be used to change the colors in the gauge’s dial

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