How to Organize and Edit Data in a WPF Pivot Grid
Data analysis is a primary need to evaluate large amount of data and derive conclusions, which provide an opportunity to plan future tasks efficiently. A commonly prevalent example could be analyzing past year sales for a multinational firm, which would comprise of millions of records. A simple review of raw data would never let you land to conclusions. Data must be organized and figures must be aggregated in a manner that they answer a few questions related to data analysis such as quarterly profit booked over the past year or which country offered the highest sales.
FlexPivot offered by ComponentOne Studio for WPF empowers you to organize and aggregate millions of records in multiple ways to analyze the data in its best possible manner. The data analysis results can be presented as a grid, chart or report to meet the user's convenience. FlexPivot documentation can let you deep dive into understanding the architecture and powerful feature set offered by the control. Here is a quick view of the FlexPivot control to get you started.
In this blog we will focus on understanding the Range Grouping feature offered by FlexPivot. Being a summarization product, FlexPivot by default groups the data to calculate meaningful aggregates. The range grouping feature further enhances the summarization and aggregation by providing the users with options to group string, numeric, date time or any other type of data using ranges defined on a criteria. The numeric data can be grouped into ranges such as 100 to 200, 200 to 300 and so on. The date ranges can be created for year, month, quarter or week. String values can be grouped using alphabetical grouping. Lastly, range grouping lets you group values by defining a custom logic, leaving all the range grouping criteria at your power.
FlexPivot supports these types of range grouping by offering the RangeType enumeration and RangeType property of RangeInfo class. The Grouping topic in the documentation elaborates the details of all these API members.
To understand the range grouping feature, its need, and implementation using C#, let's work on a real-time scenario.
Use Case
Covid-19 hit the world in 2020. Since then, all the progress made to deal with this deadly virus has been deeply integrated because all the relevant data has been collected and analyzed by different organizations for various purposes. One example is analyzing the rising or declining number of cases to arrange for all the medical resources required for the treatment of patients. Another example is comparing the available resources against the population to handle a critical situation smoothly, monitoring the mortality rate to understand the severity of the situation, and implementing appropriate measures to control the further spread of the disease. Over time, Coronavirus vaccinations were developed and distributed globally to vaccinate people, adding another dimension to the Covid-19 data analysis. We will consider working on this aspect to see how data analysis can help the government and pharmaceutical industry plan the vaccination programs and manage the vaccine availability in their country.
Consider this limited set of rows retrieved from an otherwise large raw data fetched from OurWorldInData that collects Covid-19 data worldwide from trustful resources.
You can observe two different types of data here. On the left is the number of people vaccinated in different countries daily. On the right is the number of people vaccinated in different age groups in various countries daily. A few questions that can contribute to the evaluation of current vaccination status worldwide if we group and aggregate a complete set of such raw data appropriately are listed below:
- How many people have been vaccinated worldwide?
- What was the timespan of vaccinating this number of people?
- Which age group of the population has been vaccinated most or least in each country?
The answers to the above questions can help pharmaceutical companies to plan their productions. It can simultaneously help governments to timely book the required number of vaccines. This is a very basic analysis of such powerful data, and it can answer many more questions.
We will confine ourselves to finding ways to organize and aggregate this data using FlexPivot and its range grouping feature so that just a glimpse can answer all the above questions at the data. Here is a quick look at the final data representation views created using FlexPivot, which can very easily answer the questions stated above:
FIRST VIEW: Analyzing total number of people vaccinated globally
The screenshot below clearly states the total number of people vaccinated globally, through the span of two years in a quarterly manner, till August 2021:
SECOND VIEW: Analyzing total number of people vaccinated as per age groups in different countries
The following screenshot depicts the number of people vaccinated among different countries in specific age groups till August 2021:
The sections ahead will guide you in creating both these data representation views from raw data using FlexPivot and its range grouping features.
Getting Started with FlexPivot
To start working with FlexPivot control, you need to create a .Net Core WPF application and install the C1.WPF.Pivot package using Nuget Package Manager.
After installing the required package, the WPF controls are added to the toolbox. Drag-drop the FlexPivotPage control from the toolbox onto the MainWindow.xaml, the default window added to the WPF application. For further details, you may refer to the Quickstart topic in the documentation.
Binding FlexPivot to Data
The FlexPivot control can be bound to data, either by binding it to a datatable or to a data engine. Refer to the following documentation link which describes the same. For implementing the above scenario we have downloaded the relevant csv files from OurWorldInData website and imported the information to create a database. Hence, we will be binding FlexPivot control to a relational database, as depicted in code below:
//Establish connection to database and fetch data
OleDbConnection oconn = new OleDbConnection();
oconn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=VaccinationData.mdb";
var da = new OleDbDataAdapter("select * from vaccinations", oconn);
var vaccinationSet = new DataTable();
da.Fill(vaccinationSet);
da.SelectCommand = new OleDbCommand("select * from [vaccinations-by-age-group]", oconn);
var vaccinationSubset = new DataTable();
da.Fill(vaccinationSubset);
//Bind the datatable to FlexPivot's C1PivotEngine
var fpEngine = flexPivotPage1.FlexPivotPanel.C1PivotEngine;
fpEngine.DataSource = vaccinationSet.DefaultView;
After binding the FlexPivot control to a datasource, you will observe all datasource fields start displaying in the FlexPivotPanel. To understand FlexPivotPanel and other elements that comprise the architecture of FlexPivot, you may refer to the following documentation link.
The image below depicts the populated fields in FlexPivotPanel:
Add FlexPivotFields to Create a View
This section will demonstrate how to add PivotFields to FlexPivotPage either as a RowField, ColumnField, or ValueField to generate the view. It will also show how to apply Field settings to implement the range grouping feature. To design the views described above, we need to apply range grouping to multiple fields. The sections ahead will describe each of these in detail.
Add PivotFields to show basic information
The basic information to be displayed in the FIRST VIEW is country name and number of people vaccinated in each country. To accomplish the same, we will add the location field as a RowField and people_fully_vaccinated field as a ValueField. We would also set Caption and Width properties for these fields to display the information in an appropriate format. Here is the code snippet implementing the same:
//Bind FlexPivotPage to data
fpEngine.DataSource = dt.DefaultView;
//Add and configure RowField
fpEngine.RowFields.Add("location");
fpEngine.RowFields[0].Caption = "Country";
//Add and configure ValueField
fpEngine.ValueFields.Add("people_fully_vaccinated");
fpEngine.ValueFields[0].Caption = "People fully Vaccinated";
fpEngine.ValueFields[0].Width = 200;
The generated view displayed in FlexPivotGrid would look like:
From the above image, you can observe how FlexPivot has summarized the raw data to show an entry corresponding to each country in data along with the number of people vaccinated in that country, by adding values from the people_fully_vaccinated field, from all rows that correspond to a specific country. Hence, it implements an implicit grouping and aggregation based on the type of data.
Group Countries by Region
The view generated above does not group the countries. This section will demonstrate how to use range grouping to group these countries into regions to have a more summarized form of data and aggregates.
Countries can also be grouped into regions by FlexPivot if we have a database field that saves regions corresponding to each country. In this case, we have a field named continent that serves this purpose, hence simply adding this field as a RowField with group countries and aggregates by region. The sample code below implements the same:
//Bind FlexPivot to data
fpEngine.DataSource = dt.DefaultView;
//Add and configure RowField
fpEngine.RowFields.Add("continent");
fpEngine.RowFields[0].Caption = "Continent";
//Add and configure ValueField
fpEngine.ValueFields.Add("people_fully_vaccinated");
fpEngine.ValueFields[0].Caption = "People fully Vaccinated";
fpEngine.ValueFields[0].Width = 200;
The above code generates the following view after execution:
For a summarized view, we have removed the location field, however, you can keep both continents and location if you want to display regions along with countries.
Now, let's discuss the scenario where we do not have a field that stores the region corresponding to each country. This is where range grouping comes into the picture. We can group countries by region using custom range grouping, which means we will be defining a custom logic to group countries into regions. We would need to define a dictionary storing key-value pairs for region and countries, respectively, to accomplish the same. Next, we set the RangeType property of location RowField to Custom to group this field using custom range grouping. Then, handle the CustomRange event of RangeInfo class to check for each value. We will compare the field value with the dictionary values and assign a key, i.e., a region to this country using the GroupValue property of CustomRangeEventArgs class, ensuring that all the countries are rendered as grouped regions.
The code snippet below implements the same, using another dataset from OurWorldInData, which does not consist of a continent field and hence requires custom range grouping to group countries by region:
//Define dictionary storing region and countries
Dictionary<string, List<string>> countries = new Dictionary<string, List<string>>(){
{"Europe", new List<string>(){"Austria", "Belgium", "Bulgaria", "Croatia", "Cyprus", "Czechia", "Denmark", "Estonia", "Finland", "France", "Hungary", "Iceland",
"Ireland", "Italy", "Latvia", "Lithuania", "Luxembourg", "Malta", "Norway", "Poland", "Portugal", "Romania", "Slovakia", "Slovenia", "Spain", "Sweden" } },
{"South America", new List<string>(){"Peru", "Uruguay" } },
{"USA", new List<string>(){"Jersey"} }
};
//Group countries by region using custom grouping
public void Custom_GroupCountryByRegion(C1PivotEngine fpEngine, DataTable dt)
{
//Bind FlexPivot to data
fpEngine.DataSource = dt.DefaultView;
//Add and configure RowField
fpEngine.RowFields.Add("location");
fpEngine.RowFields[0].Caption = "Continent";
//Apply custom range grouping to RowField
fpEngine.RowFields[0].Range.RangeType = C1.PivotEngine.RangeType.Custom;
fpEngine.RowFields[0].Range.CustomRange += Range_CustomRange;
//Add and configure ValueField
fpEngine.ValueFields.Add("people_fully_vaccinated_per_hundred");
fpEngine.ValueFields[0].Caption = "People fully Vaccinated (per hundred)";
fpEngine.ValueFields[0].Width = 250;
}
//Handle CustomRange event to perform custom grouping
private void Range_CustomRange(object sender, CustomRangeEventArgs e)
{
foreach (var kvp in countries)
{
if (kvp.Value.Contains(e.Value.ToString()))
{
e.GroupValue = kvp.Key;
break;
}
}
}
Note how the above code generates regions using custom grouping:
Group view data using range grouping on datetime field
In this section, we will add another field to the above-generated view i.e., date. As depicted in raw data showcased prior, we have numerous entries corresponding to each date varying based on country and age_group. Hence, it would not be meaningful to add this field as a RowField or ColumnField. We must apply a range grouping to group these dates and represent them in a meaningful manner. The dates can be grouped based on FiscalYear, FiscalMonth, FiscalQuarter, FiscalWeek, Quarter and Week criteria, depending on user requirements.
For the current scenario, we will be grouping date values using Year and Quarter grouping. The following code snippets showcase how we can group date type values by setting the RangeType property of the PivoField to the appropriate value. The first code snippet groups the date values in yearly format:
//Bind FlexPivot to data
fpEngine.DataSource = dt.DefaultView;
//Add and configure RowField
fpEngine.RowFields.Add("continent");
fpEngine.RowFields[0].Caption = "Continent";
//Add and configure ColumnField
fpEngine.ColumnFields.Add("date");
//Apply range grouping to ColumnField
fpEngine.ColumnFields[0].Format = "yyyy";
fpEngine.ColumnFields[0].Range.RangeType = RangeType.Format;
fpEngine.ColumnFields[0].Caption = "Date";
//Add and configure ValueField
fpEngine.ValueFields.Add("people_fully_vaccinated");
fpEngine.ValueFields[0].Caption = "People fully Vaccinated";
fpEngine.ValueFields[0].Width = 200;
Here is a quick look at the view generated using the above code snippet. You can observe how the field defining the number of vaccinated people is split under two different years based on the records in the datasource:
Now, let's move on to implementing the grouping date field into quarters. The code snippet below depicts the same:
//Bind FlexPivot to data
fpEngine.DataSource = dt.DefaultView;
//Add and configure RowField
fpEngine.RowFields.Add("continent");
fpEngine.RowFields[0].Caption = "Continent";
//Add and configure ColumnField
fpEngine.ColumnFields.Add("date");
fpEngine.ColumnFields[0].Caption = "Date";
//Apply range grouping to ColumnField
fpEngine.ColumnFields[0].Range.RangeType = RangeType.FiscalQuarter;
//Add and configure ValueField
fpEngine.ValueFields.Add("people_fully_vaccinated");
fpEngine.ValueFields[0].Caption = "People fully Vaccinated";
fpEngine.ValueFields[0].Width = 130;
Here is a quick look at the view generated using the above code snippet. You can observe how the field defining the number of vaccinated people is split into quarters based on the records in the datasource:
Generating FIRST VIEW
The above sections explained different types of range grouping that need to be applied to generate FIRST VIEW. This section will show how we can merge all these codes to generate the final view. The code snippet below showcases the merged code used to generate FIRST VIEW:
//Bind FlexPivot to data
fpEngine.DataSource = vaccinationSet.DefaultView;
//Add and configure RowField
fpEngine.RowFields.Add("continent");
fpEngine.RowFields[0].Caption = "Continent";
//Add and configure ColumnFields
fpEngine.ColumnFields.Add("date");
fpEngine.ColumnFields[0].Caption = "Date";
fpEngine.ColumnFields[0].Format = "yyyy";
fpEngine.ColumnFields[0].Range.RangeType = RangeType.Format;
fpEngine.ColumnFields.Add("date");
fpEngine.ColumnFields[1].Range.RangeType = RangeType.Quarter;
//Add and configure ValueField
fpEngine.ValueFields.Add("people_fully_vaccinated");
fpEngine.ValueFields[0].Caption = "People fully Vaccinated";
fpEngine.ValueFields[0].Width = 100;
The image below showcases FIRST view generated after executing the above code:
Generating SECOND VIEW
FlexPivot provides support to create numeric data ranges by using numeric range grouping. You can refer to the following documentation link, to understand how we can create numeric range groups using FlexPivot.
However, the data fetched from OurWorldInData already has a field that contains numeric range groups to represent age groups. Hence, to create the SECOND VIEW, which displays the vaccination data over a time span split into years /quarters for each country and group the vaccinated people into different age groups, all we need to do is add the age_group field as a RowField.
The code snippet below showcases the code used to generate SECOND VIEW:
//Bind the datatable to FlexPivot's C1PivotEngine
var fpEngine = flexPivotPage1.FlexPivotPanel.C1PivotEngine;
fpEngine.DataSource = vaccinationSubset.DefaultView;
//Add and configure RowField
fpEngine.RowFields.Add("location");
fpEngine.RowFields[0].Caption = "Country";
fpEngine.RowFields[0].Range.CustomRange += Range_CustomRange;
fpEngine.RowFields[0].Range.RangeType = C1.PivotEngine.RangeType.Custom;
fpEngine.RowFields.Add("age_group");
fpEngine.RowFields[1].Caption = "Age Group";
//Add and configure ColumnFields
fpEngine.ColumnFields.Add("date");
fpEngine.ColumnFields[0].Caption = "Date";
fpEngine.ColumnFields[0].Format = "yyyy";
fpEngine.ColumnFields[0].Range.RangeType = RangeType.Format;
fpEngine.ColumnFields.Add("date");
fpEngine.ColumnFields[1].Range.RangeType = RangeType.FiscalQuarter;
//Add and configure ValueField
fpEngine.ValueFields.Add("people_fully_vaccinated_per_hundred");
fpEngine.ValueFields[0].Caption = "People fully Vaccinated (per hundred)";
fpEngine.ValueFields[0].Width = 100;
The image below showcases SECOND VIEW generated after executing the above code:
Download the sample which implements all the range grouping scenarios described above here. We would be glad to hear how this range grouping feature helped you.
Manpreet Kaur