KNOWLEDGE BASE

Show or Hide Multiple Worksheets on Dashboard with Sheet Selector


Published: 15 Dec 2018
Last Modified Date: 04 Jan 2019

Question

How to select multiple worksheets to show/hide on a dashboard when using a sheet selector.

Environment

Tableau Desktop

Answer

Upgrade to Tableau Desktop 2018.3 or a newer version and use Set Actions. The attached example workbook uses the sample data set Superstore to demonstrate the following directions:
  1. Create a table that contains one field with the names of all worksheets. This example uses an Excel sheet with the field [View].
  2. In the existing workbook, create a new connection to the data source from step 1
  3. Create a new worksheet with [View] on the Rows shelf. In this example, the worksheet is named "Source Sheet"
  4. Right-click [View] in the data pane and select Create > Set...
  5. In the Create Set dialog, name the set and click OK. In this example, the set is named "View Set"
  6. Select Analysis > Create Calculated Field
  7. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Is Map?"
    2. In the formula field, create a calculation similar to the following:
      MAX(IF [View Set] THEN [View] END = "Map")
      OR COUNT(IF [View Set] THEN [View] END) = 0
  8. Navigate to the "Map" worksheet
  9. Drag [Is Map?] to the Filters shelf
  10. In the Filter dialog, do the following and click OK:
    1. Select Custom value List
    2. In the yellow bar, type in "True" without quotes
    3. Click the + symbol
  11. Repeat steps 6-10 for every worksheet that should be included in the sheet selector
  12. Drag the "Source Sheet" worksheet onto the dashboard where the worksheets to be shown/hidden are placed in one layout container
  13. Navigate to Dashboard > Actions...
  14. In the Actions dialog, click Add Action > Change Set Values...
  15. In the Add Set Action dialog, do the following and click OK:
    1. For Source Sheets, check only "Source Sheet"
    2. For Run action on, choose Select
    3. For Target Set, in the first dropdown select the data connection created in step 1
    4. For Target Set, in the second dropdown select View Set
    5. For Clearing the selection will, select Remove all values from set

 

Additional Information

The set [View Set] returns either TRUE or FALSE for every row, therefore the IF statement in [Is Map?] converts the Boolean value into the actual [View] value. If that actual [View] value is "Map", then the condition is TRUE. The first condition will return a TRUE or FALSE value for every value of [View], but we only care if at least one of the values is "Map", thus the MAX() aggregation will return TRUE if any of the returned values are TRUE.

The second condition in [Is Map?] is an optional addition that is TRUE when no values are selected in the set. This is a way to make the map the default view shown before the user selects any worksheets.
Did this article resolve the issue?