Click here to Skip to main content
14,027,988 members
Click here to Skip to main content
Add your own
alternative version

Stats

270.5K views
11.8K downloads
151 bookmarked
Posted 10 Dec 2007
Licenced CPOL

C# Pivot Table

, 11 Dec 2007
Rate this:
Please Sign up or sign in to vote.
This article explains how to read a DataTable and return an inverted or pivot table depending on the column names provided.

Screenshot -

Introduction

Pivot transformation is very useful to summarize data in a flat data table (columns and rows), providing a more clean visualization of the data.

In this article, we will see two methods to retrieve Pivot and Inverted tables from a DataTable. These methods are very useful, specially for those using metadata to retrieve information from a database, or for those who can not use Pivot transformation from SQL Server 2005.

The two methods are independent, but complement each other in a way that data can be manipulated to return the table inverted or a more complex pivot table.

The Pivot Methods

First - Simple Inversion

Read all data, and return all columns as lines and lines as columns.

The X axis column provided is used as the column header, and some columns may be ignored in the process, if desired so.

/// <summary>
/// Gets a Inverted DataTable
/// </summary>
/// <param name="table">DataTable do invert</param>
/// <param name="columnX">X Axis Column</param>
/// <param name="nullValue">null Value to Complete the Pivot Table</param>
/// <param name="columnsToIgnore">Columns that should be ignored in the pivot 
/// process (X Axis column is ignored by default)</param>
/// <returns>C# Pivot Table Method  - Felipe Sabino</returns>
public static DataTable GetInversedDataTable(DataTable table, string columnX, 
                                             params string[] columnsToIgnore)
{
    //Create a DataTable to Return
    DataTable returnTable = new DataTable();

    if (columnX == "")
        columnX = table.Columns[0].ColumnName;

    //Add a Column at the beginning of the table

    returnTable.Columns.Add(columnX);

    //Read all DISTINCT values from columnX Column in the provided DataTale
    List<string> columnXValues = new List<string>();

    //Creates list of columns to ignore
    List<string> listColumnsToIgnore = new List<string>();
    if (columnsToIgnore.Length > 0)
        listColumnsToIgnore.AddRange(columnsToIgnore);

    if (!listColumnsToIgnore.Contains(columnX))
        listColumnsToIgnore.Add(columnX);

    foreach (DataRow dr in table.Rows)
    {
        string columnXTemp = dr[columnX].ToString();
        //Verify if the value was already listed
        if (!columnXValues.Contains(columnXTemp))
        {
            //if the value id different from others provided, add to the list of 
            //values and creates a new Column with its value.
            columnXValues.Add(columnXTemp);
            returnTable.Columns.Add(columnXTemp);
        }
        else
        {
            //Throw exception for a repeated value
            throw new Exception("The inversion used must have " + 
                                "unique values for column " + columnX);
        }
    }

    //Add a line for each column of the DataTable

    foreach (DataColumn dc in table.Columns)
    {
        if (!columnXValues.Contains(dc.ColumnName) && 
            !listColumnsToIgnore.Contains(dc.ColumnName))
        {
            DataRow dr = returnTable.NewRow();
            dr[0] = dc.ColumnName;
            returnTable.Rows.Add(dr);
        }
    }

    //Complete the datatable with the values
    for (int i = 0; i < returnTable.Rows.Count; i++)
    {
        for (int j = 1; j < returnTable.Columns.Count; j++)
        {
            returnTable.Rows[i][j] = 
              table.Rows[j - 1][returnTable.Rows[i][0].ToString()].ToString();
        }
    }

    return returnTable;
}

Second - Pivoting

It uses the idea of three axes to build the new table. The X axis column is the column with the Unique Values to build the columns header. The Y axis value is the column with the values to be displayed as the rows in the first column. The Z axis is the "value" and is the match of X and Y in the DataSource, and can be the sum of values if more than one different value is found in the process. The null value is provided in case there is the need to fill empty cells of the table with a certain value.

The flag to sum values is used in case there is more than one value for a certain X and Y column combination; if it is "false", the last value that is read is displayed.

/// <summary>
/// Gets a Inverted DataTable
/// </summary>
/// <param name="table">Provided DataTable</param>
/// <param name="columnX">X Axis Column</param>
/// <param name="columnY">Y Axis Column</param>
/// <param name="columnZ">Z Axis Column (values)</param>
/// <param name="columnsToIgnore">Whether to ignore some column, it must be 
/// provided here</param>
/// <param name="nullValue">null Values to be filled</param> 
/// <returns>C# Pivot Table Method  - Felipe Sabino</returns>
public static DataTable GetInversedDataTable(DataTable table, string columnX, 
     string columnY, string columnZ, string nullValue, bool sumValues)
{
    //Create a DataTable to Return
    DataTable returnTable = new DataTable();

    if (columnX == "")
        columnX = table.Columns[0].ColumnName;

    //Add a Column at the beginning of the table
    returnTable.Columns.Add(columnY);


    //Read all DISTINCT values from columnX Column in the provided DataTale
    List<string> columnXValues = new List<string>();

    foreach (DataRow dr in table.Rows)
    {

        string columnXTemp = dr[columnX].ToString();
        if (!columnXValues.Contains(columnXTemp))
        {
            //Read each row value, if it's different from others provided, add to 
            //the list of values and creates a new Column with its value.
            columnXValues.Add(columnXTemp);
            returnTable.Columns.Add(columnXTemp);
        }
    }

    //Verify if Y and Z Axis columns re provided
    if (columnY != "" && columnZ != "")
    {
        //Read DISTINCT Values for Y Axis Column
        List<string> columnYValues = new List<string>();

        foreach (DataRow dr in table.Rows)
        {
            if (!columnYValues.Contains(dr[columnY].ToString()))
                columnYValues.Add(dr[columnY].ToString());
        }

        //Loop all Column Y Distinct Value
        foreach (string columnYValue in columnYValues)
        {
            //Creates a new Row
            DataRow drReturn = returnTable.NewRow();
            drReturn[0] = columnYValue;
            //foreach column Y value, The rows are selected distincted
            DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");

            //Read each row to fill the DataTable
            foreach (DataRow dr in rows)
            {
                string rowColumnTitle = dr[columnX].ToString();

                //Read each column to fill the DataTable
                foreach (DataColumn dc in returnTable.Columns)
                {
                    if (dc.ColumnName == rowColumnTitle)
                    {
                        //If Sum of Values is True it try to perform a Sum
                        //If sum is not possible due to value types, the value 
                        // displayed is the last one read
                        if (sumValues)
                        {
                            try
                            {
                                drReturn[rowColumnTitle] = 
                                     Convert.ToDecimal(drReturn[rowColumnTitle]) + 
                                     Convert.ToDecimal(dr[columnZ]);
                            }
                            catch
                            {
                                drReturn[rowColumnTitle] = dr[columnZ];
                            }
                        }
                        else
                        {
                            drReturn[rowColumnTitle] = dr[columnZ];
                        }
                    }
                }
            }
            returnTable.Rows.Add(drReturn);
        }
    }
    else
    {
        throw new Exception("The columns to perform inversion are not provided");
    }

    //if a nullValue is provided, fill the datable with it
    if (nullValue != "")
    {
        foreach (DataRow dr in returnTable.Rows)
        {
            foreach (DataColumn dc in returnTable.Columns)
            {
                if (dr[dc.ColumnName].ToString() == "")
                    dr[dc.ColumnName] = nullValue;
            }
        }
    }

    return returnTable;
}

Using the Code

The article provides two ways to perform a Pivot transformation.

In both ways, the table below will be used as an example of the data source:

EmployeeIDOrderIDAmountCostDate
Sam1251301/10/2007
Sam2512102/10/2007
Sam3512103/10/2007
Tom450104/10/2007
Tom53703/10/2007
Tom678,751202/10/2007
Sue711701/10/2007
Sue82,566,202/10/2007
Sue92,52203/10/2007
Jack1062302/10/2007
Jack1111719904/10/2007
Jack12132,601/10/2007
Jack1311,499,803/10/2007
Phill14372,102/10/2007
Phill1565,299,304/10/2007
Phill1634,12702/10/2007
Phill171795904/10/2007

The code below shows how to create the table above:

DataTable dt = new DataTable();

dt.Columns.Add("EmployeeID", Type.GetType("System.String"));
dt.Columns.Add("OrderID", Type.GetType("System.Int32"));
dt.Columns.Add("Amount", Type.GetType("System.Decimal"));
dt.Columns.Add("Cost", Type.GetType("System.Decimal"));
dt.Columns.Add("Date", Type.GetType("System.String"));
dt.Rows.Add(new object[] { "Sam", 1, 25.00, 13.00, "01/10/2007" });
dt.Rows.Add(new object[] { "Sam", 2, 512.00, 1.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Sam", 3, 512.00, 1.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Tom", 4, 50.00, 1.00, "04/10/2007" });
dt.Rows.Add(new object[] { "Tom", 5, 3.00, 7.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Tom", 6, 78.75, 12.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Sue", 7, 11.00, 7.00, "01/10/2007" });
dt.Rows.Add(new object[] { "Sue", 8, 2.50, 66.20, "02/10/2007" });
dt.Rows.Add(new object[] { "Sue", 9, 2.50, 22.00, "03/10/2007" });
dt.Rows.Add(new object[] { "Jack", 10, 6.00, 23.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Jack", 11, 117.00, 199.00, "04/10/2007" });
dt.Rows.Add(new object[] { "Jack", 12, 13.00, 2.60, "01/10/2007" });
dt.Rows.Add(new object[] { "Jack", 13, 11.40, 99.80, "03/10/2007" });
dt.Rows.Add(new object[] { "Phill", 14, 37.00, 2.10, "02/10/2007" });
dt.Rows.Add(new object[] { "Phill", 15, 65.20, 99.30, "04/10/2007" });
dt.Rows.Add(new object[] { "Phill", 16, 34.10, 27.00, "02/10/2007" });
dt.Rows.Add(new object[] { "Phill", 17, 17.00, 959.00, "04/10/2007" });

First - Simple Inversion

A column is provided and the DataTable has its "axes turned". This method makes it possible to ignore some columns during the inversion, so that the return table has only the columns of interest. If we want to invert with the column "OrderID" as the title (X axis column), we should use the code below:

//For example, for the DataTable  provided as Datasource 
DataTable dtReturn = GetInversedDataTable(dt, "OrderID");

The return table will be like the one below:

Second - Pivot Table

The three columns are provided and a new DataTable is returned.

The example below will use the source table and the params below to build a Pivot Table.

  • X axis column: "Date"
  • Y axis column: "EmployeeID"
  • Z axis column: "Cost"
  • Null value: "-";
  • Sum of values: true
//For example, for the DataTable  provided as Datasource 
DataTable dtReturn = GetInversedDataTable(dt, "Date", "EmployeeID", 
                                          "Cost", "-", true);

The return table will be like the one below:

EmployeeID01/12/200702/12/200703/12/200704/12/2007
Sam1311-
Tom-1271
Sue766,222-
Jack2,62399,8199
Phill-27-959

Points of Interest

You can read more about Pivot Tables at:

History

  • 10/12/2007 - First release.

License

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

Share

About the Author

felipesabino
Software Developer I.ndigo - www.i.ndigo.com.br
Brazil Brazil
No Biography provided

You may also be interested in...

Comments and Discussions

 
Questionhi i try this pivot but i want to dispkay data on view (no grid view) Pin
Elvally Boubacar25-Nov-17 15:56
memberElvally Boubacar25-Nov-17 15:56 
QuestionSorting column Pin
Member 95892224-Mar-15 3:38
memberMember 95892224-Mar-15 3:38 
GeneralThank you Felipe! Pin
Member 1070158419-Sep-14 11:23
memberMember 1070158419-Sep-14 11:23 
QuestionHow to add 2 fields in the Pivot Table X-Axis Pin
sarathi_5715-Apr-13 0:16
membersarathi_5715-Apr-13 0:16 
AnswerRe: How to add 2 fields in the Pivot Table X-Axis Pin
felipesabino15-Apr-13 1:50
memberfelipesabino15-Apr-13 1:50 
SuggestionALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns Pin
Jean-Francois Lanouette30-Mar-12 4:00
memberJean-Francois Lanouette30-Mar-12 4:00 
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(row[zAxisFields[z]]);
                    }
                    break;
                }
            }

            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
    }
}

GeneralRe: ALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns Pin
Anurag Gandhi16-Apr-12 2:56
professionalAnurag Gandhi16-Apr-12 2:56 
GeneralRe: ALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns Pin
Jean-Francois Lanouette16-Apr-12 3:07
memberJean-Francois Lanouette16-Apr-12 3:07 
GeneralRe: ALTERNATIVE VERSION: returning a Datable instead, the ability to change column headers and multiple y columns Pin
Anurag Gandhi16-Apr-12 23:19
professionalAnurag Gandhi16-Apr-12 23:19 
GeneralMy vote of 5 Pin
VinayakInfotech12-Mar-12 1:20
memberVinayakInfotech12-Mar-12 1:20 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey7-Feb-12 0:06
professionalManoj Kumar Choubey7-Feb-12 0:06 
QuestionThank you Felipe! Pin
a johnston14-Jul-11 0:48
membera johnston14-Jul-11 0:48 
AnswerRe: Thank you Felipe! Pin
felipesabino14-Jul-11 16:10
memberfelipesabino14-Jul-11 16:10 
GeneralHaving Column with ID [modified] Pin
amit121212422-Oct-10 0:50
memberamit121212422-Oct-10 0:50 
GeneralRe: Having Column with ID Pin
felipesabino22-Oct-10 1:04
memberfelipesabino22-Oct-10 1:04 
GeneralRe: Having Column with ID Pin
amit121212424-Oct-10 20:09
memberamit121212424-Oct-10 20:09 
GeneralGreat work Pin
amit121212414-Oct-10 23:13
memberamit121212414-Oct-10 23:13 
GeneralRe: Great work Pin
felipesabino19-Oct-10 3:22
memberfelipesabino19-Oct-10 3:22 
Generalusing the code with decimal row and column headers Pin
T21028-Apr-10 4:31
memberT21028-Apr-10 4:31 
GeneralRe: using the code with decimal row and column headers Pin
felipesabino8-Apr-10 5:08
memberfelipesabino8-Apr-10 5:08 
GeneralRe: using the code with decimal row and column headers [modified] Pin
T21028-Apr-10 20:47
memberT21028-Apr-10 20:47 
GeneralRe: using the code with decimal row and column headers Pin
T21028-Apr-10 22:33
memberT21028-Apr-10 22:33 
GeneralVB.NET Source + Improvements Pin
clayton neal11-Nov-09 9:02
memberclayton neal11-Nov-09 9:02 
GeneralRe: VB.NET Source + Improvements Pin
amit121212422-Oct-10 0:55
memberamit121212422-Oct-10 0:55 
GeneralSorting Pin
clayton neal11-Nov-09 9:00
memberclayton neal11-Nov-09 9:00 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.190419.4 | Last Updated 11 Dec 2007
Article Copyright 2007 by felipesabino
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid