SQL Server 2000.NET 1.0.NET 1.1.NET 3.0Design / GraphicsArchitectAdvanced.NET 2.0.NET 3.5SQL Server 2005C# 2.0C# 3.0DevSQL ServerSQL.NETASP.NETC#
Simple & Advanced Pivots with C# and ASP.NET





5.00/5 (3 votes)
This is an alternative for "Simple & Advanced Pivots with C# and ASP.NET"
Datatable version
Was pretty useful thanks, here the same code is presented but returning a DataTable instead
Also I've added the following:
- Passing the column headers as parameters
- Prevent the crash if there's null values
- Possibility to have multiple values for the same x,y
- Multiple Z columns in 2d table
using System;
using System.Data;
using System.Collections;
namespace PivotTable
{
/// <summary>
/// Create simple and advanced pivot reports.
/// </summary>
public class PivotTable
{
#region Variables
private DataTable _DataTable;
#endregion Variables
#region Constructors
public PivotTable(DataTable dataTable)
{
_DataTable = dataTable;
}
#endregion Constructors
#region Properties
public DataTable ResultTable
{
get { return _DataTable; }
}
#endregion Properties
#region Private Methods
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";
}
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(Convert.ToDecimal("0" + row[zAxisFields[z]]) + Convert.ToDecimal("0" +zAxisValues[z]));
}
// break; // If you are sure that you don't have duplicated row of data, uncomment to gain performance
}
}
return zAxisValues;
}
private string FindValue(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string zAxisField)
{
string zAxisValue = "";
foreach (DataRow row in _DataTable.Rows)
{
if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) == yAxisValue)
{
zAxisValue = Convert.ToString(row[zAxisField]);
break;
}
}
return zAxisValue;
}
#endregion Private Methods
#region Public Methods
/// <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>
/// <param name="mainColumnName">Name of the column in xAxis.</param>
/// <param name="columnTotalName">Name of the column with the totals.</param>
/// <param name="rowTotalName">Name of the row with the totals.</param>
/// <param name="zAxisFieldsNames">Name of the columns in the zAxis.</param>
/// <returns>HtmlTable Control.</returns>
public DataTable Generate(string xAxisField, string yAxisField, string[] zAxisFields, string mainColumnName, string columnTotalName, string rowTotalName, string[] zAxisFieldsNames)
{
/*
* The x-axis is the main horizontal row.
* The z-axis is the sub horizontal row.
* The y-axis is the left vertical column.
*/
//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];
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
string[] 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("0" + 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("0" + 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)
DataTable table = new DataTable();
DataColumn columnYTitle = new DataColumn(mainColumnName);
table.Columns.Add(columnYTitle);
for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
{
if (x < xAxis.Count)
{
for (int z = 0; z < zAxis; z++)
{
DataColumn column = new DataColumn();
column.ColumnName = Convert.ToString(xAxis[x] + " - " + zAxisFieldsNames[z]);
table.Columns.Add(column);
}
}
else
{
for (int z = 0; z < zAxis; z++)
{
DataColumn column = new DataColumn();
column.ColumnName = columnTotalName + " - " + zAxisFieldsNames[z];
table.Columns.Add(column);
}
}
}
//Append table items from matrix
for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis
{
DataRow dataRow = table.NewRow();
for (int z = 0; z <= (zAxis * xAxis.Count); z++) //loop thru z-axis + 1
{
if (z == 0)
{
dataRow[z] = Convert.ToString(yAxis[y]);
}
else
{
dataRow[z] = Convert.ToString(matrix[(z - 1), y]);
}
}
//append x-axis grand totals
for (int z = zAxis * xAxis.Count; z < zAxis + (zAxis * xAxis.Count); z++)
{
dataRow[z + 1] = Convert.ToString(xTotals[z - (zAxis * xAxis.Count), y]);
}
table.Rows.Add(dataRow);
}
//append y-axis totals
DataRow dataRowTotals = table.NewRow();
for (int x = 0; x <= (zAxis * xAxis.Count); x++)
{
if (x == 0)
dataRowTotals[0] = rowTotalName;
else
dataRowTotals[x] = Convert.ToString(yTotals[x - 1]);
}
//append x-axis/y-axis totals
for (int z = 0; z < zAxis; z++)
{
dataRowTotals[table.Columns.Count - zAxis + z] = Convert.ToString(xTotals[z, xTotals.GetUpperBound(1)]);
}
table.Rows.Add(dataRowTotals);
return table;
}
/// <summary>
/// Creates a simple 3D Pivot Table.
/// </summary>
/// <param name="xAxisField">The heading at the top of the table.</param>
/// <param name="yAxisField">The heading to the left of the table.</param>
/// <param name="zAxisField">The item value field.</param>
/// <param name="mainColumnName">Title of the main column</param>
/// <param name="columnTotalName">Title of the total column</param>
/// <param name="rowTotalName">Title of the row column</param>
/// <returns></returns>
public DataTable Generate(string xAxisField, string yAxisField, string zAxisField, string mainColumnName, string columnTotalName, string rowTotalName)
{
return Generate(xAxisField, yAxisField, new string[0], new string[0], zAxisField, mainColumnName, columnTotalName, rowTotalName);
}
/// <summary>
/// Creates a simple 3D Pivot Table.
/// </summary>
/// <param name="xAxisField">The heading at the top of the table.</param>
/// <param name="yAxisField">The heading to the left of the table.</param>
/// <param name="yAxisInfoFields">Other columns that we want to show on the y axis.</param>
/// <param name="yAxisInfoFieldsNames">Title of the additionnal columns on y axis.</param>
/// <param name="zAxisField">The item value field.</param>
/// <param name="mainColumnName">Title of the main column</param>
/// <param name="columnTotalName">Title of the total column</param>
/// <param name="rowTotalName">Title of the row column</param>
/// <returns></returns>
public DataTable Generate(string xAxisField, string yAxisField, string[] yAxisInfoFields, string[] yAxisInfoFieldsNames, string zAxisField, string mainColumnName, string columnTotalName, string rowTotalName)
{
//style 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.
*/
//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 x-axis/y-axis fields
string[,] matrix = new string[xAxis.Count, yAxis.Count];
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
string zAxisValue = FindValue(xAxisField, Convert.ToString(xAxis[x])
, yAxisField, Convert.ToString(yAxis[y]), zAxisField);
matrix[x, y] = zAxisValue;
}
}
//calculate totals for the y-axis
decimal[] yTotals = new decimal[xAxis.Count];
for (int col = 0; col < xAxis.Count; col++)
{
yTotals[col] = 0;
for (int row = 0; row < yAxis.Count; row++)
{
yTotals[col] += Convert.ToDecimal("0" + matrix[col, row]);
}
}
//calculate totals for the x-axis
decimal[] xTotals = new decimal[(yAxis.Count + 1)];
for (int row = 0; row < yAxis.Count; row++)
{
xTotals[row] = 0;
for (int col = 0; col < xAxis.Count; col++)
{
xTotals[row] += Convert.ToDecimal("0" + matrix[col, row]);
}
}
xTotals[xTotals.GetUpperBound(0)] = 0; //Grand Total
for (int i = 0; i < xTotals.GetUpperBound(0); i++)
{
xTotals[xTotals.GetUpperBound(0)] += xTotals[i];
}
//Build HTML Table
//Build HTML Table
//Append main row (x-axis)
DataTable table = new DataTable();
DataColumn columnYTitle = new DataColumn(mainColumnName);
foreach (string yAxisInfoFieldsName in yAxisInfoFieldsNames)
{
table.Columns.Add(yAxisInfoFieldsName);
}
table.Columns.Add(columnYTitle);
for (int x = 0; x <= xAxis.Count; x++) //loop thru x-axis + 1
{
if (x < xAxis.Count)
{
DataColumn column = new DataColumn();
column.ColumnName = Convert.ToString(xAxis[x]);
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(columnTotalName);
table.Columns.Add(column);
}
}
//Append table items from matrix
for (int y = 0; y < yAxis.Count; y++) //loop thru y-axis
{
DataRow dataRow = table.NewRow();
for (int z = 0; z <= xAxis.Count + yAxisInfoFieldsNames.Length; z++) //loop thru z-axis + 1
{
if (z < yAxisInfoFieldsNames.Length)
{
dataRow[z] = Convert.ToString(_DataTable.Rows[y][yAxisInfoFields[z]]);
}
if (z == yAxisInfoFieldsNames.Length)
{
dataRow[z] = Convert.ToString(yAxis[y]);
}
if (z > yAxisInfoFieldsNames.Length)
{
dataRow[z] = Convert.ToString(matrix[(z - 1 - yAxisInfoFieldsNames.Length), y]);
}
}
dataRow[xAxis.Count + yAxisInfoFieldsNames.Length + 1] = Convert.ToString(xTotals[y]);
table.Rows.Add(dataRow);
}
//append y-axis totals
DataRow dataRowTotals = table.NewRow();
for (int x = 0; x <= (xAxis.Count + 1) + yAxisInfoFieldsNames.Length; x++)
{
if (x == 0)
{
dataRowTotals[0] = rowTotalName;
}
if (x > yAxisInfoFieldsNames.Length)
{
if (x <= xAxis.Count + yAxisInfoFieldsNames.Length)
dataRowTotals[x] = Convert.ToString(yTotals[(x - 1 - yAxisInfoFieldsNames.Length)]);
else
dataRowTotals[x] = Convert.ToString(xTotals[xTotals.GetUpperBound(0)]);
}
}
table.Rows.Add(dataRowTotals);
return table;
}
#endregion Public Methods
}
}