Pivot Grid in ASP.NET MVC

• Download source files - 9.7 MB

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:

@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.

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.

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:

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:

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

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