Visible criteria when filtering a pivot table

LobaLuna · August 31, 2017, 1:42am

I have a pivot table coming from a large table. I am using the filter option when creating the pivot table, and when I push the Filter button on the pivot table itself, I can create a criteria for filtering a large amount of data, but the criteria itself is NOT showing on the pivot table, nor a sign that the data is filtered.

I am not refering to the buttons with small black triangles because these ones turn to blue-with-dot when some filtering is made.

I mean the left-uppermost button named Filter on the pivot table.

I do not kknow if this is a bug that I should report or is intended this way. If so, please tell me how to show off the criteria that I am using, so someone else will be aware of the filtering I am performing.

Lupp · August 31, 2017, 9:15am

I don’t think it’s a bug.
However, enhancement requests for LibO are treated the same way as bugs: File them to bugs.documentfoundation.org. You may suggest to add something to the caption of the ‘Filter’ button …

Meanwhile you may resort to user a function like the one given below in BASIC code.

(Editing1)
The originally posted code has too many shortcomings. I regard it deprcated, but will not delete it. It is now at the end of this answer. In this place I inserted a more thoroughly designed solution. It is also without a guarantee of any kind, of course. Errors expected! I do by far not completely understand the handling of pivot tables by LibO and the respective API services and structures. One example: The FilterFields have each a property ‘IsNumeric’ and two properties to keep the value operand: ‘NumericValue’ and ‘StringValue’. In fact the filters seems to handle everything using the ‘StringValue’ only, and I do not exactly understand how.
However, my code worked correctly on a few examples. See also this attachment.

Function dpFilterSettings(ByVal pDpSheet, pStartDpOutput As String, Optional pTrigger) As String
REM V0.1 Wolfgang Jäger 2017-09-02 
REM As the LibO API still uses the term 'DataPilotTable' instead of 'PivotTable' 
REM I chose the names of related items here based on 'DataPilot' (Dp).
REM pPtSheet must be either the true name (NOT single quoted) of the sheet 
REM containing the output of the pivot table or the number of this sheet ("1 based").
REM The applicable case is decided by the type of the actual parameter.
REM The true name of a sheet is the string you get if you edit the sheetname ('Rename Sheet').
REM Note that the sheetnumber may change if sheets are entered/deleted/rearranged.
REM Best use the numeric alternative with the SHEET() function of Calc.
REM pStartPtOutput must be the address of the topmost leftmost cell of the output range.
REM Since the pivot table is filtered this cell should be the cell containing the 'Filter' button.
REM Whether or not parts of the address are marked absolute is of no meaning.

On Error GoTo errorExit
msgF           = "fail "
f              = "Doc"
theDoc         = ThisComponent
f              = "Sheet"
If TypeName(pPtSheet)="String" Then
    theSheet   = theDoc.Sheets.GetByName(pDpSheet)
Else 
    theSheet   = theDoc.Sheets(Int(pDpSheet)-1)
    REM The API is numbering sheets 0 based.
End If
f              = "Table"
theDptLu       = theSheet.GetCellRangeByName(pStartDpOutput)
theLuCA        = theDptLu.CellAddress
For k = 0 To theSheet.DataPilotTables.Count-1
    theDpT     = theSheet.DataPilotTables(k) 
    theDpOR    = theDpT.OutputRange
    If (theDpOR.StartColumn=theLuCA.Column) AND (theDpOR.StartRow=theLuCA.Row) Then 
        Exit For
    End If
Next k
theDpSrA       = theDpT.SourceRange
theDpSRow0     = theSheet.GetCellRangeByPosition _
                  (theDpSrA.StartColumn, theDpSrA.StartRow, theDpSrA.EndColumn, theDpSrA.StartRow)
theColIDs      = theDpSRow0.DataArray
f              = "FilterDescriptor"
theFD          = theDpT.FilterDescriptor
For k = 0 To theDpSRow0.Columns.Count - 1
    theColName = "Column_" & (theDpSRow0.Columns(k).Name)
    Select Case theFD.ContainsHeader
        Case True: If theColIDs(0)(k)="" Then theColIDs(0)(k) = theColName
        Case False:theColIDs(0)(k) = theColName
    End Select
Next k
theFFs         = theFD.FilterFields
theOperators   = Split(";;=;<>;>;>=;<;<=;;;;", ";")
r              = ""
If theFD.IsCaseSensitive Then 
    r = r & "C"
End If
If theFD.SkipDuplicates Then 
    r = r & "S"
End If
If theFD.UseRegularExpressions Then 
    r = r & "R"
End If
If r<>"" Then r= "Options: " & r & " "
r              = r & "Fields: "
If Ubound(theFFs) <0 Then 
    r          = r & "none"
Else
    For k = 0 To Ubound(theFFs)
        If k>0 Then r = r & "; "
        r      = r & (k + 1) & ":" & theColIDs(0)(theFFs(k).Field) _
                   & theOperators(theFFs(k).Operator) _
                   & theFFs(k).StringValue 
        REM The properties IsNumeric and NumericValue are disregarded.
    Next k
End If
dpFilterSettings = r
GoTo done
errorExit:
dpFilterSettings = msgF & f
done:
End Function

Comments welcome to jag@psilosoph.de

Function dpFilterField1(pSheet, pDpTable) As String
On Error GoTo errorExit
msgF = "fail "
f = "Doc"
theDoc = ThisComponent
f = "Sheet"
If TypeName(pSheet) = "String" Then
    theSheet = theDoc.Sheets.GetByName(pSheet)
Else 
    theSheet = theDoc.Sheets(Int(pSheet)-1)
End If
f = "Table"
If TypeName(pDpTable) = "String" Then
    theDpT = theSheet.DataPilotTables.GetByName(pDpTable)
Else 
    theDpT = theSheet.DataPilotTables(pDpTable-1)
End If
f = "FilterDescriptor" REM This error message will also be returned if a 'Filter' is set, but not used.
theDpFD = theDpT.FilterDescriptor.FilterFields(0)
ffIsNumeric = theDpFD.IsNumeric
If ffIsNumeric Then
    r = "FilterField1 is numeric field: Op. " & theDpFD.Operator & "; Val. " & theDpFD.NumericValue
Else
    r = "FilterField1 is textfield:     Op. " & theDpFD.Operator & "; Val. " & theDpFD.StringValue
End If
dpFilterField1 = r
GoTo done
errorExit:
dpFilterField1 = msgF & f
done:
End Function

(The automatic naming and numeration of pivot tables may be a bit cumbersome in the context if you decide to move the output range e.g.)
Please note that the function must be applied in an unlocked cell. The adjacent cell right of the ‘Filter’ button will mostly be locked.