[Pivot Tables] filtering pivot tables

[JanMarvin]

G'day Jan,

I don't even really understand what a pivot table is (as far as I can tell, its like dplyr::summarise?), but I need to re-create one with a filter for someone where the value of the filter is already selected. That is the filter arg to wb_add_pivot_table specifies the col part of dplyr::filter(df, col == val), but not the val part). I've made a test xlsx and unzipped it and had a poke around to try to figure out where the value is stored, but haven't managed to figure it out yet. I plan to look into this further however first I wanted to check in if:

a. specifying the value to filter by is an already existing functionality of openxlsx2 that I have just not discovered;
b. assuming that it is not, if this is something that you think should go into the list of todos for pivot tables you set out above;
c. assuming that it is, if you have any pointers about where you think I might be able to find the xml setting; and
d. if you have any thoughts about what the API should look like for any PR that I put together. Another arg for wb_add_pivot_table called filter_value?

Originally posted by @vorpalvorpal in #820 (comment)

[JanMarvin]

Hi @vorpalvorpal,

I moved your comment here, because it is not really related to the issue it was posted in, aside from the fact that it mentions another unimplemented feature. Because filtering for a case is not yet possible in openxlsx2 and will require at least another cold and rainy weekend for me to begin working on this.

But lets start from the top. A pivot table - I also didn't know them for the longest (and happiest? 😉 ) part of my life - is something like an interactive table. You add data to column and rows and the spreadsheet software will calculate what you want, frequencies, sums, means, etc. Indeed similar to certain dplyr or data.table functions, but way more interactive. Often people simply throw some basically aggregated data into a worksheeet and create a pivot table in the next. And whatever people are interested in, they can click themself together.

Lets take some example:

library(openxlsx2)

# "https://www.dcceew.gov.au/sites/default/files/documents/national-waste-database-2022.xlsx"

fl <- "~/Downloads/national-waste-database-2022.xlsx"
waste_db <- wb_load(fl)

wb <- wb_workbook()
wb$clone_worksheet(old = "Database 2022", new = "DB2022", from = waste_db)

## remove all the interactive parts and make the data digestable to openxlsx2
# the current selected dimensions cover the entire sheet A1:XFD1048576. This is
# a 1048576 x 16384 matrix wb_data() will have to import.
wb$worksheets[[1]]$dimension <- "<dimension ref=\"A1:Q71497\"/>"
# the data validation part is in here
wb$worksheets[[1]]$extLst <- character()
# we bail over something in here, maybe a column style we didn't clone?
wb$worksheets[[1]]$cols_attr <- character()
# since we removed the column attributes, we have to remove the sheet format
wb$worksheets[[1]]$sheetFormatPr <- character()

df <- wb_data(wb, sheet = "DB2022")

wb$add_pivot_table(df, filter = "Jurisdiction", row = "Category", col = "Fate", data = "Tonnes", 
                   param = list(numfmts = c(formatCode = "#,###0")))

# looking great, opening fast
if (interactive()) wb$open()

And now to answer your questions:

• a) That's correct. It is not possible (yet), but I'd strongly recommend to open the file and set the filters manually. Unless you have to do it every few hours. At one point you have to open and save the file with spreadsheet software either way, because of the nature of openxlsx2s pivot tables. We simply create the receipt for the spreadsheet software how the pivot table should be constructed and not how it actually looks like. The area where openxlsx2 creates the pivot table is entirely empty (no data, no pivot table, it is completely blank and filled by the spreadsheet software).
• b) Yes, definitely, I'll add it to the todos, but like I've said, I do not want.
• c) Sure! IIRC you have to look at the pivotTable in R:

wb$pivotTables %>% xml_node("pivotTableDefinition", "pivotFields", "pivotField")

or in xml:

xl/pivotTables/pivotTable1.xml

This is the XML for the filter (axisPage) of the table above. Selected is NSW element x = 2. The unselected cases get h = "1" (no clue why, maybe its short for hidden):

<pivotField axis="axisPage" multipleItemSelectionAllowed="1" showAll="0">
  <items count="10">
    <item h="1" x="0"/>
    <item h="1" x="1"/>
    <item x="2"/>
    <item h="1" x="3"/>
    <item h="1" x="4"/>
    <item h="1" x="5"/>
    <item h="1" x="6"/>
    <item h="1" x="7"/>
    <item h="1" x="8"/>
    <item t="default"/>
  </items>
</pivotField>

So to get everything in order, you have to dig a bit into the pivot table creation code. Somewhere there is get_item() to create the items and you'd have to create some kind of logic to flag the elements you want to hide and push that into the logic here:

pivotTable

To make things a bit complicated, we have different data types: numeric, character and dates. And might require some logic to select single values, values ranges or negate our selections.

Also, what people that use pivot tables like to do a lot: use slicers. The logic here is a bit different (have a look at #822 if you fancy). The slicer has another way of selecting elements, using s="1" in the get_item() logic iirc.

• d. I have no idea, do what you like and what works for you. I'd really wouldn't want to add new dependencies for a feature like this, so it would be great if you can do it with the existing (aka without integration of dplyr::filter()).

[vorpalvorpal]

Thanks for that! That is a great help! I'll see what I can put together. And yes, of course anything I come up with I won't include dplyr or other new dependencies.

[JanMarvin]

Great, thanks for looking into it!

Two additional remarks:

• Might be obvious, but it is possible to filter on any variable in the pivot table input. This includes column, row, filter, and slicer variables.
• Maybe a simple solution would be to push something like R filters like 'vs > 0' and evaluate this, but pivot tables use a different approach to select unique elements.