Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > .NET 2.0 and Visual Studio. 2005 > .NET Framework 2.0
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
.NET Framework 2.0 For discussion of the Microsoft .NET Framework 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the .NET Framework 2.0 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 24th, 2005, 02:30 PM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Create a Pivot table report using vb.net

Hi All!!

I need to create a pivot table using VB.NET. I am using a dataset to create a Excel pivot table. Can some one suggest how should I go about it?

Please ASAP.

Thanks and regards
Spowari

Reply With Quote
  #2 (permalink)  
Old March 28th, 2005, 04:38 AM
Friend of Wrox
 
Join Date: Jul 2004
Location: , , India.
Posts: 345
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to gokul_blr Send a message via Yahoo to gokul_blr
Default

http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Gokulan Ethiraj
Reply With Quote
  #3 (permalink)  
Old March 28th, 2005, 04:09 PM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Gokulan ,

Thanks a lot for the reply. The link that you sent me doesnt say a word about .NET . I need to create a Pivot table report using VB.NET in ASP.NET. I have all the data in a dataset.

I will appriciate ur help in this regard

Thanks
spowari

Reply With Quote
  #4 (permalink)  
Old March 29th, 2005, 02:19 PM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Are you using v2.0 of the framework or v1.1?


Hal Levy
I am here to help you, not do it for you.
That is, unless you hire me. I am looking for work.
Reply With Quote
  #5 (permalink)  
Old November 28th, 2006, 09:54 AM
Registered User
 
Join Date: Nov 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
Quote:
quote:Originally posted by spowari
 Hi All!!

I need to create a pivot table using VB.NET. I am using a dataset to create a Excel pivot table. Can some one suggest how should I go about it?

Please ASAP.

Thanks and regards
Spowari

Reply With Quote
  #6 (permalink)  
Old November 28th, 2006, 09:57 AM
Registered User
 
Join Date: Nov 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
  I have the same requirement. could you please help on how I can implement pivot table using dot net, if you have done this already?

thanks in advance,
kavitha

Quote:
quote:Originally posted by spowari
 Hi Gokulan ,

Thanks  a lot for the reply. The link that you sent me doesnt say a word about .NET . I need to create a Pivot table report using VB.NET in ASP.NET. I have all the data in a dataset.

I will appriciate ur help in this regard

Thanks
spowari

Reply With Quote
  #7 (permalink)  
Old December 11th, 2009, 04:22 PM
Registered User
 
Join Date: Dec 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Talking from my code

Private Sub CreatePivotTable(ByVal strClient As String)
Dim DS As New DataSet
Dim objPivotTable As Excel.PivotTable
Dim objField As Excel.PivotField
Dim strSQL As String
Dim strTitle As String
Dim strDept As String

'Grab the department name.
If Not strClient = "(All)" Then
strSQL = "SELECT FacilityName FROM Facilities WITH (NOLOCK) where FacilityCode = " & Left(strClient, strClient.Length - 2) + "'"
Dim command As New SqlCommand(strSQL, _Connection)
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(DS)
strTitle = StrConv(DS.Tables(0).Rows(0).Item(0), VbStrConv.ProperCase)
Select Case Replace(Right(strClient, 2), "'", "")
Case "1"
strDept = "Liability"
Case "2"
strDept = "Outpatient"
Case "3"
strDept = "Inpatient"
Case "4"
strDept = "Out of State"
Case "6"
strDept = "Charity"
Case "8"
strDept = "Special Project"
Case "U"
strDept = "Undocumented"
End Select
Else
strTitle = Me.ReportTitle
strDept = "Rolled Up"
End If

objPivotTable = m_xlWS.PivotTableWizard
With objPivotTable
.PivotFields("Client").Orientation = Excel.XlPivotFieldOrientation.xlPageField
.PivotFields("Assignment Date").Orientation = Excel.XlPivotFieldOrientation.xlRowField
.PivotFields("Program").Orientation = Excel.XlPivotFieldOrientation.xlRowField

.PivotFields("Account").Orientation = Excel.XlPivotFieldOrientation.xlDataField
.PivotFields("Age").Orientation = Excel.XlPivotFieldOrientation.xlDataField

.DataFields(1).Function = Excel.XlConsolidationFunction.xlCount
.DataFields(2).Function = Excel.XlConsolidationFunction.xlAverage

.PivotFields("Count of Account").Caption = "Number of Accounts"
.PivotFields("Average of Age").NumberFormat = "0"
.PivotFields("Average of Age").Caption = "Average Turnaround from Placement to Certification"

.Format(Excel.XlPivotFormatType.xlReport1)
.Application.Cells.Font.Size = 8

.PivotFields("Client").CurrentPage = Replace(strClient, "'", "")

.NullString = "0"
End With

m_xlPivotWS = m_xlWB.ActiveSheet
With m_xlPivotWS
If strClient = "(All)" Then
.Name = "Rollup Pivot"
Else
.Name = Replace(strClient, "'", "") & " - Pivot Table"
End If
If Not _totals.ContainsKey(strDept) Then
_totals.Add(strDept, New Integer(1) {0, 0})
End If
.Rows("1:11").Insert(Excel.XlInsertShiftDirection. xlShiftDown)
'set the pivot page setup.
Dim pagesetup As Excel.PageSetup = .PageSetup
With pagesetup
.PrintTitleRows = "$1:$12"
.PrintTitleColumns = ""
.RightHeader = _
"&""Arial""&14Certification Turnaround Time Report by Placement Month" & Chr(10) & "&12" & strTitle & Chr(10) & strDept
.LeftHeader = "&G"
.LeftHeaderPicture.Filename = "C:\Inetpub\wwwroot\CHNWebPortal\Images\chn.pn g"
.LeftHeaderPicture.Height = 43
.LeftHeaderPicture.Width = 190
.LeftFooter = Today.Date()
.CenterFooter = "Confidential"
.RightFooter = "©2009 Cardon Healthcare Network, Inc."
.TopMargin = m_xlApp.InchesToPoints(0.5)
.BottomMargin = m_xlApp.InchesToPoints(0.8)
.HeaderMargin = m_xlApp.InchesToPoints(0.25)
.FooterMargin = m_xlApp.InchesToPoints(0.25)
.LeftMargin = m_xlApp.InchesToPoints(0.03)
.RightMargin = m_xlApp.InchesToPoints(0.03)
.CenterHorizontally = True
.Orientation = XlPageOrientation.xlLandscape
End With
End With

Dim temp() As Integer = _totals(strDept)
temp(0) += objPivotTable.GetData("'Number of Accounts' 'Grand Total'")
temp(1) += objPivotTable.GetData("'Average Turnaround from Placement to Certification' 'Grand Total'")
_totals(strDept) = temp

If Not objField Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objField)
objField = Nothing
End If

If Not objPivotTable Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objPivotTable)
objPivotTable = Nothing
End If
End Sub
Reply With Quote
  #8 (permalink)  
Old September 29th, 2010, 09:07 PM
Registered User
 
Join Date: Sep 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi i have the same problem but i need both the codes ...that is i am displaying the pivot table on a web page using Visual studio.I need the asp.net code that is the .aspx file and the code behind it in vb or c#.



Thank you.
Abhinav Singh Bhadoria
Reply With Quote
  #9 (permalink)  
Old December 7th, 2010, 11:57 PM
Registered User
 
Join Date: Dec 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

C#/.NET Component Recommend:
Spire.XLS for .NET
Spire.Doc for .NET
Reply With Quote
  #10 (permalink)  
Old June 25th, 2015, 05:26 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating a Pivot Table from a DataTable using VB.Net

This code allows you to create a pivot table (sorted by either the column values, row values or both). First the code that creates the datatable and calls the function:

Code:
            Dim TaskTable As New DataTable
            Dim dr As DataRow

            Dim TaskPivotTable As New DataTable

            TaskTable = MailboxDs.Tables.Add("TaskPivotTable")
            With MailboxDs.Tables("TaskPivotTable")
                .Columns.Add(New DataColumn("BowlType", GetType(String)))
                .Columns.Add(New DataColumn("ColorCode", GetType(String)))
                .Columns.Add(New DataColumn("Remaining", GetType(Integer)))
            End With

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "CW"
            dr.Item("Remaining") = 1

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "GW"
            dr.Item("Remaining") = 2

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "BI"
            dr.Item("Remaining") = 3

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "BI"
            dr.Item("Remaining") = 3

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "815"
            dr.Item("ColorCode") = "CW"
            dr.Item("Remaining") = 1

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "815"
            dr.Item("ColorCode") = "WN"
            dr.Item("Remaining") = 2

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "809"
            dr.Item("ColorCode") = "WN"
            dr.Item("Remaining") = 2

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "GW"
            dr.Item("Remaining") = 10


            TaskPivotTable = CreatePivotTable(TaskTable, 1, 0, 2, True, True)
Now the function that creates the pivot table:

Code:

    Private Function CreatePivotTable(ByVal OrigTable As DataTable, Optional ByVal pivotColumnOrdinal As Integer = 0, Optional ByVal pivotRowOrdinal As Integer = 1, _
            Optional ByVal pivotDataOrdinal As Integer = 3, Optional ByVal SortColumn As Boolean = True, Optional ByVal SortRow As Boolean = True) As DataTable

        Dim PivotTable As New DataTable
        Dim OrigArray() As DataRow
        Dim dr As DataRow
        Dim SortString As String
        Dim origRowInd As Integer
        Dim PivotRowInd As Integer
        Dim PivotcolInd As Integer

        Dim CurRowInd As Integer
        Dim CurColInd As Integer
        Dim teststr As String


        Try
            ' add pivot column name 
            PivotTable.Columns.Add(OrigTable.Columns(pivotRowOrdinal).ColumnName)
            ' add pivot column values in each row as column headers to new Table 

            If (SortColumn = True) Then
                SortString = OrigTable.Columns(pivotColumnOrdinal).ColumnName + " ASC"
            Else
                SortString = " "
            End If


            OrigArray = OrigTable.Select("", SortString, DataViewRowState.CurrentRows)

            For origRowInd = 0 To OrigArray.GetUpperBound(0)
                Try
                    PivotTable.Columns.Add(OrigArray(origRowInd).Item(pivotColumnOrdinal))

                Catch ex As Exception

                End Try
            Next

            For PivotcolInd = 0 To PivotTable.Columns.Count - 1
                teststr = PivotTable.Columns(PivotcolInd).ColumnName
            Next

            If (SortRow = True) Then
                SortString = OrigTable.Columns(pivotRowOrdinal).ColumnName + " ASC"
            Else
                SortString = " "
            End If

            OrigArray = OrigTable.Select("", SortString, DataViewRowState.CurrentRows)

            ' loop through rows 
            For origRowInd = 0 To OrigArray.GetUpperBound(0)
                teststr = OrigArray(origRowInd).Item(pivotRowOrdinal)
                For PivotRowInd = 0 To PivotTable.Rows.Count - 1
                    teststr = PivotTable.Rows(PivotRowInd).Item(0)
                    If (OrigArray(origRowInd).Item(pivotRowOrdinal) = PivotTable.Rows(PivotRowInd).Item(0)) Then
                        CurRowInd = PivotRowInd
                        GoTo RowFound
                    End If
                Next

                'add the DataRow to the new table 
                CurRowInd = PivotTable.Rows.Count
                dr = PivotTable.NewRow()
                dr.Item(0) = OrigArray(origRowInd).Item(pivotRowOrdinal)
                teststr = dr.Item(0)
                PivotTable.Rows.Add(dr)

                For PivotcolInd = 1 To PivotTable.Columns.Count - 1
                    PivotTable.Rows(CurRowInd).Item(PivotcolInd) = 0
                Next


RowFound:
                ' loop through columns 
                For PivotcolInd = 0 To PivotTable.Columns.Count - 1
                    teststr = OrigArray(origRowInd).Item(pivotColumnOrdinal)
                    If (OrigArray(origRowInd).Item(pivotColumnOrdinal) = PivotTable.Columns(PivotcolInd).ColumnName) Then
                        CurColInd = PivotcolInd
                        GoTo ColumnFound
                    End If
                Next

ColumnFound:
                PivotTable.Rows(CurRowInd).Item(CurColInd) = PivotTable.Rows(CurRowInd)(CurColInd) + OrigArray(origRowInd)(pivotDataOrdinal)
                teststr = PivotTable.Rows(CurRowInd).Item(0) + " - " + PivotTable.Columns(CurColInd).ColumnName + " - " + PivotTable.Rows(CurRowInd).Item(CurColInd)
            Next

            For CurRowInd = 0 To PivotTable.Rows.Count - 1
                For CurColInd = 0 To PivotTable.Columns.Count - 1
                    teststr = PivotTable.Rows(CurRowInd).Item(0) + " - " + PivotTable.Columns(CurColInd).ColumnName + " - " + PivotTable.Rows(CurRowInd).Item(CurColInd)
                Next
            Next

        Catch ex As Exception

        End Try

        Return PivotTable

    End Function
I hope that this is helpful.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
simple report and crystal report in vb.net saket123 .NET Framework 2.0 0 August 13th, 2008 07:55 AM
Pivot table on web using asp.net raj9211 General .NET 1 May 7th, 2008 12:39 AM
How to create Pivot Table on a Web form cesemj ASP.NET 2.0 Basics 0 October 2nd, 2007 02:14 PM
Best way to create a Report in VB.NET ETSUSnake VB How-To 0 October 23rd, 2006 12:54 PM
How to use create table command in VB.net ahali ADO.NET 2 February 21st, 2006 01:55 AM



All times are GMT -4. The time now is 08:11 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.