ASP.NET Pivot Table made easy with DevExpress PivotGrid

Have you ever taken results directly from the database admin and dropped it into an Excel spreadsheet just to do a pivot table? I have many times and it's a painful process to keep it updated for every meeting.

Recently I found a great solution to this problem using an ASP.NET PivotTable from DevExpress.

Before you run off and completely ignore the rest of my post let me give you a few suggestion.

• Look at the features pages for the control and especially the Demo.
• There is an excellent 10 minute video on getting started at tv.devexpress.com/ASPxPivotGrid101intro.movie
  

My first use of the DevExpress PivotGrid control was to convert an existing Excel spreadsheet I updated frequently for one of my clients. It simply shows the number of registered users for their website by State and grouped by month/year since 2006.

I'm going to reduce the data to just a few years and only a few months for this example. As you will see showing the full dataset is as easy as updating the SQL statement, the PivotGrid will remain the same.

Database Schema for example
User
-UserID
-Created
UserLocation
-UserID
-State

SQL Statement in my case it's MySQL but the PivotGrid doesn't care.
<br />SELECT<br />count(User.UserID) As Users<br />,UserLocation.State As State<br />,date(User.Created) As 'Date'<br />FROM User INNER JOIN UserLocation ON User.UserID = UserLocation.UserID<br />WHERE year(User.Created) IN (2006,2007,2008) AND UserLocation.State IN ('WY','NM','NV')<br />AND month(User.Created) BETWEEN 1 AND 3<br />GROUP BY date(User.Created),UserLocation.State<br />

Here is the PivotGrid markup (zoom code view):
<br /><dxwpg:aspxpivotgrid id="ASPxPivotGrid" runat="server" cssclass="" datasourceid="MySqlDataSource_Users" enablecallbacks="false" rowsperpage="100"><br /> <fields><br /> <dxwpg:pivotgridfield fieldname="Users" id="fieldUsers" area="DataArea" sortorder="Descending" areaindex="0" caption="Users"><br /> </dxwpg:PivotGridField><br /> <dxwpg:pivotgridfield fieldname="State" id="fieldState" area="RowArea" areaindex="0" caption="State"> <br /> </dxwpg:PivotGridField><br /> <dxwpg:pivotgridfield fieldname="Date" id="fieldYear" area="ColumnArea" areaindex="0" groupinterval="DateYear" caption="Year"><br /> </dxwpg:PivotGridField><br /> <dxwpg:pivotgridfield fieldname="Date" id="fieldMonth" area="ColumnArea" areaindex="0" groupinterval="DateMonth" caption="Month"><br /> </dxwpg:PivotGridField><br /> </fields><br /></dxwpg:ASPxPivotGrid><br />

And this is what the actual PivotGrid looks like (view full size image):


The column and row fields in the PivotGrid are draggable and will change the visualization of your data immediately, no change to SQL statement required. The component actually does all the work once it has the dataset. Take a look at this example.

As you can see it is very easy to visualize your data using this PivotGrid component and this is a very simple example. If you want to learn more and see all the possibilities go to DevExpress.com website and take a look at the PivotGrid feature and Live Demo pages.

If you have any questions or comments please feel free to post a comment to this post. I will have a more advanced post on the PivotGrid next month after I have a little more experience with it.

Finally here is a standard GridView of the same dataset.