Slicers : Hide items with no data

[lauraearle515]

I use the wb_add_slicer function quite frequently, but have to go into the output after to adjust the slicer settings to check "Hide items with no data." I would love a way to apply the setting in the code itself so I don't have to go in and change it in the output.

[JanMarvin]

Maybe with params show_missing = TRUE?

[lauraearle515]

Absolutely! This is the code I'm using:

library(openxlsx2)

dat <- data.frame(
  var = c("x", "y", "y", "z", "z", "x"),
  speed = c( 4, 4, 7, 7, 8, 9),
  dis = c( 2, 10, 4, 22, 16, 10),
  slicervar1 = c("option1", "option1", "option1", "option2", "option2", "option2" ),
  slicervar2 = c("choice1", "choice1", "choice2", "choice3", "choice3", "choice1")
)

wb<-wb_workbook()
wb$add_worksheet("dat")$add_data(x = dat)
wb$add_worksheet("pivot")
df<- wb_data(wb, 1)

wb$add_pivot_table(df, 2, dims = "A3", slicer = c("slicervar1", "slicervar2"), rows = c("var"), data = "speed", params = list(name = "pivot_1"))$add_slicer(x = df, dims = "D3:E9", slicer = "slicervar1", pivot_table = "pivot_1", params = list(show_missing = TRUE))$add_slicer(x = df, dims = "F3:G9", slicer = "slicervar2", pivot_table = "pivot_1",params = list(show_missing = TRUE))

wb_save(wb, "outputname.xlsx", overwrite = T)

xl_open("outputname.xlsx")

The setting I'm trying to change is reached by selecting the slicer, hitting slicer in the top ribbon of excel, and then Slicer Settings in the Slicer section. By default, the "Hide items with no data" is unselected as pictured below (this is true with openxlsx2 or just making a pivot/slicer in excel manually). In this example, choosing option1 in the first slicer should make choice3 disappear in the second slicer. Alternatively, choosing choice3 in the second slicer should make option1 disappear. Changing the param show_missing doesn't seem to effect anything.

As always, thank you so much for your help and if this isn't currently possible, no worries -- just figured I'd ask :)

[JanMarvin]

Thanks! This PR should enable what you want to do: #1169

You can install it as follows: remotes::install_github("JanMarvin/openxlsx2#1169)

Let me know if you prefer another name for hide_no_data_items.

library(openxlsx2)

dat <- data.frame(
  var = c("x", "y", "y", "z", "z", "x"),
  speed = c(4, 4, 7, 7, 8, 9),
  dis = c(2, 10, 4, 22, 16, 10),
  slicervar1 = c("option1", "option1", "option1", "option2", "option2", "option2" ),
  slicervar2 = c("choice1", "choice1", "choice2", "choice3", "choice3", "choice1")
)

wb <-wb_workbook()$
  add_worksheet("pivot")$
  add_worksheet("dat")$add_data(x = dat)

df <- wb_data(wb)

wb$
  add_pivot_table(df, sheet = "pivot", dims = "A3", slicer = c("slicervar1", "slicervar2"), rows = c("var"), data = "speed", params = list(name = "pivot_1"))$
  add_slicer(x = df, sheet = "pivot", dims = "D3:E9", slicer = "slicervar1", pivot_table = "pivot_1", param = list(hide_no_data_items = TRUE))$
  add_slicer(x = df, sheet = "pivot", dims = "F3:G9", slicer = "slicervar2", pivot_table = "pivot_1", param = list(hide_no_data_items = TRUE))

if (interactive()) wb$open()

[lauraearle515]

This did the trick - thank you!! You are the BEST!!

[JanMarvin]

Perfect! You know how to keep your developer happy 😄

[JanMarvin]

It's now merged into main