PivotTable. Data Grouping

PivotTable. Data Grouping

Sometime you may face a necessity to group the source data and build a Pivot Table for every group.

1. Incorrect use of PivotTable

There is one common mistake when a developer tries to use Pivot Table inside the GroupBand control to achieve this goal.

This is an incorrect approach and most likely you will not the desired result. This is because the GroupBand just filters the source data and allows using it analogously to the DataBand control. But using the PivotTable control it is needed to set the dataSource and the whole work on the data visualization it encapsulates inside using a new inner data navigator.

2.  Working approach

The main idea here is as follows: we need to transform the data before visualizing. First of all, let’s build a unique list of all groups in our data, and then extract only the data which relate to a particular group. Thus, a report will look the following way: we will place the PivotTable control inside DataBand that will build groups, and using scripts we will prepare the corresponding data source and set it for the PivotTable control.

+

3. Implementation

1. The first thing we need to do is to add the corresponding controls to a report: DataBand and PivotTabel where PivotTable should be embedded into the DataBand.

2. Write a script that will extract a list for groups and create a new data source for a report. These data will be used in our DataBand.

System.Data.DataTable data = DataObjects["Sales"] as System.Data.DataTable;

//create dataTable

var columns = new string[] { "CategoryName" };

DataTable categoryNames = new DataTable();

 

foreach (var column in columns){

  categoryNames.Columns.Add(column);

}

 

//Distinct CategoryName

System.Collections.Generic.List<string> categotyNamesString = new System.Collections.Generic.List<string>();

      

foreach (DataRow row in data.Rows){

  string category = (string) row["CategoryName"];

             

  if (!categotyNamesString.Contains(category)){

    categotyNamesString.Add(category);

  }

}

      

//Fill dataTable

foreach (string category in categotyNamesString){

  System.Data.DataRow row = categoryNames.NewRow();   

  row["CategoryName"] = category;

  categoryNames.Rows.Add(row);

}

 

//Register dataTable

DataObjects.Remove("categoryNames");

DataObjects.Add("categoryNames", categoryNames);

 

//Set dataBand1.dataSource

dataBand1.DataSource = "categoryNames";

3. And write one more script that creates a new data source that includes records for one particular group.

System.Data.DataTable data = DataObjects["Sales"] as System.Data.DataTable;

string categoryName = (string) dataBand1["CategoryName"];

      

//create dataTable

DataTable dataTableItem = data.Clone();

 

//Fill data by group

foreach (DataRow row in data.Rows){

  string currentCategoryName = (string) row["CategoryName"];

  if (currentCategoryName == categoryName){

    InsertRow(dataTableItem, row);

  }

}

      

//Register dataTable

string dataTableName = string.Format("dataTable{0}", dataBand1.LineNumber);

DataObjects.Remove(dataTableName);

DataObjects.Add(dataTableName, dataTableItem);

 

//Set PivotTable.dataSource

pivotTable1.DataSource = dataTableName;

 

To see the implementation on a example, you can download a sample report here.

Add Feedback