472,353 Members | 983 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

pivot table in asp.net using sql dataset


I have a regular dataset and all i want to do is make a pivot table
display in a browser with the datasource of the pivot table to be this
dataset and then the end-user will be able to do whatever they want ...
i dont need to do any special formatting just a straigh ot pivot tables

usign sql server, asp.net, vb.net, OWC 10
kingster's Profile: http://www.highdots.com/forums/m1555
View this thread: http://www.highdots.com/forums/t3145902

Dec 13 '05 #1
1 7207
The following code sample is not mine but I had looked at it before on
a project.. maybe it will give you some ideas..

demo.aspx file:
<%@ Page language="c#" Codebehind="Demo.aspx.cs"
AutoEventWireup="false" Inherits="Pivot.Demo" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
<body MS_POSITIONING="FlowLayout">
<form id="Form1" method="post" runat="server">
<table cellSpacing="0" cellPadding="0" border="0">
<tr vAlign="top">
<td>Raw Data</td>
<td>Pivot Data</td>
<tr vAlign="top">
<td><asp:datagrid id="sourceGrid" runat="server" CellSpacing="4">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
<td><asp:datagrid id="pivotGrid" runat="server"
<table cellSpacing="0" cellPadding="0" border="1">
<tr vAlign="top">
<td><asp:datagrid id="pageGrid" runat="server" CellPadding="4"
<HeaderStyle Font-Bold="True"
<tr vAlign="top">
<td><asp:button id="prevBtn" runat="server" Text="<
<asp:button id="nextBtn" runat="server" Text="Next

c# code behind

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace Pivot
/// <summary>
/// Demo Page to Show Pivot table in Horizontal Paged Grid
/// </summary>
public class Demo : System.Web.UI.Page
const int pageSize = 3; // constant number of columns displayed in
each page

private DataTable sourceTbl;
private DataTable pivotTbl;

protected System.Web.UI.WebControls.DataGrid pivotGrid;
protected System.Web.UI.WebControls.DataGrid pageGrid;
protected System.Web.UI.WebControls.Button nextBtn;
protected System.Web.UI.WebControls.Button prevBtn;
protected System.Web.UI.WebControls.DataGrid sourceGrid;

private void Page_Load(object sender, System.EventArgs e)
// we'll get the tables on every postback, but Session state would
do fine as well
sourceTbl = GetSourceTable();
pivotTbl = PivotTable( sourceTbl );

if ( ! IsPostBack )
{ // 1st time, setup the static grids
sourceGrid.DataSource = sourceTbl;

pivotGrid.DataSource = pivotTbl;

pageGrid.DataSource = pivotTbl;

// 1st view of HorzGrid
PageIndex = 0;

private int PageIndex
get{ return (int)ViewState["PageIndex"]; }
set{ ViewState["PageIndex"] = value; }
private void BindHorzGrid()
int colCount = pivotTbl.Columns.Count - 1;
int pageMax = colCount / pageSize;

if ( PageIndex >= pageMax )
PageIndex = pageMax;
else if ( PageIndex <= 0 )
PageIndex = 0;

prevBtn.Enabled = ( PageIndex > 0 );
nextBtn.Enabled = ( PageIndex < pageMax );


// frist create the "anchored" column[0] -- Product
BoundColumn c = new BoundColumn();
c.HeaderText = pivotTbl.Columns[0].ColumnName;
c.HeaderStyle.Font.Bold = true;
c.DataField = pivotTbl.Columns[0].ColumnName;
c.ItemStyle.Font.Bold = true;
pageGrid.Columns.Add( c );

// now create the bound columns for this page's set of columns
int count = 0;
for( int i = PageIndex * pageSize; count < pageSize && i < colCount;
i++, count++ )
c = new BoundColumn();
c.HeaderStyle.Width = Unit.Pixel( 150 );
c.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
c.ItemStyle.Width = Unit.Pixel( 150 );
c.HeaderText = pivotTbl.Columns[ i + 1 ].ColumnName;
c.DataField = pivotTbl.Columns[ i + 1].ColumnName;
pageGrid.Columns.Add( c );

// now bind the grid to our new bound columns
pageGrid.DataSource = pivotTbl;

private DataTable GetSourceTable()
DataTable tbl = new DataTable("Products");

tbl.Columns.Add("Product", typeof( string ) );
tbl.Columns.Add("Low Msrp", typeof( double ) );
tbl.Columns.Add("Avg Msrp", typeof( double ) );
tbl.Columns.Add( "Mpg", typeof( int ) );
tbl.Columns.Add( "Passengers", typeof( int ) );
tbl.Columns.Add( "Comments", typeof( string ) );

tbl.Rows.Add( new object[] { "Acura RSX", 19200.00, 20025.00, 22,
5, "Sporty Coupe" } );
tbl.Rows.Add( new object[] { "Ford Mustang", 17150.00, 18150.00,
19, 6, "Hot Model" } );
tbl.Rows.Add( new object[] { "Hyundai Tiburon", 16050.00, 17000.00,
26, 4, "Economy fine" } );
tbl.Rows.Add( new object[] { "Mini Cooper",16000.00, 16499.00, 31,
4, "Plenty Fun" } );

tbl.Rows.Add( new object[] { "Mitsubish Eclipse", 18799.00,
19499.00, 19, 3, "Fast" } );
tbl.Rows.Add( new object[] { "Pontiac GTO", 30100.00, 31795.00, 14,
5, "Old School" } );
tbl.Rows.Add( new object[] { "Toyota Celica", 17000.00, 17340.00,
22, 4, "Economic" } );
tbl.Rows.Add( new object[] { "Volkswagen Beetle",16220.00, 16330.00,
26, 4, "Classic" } );

return tbl;

public DataTable PivotTable( DataTable source )
DataTable dest = new DataTable("Pivoted" + source.TableName );

// create shchema (string columns) for the destination
// the first column is for the source column name
dest.Columns.Add( " " );

// the remaining dest columns are from each source table row (1st
foreach( DataRow r in source.Rows )
dest.Columns.Add( r[0].ToString() ); // assign each row the Product
name (r[0])

// now add one row to the dest table for each column in the source,
// the first which is the Product, in our case
for( int i = 0; i < source.Columns.Count - 1; i++ )
dest.Rows.Add( dest.NewRow() );

// now move the source columns to their position in the dest
row/cell matrix
// starting down the destination rows, and across the columns
for( int r = 0; r < dest.Rows.Count; r++ )
for( int c = 0; c < dest.Columns.Count; c++ )
if ( c == 0 )
dest.Rows[r][0] = source.Columns[r + 1].ColumnName; // the
Product name
dest.Rows[r][c] = source.Rows[c - 1][r + 1];
return dest;

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
// CODEGEN: This call is required by the ASP.NET Web Form Designer.

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
this.pageGrid.ItemDataBound += new
System.Web.UI.WebControls.DataGridItemEventHandler (this.pageGrid_ItemDataBound);
this.prevBtn.Click += new System.EventHandler(this.prevBtn_Click);
this.nextBtn.Click += new System.EventHandler(this.nextBtn_Click);
this.Load += new System.EventHandler(this.Page_Load);

private void nextBtn_Click(object sender, System.EventArgs e)
PageIndex = PageIndex + 1;

private void prevBtn_Click(object sender, System.EventArgs e)
PageIndex = PageIndex - 1;

private void pageGrid_ItemDataBound(object sender,
System.Web.UI.WebControls.DataGridItemEventArgs e)
DataGridItem item = e.Item;

if ( item.ItemType == ListItemType.Item ||
item.ItemType == ListItemType.AlternatingItem )
{ // detail row
item.Cells[0].BackColor = Color.LightGray;
for( int i = 1; i < item.Cells.Count; i++ )
switch( item.Cells[0].Text )
case "Comments":
item.Cells[i].Wrap = true;
item.Cells[i].HorizontalAlign = HorizontalAlign.Left;

case "Low Msrp":
case "Avg Msrp":
item.Cells[i].Wrap = false;
item.Cells[i].HorizontalAlign = HorizontalAlign.Right;
item.Cells[i].Text = Double.Parse( item.Cells[i].Text

case "Mpg":
case "Passengers":
item.Cells[i].Wrap = false;
item.Cells[i].HorizontalAlign = HorizontalAlign.Right;

item.Cells[i].HorizontalAlign = HorizontalAlign.Center;
else if ( item.ItemType == ListItemType.Header )
{ // header row




Dec 14 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Rob | last post by:
I'm just getting around to using pivot tables and charts. I find the Pivot table interface to be INCREDIBLY frustrating. When I view a table in...
by: matthew kramer | last post by:
Hi, I'm using the reverse pivot technique on an excel spreadsheet to create a list dataset from which I can make a pivot table in excell. The...
by: Rami | last post by:
Has any body tried using pivot tables in C# I am trying to achieve pivot table functionality ( Rendering Row fields / Column Fields / Data fields )...
by: Grey | last post by:
I have created a asp.net form for user to input data. After input the data, user need to click a button to export the input data to excel for data...
by: Jerry K via DotNetMonster.com | last post by:
I'm creating a pivot table using vb.net and the data is from sqlserver (desktop). I have been successful at creating the pivot table, which includes...
by: Andrew van Zyl | last post by:
Hi Folks Can anyone direct me to a simple example of using a pivot table to consume a dataset from a web service? Using Web Service Reference...
by: PeteCresswell | last post by:
I've got something called "Reference Rates". The idea is that on a given day, we have various rates of return for various entities. e.g. Libor...
by: thomas.wordsworth | last post by:
This is a bit of a two-part question. 1. Is there a way to control what filters are placed on a pivot chart via vba. I can easily change the...
by: kcdoell | last post by:
I have been trying for the last several days to create a query that will give me all of the values I need to create a report. Background: The...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.