Ask Your Question

Visible criteria when filtering a pivot table

asked 2017-08-31 03:42:43 +0200

celiapgt gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-08-31 11:15:20 +0200

Lupp gravatar image

updated 2017-09-02 18:28:19 +0200

I don't think it's a bug.
However, enhancement requests for LibO are treated the same way as bugs: File them to 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.

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)
    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 ...
edit flag offensive delete link more


@Lupp, I will report the feature then, as you told me. Done:

Now, the macro is a great solution! I definetely mark this as an answer.

However, any ideas of how to turn the rather technical messages like «FilterField1 is textfield: Op. 2; Val. O» into something like «Text <> 'O'», a more meaningful way of presenting the info?... Sorry to bother, but I am very rusty at programming.

celiapgt gravatar imageceliapgt ( 2017-09-01 23:22:41 +0200 )edit

@Lupp, I see your code as a proper solution to me. It makes much more sense. Thank you!

celiapgt gravatar imageceliapgt ( 2017-09-08 23:03:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-08-31 03:42:43 +0200

Seen: 197 times

Last updated: Sep 02 '17