Introducing KPIs in FlexPivot for WinForms

Pivot grids read 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 FlexPivot - a powerful analysis tool that is similar to Excel pivot tables. It presents a specific view of data based on the fields selected in the pivot table's field list.

Fortifying pivot grid, in the latest release of ComponentOne, introduces support for Key Performance Indicator (KPI) in FlexPivot for WinForms. Here, we'll discuss what KPIs are and walk you through how to visualize them in ComponentOne’s pivot grid.

The feature allows developers to show KPI data information in FlexPivot. This will help them to show KPI trends, status, (and other information) in the grid view (Pivot table).

Through this feature, FlexPivot will automatically be able to detect the presence of KPI type data in data sources. Once KPI fields are added to collection it will be able to present the status, trends, and goals related to that field.

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 the goal.

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

KPIs in Pivot Grid

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

Let’s see how to connect the pivot grid to an OLAP Cube and display KPIs.

Connecting Pivot Grid to an OLAP Cube

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

Pivot grid exposes a method ‘ConnectCube’ that helps connect the pivot grid 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
//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 the demonstration, the above example connects FlexPivot to the ‘AdventureWorks’ OLAP Cube.

Displaying KPIs in the pivot grid

Once the pivot grid is connected to the cube, it will automatically identify the KPI information. Assuming, you have already added data to pivot grid and have used the following code, we shall focus on how to show KPI fields in pivot grid.

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 pivot grid’s FlexPivotEngine.ValueFields collection to show them in the pivot grid.

//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 pivot grid.

Modifying KPI Graphics in the Pivot Grid

The KPI fields Status and Trend appear with default graphics ‘Cylinder’ and ‘StandardArrow’ respectively. However, the pivot grid 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 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 change it’s graphics to the one chosen in a combobox. Therefore, to change graphics for all fields in the pivot grid, just pass all fields to the above method:

Scan(c1FlexPivotPage1.FlexPivotEngine.Fields);

In this article, we discussed what Key Performance Indicators (KPIs) are, how to visualize them in pivot grid 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 application