473,686 Members | 2,159 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

pivot table in asp.net using sql dataset


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
1 7343
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" >
<HTML>
<HEAD>
<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">
</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:datagr id id="sourceGrid " runat="server" CellSpacing="4" >
<AlternatingIte mStyle
HorizontalAlign ="Right"></AlternatingItem Style>
<ItemStyle HorizontalAlign ="Right"></ItemStyle>
</asp:datagrid></td>
<td>&nbsp;</td>
<td><asp:datagr id 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:datagr id id="pageGrid" runat="server" CellPadding="4"
AutoGenerateCol umns="False">
<HeaderStyle Font-Bold="True"
BackColor="Ligh tGray"></HeaderStyle>
</asp:datagrid></td>
</tr>
<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
"></asp:button></td>

</tr>
</table>
</form>
</body>
</HTML>
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;
BindHorzGrid();
}
}

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
column)
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,
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.Co unt; 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.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();
base.OnInit(e);
}

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

}
#endregion
private void nextBtn_Click(o bject sender, System.EventArg s e)
{
PageIndex = PageIndex + 1;
BindHorzGrid();
}

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

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

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

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

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

}

}
}
}
hth

Dave

Dec 14 '05 #2

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

Similar topics

2
5836
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...
1
4197
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...
0
833
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...
1
3141
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
3
10259
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
4
3324
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
9
6792
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
1
6802
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
22
2722
kcdoell
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...
0
8586
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8772
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8780
tracyyun
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...
0
7603
agi2029
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...
1
6442
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4312
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...
0
4536
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2208
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1941
bsmnconsultancy
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.