Click here to Skip to main content
12,510,762 members (48,951 online)
Click here to Skip to main content
Add your own
alternative version

Stats

12.3K views
17 bookmarked
Posted

Simple & Advanced Pivots with C# and ASP.NET

, 1 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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
    }
}

License

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

Share

About the Author

Jean-Francois Lanouette
Web Developer
Canada Canada
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionPlease help about how to get grand total on left side instead of right side Pin
DeepikaSKC23-Sep-13 19:47
memberDeepikaSKC23-Sep-13 19:47 
SuggestionTake a look at this one: Pin
Anurag Gandhi2-May-12 7:30
memberAnurag Gandhi2-May-12 7:30 
GeneralRe: Take a look at this one: Pin
Jean-Francois Lanouette2-May-12 7:36
memberJean-Francois Lanouette2-May-12 7:36 
Hi, looks not bad but im still in .net 2.0
QuestionNot an article... Pin
Dave Kreskowiak1-May-12 3:55
mvpDave Kreskowiak1-May-12 3:55 
AnswerRe: Not an article... Pin
Jean-Francois Lanouette1-May-12 7:19
memberJean-Francois Lanouette1-May-12 7:19 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 1 May 2012
Article Copyright 2012 by Jean-Francois Lanouette
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid