Click here to Skip to main content
15,867,305 members
Articles / Web Development / HTML

Pivot Grid in ASP.NET MVC

Rate me:
Please Sign up or sign in to vote.
4.92/5 (47 votes)
5 Sep 2014CPOL3 min read 86.7K   10.2K   74   25
A Grid/Report control in ASP.NET MVC with pivot option

Introduction

Displaying data in a pivot form has become an essential part of reporting now a days. When you have a large amount of data or you wish to visualize data column wise with logical grouping/aggregation, here is the extension method which will help you. This article aims to simplify the process of pivoting of data when you have your IEnumerable<Model> in your presentation layer.

To display the data in UI, a grid control is also required. I have, therefore, created a simple grid control of my own which renders HTML from the IEnumerable<Model>.

Background

After my article, Pivoting DataTable Simplified, I got many email requests from the readers that they wish to Pivot the data in ASP.NET MVC too. I, therefore, decided to write a separate article regarding pivoting the data in ASP.NET MVC. In my previous article, I provided many samples to display data in different pivot forms. To keep the article simple and due to lack of time, I have not provided many samples in this article. But those samples can easily be derived the same way as it is provided in the previous article. I may provide many different options to pivot in this article in future. For now, please have a look at the basic pivoting with a simple grid/report control.

Using the Code

To use the code, you just need to add the reference to the ReportControl assembly. Then, you can directly use the code in your view as follows:

C#
@Model.ReportWithPivot("", "ShopName", "SellingPrice", 
                       AggregateFunction.Sum, "ItemType", "ItemName")

Please note that your model should be IEnumerable or derived from IEnumerable only.

How It Works

To understand the working of pivot related stuff, please have a look at my previous article: Pivoting DataTable Simplified. Further to that, ReportWithPivot is an extension method to an IEnumerable object which takes RowField, DataField, Aggregate Function and ColumnFields as parameter and returns an HTML table with the data pivoted according to the specified parameter.

C#
public static HtmlString ReportWithPivot<T>(this IEnumerable<T> source, string cssClass, 
 string rowField, string dataField, AggregateFunction aggregate, params string[] columnFields)
 where T : class
        {
            DataTable dt = source.ToDataTable();
            return dt.ReportWithPivot
                   (cssClass, rowField, dataField, aggregate, columnFields);
        }

public static HtmlString ReportWithPivot(this DataTable source, string cssClass, 
 string rowField, string dataField, AggregateFunction aggregate, params string[] columnFields)
        {
            Pivot p = new Pivot(source);
            DataTable dt = p.PivotData(rowField, dataField, aggregate, columnFields);
            return dt.Report(cssClass, columnFields.Length, 
                   dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray());
        } 

To simplify things, the working of this method can be explained in the following steps:

  1. An extension method ReportWithPivot gets called which takes pivot parameters.
  2. This method first converts the IEnumerable source to a DataTable and then calls another overload of ReportWithPivot method which takes DataTable as parameter. To pivot the source, we first convert the data to DataTable because it is easy to dynamically operate on DataTable by splitting it into different parts.
  3. Now, it pivots the data present in DataTable the same way as explained in my previous article. We get the Pivoted Data in a new DataTable.
  4. After we get pivoted data, another extension method is called to render the data into HTML Report. You may download the source code attached with this article and have a look over the "Report" extension method present in ReportEx class.

Setting Up Header for Pivot

The header for Pivot table has been set by PivotHeader(this Table table, string separator, int pivotLevel) function present in ReportHelper class. It first adds the number of header rows in the table and applies the data according to the pivot level and content. Then, it checks and merges the duplicate cell data.

C#
public static void PivotHeader(this Table table, string separator, int pivotLevel)
        {
            TableRow row = table.Rows[0];
            if (row.TableSection == TableRowSection.TableHeader)
            {
                TableRow r = new TableRow();
                var headers = row.Cells.Cast<tablecell>().Select(x => x.Text);

                for (int i = 0; i < pivotLevel; i++)
                {
                    r = new TableRow();
                    r.TableSection = TableRowSection.TableHeader;
                    foreach (var x in headers)
                    {
                        string headerText = GetNthText(x, i, separator);
                        if (r.Cells.Count > 0 && r.Cells[r.Cells.Count - 1].Text == headerText)
                            r.Cells[r.Cells.Count - 1].ColumnSpan++;
                        else
                            r.Cells.Add(new TableHeaderCell 
                                       { Text = headerText, ColumnSpan = 1 });
                    }
                    table.Rows.AddAt(i, r);
                }
            }
            table.Rows.Remove(row);
        }

The below image is the screen shot of the pivoted and the raw report:

Report With Pivot

Raw Data

Points of Interest

This article also provides a sample on how you can create a simple Report/Grid extension method to render HTML from your collection. For beginners, the code in it may also act as a sample on how to use reflection to read the property and get the value from it. The below code demonstrates this:

C#
public static DataTable ToDataTable<t>(this IEnumerable<t> data)
        {
            //PropertyDescriptorCollection properties = 
            //     TypeDescriptor.GetProperties(typeof(T));
            PropertyInfo[] properties = typeof(T).GetProperties();
            DataTable table = new DataTable();
            foreach (PropertyInfo prop in properties)
                table.Columns.Add(prop.Name, 
                Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyInfo prop in properties)
                    row[prop.Name] = prop.GetValue(item, null) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }

Future Consideration

Right now, just a basic sample to pivot a report has been added. Please keep following this article to see more features on pivot.

History

  • 22nd August, 2014: First version release
  • 5th September, 2014: Issue fix: Header columns were rendering incorrectly on multi-column pivot

License

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


Written By
Architect
India India
Anurag Gandhi is a Freelance Developer and Consultant, Architect, Blogger, Speaker, and Ex Microsoft Employee. He is passionate about programming.
He is extensively involved in Asp.Net Core, MVC/Web API, Node/Express, Microsoft Azure/Cloud, web application hosting/architecture, Angular, AngularJs, design, and development. His languages of choice are C#, Node/Express, JavaScript, Asp .NET MVC, Asp, C, C++. He is familiar with many other programming languages as well. He mostly works with MS SQL Server as the preferred database and has worked with Redis, MySQL, Oracle, MS Access, etc. also.
He is active in programming communities and loves to share the knowledge with others whenever he gets the time for it.
He is also a passionate chess player.
Linked in Profile: https://in.linkedin.com/in/anuraggandhi
He can be contacted at soft.gandhi@gmail.com

Comments and Discussions

 
QuestionHow to refresh with filters using column change Pin
Member 122903813-Jul-20 12:31
Member 122903813-Jul-20 12:31 
AnswerRe: How to refresh with filters using column change Pin
Anurag Gandhi15-Apr-21 6:12
professionalAnurag Gandhi15-Apr-21 6:12 
QuestionMultiple aggregate function Pin
Member 1151501130-Jun-20 8:32
Member 1151501130-Jun-20 8:32 
Generalgreat Pin
Member 1487190328-Jun-20 22:57
Member 1487190328-Jun-20 22:57 
GeneralRe: great Pin
Anurag Gandhi15-Apr-21 6:10
professionalAnurag Gandhi15-Apr-21 6:10 
Questionhow to show pivot chart in dashboard. Pin
Member 1334862012-Sep-17 2:24
Member 1334862012-Sep-17 2:24 
General5 star article Pin
sahilgupta56215-May-15 9:09
sahilgupta56215-May-15 9:09 
AnswerRe: 5 star article Pin
Anurag Gandhi24-Sep-16 9:06
professionalAnurag Gandhi24-Sep-16 9:06 
QuestionTotal row at the bottom Pin
Member 105600146-Nov-14 22:53
Member 105600146-Nov-14 22:53 
GeneralMy vote of 5 Pin
Volynsky Alex7-Sep-14 10:49
professionalVolynsky Alex7-Sep-14 10:49 
GeneralRe: My vote of 5 Pin
Anurag Gandhi7-Sep-14 19:20
professionalAnurag Gandhi7-Sep-14 19:20 
GeneralRe: My vote of 5 Pin
Volynsky Alex8-Sep-14 9:26
professionalVolynsky Alex8-Sep-14 9:26 
QuestionBug when rendering column headers Pin
skaus1234-Sep-14 1:58
professionalskaus1234-Sep-14 1:58 
AnswerRe: Bug when rendering column headers Pin
Anurag Gandhi5-Sep-14 3:58
professionalAnurag Gandhi5-Sep-14 3:58 
GeneralRe: Bug when rendering column headers Pin
skaus1236-Sep-14 21:20
professionalskaus1236-Sep-14 21:20 
BugUseful one Pin
Member 429919826-Aug-14 6:52
Member 429919826-Aug-14 6:52 
GeneralMy vote of 5 Pin
gicalle7525-Aug-14 8:58
professionalgicalle7525-Aug-14 8:58 
GeneralRe: My vote of 5 Pin
Anurag Gandhi25-Aug-14 17:33
professionalAnurag Gandhi25-Aug-14 17:33 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun24-Aug-14 19:46
Humayun Kabir Mamun24-Aug-14 19:46 
GeneralRe: My vote of 5 Pin
Anurag Gandhi24-Aug-14 21:43
professionalAnurag Gandhi24-Aug-14 21:43 
QuestionNice Article Pin
Kannan.Ramjalwar24-Aug-14 18:35
Kannan.Ramjalwar24-Aug-14 18:35 
AnswerRe: Nice Article Pin
Anurag Gandhi24-Aug-14 18:37
professionalAnurag Gandhi24-Aug-14 18:37 
QuestionGreat work Pin
skaus12323-Aug-14 2:53
professionalskaus12323-Aug-14 2:53 
This is an excellent project. But it seems we can only pivot multiple fields for columns only while rows allow only one row field value. Can we pivot on multiple row field values as well ?
Thanks
John. Smile | :)
AnswerRe: Great work Pin
Anurag Gandhi23-Aug-14 6:53
professionalAnurag Gandhi23-Aug-14 6:53 
GeneralRe: Great work Pin
skaus12323-Aug-14 20:49
professionalskaus12323-Aug-14 20:49 

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.