Hidden filters are a great tool to use to allow you to curate the data your viewers see in dashboards!
Let’s say you have the national sales data for your company, and want to make a dashboard that automatically customizes itself to display the data pertaining to the region of that viewer, but also have certain executives view all the sales data.
It’s surprisingly easy! All we have to do is use one of Sigma’s System Functions to filter a table and move that filter to a hidden page in the workbook. For our example, we will use the CurrentUserInTeam function to determine what data each user should see.
Beforehand, you will need to have your viewers assigned to teams categorized by the data portion they should see, either by using the Teams page in the Administration portal or via SCIM depending on how your organization provisions its users. In our example, we wish to show users the data relevant to their store regions, so we will create and assign users to teams titled “Midwest,” “South,” etc.
You will want to structure your dashboard to have all its visualizations and other page elements as children of a parent table, that way filters applied to the upstream element will automatically filter your whole dashboard. In this parent table within your workbook, the first step will be to create a new calculated column, with a formula utilizing the column we wish to categorize our viewers with.
For example, we will use the formula CurrentUserInTeam( [Store Region], “Executive Team” ). This formula will return a value of True if the current user is in any of the teams listed. So a viewer in the “Midwest” team will have a value of True for the rows with the Store Region column equal to “Midwest,” and a viewer in the “Executive” team will have a value of True for all rows.
Using the dropdown menu of the newly created column, select Filter to open the filter window and create a new filter. Deselect the “null” and “False” options so that the filter hides the rows that don’t match the CurrentUserInTeam formula. Then, using the three dot menu in the top right corner of the filter, select the option to “Convert to page control.” We can then use the three dot menu of the newly detached filter to select the option “Move to” and select a “New Page.”
This will bring us to the newly created page that now contains our page control filter. In the tab bar at the bottom edge of the screen, we can find the tab for this newly created page and use the “Hide” option in the tab’s dropdown menu. Finally, we need to navigate back to the page with our data table and, using the calculated column’s dropdown menu, select the “Hide Column” option.
While a user with “Can View” permission is looking at the workbook, they will not be able to see or edit the calculated column or the associated filter. In this way, they will only have access to entries in the table relevant to them and automatically all child visualizations of the table will automatically scope themselves to the relevant data.
Now, your meticulously crafted dashboard will automatically customize itself to whomever is viewing it, you can even add Dynamic Text to the dashboard to have labels and text that reflect the scope of the data!
