Simple & Advanced Pivots with C# and ASP.NET






4.89/5 (33 votes)
How to create simple and advanced pivot tables in C# and ASP.NET
Introduction
A pivot table provides a 3 dimensional view of data. Microsoft Excel has the ability to create pivot tables, however, it's not always convenient to use Excel. You may want to create a pivot report in your web application, for example. Creating even a simple pivot table can be a complex task. So it is my intention to not only provide you with an excellent tool for creating simple and advanced pivot reports, but also to remove some of the mystery that shrouds them.
Objective
We want the ability to transform 2 dimensional data in a data table into a 3 dimensional (pivot) report.
The data table
In most instances, you'll populate a data table from a database query, like this:
SELECT
SalesPeople.FullName AS [Sales Person]
, Products.FullName AS [Product]
, SUM(Sales.SalesAmount) AS [Sale Amount]
, SUM(Sales.Qty) AS [Quantity]
FROM
Sales
JOIN
SalesPeople WITH (NOLOCK)
ON SalesPeople.SalesPersonID = Sales.SalesPersonID
JOIN
Products WITH (NOLOCK)
ON Products.ProductCode = Sales.ProductCode
GROUP BY
SalesPeople.FullName
, Products.FullName
|
Sales Person |
Product |
Quantity |
Sale Amount |
John |
Pens |
200 |
350 |
John |
Pencils |
400 |
500 |
John |
Notebooks |
100 |
300 |
John |
Rulers |
50 |
100 |
John |
Calculators |
120 |
1200 |
John |
Back Packs |
75 |
1500 |
Jane |
Pens |
225 |
393.75 |
Jane |
Pencils |
335 |
418.75 |
Jane |
Notebooks |
200 |
600 |
Jane |
Rulers |
75 |
150 |
Jane |
Calculators |
80 |
800 |
Jane |
Back Packs |
97 |
1940 |
Sally |
Pens |
202 |
353.5 |
Sally |
Pencils |
303 |
378.75 |
Sally |
Notebooks |
198 |
600 |
Sally |
Rulers |
98 |
594 |
Sally |
Calculators |
80 |
800 |
Sally |
Back Packs |
101 |
2020 |
Sarah |
Pens |
112 |
196 |
Sarah |
Pencils |
245 |
306.25 |
Sarah |
Notebooks |
198 |
594 |
Sarah |
Rulers |
50 |
100 |
Sarah |
Calculators |
66 |
660 |
Sarah |
Back Packs |
50 |
2020 |
As you can see, this is a 2 dimensional table, and it’s not very useful as a report. So we’ve got to transform this data table into something more readable.
The Pivot structure
A pivot table has 3 dimensions.

The x-axis makes up the main headings at the top of the table. The y-axis makes up the left column of the table, and the z-axis makes up the values corresponding to both the x-axis and y-axis. A simple pivot will have one z-axis column for every x-axis value, while an advanced pivot will have multiple z-axis columns per x-axis value.
A very important point is that the z-axis values can only be numeric. This is because the z-axis values are totaled horizontally and vertically. Using a non-numeric field as a z-axis field will throw an exception.
So if you look at the data table above, you’ll notice that the “Sales Person” and “Product” fields can be assigned to either the x-axis or y-axis, but not the z-axis. The “Quantity” and “Sale Amount” fields can be assigned to the z-axis.
The Pivot class
The Pivot class transforms a data table into an html table, which you can then add to a web form. Well this is just one way of implementing it. If you wish, you can create a user control with the logic from this class.
#region Variables
private DataTable _DataTable;
private string _CssTopHeading;
private string _CssSubHeading;
private string _CssLeftColumn;
private string _CssItems;
private string _CssTotals;
private string _CssTable;
#endregion Variables
#region Constructors
public Pivot(DataTable dataTable)
{
Init();
_DataTable = dataTable;
}
#endregion Constructors
|
This section of the code is pretty self explanatory. You’ll construct a Pivot object by passing a data table as an argument. The Init() method simply assigns an empty string value to the CSS variable. If the CSS variable is an empty string, the styling method will use the default style. Each of the CSS variables has a corresponding property.
private string FindValue(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string zAxisField)
{
string zAxisValue = "";
try
{
foreach (DataRow row in _DataTable.Rows)
{
if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue)
{
zAxisValue = Convert.ToString(row[zAxisField]);
break;
}
}
}
catch
{
throw;
}
return zAxisValue;
}
|
The FindValue(…) method searches the data table for a z-axis value corresponding to the x-axis and y-axis values. The xAxisField is the column name of the x-axis field (example “Product”), and the xAxisValue is a value in that column. The yAxisField is the column name of the y-axis field (example “Sales Person”), and the yAxisValue is a value in that column. The zAxisField is the name of the column in which the z-axis value that you’re looking for resides (example “Sale Amount”).
private string[] FindValues(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string[] zAxisFields)
{
int zAxis = zAxisFields.Length;
if (zAxis < 1)
zAxis++;
string[] zAxisValues = new string[zAxis];
//set default values
for (int i = 0; i <= zAxisValues.GetUpperBound(0); i++)
{
zAxisValues[i] = "0";
}
try
{
foreach (DataRow row in _DataTable.Rows)
{
if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue)
{
for (int z = 0; z < zAxis; z++)
{
zAxisValues[z] = Convert.ToString(row[zAxisFields[z]]);
}
break;
}
}
}
catch
{
throw;
}
return zAxisValues;
}
|
The FindValues(…) method is similar to the FindValue(…) method, however it will return multiple z-axis values. This is used for the advanced pivot table where you have multiple z-axis fields corresponding to an x-axis value.
private void MainHeaderTopCellStyle(HtmlTableCell cell)
{
if (_CssTopHeading == "")
{
cell.Style.Add("font-family", "tahoma");
cell.Style.Add("font-size", "10pt");
cell.Style.Add("font-weight", "normal");
cell.Style.Add("background-color", "black");
cell.Style.Add("color", "white");
cell.Style.Add("text-align", "center");
}
else
cell.Attributes.Add("Class", _CssTopHeading);
}
|
/// <summary>
/// Creates an advanced 3D Pivot table.
/// </summary>
/// <param name="xAxisField">The main heading at the top of the report.</param>
/// <param name="yAxisField">The heading on the left of the report.</param>
/// <param name="zAxisFields">The sub heading at the top of the report.</param>
/// <returns>HtmlTable Control.</returns>
public HtmlTable PivotTable(string xAxisField, string yAxisField, string[] zAxisFields)
{
HtmlTable table = new HtmlTable();
//style table
TableStyle(table);
/*
* The x-axis is the main horizontal row.
* The z-axis is the sub horizontal row.
* The y-axis is the left vertical column.
*/
try
{
//get distinct xAxisFields
ArrayList xAxis = new ArrayList();
foreach (DataRow row in _DataTable.Rows)
{
if (!xAxis.Contains(row[xAxisField]))
xAxis.Add(row[xAxisField]);
}
//get distinct yAxisFields
ArrayList yAxis = new ArrayList();
foreach (DataRow row in _DataTable.Rows)
{
if (!yAxis.Contains(row[yAxisField]))
yAxis.Add(row[yAxisField]);
}
//create a 2D array for the y-axis/z-axis fields
int zAxis = zAxisFields.Length;
if (zAxis < 1)
zAxis = 1;
string[,] matrix = new string[(xAxis.Count * zAxis), yAxis.Count];
string[] zAxisValues = new string[zAxis];
for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis fields
{
//rows
for (int x = 0; x < xAxis.Count; x++) //loop thru x-axis fields
{
//main columns
//get the z-axis values
zAxisValues = FindValues(xAxisField, Convert.ToString(xAxis[x])
, yAxisField, Convert.ToString(yAxis[y]), zAxisFields);
for (int z = 0; z < zAxis; z++) //loop thru z-axis fields
{
//sub columns
matrix[(((x + 1) * zAxis - zAxis) + z), y] = zAxisValues[z];
}
}
}
//calculate totals for the y-axis
decimal[] yTotals = new decimal[(xAxis.Count * zAxis)];
for (int col = 0; col < (xAxis.Count * zAxis); col++)
{
yTotals[col] = 0;
for (int row = 0; row < yAxis.Count; row++)
{
yTotals[col] += Convert.ToDecimal(matrix[col, row]);
}
}
//calculate totals for the x-axis
decimal[,] xTotals = new decimal[zAxis, (yAxis.Count + 1)];
for (int y = 0; y < yAxis.Count; y++) //loop thru the y-axis
{
int zCount = 0;
for (int z = 0; z < (zAxis * xAxis.Count); z++) //loop thru the z-axis
{
xTotals[zCount, y] += Convert.ToDecimal(matrix[z, y]);
if (zCount == (zAxis - 1))
zCount = 0;
else
zCount++;
}
}
for (int xx = 0; xx < zAxis; xx++) //Grand Total
{
for (int xy = 0; xy < yAxis.Count; xy++)
{
xTotals[xx, yAxis.Count] += xTotals[xx, xy];
}
}
//Build HTML Table
//Append main row (x-axis)
HtmlTableRow mainRow = new HtmlTableRow();
mainRow.Cells.Add(new HtmlTableCell());
for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
{
HtmlTableCell cell = new HtmlTableCell();
cell.ColSpan = zAxis;
if (x < xAxis.Count)
cell.InnerText = Convert.ToString(xAxis[x]);
else
cell.InnerText = "Grand Totals";
//style cell
MainHeaderTopCellStyle(cell);
mainRow.Cells.Add(cell);
}
table.Rows.Add(mainRow);
//Append sub row (z-axis)
HtmlTableRow subRow = new HtmlTableRow();
subRow.Cells.Add(new HtmlTableCell());
subRow.Cells[0].InnerText = yAxisField;
//style cell
SubHeaderCellStyle(subRow.Cells[0]);
for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
{
for (int z = 0; z < zAxis; z++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.InnerText = zAxisFields[z];
//style cell
SubHeaderCellStyle(cell);
subRow.Cells.Add(cell);
}
}
table.Rows.Add(subRow);
//Append table items from matrix
for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis
{
HtmlTableRow itemRow = new HtmlTableRow();
for (int z = 0 ; z <= (zAxis * xAxis.Count); z++) //loop thru z-axis + 1
{
HtmlTableCell cell = new HtmlTableCell();
if (z == 0)
{
cell.InnerText = Convert.ToString(yAxis[y]);
//style cell
MainHeaderLeftCellStyle(cell);
}
else
{
cell.InnerText = Convert.ToString(matrix[(z-1), y]);
//style cell
ItemCellStyle(cell);
}
itemRow.Cells.Add(cell);
}
//append x-axis grand totals
for (int z = 0; z < zAxis; z++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.InnerText = Convert.ToString(xTotals[z, y]);
//style cell
TotalCellStyle(cell);
itemRow.Cells.Add(cell);
}
table.Rows.Add(itemRow);
}
//append y-axis totals
HtmlTableRow totalRow = new HtmlTableRow();
for (int x = 0; x <= (zAxis * xAxis.Count); x++)
{
HtmlTableCell cell = new HtmlTableCell();
if (x == 0)
cell.InnerText = "Totals";
else
cell.InnerText = Convert.ToString(yTotals[x-1]);
//style cell
TotalCellStyle(cell);
totalRow.Cells.Add(cell);
}
//append x-axis/y-axis totals
for (int z = 0; z < zAxis; z++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.InnerText = Convert.ToString(xTotals[z, xTotals.GetUpperBound(1)]);
//style cell
TotalCellStyle(cell);
totalRow.Cells.Add(cell);
}
table.Rows.Add(totalRow);
}
catch
{
throw;
}
return table;
}
|
Using the Pivot class
I've included two solutions in the Pivot.zip file. The Pivot solution is a class library. You can compile the solution and reference the Pivot.dll in your web application. The other solution called PivotTest is an ASP.NET application. This shows how to implement the Pivot class.
public DataTable DataTableForTesting
{
get
{
DataTable dt = new DataTable("Sales Table");
dt.Columns.Add("Sales Person");
dt.Columns.Add("Product");
dt.Columns.Add("Quantity");
dt.Columns.Add("Sale Amount");
dt.Rows.Add(new object[] { "John", "Pens", 200, 350.00 });
dt.Rows.Add(new object[] { "John", "Pencils", 400, 500.00 });
dt.Rows.Add(new object[] { "John", "Notebooks", 100, 300.00 });
dt.Rows.Add(new object[] { "John", "Rulers", 50, 100.00 });
dt.Rows.Add(new object[] { "John", "Calculators", 120, 1200.00 });
dt.Rows.Add(new object[] { "John", "Back Packs", 75, 1500.00 });
dt.Rows.Add(new object[] { "Jane", "Pens", 225, 393.75 });
dt.Rows.Add(new object[] { "Jane", "Pencils", 335, 418.75 });
dt.Rows.Add(new object[] { "Jane", "Notebooks", 200, 600.00 });
dt.Rows.Add(new object[] { "Jane", "Rulers", 75, 150.00 });
dt.Rows.Add(new object[] { "Jane", "Calculators", 80, 800.00 });
dt.Rows.Add(new object[] { "Jane", "Back Packs", 97, 1940.00 });
dt.Rows.Add(new object[] { "Sally", "Pens", 202, 353.50 });
dt.Rows.Add(new object[] { "Sally", "Pencils", 303, 378.75 });
dt.Rows.Add(new object[] { "Sally", "Notebooks", 198, 600.00 });
dt.Rows.Add(new object[] { "Sally", "Rulers", 98, 594.00 });
dt.Rows.Add(new object[] { "Sally", "Calculators", 80, 800.00 });
dt.Rows.Add(new object[] { "Sally", "Back Packs", 101, 2020.00 });
dt.Rows.Add(new object[] { "Sarah", "Pens", 112, 196.00 });
dt.Rows.Add(new object[] { "Sarah", "Pencils", 245, 306.25 });
dt.Rows.Add(new object[] { "Sarah", "Notebooks", 198, 594.00 });
dt.Rows.Add(new object[] { "Sarah", "Rulers", 50, 100.00 });
dt.Rows.Add(new object[] { "Sarah", "Calculators", 66, 660.00 });
dt.Rows.Add(new object[] { "Sarah", "Back Packs", 50, 2020.00 });
return dt;
}
}
|
I’ve created this data table property, which builds the data table in the example above. This is just for demonstration purposes.
protected void Page_Load(object sender, EventArgs e)
{
//Advanced Pivot
Pivot advPivot = new Pivot(DataTableForTesting);
HtmlTable advancedPivot = advPivot.PivotTable("Sales Person", "Product", new string[] { "Sale Amount", "Quantity" });
div1.Controls.Add(advancedPivot);
//Simple Pivot
Pivot pivot = new Pivot(DataTableForTesting);
//override default style with css
pivot.CssTopHeading = "Heading";
pivot.CssLeftColumn = "LeftColumn";
pivot.CssItems = "Items";
pivot.CssTotals = "Totals";
pivot.CssTable = "Table";
HtmlTable simplePivot = pivot.PivotTable("Product", "Sales Person", "Sale Amount");
div2.Controls.Add(simplePivot);
}
|
The code above instantiates two pivot objects. The first is for an advanced pivot and the second is for a simple pivot. As you can see I've added the HtmlTable controls to divs. I created the divs with the runat="server" attribute so that I can access it in the code. The divs simply helps with the placement of the HtmlTable.
The advanced pivot with default styling.
John | Jane | Sally | Sarah | Grand Totals | ||||||
Product | Sale Amount | Quantity | Sale Amount | Quantity | Sale Amount | Quantity | Sale Amount | Quantity | Sale Amount | Quantity |
Pens | 350 | 200 | 393.75 | 225 | 353.5 | 202 | 196 | 112 | 1293.25 | 739 |
Pencils | 500 | 400 | 418.75 | 335 | 378.75 | 303 | 306.25 | 245 | 1603.75 | 1283 |
Notebooks | 300 | 100 | 600 | 200 | 600 | 198 | 594 | 198 | 2094 | 696 |
Rulers | 100 | 50 | 150 | 75 | 594 | 98 | 100 | 50 | 944 | 273 |
Calculators | 1200 | 120 | 800 | 80 | 800 | 80 | 660 | 66 | 3460 | 346 |
Back Packs | 1500 | 75 | 1940 | 97 | 2020 | 101 | 2020 | 50 | 7480 | 323 |
Totals | 3950 | 945 | 4302.50 | 1012 | 4746.25 | 982 | 3876.25 | 721 | 16875.00 | 3660 |
The simple pivot with custom styling using css.
Sales Person |
Pens |
Pencils |
Notebooks |
Rulers |
Calculators |
Back Packs |
Grand Totals |
John |
350 |
500 |
300 |
100 |
1200 |
1500 |
3950 |
Jane |
393.75 |
418.75 |
600 |
150 |
800 |
1940 |
4302.50 |
Sally |
353.5 |
378.75 |
600 |
594 |
800 |
2020 |
4746.25 |
Sarah |
196 |
306.25 |
594 |
100 |
660 |
2020 |
3876.25 |
Totals |
1293.25 |
1603.75 |
2094 |
944 |
3460 |
7480 |
16875.00 |