pivot tables

[Jerry K via DotNetMonster.com]

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

Imports System
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.
EventArgs) Handles MyBase.Load

'' COMs for excel and office references were added to project

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheets As Excel.Worksheets

Dim ConnectionString As String = _
"Server=jerry;" & _
"DataBase=CTS;" & _
"user ID=sa;password=ruth2jerry"

Dim ptSQL As String
ptSQL = "SELECT * FROM tblActualForecast2"

Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim drSQL As SqlDataReader
Dim dsSQL As DataSet

Dim Row As Integer

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

xlApp.Visible = False

Try
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
cmSQL = New SqlCommand(ptSQL, cnSQL)
drSQL = cmSQL.ExecuteReader

xlSheet.Cells(1, 1).value = "ActualCase"
xlSheet.Cells(1, 2).value = "ActualDate"
xlSheet.Cells(1, 3).value = "ActualComm"
xlSheet.Cells(1, 4).value = "ActualAcctHandler"

Row = 2

While drSQL.Read
xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase")
xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate")
xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm")
xlSheet.Cells(Row, 4).value = drSQL.Item("actualAcctHandler")
Row = Row + 1
End While

Catch ex As Exception
MsgBox(ex.Message)
Finally
drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
End Try

xlSheet.Cells.EntireColumn.AutoFit()

Dim xllastcell As String
xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType.
xlCellTypeLastCell).Address

xlApp.Sheets.Add.name = "CTS Pivot Table"

xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType.
xlDatabase, xlSheet.Range("A1:" & xllastcell))

xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase").
Orientation = Excel.XlPivotFieldOrientation.xlRowField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate").
Orientation = Excel.XlPivotFieldOrientation.xlColumnField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm").
Orientation = Excel.XlPivotFieldOrientation.xlDataField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler").
Orientation = Excel.XlPivotFieldOrientation.xlPageField

' Get the last cell in the pivot table.
xllastcell = xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type.
xlCellTypeLastCell).Address
' Set the number format for the data cells
xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat = "$##,##0.
00"

'' Worth considering ---
xlApp.CommandBars("PivotTable").Visible = False

'''' Group the selection... ??
'''' Here's where I need to the help to group the date column..

xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals
(1) = False
xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()

xlApp.Visible = True

End Sub

End Class

[W.G. Ryan eMVP]

Jerry - the code you're using is at least syntactically correct. I created
a dummy db with those fields and created a pivot table from there. Here's
what I'd advise doing since I don't have access to yoru db (BTW, as a
security measure, change your password as soon as you read this and disable
your SA account - the bad guys have a lot of bots that paruse these
newsgroups looking for stuff just like that- not trying to lecture, just
looking out ;-) ).

Record a new macro. Then, Go ahead and use Get External data and then pull
the data in using Microsoft Query. This code you can ignore although to be
honest, in many instances it's a lot faster than using ADO.NET to get t the
same place.. Then just manually create the code for your pivot table and
stop recording the macro. You'll have a ton of crap code written, but it
will ultimately provide you with the correct code . the only problem other
than erroneous code (which it tneds to generate a lot of) is that it may
hard code stuff in there instead of using relative references. In general
though, this is pretty easy to work around. I know I'm probably just
telling you stuff you already know, but without the data, it's hard for me
to figure it out.

If you want, write me out a small csv or xml file and send it to WilliamRyan
At Gmaildotcom and I'll try to work through . I'll be up for a while
tonight so i'll do what I can for you.

Cheers,

bill
"Jerry K via DotNetMonster.com" <fo***@DotNetMonster.com> wrote in message
news:53***********@DotNetMonster.com...

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

Imports System
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.
EventArgs) Handles MyBase.Load

'' COMs for excel and office references were added to project

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheets As Excel.Worksheets

Dim ConnectionString As String = _
"Server=jerry;" & _
"DataBase=CTS;" & _
"user ID=sa;password=ruth2jerry"

Dim ptSQL As String
ptSQL = "SELECT * FROM tblActualForecast2"

Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim drSQL As SqlDataReader
Dim dsSQL As DataSet

Dim Row As Integer

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

xlApp.Visible = False

Try
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
cmSQL = New SqlCommand(ptSQL, cnSQL)
drSQL = cmSQL.ExecuteReader

xlSheet.Cells(1, 1).value = "ActualCase"
xlSheet.Cells(1, 2).value = "ActualDate"
xlSheet.Cells(1, 3).value = "ActualComm"
xlSheet.Cells(1, 4).value = "ActualAcctHandler"

Row = 2

While drSQL.Read
xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase")
xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate")
xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm")
xlSheet.Cells(Row, 4).value =
drSQL.Item("actualAcctHandler")
Row = Row + 1
End While

Catch ex As Exception
MsgBox(ex.Message)
Finally
drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
End Try

xlSheet.Cells.EntireColumn.AutoFit()

Dim xllastcell As String
xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType.
xlCellTypeLastCell).Address

xlApp.Sheets.Add.name = "CTS Pivot Table"

xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType.
xlDatabase, xlSheet.Range("A1:" & xllastcell))

xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase").
Orientation = Excel.XlPivotFieldOrientation.xlRowField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate").
Orientation = Excel.XlPivotFieldOrientation.xlColumnField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm").
Orientation = Excel.XlPivotFieldOrientation.xlDataField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler").
Orientation = Excel.XlPivotFieldOrientation.xlPageField

' Get the last cell in the pivot table.
xllastcell =
xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type.
xlCellTypeLastCell).Address
' Set the number format for the data cells
xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat =
"$##,##0.
00"

'' Worth considering ---
xlApp.CommandBars("PivotTable").Visible = False

'''' Group the selection... ??
'''' Here's where I need to the help to group the date column..
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals
(1) = False
xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()

xlApp.Visible = True

End Sub

End Class

[W.G. Ryan eMVP]

Jerry - the code you're using is at least syntactically correct. I created
a dummy db with those fields and created a pivot table from there. Here's
what I'd advise doing since I don't have access to yoru db (BTW, as a
security measure, change your password as soon as you read this and disable
your SA account - the bad guys have a lot of bots that paruse these
newsgroups looking for stuff just like that- not trying to lecture, just
looking out ;-) ).

Record a new macro. Then, Go ahead and use Get External data and then pull
the data in using Microsoft Query. This code you can ignore although to be
honest, in many instances it's a lot faster than using ADO.NET to get t the
same place.. Then just manually create the code for your pivot table and
stop recording the macro. You'll have a ton of crap code written, but it
will ultimately provide you with the correct code . the only problem other
than erroneous code (which it tneds to generate a lot of) is that it may
hard code stuff in there instead of using relative references. In general
though, this is pretty easy to work around. I know I'm probably just
telling you stuff you already know, but without the data, it's hard for me
to figure it out.

If you want, write me out a small csv or xml file and send it to WilliamRyan
At Gmaildotcom and I'll try to work through . I'll be up for a while
tonight so i'll do what I can for you.

Cheers,

bill
"Jerry K via DotNetMonster.com" <fo***@DotNetMonster.com> wrote in message
news:53***********@DotNetMonster.com...

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

Imports System
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.
EventArgs) Handles MyBase.Load

'' COMs for excel and office references were added to project

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheets As Excel.Worksheets

Dim ConnectionString As String = _
"Server=jerry;" & _
"DataBase=CTS;" & _
"user ID=sa;password=ruth2jerry"

Dim ptSQL As String
ptSQL = "SELECT * FROM tblActualForecast2"

Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim drSQL As SqlDataReader
Dim dsSQL As DataSet

Dim Row As Integer

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

xlApp.Visible = False

Try
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
cmSQL = New SqlCommand(ptSQL, cnSQL)
drSQL = cmSQL.ExecuteReader

xlSheet.Cells(1, 1).value = "ActualCase"
xlSheet.Cells(1, 2).value = "ActualDate"
xlSheet.Cells(1, 3).value = "ActualComm"
xlSheet.Cells(1, 4).value = "ActualAcctHandler"

Row = 2

While drSQL.Read
xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase")
xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate")
xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm")
xlSheet.Cells(Row, 4).value =
drSQL.Item("actualAcctHandler")
Row = Row + 1
End While

Catch ex As Exception
MsgBox(ex.Message)
Finally
drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
End Try

xlSheet.Cells.EntireColumn.AutoFit()

Dim xllastcell As String
xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType.
xlCellTypeLastCell).Address

xlApp.Sheets.Add.name = "CTS Pivot Table"

xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType.
xlDatabase, xlSheet.Range("A1:" & xllastcell))

xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase").
Orientation = Excel.XlPivotFieldOrientation.xlRowField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate").
Orientation = Excel.XlPivotFieldOrientation.xlColumnField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm").
Orientation = Excel.XlPivotFieldOrientation.xlDataField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler").
Orientation = Excel.XlPivotFieldOrientation.xlPageField

' Get the last cell in the pivot table.
xllastcell =
xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type.
xlCellTypeLastCell).Address
' Set the number format for the data cells
xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat =
"$##,##0.
00"

'' Worth considering ---
xlApp.CommandBars("PivotTable").Visible = False

'''' Group the selection... ??
'''' Here's where I need to the help to group the date column..
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals
(1) = False
xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()

xlApp.Visible = True

End Sub

End Class

[Jerry]

Thanks Bill. I realized the sa,pw after I sent the message. I'll fix it.

I recorded a macro and used the code, modified a bit because I'm using
VB.net and am referencing excel. So my code looks like the following... it
contains hard cell references, but that's ok for now. It worked up until the
last few lines that set up the grouping and that's when I get an errror
message about object not set. If I comment out the grouping lines it works
great.
xlBook.PivotCaches.Add(SourceType:=Excel.XlPivotTa bleSourceType.xlDatabase,
SourceData:= _
"Sheet1!R1C1:R37C4").CreatePivotTable(TableDestina tion:="",
TableName:= _
"PivotTable1")

xlBook.ActiveSheet.PivotTableWizard(TableDestinati on:=xlBook.ActiveSheet.Cells(3, 1))
xlBook.ActiveSheet.Cells(3, 1).Select()
xlBook.ActiveSheet.PivotTables("PivotTable1").Smal lGrid = False
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualCase")
.Orientation = Excel.XlPivotFieldOrientation.xlRowField
.Position = 1
End With
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualDate")
.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
.Position = 1
End With
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualComm")
.Orientation = Excel.XlPivotFieldOrientation.xlDataField
.Position = 1
End With
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualAcctHandler")
.Orientation = Excel.XlPivotFieldOrientation.xlPageField
.Position = 1
End With

''''''' Here's where the trouble is ---
Dim array As Array
Dim xlrange As Excel.Range
xlrange = xlBook.ActiveSheet.range("B3")
xlrange.Group(Start:=True, End:=True, Periods:=array(False, _
,False, False, False, True, True, False))

"Jerry K via DotNetMonster.com" wrote:

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

Imports System
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.
EventArgs) Handles MyBase.Load

'' COMs for excel and office references were added to project

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheets As Excel.Worksheets

Dim ConnectionString As String = _
"Server=jerry;" & _
"DataBase=CTS;" & _
"user ID=sa;password=ruth2jerry"

Dim ptSQL As String
ptSQL = "SELECT * FROM tblActualForecast2"

Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim drSQL As SqlDataReader
Dim dsSQL As DataSet

Dim Row As Integer

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

xlApp.Visible = False

Try
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
cmSQL = New SqlCommand(ptSQL, cnSQL)
drSQL = cmSQL.ExecuteReader

xlSheet.Cells(1, 1).value = "ActualCase"
xlSheet.Cells(1, 2).value = "ActualDate"
xlSheet.Cells(1, 3).value = "ActualComm"
xlSheet.Cells(1, 4).value = "ActualAcctHandler"

Row = 2

While drSQL.Read
xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase")
xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate")
xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm")
xlSheet.Cells(Row, 4).value = drSQL.Item("actualAcctHandler")
Row = Row + 1
End While

Catch ex As Exception
MsgBox(ex.Message)
Finally
drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
End Try

xlSheet.Cells.EntireColumn.AutoFit()

Dim xllastcell As String
xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType.
xlCellTypeLastCell).Address

xlApp.Sheets.Add.name = "CTS Pivot Table"

xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType.
xlDatabase, xlSheet.Range("A1:" & xllastcell))

xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase").
Orientation = Excel.XlPivotFieldOrientation.xlRowField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate").
Orientation = Excel.XlPivotFieldOrientation.xlColumnField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm").
Orientation = Excel.XlPivotFieldOrientation.xlDataField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler").
Orientation = Excel.XlPivotFieldOrientation.xlPageField

' Get the last cell in the pivot table.
xllastcell = xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type.
xlCellTypeLastCell).Address
' Set the number format for the data cells
xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat = "$##,##0.
00"

'' Worth considering ---
xlApp.CommandBars("PivotTable").Visible = False

'''' Group the selection... ??
'''' Here's where I need to the help to group the date column..

xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals
(1) = False
xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()

xlApp.Visible = True

End Sub

End Class