Combinging Row Level Security with Rankings

For today I have something straight out of actual day-business. The challenge was to combine row-level-security with rankings.

Now the problematic part here is that for ranking we need all the data whereas by definition, row level security in Tableau will filter out all the data that is not within the scope of the allowed data visibility.

Without full data, how do we calculate the rank?

What you can do is use Tableau Prep or SQL to have the rank pre-calculated in your data source but still, this would only allow to have the rank as a sheer number shown somewhere on your dashboard. It would not allow to filter the data thereby adjusting your ranking (say from global to national level). Yes, obviously you could also precompute all these rankings as individual values but still, you would not be able to see how your entity ranks vs all other entities or, you would have to add even more calcs to your data source computing every possible ranking mixture you can think of and at the same time anonymize all your data.

Possible but tedious. And, on a side note, this blog is “all things Tableau” so my aim is to show ways to do things with “onboard” options provided by Tableau directly. Which does not mean this is always the best to do, absolutely not.

Anyways…

The challenge

In my case I wanted to show the ranking over time based on cumulative measure values and the individual rankings per point in time as per the back then measure value.

Also, the user should be able the pick from his shops or the shops attributed to that license (like Category and Sub-Category in Superstore terminology). The result should look like this:

Now since a user can have multiple shops we have actually a double fold problem here:

• The user must be able to select his shops, but only his
• We cannot simply use standard methods of row level security as this would filter out all others thus obstructing the ranking calculations.

The Approach (not in Eddie Quinn terms…)

The solution is actually pretty straight forward.

First, create a row level user filter via server –>create user filter

Here, set your users with the appropriate rights. However, do not put the just created user filter set on the filter shelf, just leave it sitting on the tables pane.

Next, create a calculated field equivalent to this (remember in my case we wanted the user to be able to select his shops individually)

//RLS - limit shops for ranking
if [RLS - Ranking] then [Shop Name] END

Finally, create a parameter that we set to string, list, and “on workbook opens” we reference the just created calculated field

And with that, we are done.

When the user logs in to Tableau Server the RLS User Filter Set will only return his licenses thereby putting a TRUE to all his shops, which then triggers our calculated field which then feeds the parameter we can use to allow the user to select only his shops.

I hope you found that useful.

As always appreciate your feedback.

Until next time.

Steffen