How to apply a Server User function as a filter

You need to control user access, be it either at the row-level, or, to a complete workbook; maybe, you’re using a landing page as a control, with some links to general access workbooks, and some to restricted-access workbooks.

Needing to define an access model that will maybe use a full-colour image and enable the url for when access is allowed, and disable the url and maybe use a different colour image say grey for when the access is restricted, you choose to use the IsMemberOf() Tableau function, and have defined a group on Tableau Server to serve your access model. Job Done.

But what about when a non-named user needs access to a restricted access dashboard, maybe its because they’re a temporary employee or a guest, what do you do then?

Easy, create a table on your data server, and then use the Username() function to check whether the user is on the list, if they are, return True and define the url and image around this True/False expression.

All good so far, but hold-on, there are more members on this list so what is happening is both True and False are being simultaneously returned: what you need is a way to return just a single True or False for any given user.

How do we do it?

With a Level-of-detail expression of course.

The problem is, that when the username is tested, it is done so at the row-level, which quite rightly returns True for the match and False for the non-match:

Which will then return both the true and false when the calculation is retained.

What we really need is a means of definitively returning only one state - True if the username is present, and False if not, in essence, marking the output of every record as True even if the Username() matches just one entry:

So how do we achieve this?

A table-grain LODx will do this - table grain in that we do not specify any field in the LODx with which to group on, defining as a Sum If calculation, means that the underlying sql query will return a value of 1 for every record in the set, and then we simply set this up as a boolean expression to test that the output is greater than zero, to define a True/False we can hinge from:

 

Min({Sum(If Username() = User_name Then 1 Else 0 End)}) >0

 

Related articles