473,847 Members | 1,499 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 7359
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="Dem o.aspx.cs"
AutoEventWireup ="false" Inherits="Pivot .Demo" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<title>GridDemo </title>
<meta content="Micros oft Visual Studio .NET 7.1" name="GENERATOR ">
<meta content="C#" name="CODE_LANG UAGE">
<meta content="JavaSc ript" name="vs_defaul tClientScript">
<meta content="http://schemas.microso ft.com/intellisense/ie5"
name="vs_target Schema">
<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>&nbsp;&nbsp ;&nbsp;
<td>Pivot Data</td>
<tr vAlign="top">
<td><asp:datagr id id="sourceGrid " runat="server" CellSpacing="4" >
<AlternatingIte mStyle
HorizontalAlign ="Right"></AlternatingItem Style>
<ItemStyle HorizontalAlign ="Right"></ItemStyle>
<td><asp:datagr id id="pivotGrid" runat="server"
CellSpacing="4" ></asp:datagrid></td>
<table cellSpacing="0" cellPadding="0" border="1">
<tr vAlign="top">
<td><asp:datagr id id="pageGrid" runat="server" CellPadding="4"
AutoGenerateCol umns="False">
<HeaderStyle Font-Bold="True"
BackColor="Ligh tGray"></HeaderStyle>
<tr vAlign="top">
<td><asp:butt on id="prevBtn" runat="server" Text="<
Prev"></asp:button>&nbs p;&nbsp;&nbsp;
<asp:button id="nextBtn" runat="server" Text="Next

c# code behind

using System;
using System.Collecti ons;
using System.Componen tModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.Sess ionState;
using System.Web.UI;
using System.Web.UI.W ebControls;
using System.Web.UI.H tmlControls;

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

private DataTable sourceTbl;
private DataTable pivotTbl;

protected System.Web.UI.W ebControls.Data Grid pivotGrid;
protected System.Web.UI.W ebControls.Data Grid pageGrid;
protected System.Web.UI.W ebControls.Butt on nextBtn;
protected System.Web.UI.W ebControls.Butt on prevBtn;
protected System.Web.UI.W ebControls.Data Grid sourceGrid;

private void Page_Load(objec t sender, System.EventArg s 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.Data Source = sourceTbl;
sourceGrid.Data Bind();

pivotGrid.DataS ource = pivotTbl;
pivotGrid.DataB ind();

pageGrid.DataSo urce = pivotTbl;
pageGrid.DataBi nd();

// 1st view of HorzGrid
PageIndex = 0;

private int PageIndex
get{ return (int)ViewState["PageIndex"]; }
set{ ViewState["PageIndex"] = value; }
private void BindHorzGrid()
int colCount = pivotTbl.Column s.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 );

pageGrid.Column s.Clear();

// frist create the "anchored" column[0] -- Product
BoundColumn c = new BoundColumn();
c.HeaderText = pivotTbl.Column s[0].ColumnName;
c.HeaderStyle.F ont.Bold = true;
c.DataField = pivotTbl.Column s[0].ColumnName;
c.ItemStyle.Fon t.Bold = true;
pageGrid.Column s.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.W idth = Unit.Pixel( 150 );
c.HeaderStyle.H orizontalAlign = HorizontalAlign .Center;
c.ItemStyle.Wid th = Unit.Pixel( 150 );
c.HeaderText = pivotTbl.Column s[ i + 1 ].ColumnName;
c.DataField = pivotTbl.Column s[ i + 1].ColumnName;
pageGrid.Column s.Add( c );

// now bind the grid to our new bound columns
pageGrid.DataSo urce = pivotTbl;
pageGrid.DataBi nd();

private DataTable GetSourceTable( )
DataTable tbl = new DataTable("Prod ucts");

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.0 0, 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.0 0, 16330.00,
26, 4, "Classic" } );

tbl.AcceptChang es();
return tbl;

public DataTable PivotTable( DataTable source )
DataTable dest = new DataTable("Pivo ted" + source.TableNam e );

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

// the remaining dest columns are from each source table row (1st
foreach( DataRow r in source.Rows )
dest.Columns.Ad d( 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.Co unt; 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];
dest.AcceptChan ges();
return dest;

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

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeCompo nent()
this.pageGrid.I temDataBound += new
System.Web.UI.W ebControls.Data GridItemEventHa ndler(this.page Grid_ItemDataBo und);
this.prevBtn.Cl ick += new System.EventHan dler(this.prevB tn_Click);
this.nextBtn.Cl ick += new System.EventHan dler(this.nextB tn_Click);
this.Load += new System.EventHan dler(this.Page_ Load);

private void nextBtn_Click(o bject sender, System.EventArg s e)
PageIndex = PageIndex + 1;

private void prevBtn_Click(o bject sender, System.EventArg s e)
PageIndex = PageIndex - 1;

private void pageGrid_ItemDa taBound(object sender,
System.Web.UI.W ebControls.Data GridItemEventAr gs e)
DataGridItem item = e.Item;

if ( item.ItemType == ListItemType.It em ||
item.ItemType == ListItemType.Al ternatingItem )
{ // detail row
item.Cells[0].BackColor = Color.LightGray ;
for( int i = 1; i < item.Cells.Coun t; i++ )
switch( item.Cells[0].Text )
case "Comments":
item.Cells[i].Wrap = true;
item.Cells[i].HorizontalAlig n = HorizontalAlign .Left;

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

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

item.Cells[i].HorizontalAlig n = HorizontalAlign .Center;
else if ( item.ItemType == ListItemType.He ader )
{ // 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 Design view, then choose Pivot table view, I get the PivotTable Field list, from which I can choose to add fields to the view. 1. How do you get RID of a field once you put it in? I can not right click on anywhere in the view and have ANY of the submenu options be availble. From the PivotTable...
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 only problem is that because there are so many years going across, whenever I try to do this the computer tells me it was only able to do an incomplete list because the full list exceeds 65,000 lines. Apart from having to break the dataset down into smaller datasets, is there some way that I...
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 ) in C# with Excel 2003. I have complete data in one dataset and i would like to render it on Excel similar to pivot table Is anybody have idea how Excel renders and presents data in the form of pivot table?Are is there any articles which explain the rendering logic of pivot tables Thanks in...
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 analysis with excel pivot table function. is it possible to do that?? if so, any sample for me as i am a new in designing pivot table with asp.net million thanks
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 a 'date' column field. I'd like to group the date column field by months and quarters, but can't come up with the correct code. Here is the code I've written to create the pivot table. Any help to code grouping the date column field would be appreciated. Thanks, Jerry
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 Tool, Office 11. Web service comes from .NET application Thanks in advance! Andrew
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 3-month return, Libor 6-month return, US Treasury Bonds, the Prime rate, and so-forth. We associate a security with one of those rates. There are a set of rates for each calendar day, and the rates for that
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 underlying query and refresh pivot chart but it's a big dataset and takes some time to load - my users are impatient! 2. If this is not possible to code filters on the chart are the any
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 report is different than anything I have done but I am hoping that for someone out there has. Visually it looks like the following: Product Name__Week 1__Week 2__Week 3__Week 4__Week 5__BudgetGWP__PriorGWP Product 1________45______56_______0_______0_______12_______300_________250__ Product...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.