Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET

Simple & Advanced Pivots with C# and ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.89/5 (36 votes)
29 Jan 2009CPOL5 min read 144.3K   4.4K   121   30
How to create simple and advanced pivot tables in C# and ASP.NET
Download Pivot.zip - 27.76 KB

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:

SQL
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

The query will generate a data table like this:

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.

PivotStructure.jpg <shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"><shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><stroke joinstyle="miter"><path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"><lock aspectratio="t" v:ext="edit"><shape id="_x0000_i1025" style="WIDTH: 276pt; HEIGHT: 115.5pt" type="#_x0000_t75"><imagedata src="file:///C:\Users\Nick\AppData\Local\Temp\msohtml1\02\clip_image001.png">

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.

C#
#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.

C#
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”).

C#
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.

C#
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); 
}

This is one of the CSS styling methods. This method in particular styles the x-axis (the row at the top of the table). If you don’t assign a CSS class name to the property, the method will use the default style. The CSS class will be picked up from which ever page you add the HTML table to.

C#
/// <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; 
}

The PivotTable(…) method is where all the magic happens. There are two overloads of this method; one creates a simple pivot, and the other (the one above) creates an advanced pivot. The only difference is that the simple pivot has one z-axis field while the advanced pivot has more than one.

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.

C#
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.

C#
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.

JohnJaneSallySarahGrand Totals
ProductSale AmountQuantitySale AmountQuantitySale AmountQuantitySale AmountQuantitySale AmountQuantity
Pens350200393.75225353.52021961121293.25739
Pencils500400418.75335378.75303306.252451603.751283
Notebooks3001006002006001985941982094696
Rulers10050150755949810050944273
Calculators12001208008080080660663460346
Back Packs15007519409720201012020507480323
Totals39509454302.5010124746.259823876.2572116875.003660

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
South Africa South Africa
Nitesh Maharaj is currently working as a software developer for a company in South Africa. He is extensively involved in enterprise software development. His preferred technologies are Microsoft's SQL Server, Visual Studio, and C# is his language of choice.

Comments and Discussions

 
QuestionGreat tool! How to have more than 6 columns? Pin
Paul B Martin6-Jan-18 10:08
Paul B Martin6-Jan-18 10:08 
AnswerRe: Great tool! How to have more than 6 columns? Pin
Paul B Martin6-Jan-18 11:54
Paul B Martin6-Jan-18 11:54 
QuestionSimple & Advanced Pivots with y-axis multiple columns Pin
Member 1308118331-Mar-17 0:11
Member 1308118331-Mar-17 0:11 
QuestionGreat work Pin
MuhUsman18-Mar-16 22:06
MuhUsman18-Mar-16 22:06 
QuestionHacing two yAxis Pin
mahshid28-Feb-14 21:31
mahshid28-Feb-14 21:31 
GeneralYOUR A GENIUS! Pin
Member 41658519-Jan-14 7:14
Member 41658519-Jan-14 7:14 
QuestionPlease help about how to get grand total on left side instead of right side Pin
DeepikaSKC23-Sep-13 19:42
DeepikaSKC23-Sep-13 19:42 
QuestionHow to change background color of a cell in the z-axis Pin
PolySage3-Jun-13 6:20
PolySage3-Jun-13 6:20 
AnswerRe: How to change background color of a cell in the z-axis Pin
Nitesh Maharaj4-Jun-13 1:10
Nitesh Maharaj4-Jun-13 1:10 
Yes it can be done. There's a line where the cell style is set (ItemCellStyle). You'll need to add in some logic here. Unfortunately I didn't write this in a very object oriented manner, so some refactoring is needed.
Those whom have the ability to take action, have the responsibility to take action.

GeneralRe: How to change background color of a cell in the z-axis Pin
PolySage4-Jun-13 18:36
PolySage4-Jun-13 18:36 
QuestionHow to bring in another X-axis field with the pivot table Pin
sarathi_5712-Apr-13 4:27
sarathi_5712-Apr-13 4:27 
GeneralMy vote of 1 Pin
Member 894945029-Aug-12 2:24
Member 894945029-Aug-12 2:24 
QuestionAdd a column with a edit/select button Pin
pmcm29-Aug-12 0:02
pmcm29-Aug-12 0:02 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey7-Feb-12 0:05
professionalManoj Kumar Choubey7-Feb-12 0:05 
QuestionN deep grouping? Pin
SHMMI23-Dec-11 13:58
SHMMI23-Dec-11 13:58 
QuestionGreat Code! Pin
Tito Ruiz2-Dec-11 9:04
Tito Ruiz2-Dec-11 9:04 
QuestionSome questions Pin
enexooone9-Sep-11 0:56
enexooone9-Sep-11 0:56 
GeneralGreat Code!! Pin
syatwork28-Mar-11 15:51
syatwork28-Mar-11 15:51 
GeneralHELP with the code Pin
abhinav86430-Sep-10 17:53
abhinav86430-Sep-10 17:53 
Generalthank you Pin
monkypants20-Sep-10 23:03
monkypants20-Sep-10 23:03 
GeneralDrill down Pin
mbowles2011-Apr-10 9:04
mbowles2011-Apr-10 9:04 
GeneralRe: Drill down Pin
Nitesh Maharaj2-Oct-10 10:06
Nitesh Maharaj2-Oct-10 10:06 
Questionhow to load data from an existing Database Pin
ZeLLe2122-Feb-10 22:00
ZeLLe2122-Feb-10 22:00 
AnswerRe: how to load data from an existing Database Pin
Nitesh Maharaj2-Oct-10 10:13
Nitesh Maharaj2-Oct-10 10:13 
GeneralNice Component, can be customized Pin
walcom21-Dec-09 5:11
walcom21-Dec-09 5:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.