RadPivotGrid for WinForms gives you the ability to visualize and monitor your measured key performance indicators (KPI) directly from your online analytical processing (OLAP) cube. When paired with the RadPivotFieldList control, you can give your users complete control over the dimensions displayed in the RadPivotGrid. In this blog post, I will show you how to how to connect to an OLAP cube then walk you through defining a KPI visualization from scratch by utilizing RadPivotGrid and RadFieldList. Next I’ll show you how to define a starting state for the in code.
Create a new RadControls for Windows Forms Application, name the project RadPivotGridKPI, when shown the Telerik Project Configuration Wizard, select Telerik.WinControls. Once the project is loaded, open the designer for Form1.cs and expand the form so there is more screen real estate to work with. Next drag an instance of the RadPivotFieldList control to the design surface, set its Dock property to Right, then drag an instance of the RadPivotGrid to the design surface and set its Dock property to fill. Then, use the SmartTag on the RadPivotFieldList to associate the field list with the RadPivotGrid as in the following screenshot:
Figure 1 - Associating the RadPivotFieldList with the RadPivotGrid
In this example we will be connecting to the AdventureWorks OLAP cube exposed on the Telerik demo site. Open the code view of Form1 and add the following method:
private void SetupAdomdConnection()
{
AdomdDataProvider provider = new AdomdDataProvider();
AdomdConnectionSettings settings = new AdomdConnectionSettings();
settings.Cube = "Adventure Works";
settings.Database = "Adventure Works DW 2008R2";
settings.ConnectionString = "Data Source=http://demos.telerik.com/olap/msmdpump.dll;Catalog=Adventure Works DW 2008R2";
provider.ConnectionSettings = settings;
provider.AggregatesPosition = Telerik.Pivot.Core.PivotAxis.Rows;
this.radPivotGrid1.DataProvider = provider;
provider.Refresh();
provider.DeferUpdates = false;
}
Figure 2 - Method used to connect to the AdventureWorks OLAP cube
In addition to making the connection to the OLAP cube, this method also defines that the aggregates are to be displayed as rows in the RadPivotGrid. The datasource is also then assigned to the PivotGrid on the form. In the constructor of the Form, after InitializeComponent, add the following line of code to make the connection to the cube when the application is started:
SetupAdomdConnection();
Figure 3 – Call the connection method in the constructor of Form1
Now when the application is run, an indicator will display showing feedback that the connection to the OLAP cube is being made. Once loaded, the RadPivotField list control displays all available fields, KPI’s and measures that are provided from the cube. Let’s go ahead and define a useful visualization. Let’s first add the KPI’s that we want to visualize in our RadPivotGrid. Drill into the KPI item and navigate to Financial Perspective, Grow Revenue, then expand Internet Revenue. Check each of the checkboxes under this item. The RadPivotGrid will now update itself automatically based on these selections
Figure 4 – Adding KPI measures to the RadPivotGrid
Next we will want to define the row grouping and column groupings. In this case we would like to visualize the dimension of internet sales KPIs by Product Category for each Fiscal Year. To set this up, drill into the Date field in the RadPivotFieldList control, then select Fiscal, then click on and hold the Date.Fiscal Year text and drag it to the Column Labels box. Similarly to visualize this data with respect to Product Category, drill into the Product Field, select the More Fields item then drag and drop the Category field to the Row Labels box. Re-arrange the order in the Row Labels box by dragging the Category field above the Values field in the box if needed. We now have our visualization.
Figure 5 – Final KPI data visualization
It’s not every user that wants to start each visualization from scratch. It is beneficial that you know how to give the user a starting state for the KPI visualization. To do this we will establish the row, column and aggregate fields in code. Open the Form1 code-behind and modify the SetupAdomdConnection as follows, please note the square braces and spacing in the field name values are required:
private void SetupAdomdConnection()
{
AdomdDataProvider provider = new AdomdDataProvider();
AdomdConnectionSettings settings = new AdomdConnectionSettings();
settings.Cube = "Adventure Works";
settings.Database = "Adventure Works DW 2008R2";
settings.ConnectionString =
"Data Source=http://demos.telerik.com/olap/msmdpump.dll;Catalog=Adventure Works DW 2008R2";
provider.ConnectionSettings = settings;
provider.AggregatesPosition = Telerik.Pivot.Core.PivotAxis.Rows;
provider.RowGroupDescriptions.Add(new AdomdGroupDescription()
{ MemberName = "[Product].[Category]" });
provider.ColumnGroupDescriptions.Add(new AdomdGroupDescription()
{ MemberName = "[Date].[Fiscal Year]" });
provider.AggregateDescriptions.Add(new AdomdAggregateDescription()
{ MemberName = "[Measures].[Internet Sales Amount]" });
provider.AggregateDescriptions.Add(new AdomdAggregateDescription()
{ MemberName = "[Measures].[Internet Revenue Goal]" });
provider.AggregateDescriptions.Add(new AdomdAggregateDescription()
{ MemberName = "[Measures].[Internet Revenue Status]" });
provider.AggregateDescriptions.Add(new AdomdAggregateDescription()
{ MemberName = "[Measures].[Internet Revenue Trend]" });
this.radPivotGrid1.DataProvider = provider;
provider.Refresh();
provider.DeferUpdates = false;
}
Figure 6 – Defining the initial state of the visualization in code
Now when the application is run again, you will begin with the visualization as displayed in Figure 5.
In this blog post we were able to build a KPI visualization from scratch using nothing more than the RadPivotGrid, the RadPivotFieldList and an OLAP cube connection. We also demonstrated how to define a beginning state in code for the visualization in the RadPivotGrid. These powerful controls are both easy to implement and intuitive for our users to use.
Carey Payette is a Senior Software Engineer with Trillium Innovations (a Solliance partner), an ASPInsider, a Progress Ninja, a Microsoft Certified Trainer and a Microsoft Azure MVP. Her primary focus is cloud integration and deployment for the web, mobile, big data, AI, machine learning and IoT spaces. Always eager to learn, she regularly tinkers with various sensors, microcontrollers, programming languages and frameworks. Carey is also a wife and mom to three fabulous boys.