Display KPIs in FlexPivot

Introducing KPIs in FlexPivot for WinForms

PivotGrid reads multi-dimensional data sources like OLAP Cube and enables visualizing the data through different analysis views.

To facilitate reading the multi-dimensional data sources, ComponentOne provides a powerful analysis tool, FlexPivot, that, similar to excel pivot tables, presents a specific view of data based on the fields selected in the pivot table field list.

Fortifying PivotGrid, the latest release of ComponentOne introduces support for Key Performance Indicator (KPI) in FlexPivot for WinForms. This blog will discuss what KPIs are and walk you through how to visualize them in ComponentOne’s PivotGrid.

Key Performance Indicators (KPIs)

KPIs are data metrics present in an On-Line Analytical Processing (OLAP) Cube, a multi-dimensional data structure. They help evaluate the success of a business by measuring the progress towards set goals. Typically, a KPI consists of status, trend, and goal.

The above image shows Categories KPI fields: Trends and Status for three financial years.

KPIs in PivotGrid

When connected to an OLAP Cube, FlexPivot, being an advanced processing control, will automatically recognize any KPI information and render it accordingly in PivotGrid.

(Right) Pivot not detecting KPI fields, (Left) Pivot detecting KPI fields

Let’s see how to connect PivotGrid to an OLAP Cube and display KPIs.

Connecting PivotGrid to an OLAP Cube

Connecting FlexPivot to an OLAP Cube, a multi-dimensional database, is just a one-step process.

PivotGrid exposes a method ‘ConnectCube’ that helps connect it to a cube data source.

//Set up data-connection string
string connectionString = @"Data Source=http://ssrs.componentone.com/OLAP/msmdpump.dll;Provider=msolap;Initial Catalog=AdventureWorksDW2012Multidimensional";
string cubeName = "Adventure Works";

//Connect Cube Data to FlexPivotPage
c1FlexPivotPage1.FlexPivotPanel.ConnectCube(cubeName, connectionString);

For demonstration, the above example connects FlexPivot to the ‘AdventureWorks’ OLAP Cube.

Displaying KPIs in PivotGrid

Once the PivotGrid is connected to the cube, it will automatically identify the KPI information. Assuming you have already added data to the PivotGrid and have used the following code, it's time to focus on how to show KPI fields in the PivotGrid.

var fp = c1FlexPivotPage1.FlexPivotEngine;
fp.BeginUpdate();
fp.ColumnFields.Add("Date.Fiscal Year");
fp.RowFields.Add("Category ");
fp.EndUpdate();

For each KPI, there will be a field for Status and Trend. To add these KPI fields from OLAP Cube, use PivotGrid’s FlexPivotEngine.ValueFields collection to show them in the PivotGrid.

//Show KPI Field data in the FlexPivotPage using ValueFields
var fp = c1FlexPivotPage1.FlexPivotEngine;
fp.BeginUpdate();
fp.ValueFields.Add("Internet Revenue Trend");
fp.ValueFields.Add("Internet Revenue Status");
fp.EndUpdate();

The code above adds Internet Revenue KPI Trend and Status to the PivotGrid.

Modifying KPI graphics in PivotGrid

The KPI fields Status and Trend appear with default graphics ‘Cylinder’ and ‘StandardArrow’. However, PivotGrid enables the developer to change the default graphics and use graphics according to requirements.

The graphics associated with each KPI can be specified using KpiGraphics enumeration. Therefore, to visualize KPI values with custom graphics, set the KpiGraphics property for the Status and Trend field.

void Scan(IEnumerable<C1FlexPivotField> list)
{
foreach (var field in list)
       {
           if (field is C1KpiField)
              (field as C1KpiField).KpiGraphics = (KpiGraphics)Enum.Parse(typeof(KpiGraphics), KpiGraphicsCombo.SelectedValue.ToString());
              if (field is C1CubeField)
                  Scan((field as C1CubeField).SubFields);
}
}

The above method 'Scan' traverses a list of fields and if the field is a KPI field, then changes its graphics to the one chosen in a combobox. Therefore, to change graphics for all fields in PivotGrid, pass all fields to the above method.

Scan(c1FlexPivotPage1.FlexPivotEngine.Fields);

In this article, we discussed Key Performance Indicators (KPIs), how to visualize them in PivotGrid for WinForms (FlexPivot), and how to modify the built-in graphics for KPI field values.

I hope you find this feature useful in your application.

Download the application here.