ASP.NET Pivot Table Made Easy with Telerik’s Pivot Grid

Do you have extensive amount of data that you want to make sense of? Then, the Pivot Table is the perfect tool for you. In this blog post I am going to discuss what can be achieved using a Pivot Table in ASP.NET, what features are provided out-of-the-box in Telerik’s Pivot Grid and how to quickly implement a simple scenario.

What is a Pivot Table and what is it good for?

I dare to make the assumption that you work for a company which needs to manage a handful of statistical figures, be it sales, logistics, salaries, prices or units.

Logically, you will store and display that data in a tabular source or control. But does this give your end users all the important information? Does it enable them to see the total price of sold products for a given period of time? Or the count of stores in a region and their profit difference by months? Or max and min revenue by city or each employee? And the final test – is it an easy task to change these relations dynamically at real-time?

All these questions lead to one thought – “I will need to do horrifying formulas and grouping to get this information! Is there a tool I can use to achieve all of that quickly and without all of this effort?” Yes, there is, the mighty Pivot Table.

What is the PivotGrid all about?

The Pivot Table control, albeit complex at first glance, is one of the most powerful and flexible data management tools. It brings tremendous convenience when you organize, summarize and analyze data. The control makes it possible to see trends and patterns instantly, offers data comparisons, calculates all the numbers automatically and as a result – helps you make sense of your data by revealing its meaning.

Power overwhelming

Have you imagined being a Hero or Wizard as a kid? Using the Force to move and control objects around? I’m afraid I have to revoke you from that dream into the harsh reality of being a software developer, however, after getting used to the Pivot Table, you will have the power to control and move thousands and thousands of rows of raw data in the fist of your hand (some call it the mouse).

The most important and useful aspect within the features of the Pivot Table, unquestionably, is the dynamic and flexible re-arrangement of its layout structure. You choose the wrong field by mistake, want to change the relation of products or add another aggregate calculation – no problem, just drag and drop the fields as you wish. And combine that with the calculation power of the control’s engine, which is necessary to handle all this massive information, I guess you start to comprehend how irreplaceable the Pivot Table is for managing data.

Meet Telerik’s Pivot Grid for ASP.NET AJAX

Now, knowing what a Pivot Table is capable of, it is time to introduce you to RadPivotGrid, part of the award-winning  ASP.NET AJAX control toolset provided by Telerik. This advanced Pivot Table control will allow you to implement intuitive and flexible statistical reports in your web application without any particular effort.

RadPivotGrid can be used in various scenarios, where the end-users will be able to generate a report dynamically, sort, filter, analyze or export it to Microsoft Excel for further inquiry. This can be best demonstrated with a ludicrously basic sample, easy like taking a candy from a debugging programmer.

How to create a Pivot Table Step by Step

I’m sure that ideas start to come up in your mind for some real-time implementations using the Pivot Table. I’ll select a simple yet meaningful scenario just to show how easy and effortless it is to include an efficient financial report on your web page. After completing the sample it will be child's play to tell the sum of order freights for each city.

Preparing the data

In this sample I will use the Orders table from the widespread Northwind source:

 
Here is how it looks using a standard Grid control with enabled filtering:

A glance at the layout

Before starting to configure the Pivot Table, let’s see the main regions of its layout:

   Data Area: here go the numeric  
   fields upon which the Pivot Grid  
   makes automatic calculations. In
   this region we will get some
   aggregated results out of our
   data.
   Row Fields Area: this region
   holds the fields that will group
   and divide the data in a     
   meaningful way – e.g.
                                                                              EmployeeName, CustomerID,
                                                                              Country, City, etc.
Column Fields Area:  to complete the picture, this area brings the required relation fields which will subdivide our defined data rows in column groups – e.g. Years, Months, Country, City, etc.

Call in the cavalry

Finally, it’s time to create the Pivot Table. I  will use a straightforward mark-up to answer the question  “What are the total Year Freights for Orders shipped in different countries?” To do that, I set the ShipCountry field as a row field and the Year part of OrderDate as a column field. And to put the finishing touch, I add the Freight numeric field as an aggregate field:

Voilà! See how it looks like:

Feel the power

You can easily change the structure of the Pivot Table just by dragging one of the fields and dropping it onto another region. The example above was just the tip of the iceberg - the combinations and scenarios can be endless. Let’s play some more with our new toy.

I will enable the ConfigurationPanel provided by RadPivotGrid, as well as add some conditional formatting in the code-behind:

 
C#:

The panel contains a list with all of the available fields in the source. I will drag the ShipCity field onto the row fields area so that our report will give us a deeper insight which cities generate significant Orders. And here is the result:

Final Testament

I hope it is now clear how mandatory this control is for managing and organizing data in your ASP.NET AJAX project. You can build complex summaries, sophisticated multi-dimensional table calculations, even bind it to an OLAP cube. Beside these features, constantly improving Telerik’s ASP.NET AJAX PivotGrid provides integrated paging, sorting, filtering, templates, fields drag and drop, skins, range grouping, tooltips configuration, total formatting, exporting, various layout modes and localization.

Why not give it a try?

You can either download a runnable version of the sample above, or download Telerik’s Controls to make avail of all the features provided by the bundle out-of-the-box.

Feel free to go through PivotGrid’s demos to see whether you will meet the exact functionality your company needs. Don’t you think your client or boss would be impressed if you present them with a real-time report using a Pivot Table along with other graphic or diagram controls to back it up? Here is a sample to help your imagination.

And of course, let’s not forget how valuable your feedback is. Was the blog post helpful to you?