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
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
SQL Statement in my case it's MySQL but the PivotGrid doesn't care.
Here is the PivotGrid markup (zoom code view):
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.