How To Create Report Pivot with Gridview

See more:

C#

ASP.NET

Gridview

Dear All,

I have to create Report Like This:

NO |DESC |DATE-1              |DATE.NOW           |INFO
22 |XXXX |val1 | val2 | Total |val1 | val2 | Total |Val1(now-(date-1))| Val2(now-(date-1))| Total(now-(date-1)) | Total(now)/Total(date-1)

My data table like this one :

No	desc	date	        category   value
1	xxx	10/11/2012	a	   1
2	xxx	10/12/2012	b	   2
3	xxx	10/11/2012	a	   3
4	xxx	10/12/2012	b	   4

I have used pivot table on my query to show in gridview, but the result not show all the category,

Select no, [10/11/2012], [10/12/2012]
From 
(select no, date, category, value from tb_detail) p
Pivot
(
Max (value)
For date in
( 
 [10/11/2012], [10/12/2012]
)
) as pvt
Order by no

And i used pivotgrid, but in info column just show sum of field, not show the calculation like the formula.

What the best way to solve this problem.

Regards,
Chairul