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=XXXXXXXXX"

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

Hi,
The grouping method within a pivot table is actually the grouping of a
regular Range. In vba:
'-------------------------------------------------------
Sub test()
Dim pt As PivotTable, pf As PivotField, rg As Range

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("date")
Set rg = pf.LabelRange 'ref to the field header

'group by month
rg.Group Start:=True, End:=True, _
Periods:=Array(False, False, False, False, True, False, False)

End Sub
'------------------------------------------------------------------------

The best way to determine code (and specially with PivotTables) is to
manually go through the process in Excel with the Macro Recorder 'On' then
study the generated code.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jerry" 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=XXXXXXXXX"

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