This project has moved. For the latest updates, please go here.

Filter DateTime field for Years,Months and Days in Pivot

Sep 8, 2011 at 3:39 PM
Edited Sep 8, 2011 at 3:45 PM

Hello,

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 data worksheet automatically, 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).

Sep 16, 2011 at 5:12 PM
Edited Sep 16, 2011 at 5:43 PM

I would like help with this too. 
If not, is there a work around?Here I show a filter that I also would like to


would be very helpful to support on this issue thanks

Sep 19, 2011 at 8:52 AM
Edited Sep 19, 2011 at 10:14 AM
carlozsilva wrote:

I would like help with this too. 
If not, is there a work around?Here I show a filter that I also would like to 

Sorry, i cannot understand your problem. If you've added that as PageField to your pivot, the data will be already filtered on the selected customer(s).

Meanwhile i've added additional fields to my pivot to simulate the filter for year, month and date. But that is an ugly solution. Too many fields and no relation between them, so the user can choose year 2010 and month Februar-2011 although that makes no sense at all. Besides i've found no way to sort correctly the months since i have following format: August 2011. I can sort via eSortType.Ascending (or Descending) but that would sort only alphabetically.

Another approach was to add that field as RowField to the Pivot and add a DateGrouping. I've realized that this works perfectly in OpenOffice but not in Excel(2010). [see screenshots below]

 

Dim ownedAtField = pivotTable.Fields("OwnedAt")
pivotTable.RowFields.Add(ownedAtField)
ownedAtField.AddDateGrouping(eDateGroupBy.Years Or eDateGroupBy.Months Or eDateGroupBy.Days)

Works in OpenOffice:

Bilder Upload


Has no effect in Excel:

Bilder Upload


So my question: why does this work in OO but not in Excel itself?
I would assume that excel knows how to group fields together because it supports this too.
Coordinator
Sep 20, 2011 at 7:35 PM

Hi,

I haven't added support for grouping pagefields (I had missed that), so I'll add it to my TODO list.

Not sure why the grouping is'nt working. If you don't get it to work create an issue with code to replicate the problem and I will have a look at it.

Jan

Sep 27, 2011 at 8:41 AM
Edited Sep 27, 2011 at 10:08 AM

Hi Jan,

i've solved this issue. I wasn't able to group by the date-field because there was still one unformatted row.

The following line caused this issue(see complete code in my above):   

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

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

So this fixed it:  

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


Besides, how can i collapse these grouped date-rowfields initially?
Jan 21, 2014 at 8:47 AM