This project has moved and is read-only. For the latest updates, please go here.

Filter Pivot for current month

Oct 17, 2011 at 3:56 PM

Hello,

i need to filter a generated PivotTable by current month by default.

So what i want to achieve is following(watch out the filter icon besides "Country" and the date-values which are only October):

Bilder Upload

 

This is the code i use to generate the Pivot:

Private Shared Function GeneratePivotSheets(ByVal log As Logging.Log, ByVal excel As ExcelPackage, ByVal workSheet As ExcelObject) As List(Of ExcelWorksheet)
        Dim sheets As New List(Of ExcelWorksheet)
        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(2, colNumber, source.Rows.Count + 1, 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, workSheet.ReportRow.Description)
        pivotTable.MultipleFieldFilters = True
        pivotTable.RowGrandTotals = True
        pivotTable.ColumGrandTotals = True
        
        pivotTable.PageFields.Add(pivotTable.Fields(0))
        pivotTable.RowFields.Add(pivotTable.Fields(4))
        
        pivotTable.RowHeaderCaption = pivotTable.RowFields(0).Name
        pivotTable.Fields(0).Sort = eSortType.Ascending

        Dim weekField = pivotTable.Fields(2)
        pivotTable.PageFields.Add(weekField)
        weekField.Sort = eSortType.Ascending

        Dim countField = pivotTable.Fields("Count")
        pivotTable.DataFields.Add(countField)

        Dim statusField = pivotTable.Fields("Status")
        pivotTable.ColumnFields.Add(statusField)

        ' i want to filter this field for current month by default: '
        Dim timeField = pivotTable.Fields(pivotTable.Fields.Count - 1)
        pivotTable.RowFields.Add(timeField)
        timeField.AddDateGrouping(eDateGroupBy.Months Or eDateGroupBy.Days)

        Dim monthGroupField = pivotTable.Fields.GetDateGroupField(eDateGroupBy.Months)
        monthGroupField.ShowAll = False
        monthGroupField.

        Dim dayGroupField = pivotTable.Fields.GetDateGroupField(eDateGroupBy.Days)
        dayGroupField.ShowAll = False

        sheets.Add(wsPivot)
        sheets.Add(wsData)
        Return sheets
    End Function

Oct 19, 2011 at 7:07 AM

Sorry, you cant set filters in the current version.