404notfound 404notfound - 7 months ago 23
Vb.net Question

Filter column labels in pivot by days using VB.NET

I have my pivot field code for submitted date.

ptField2 = ptTable2.PivotFields("Submitted Date")
With ptField2
.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
.Name = "Submitted Date"
.Caption = dtIntFrom.Value.ToString("MM-dd-yyyy")
.ClearAllFilters()
.PivotFilters.Add(Type:=Excel.XlPivotFilterType.xlCaptionEquals, DataField:="Submitted Date", Value1:=date_stamp)
End With


Here is my target date: mar15-mar31, 2016

date_stamp = Format(dtIntFrom.Value(), "m/d/yyyy") '3/15/2016
date_stamp2 = Format(dtIntTo.Value(), "m/d/yyyy") '3/31/2016


When I run my program, it displays all dates from my target date.

enter image description here

I want to filter my selected target date per week and it must look like this:

enter image description here

Thanks for any help!

Answer

After a couple of days, I already found the answer using the Range.Group Method

Also another mistake is I code .xlColumnField first before the .xlDataField . It should be on the last.

Sample below:

ptField2 = ptTable2.PivotFields("Total Pages")
With ptField2
     .Orientation = Excel.XlPivotFieldOrientation.xlDataField
     .Function = Excel.XlConsolidationFunction.xlSum
     .Name = "Pages"
     .NumberFormat = "#,##0"
End With

ptField2 = ptTable2.PivotFields("Group")
With ptField2
     .Orientation = Excel.XlPivotFieldOrientation.xlRowField
     .Name = "Group"
End With

ptField2 = ptTable2.PivotFields("Submitted Date")
With ptField2
     .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
     .Name = "Submitted Date"
     .DataRange.Cells(1).Group(True, True, 7, New Boolean() {False, False, False, True, False, False, False})
End With