Row-level security restricts access to data based on the characteristics of the person viewing the data. Within Sigma, membership in a team and a user’s email can be used to filter what data is visible.
Setting up dashboard row-level security for a Sigma Worksheet takes four steps.
- Create a column with filter criteria
- Filter the worksheet based on your filter column
- Create visualizations of your data
- Create a dashboard
You utilize System functions to set up criteria based on who is viewing the worksheet. Then, you filter the worksheet based on the criteria. You create visualizations of data. Finally, you create a dashboard of your visualizations. By having the source worksheet in a private or restricted team folder and the dashboard in a more widely accessible folder, you can prevent people from editing the filters that restrict the information in the dashboard.
1. Create a Column with Filter Criteria
The first step to creating row-level security is to create the filter logic. Sigma has two functions that will provide information about the current viewer. CurrentUserEmail will return the email of the user viewing the Worksheet. CurrentUserInTeam returns True if the current user is a member of any of the given teams.
Both functions can be used as a test to filter data.
Let’s say you have a table with sales results, and you want each salesperson to see only their results.
First, you need to pull in the emails associated with each line of data in the Sales Results. You can then create a new column [Filter] and set up the formula that tests for what data the worksheet viewer should see.
[Salesperson Email] = CurrentUserEmail()
This formula looks at emails in the column [Salesperson Email] and checks if it matches the email of the user currently viewing the worksheet. It will return True for all rows where the current user’s email matches the salesperson email. You now have a test for what data should be shown to which user.
Sigma can also create team level row security using CurrentUserInTeam. This function checks to see if the current user viewing the worksheet is part of any of the specified teams.
To set up team security, create a column that associates the data with the correct Sigma team. If the Southwest should only see data from California, Nevada and Arizona, set up a column with that logic.
If( [Region] = “California”, “South West”, [Region] = “Nevada”, “South West”, [Region] = “Arizona”, “South West”)
This will create a column that has the value Southwest for all rows of data that have the Region set to California, Nevada, or Arizona.
Expand this logic to include all of the teams you want to test for. Then, create your filter column.
This will return True for every row of data where the current user belongs to the team indicated in that row of the [Team] column. You now know which data should be shown to someone, based on what teams they are a member of.
Filters can also be set up to test for multiple conditions. You could filter a sheet to show salespeople only data from leads that they own, and show all the data to sales managers.
( [Salesperson Email] = CurrentUserEmail() ) or ( CurrentUserInTeam(“Sales Manager”) )
This will return TRUE for all rows when the viewer is in the “Sales Manager” team and will return TRUE only for rows where the viewer email matches the Salesperson Email for all other viewers.
2. Set Up Your Filter
Once you have a column with your filter criteria set up, you can filter your worksheet to only show the correct data. Simply select your column and filter to only show rows where the value is TRUE. This will filter the worksheet to only show data when the current user matches the filter criteria.
3. Create Visualizations of Your Data
Now that you have the filter set up, you can create visualizations of your data. You can create table charts that display relevant table data, pivot tables to show individualized stats, bar charts to show growth in numbers or any of the other visualizations Sigma offers.
4. Create a Dashboard
The last step is to create a dashboard. Place your data visualizations on a dashboard, then place the dashboard in a folder where the correct people can access it.
By keeping the worksheet powering the dashboard in a secure folder, only those with access to the folder will be able to manipulate and change the filters on the worksheet. Those with access to the dashboard will be able to see the data in the dashboard, but will only be able to click through to the worksheet if they have access to the folder where the underlying worksheet is saved.
By following these four steps, you can secure your data on Sigma based on the characteristics of the person viewing the data.