Click here to Skip to main content
Click here to Skip to main content

Pivot Grid in Asp.Net MVC

, 5 Sep 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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 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 in 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 http://www.codeproject.com/Articles/46486/Pivoting-DataTable-Simplified, I have got many email request 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 have 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 sample can easily be derrived the same way as it is provided in previous article. I may provide many different options to pivot in this article in future. For now, please have a look over 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:

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

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

How it works

To understand the working of pivot related stuff, please have a look over 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.

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 call the 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 no. of header rows in the table and appies the the data according to the pivot level and content. Then, it checks and merges the duplicate cell data.

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);
        }
</tablecell>

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 provide 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 demonstrate this:

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;
        }
</t></t>

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

22 Aug, 2014: First Version Release.

05 Sep 2014: Issue Fix: Header columns was 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)

Share

About the Author

Anurag Gandhi
Technical Lead
India India
Anurag Gandhi currently works in web application design/development and has done so for many years now as he is passionate about programming.
He is extensively involved in Asp.Net and Asp.Net MVC web application architecture, AngularJs, design and development. His languages of choice are C#, Asp.Net, MVC, Asp, C, C++. But he is familiar with many other coding languages. He works with MS Sql Server as the database architecture of choice.
He is active in programming communities and loves to share his knowledge with other developers whenever he gets the opportunity.
He is also a passionate chess player.
 
He can be contacted at: soft.gandhi@gmail.com
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinprofessionalVolynsky Alex7-Sep-14 10:49 
GeneralRe: My vote of 5 PinprofessionalAnurag Gandhi7-Sep-14 19:20 
GeneralRe: My vote of 5 PinprofessionalVolynsky Alex8-Sep-14 9:26 
QuestionBug when rendering column headers Pinprofessionalskaus1234-Sep-14 1:58 
AnswerRe: Bug when rendering column headers PinprofessionalAnurag Gandhi5-Sep-14 3:58 
GeneralRe: Bug when rendering column headers Pinprofessionalskaus1236-Sep-14 21:20 
BugUseful one PinmemberMember 429919826-Aug-14 6:52 
GeneralMy vote of 5 Pinmembergicalle7525-Aug-14 8:58 
GeneralRe: My vote of 5 PinprofessionalAnurag Gandhi25-Aug-14 17:33 
GeneralMy vote of 5 PinmemberHumayun Kabir Mamun24-Aug-14 19:46 
GeneralRe: My vote of 5 PinprofessionalAnurag Gandhi24-Aug-14 21:43 
QuestionNice Article PinmemberKannan.Ramjalwar24-Aug-14 18:35 
AnswerRe: Nice Article PinprofessionalAnurag Gandhi24-Aug-14 18:37 
QuestionGreat work Pinmemberskaus12323-Aug-14 2:53 
AnswerRe: Great work [modified] PinprofessionalAnurag Gandhi23-Aug-14 6:53 
GeneralRe: Great work Pinprofessionalskaus12323-Aug-14 20:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web02 | 2.8.141015.1 | Last Updated 5 Sep 2014
Article Copyright 2014 by Anurag Gandhi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid