By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

pivot table in asp.net using sql dataset

P: n/a

Hi,

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
------------------------------------------------------------------------
kingster's Profile: http://www.highdots.com/forums/m1555
View this thread: http://www.highdots.com/forums/t3145902

Dec 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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" >
<HTML>
<HEAD>
<title>GridDemo</title>
<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"
name="vs_targetSchema">
</HEAD>
<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>
<td>Pivot Data</td>
</tr>
<tr vAlign="top">
<td><asp:datagrid id="sourceGrid" runat="server" CellSpacing="4">
<AlternatingItemStyle
HorizontalAlign="Right"></AlternatingItemStyle>
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:datagrid></td>
<td>&nbsp;</td>
<td><asp:datagrid id="pivotGrid" runat="server"
CellSpacing="4"></asp:datagrid></td>
</tr>
</table>
<br>
<br>
<table cellSpacing="0" cellPadding="0" border="1">
<tr vAlign="top">
<td><asp:datagrid id="pageGrid" runat="server" CellPadding="4"
AutoGenerateColumns="False">
<HeaderStyle Font-Bold="True"
BackColor="LightGray"></HeaderStyle>
</asp:datagrid></td>
</tr>
<tr vAlign="top">
<td><asp:button id="prevBtn" runat="server" Text="<
Prev"></asp:button>&nbsp;&nbsp;&nbsp;
<asp:button id="nextBtn" runat="server" Text="Next
"></asp:button></td>

</tr>
</table>
</form>
</body>
</HTML>
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;
sourceGrid.DataBind();

pivotGrid.DataSource = pivotTbl;
pivotGrid.DataBind();

pageGrid.DataSource = pivotTbl;
pageGrid.DataBind();

// 1st view of HorzGrid
PageIndex = 0;
BindHorzGrid();
}
}

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 );

pageGrid.Columns.Clear();

// 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;
pageGrid.DataBind();
}

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" } );

tbl.AcceptChanges();
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
column)
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,
except
// 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
else
dest.Rows[r][c] = source.Rows[c - 1][r + 1];
}
}
dest.AcceptChanges();
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.
//
InitializeComponent();
base.OnInit(e);
}

/// <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);

}
#endregion
private void nextBtn_Click(object sender, System.EventArgs e)
{
PageIndex = PageIndex + 1;
BindHorzGrid();
}

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

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;
break;

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
).ToString("C");
break;

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

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

}

}
}
}
hth

Dave

Dec 14 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.