Asp. NET Instance Tutorial: Creating a PivotTable report

Generation Code: Pivot.zip

A PivotTable report provides a three-dimensional view of the data that you can create in Microsoft Excel, but it doesn't always make it easy to use Excel. You may want to create a PivotTable report in your Web application. Creating a simple PivotTable report can be a very complex task. So, I'm going to not only provide you with a very useful tool to create simple and advanced PivotTable reports, but also to remove some of the mystery that surrounds them.

The goal: we want to be able to convert two-dimensional data from a DataTable into a three-dimensional view.

In most cases, you will populate the data table with query data from the database, such as

SELECT
Salespeople.fullname as [Sales person]
, products.fullname as [Product]
, SUM (Sales.salesamount) as [Sale Amount]
, SUM (Sales.qty) as [Quantity]
From
Sales
JOIN
Salespeople with (NOLOCK)
On salespeople.salespersonid = Sales.salespersonid
JOIN
Products with (NOLOCK)
On products.productcode = Sales.productcode
GROUP by
Salespeople.fullname
, Products.fullname

The query produces the following data table:

Sales person	Product	Quantity	Sale Amount
John	Pens	200	350
John	Pencils	400	500
John	Notebooks	100	300
John	Rulers	50	100
John	Calculators	120	1200
John	Back packs	75	1500
Jane	Pens	225	393.75
Jane	Pencils	335	418.75
Jane	Notebooks	200	600
Jane	Rulers	75	150
Jane	Calculators	80	800
Jane	Back packs	97	1940
Sally	Pens	202	353.5
Sally	Pencils	60V	378.75
Sally	Notebooks	198	600
Sally	Rulers	98	594
Sally	Calculators	80	800
Sally	Back packs	101	By 2020
Sarah	Pens	112	196
Sarah	Pencils	245	306.25
Sarah	Notebooks	198	594
Sarah	Rulers	50	100
Sarah	Calculators	66	660
Sarah	Back packs	50	By 2020

As you can see, this is a two-dimensional table, and it's not a very useful report. So we have to change it and turn it into a more readable datasheet.