Filter Component Guide

A Filter Component is a component displayed on your dashboard which allows your users to filter the data in other components as per their requirements.

When you build an interactive dashboard, there’s a standard way components interact. Whether it be using autoLink or a custom interaction, a Source component makes a modification to data access rules of one or more target components.

Components like Table or Chart can be used to display data, but also act as a source of interaction. However, when you don’t need to display data but configure one or more parameters of other components, you need to use a Filter Component.

Auto Filter Component

An AutoFilterComponent is an easy to use and powerful tool. This populates the data of the filters with the values from the database. It might seem a little difficult to understand right now, but it will become clear with the use of some examples.

Filter Demo Table

Placing only a Filter Component on a dashboard kinda defeats the purpose. Because there’s no components to filter. So for the purpose of demonstration, you need to have a component which gets filtered in order for us to fully understand how the Filter Component works.

First, create the empty dashboard skeleton. See ../components for a detailed introduction

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

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

Dashboard::Render();

You are creating a DataSource from the sample datasource with the Invoice table.

Let’s create a Table Component to show a table of invoice data. See the Table Component Guide for a detailed introduction to the TableComponent

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
        'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

You are adding four columns:

  1. The billing country
  2. The billing city
  3. The date of the invoice.
  4. The amount of the invoice

Text Filter

The first filter you’ll try out is the text filter. You will allow your dashboard users to filter the sales by entering the city name in a text field. Create a dashboard and add the Filter Demo Table

For that you need to create a filter component, and configure it like this

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addTextFilter("Enter City", "Invoice.BillingCity");
Dashboard::addcomponent($filter);

Here, you are creating a Component Object of type AutoFilterComponent and setting the caption, datasource and adding it to the dashboard.

You’re also adding a Text Filter using the AutoFilterComponent::addTextFilter. The arguments to addTextFilter are :

  1. The label of the filter. This is the piece of text displayed on the dashboard for the user and should be informative of the purpose of the filter item.
  2. A Database expression on which the filter is applied. Here you are specifying a single column - Invoice.BillingCity, but it could have been a complex SQL Expression as well.

Note that Text Filter is case sensitive.

Now load the dashboard in your browser. In the filter component, enter a name of a valid city and click “Apply”. You will instantly see the records from only your specified city. You can verify that the filter works using the demo table.

Text Filter

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

Dashboard::setTitle("AutoFilter Component Example");

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

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addTextFilter("Enter City", "Invoice.BillingCity");
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Select Filter

The next filter you will be using is called the Select Filter. This filter presents the user a drop down list of options and the user can select any one of them.

In this example, you will be allowing the user to see the sales from a specified country. The list of countries are presented in a drop down menu.

Note

Before, we’d briefly mentioned that the Auto Filter Component is simple, yet powerful. Now, you’ll get a clear idea of why this is so.

Normally, while populating a drop down list of options, you’ll be required to get the list of items from the database, or from an external source, but with the Auto Filter Component, this list of values is inferred automatically.

Let’s create a filter component similar to how you did it earlier

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addDropdownFilter("Select Country", "Invoice.BillingCountry");
Dashboard::addcomponent($filter);

However, instead of calling addTextFilter, you are calling AutoFilterComponent::addDropdownFilter with two arguments:

  1. The label of the filter item. Here, it’s “Select Country”.
  2. The Expression on which the filter is applied. Since you are interested in letting your users filter by the country in which the invoice was filed, you specify the expression as Invoice.BillingCountry.

Load the dashboard in the browser. You’ll notice that the list of all the countries are already listed in the drop down. This is because RazorFlow extracted the list of all the possible countries, and populated the drop down! Now isn’t that cool?

Select Filter

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

Dashboard::setTitle("AutoFilter Component Example");

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

$filter = new AutoFilterComponent();
$filter->setCaption("Filter the Sales Table");
$filter->setDataSource($dataSource);
$filter->addSelectFilter("Select a Country", "Invoice.BillingCountry");
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Multi Select Filter

Now being able to let your users see the sales is any country is great! But what if they are interested in filtering the sales table to show invoices from four countries? For that, you need to use a Multi Select Filter.

Just like last time, create an AutoFilterComponent but instead of using AutoFilterComponent::addDropdownFilter, use AutoFilterComponent::addMultiSelectFilter like this

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addMultiSelectFilter("Select Countries", "Invoice.BillingCountry");
Dashboard::addcomponent($filter);

Just like in the previous example, you pass two parameters to AutoFilterComponent::addMultiSelectFilter:

  1. The label of the filter - “Select Countries”.
  2. The expression with which to apply the filter. Here you are filtering by country so the expression is Invoice.BillingCountry.

Reload the dashboard in your browser and you will be given a multi select filter. Select one or more items by holding “Control” and click “Apply”. Only sales from the selected cities are shown.

Multi Select Filter

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

Dashboard::setTitle("AutoFilter Component Example");

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

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales Table");
$filter->setDataSource($dataSource);
$filter->addMultiSelectFilter("Select Countries", "Invoice.BillingCountry");
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Numeric Range Filter

Next comes the Numeric Range Filter. This will allow your users to filter the sales table by ensuring that only sales with the sale amounts in a specific range are selected. We work with the Filter Demo Table as usual, and use AutoFilterComponent::addNumericRangeFilter to add a numeric range filter

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addNumericRangeFilter("Sale Amounts", "Invoice.Total");
Dashboard::addcomponent($filter);

You pass two parameters to addNumericRangeFilter:

  1. The caption for the filter
  2. The SQL Expression on which to apply the filter. For example, here, the Filter Component will automatically pick only records which contain Invoice.Total between the limits specified by the user.

Numeric Range Filter

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

Dashboard::setTitle("AutoFilter Component Example");

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

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales Table");
$filter->setDataSource($dataSource);
$filter->addNumericRangeFilter("Sale Amounts", "Invoice.Total");
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Time Range Filter

The Time Range Filter allows your users to filter the sales table by ensuring only the sales which are between specific dates are being shown in the table. We will work with the Filter Demo Table as usual, and will use AutoFilterComponent::addTimeRangeFilter to add the Time Range Filter

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addTimeRangeFilter("Sale Date", "Invoice.InvoiceDate");
Dashboard::addcomponent($filter);

You pass two parameters to addTimeRangeFilter:

  1. The caption of the filter.
  2. The SQL Expression on which to apply the filter. For example, here, the Filter Component will automatically pick only records which have Invoice.InvoiceDate to be between a specified start and end date.

Time Range Filter

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

Dashboard::setTitle("AutoFilter Component Example");

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

$filter = new AutoFilterComponent();
$filter->setCaption("Filter Sales Table");
$filter->setDataSource($dataSource);
$filter->addTimeRangeFilter("Sale Date", "Invoice.InvoiceDate");
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Conditional Filter Component

Text Condition

Text Condition

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

Dashboard::setTitle("Conditional Filter Example");

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

$filter = new ConditionFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addTextCondition("City", "Invoice.BillingCity={{value}}");
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Boolean/Checkbox Conditions

Boolean/Checkbox Condition

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

Dashboard::setTitle("Conditional Filter Example");

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

$filter = new ConditionFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addCheckboxCondition("High Value Invoice", "Invoice.Total > 15");
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Select Condition

Select Condition

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

Dashboard::setTitle("Conditional Filter Example");

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

$filter = new ConditionFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addSelectCondition("Sale Amount", 
							array(
								'Low',
								'Medium',
								'High'
							),
							array(
								"Invoice.Total < 5",
								"Invoice.Total >= 5 AND Invoice.Total < 15",
								"Invoice.Total >= 15"
							)
);
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Multi Select Conditions

Multi Select Condition

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

Dashboard::setTitle("Conditional Filter Example");

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

$filter = new ConditionFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addMultiSelectCondition("Sale Amount", 
							array(
								'Low',
								'Medium',
								'High'
							),
							array(
								"Invoice.Total < 5",
								"Invoice.Total >= 5 AND Invoice.Total < 15",
								"Invoice.Total >= 15"
							)
);
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Time Range Conditions

Time Range Condition

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

Dashboard::setTitle("Conditional Filter Example");

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

$filter = new ConditionFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addTimeRangeCondition("Sales Between", 
	"Invoice.InvoiceDate > {{start}} AND Invoice.InvoiceDate < {{end}}",
	array("1/1/2007", "1/1/2011"));
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

Numeric Range Conditions

Numeric Range Conditions

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

Dashboard::setTitle("Conditional Filter Example");

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

$filter = new ConditionFilterComponent();
$filter->setCaption("Filter Sales");
$filter->setDataSource($dataSource);
$filter->addNumericRangeCondition("Sales Between", 
	"Invoice.Total > {{start}} AND Invoice.Total < {{end}}",
	array(0, 20));
Dashboard::addcomponent($filter);

$sales = new TableComponent();
$sales->setCaption("Sales Table");
$sales->setDataSource($dataSource);
$sales->addColumn("Billing Country", "Invoice.BillingCountry");
$sales->addColumn("Billing City", "Invoice.BillingCity");
$sales->addColumn("Date", "Invoice.InvoiceDate");
$sales->addColumn("Amount", "Invoice.Total", array(
	'numberPrefix' => '$ '
));
Dashboard::addComponent($sales);

$filter->addFilterTo($sales);

Dashboard::Render();

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