Filter a DateTime field for year,month and day in excel pivot

Question

I'm using EPPlus to create Excel reports. Now i'm trying to create a Pivot with a DateTime PageField, so that the user can filter the period he want to see by himself. But although i can filter this for year,month or days in the according data worksheet by default, i don't get it working in the Pivot.

Here is what i have:

Dim wsPivot = excel.Workbook.Worksheets.Add("Pivot")
Dim wsData = excel.Workbook.Worksheets.Add("Data")
Dim source = workSheet.GetDataSource
wsData.Cells("A1").LoadFromDataTable(source, True, OfficeOpenXml.Table.TableStyles.Medium6)
For Each col As DataColumn In source.Columns
    If col.DataType = GetType(Date) Then
        Dim colNumber = col.Ordinal + 1
        Dim range = wsData.Cells(1, colNumber, source.Rows.Count, colNumber)
        range.Style.Numberformat.Format = "dd.mm.yyyy"
   End If
Next
Dim dataRange = wsData.Cells(wsData.Dimension.Address.ToString())
dataRange.AutoFitColumns()
Dim pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells("A3"), dataRange, "Open Contacts")
pivotTable.PageFields.Add(pivotTable.Fields("OwnedAt")) '** this is the date column i want to group **'
pivotTable.PageFields.Add(pivotTable.Fields("Owner"))
pivotTable.RowFields.Add(pivotTable.Fields("Country"))
pivotTable.ColumnFields.Add(pivotTable.Fields("Status"))
pivotTable.DataFields.Add(pivotTable.Fields("Count"))

It's only possible to select datetime values from the list including hours and minutes and not to group by month f.e.(see picture below).

enter image description here

Here is the filter-list from the data-worksheet. How to get this in pivot?

enter image description here

Thank you in advance.

Note: I've also asked this on codeplex without success. Maybe someone else can help even if he has no experiences with EPPlus but knows similar issues with other libraries. I could split the DateTime values into separate columns per sql(year,month,day). But i'm afraid that the pivot will get more confusing with so many fields( i also would have to split the other date fields). Even with no experiences at all in programatically generating pivots, what would experienced excel users(i'm not) recommend?

Tim Schmelter Tim Schmelter · Accepted Answer · 2011-09-26T15:19:08

The reason why excel could not group by my date-field neither in PageField nor in RowField was because there was one unformatted row in the data sheet. The following lines cause this issue(see complete code in my question):

Dim range = wsData.Cells(1, colNumber, source.Rows.Count, colNumber)
range.Style.Numberformat.Format = "dd.mm.yyyy"

The parameters in the range's Cells-Method are:

1. From-Row 2. From-Column 3. To-Row 4. To-Column

Because LoadFromDataTable loaded the DataTable with the Colums' names as header, the last row kept unformatted, therefore the pivot cannot group the date.

So this fixed it:

Dim range = wsData.Cells(2, colNumber, source.Rows.Count + 1, colNumber)

Filter a DateTime field for year,month and day in excel pivot

1 Answers