The boss knows first: how to use tableau user calculations to allow high level users to see the latest data before everyone else

Giving all employees access to the latest sales data is the norm in some companies. However, some managers insist on having a preview of the data before the rest of the company. One project I was working on required a period of five days where only the senior managers could view the latest data. On the sixth day, the latest report became available to all viewers of the project. The data was a monthly report, containing data from the previous month and further back in time. Tableau server has built in capabilities to include users in groups and set access roles and even filter data. So what are the options?

Option 1 –Two Projects

Create two projects on the same site. Project A is accessed by all of the team. The data is refreshed on the sixth day of the month to show the previous month’s data. Project B can be accessed only by users in the Management group and  is refreshed on the first day of the month. For the first five days of the month the Management group are seeing data that is newer than the rest of the users can see. So what are the pros and cons of this approach?If the monthly report was a single workbook then this solution would be pretty straightforward. One possible downside is that two data extracts need to be published, which could take up a lot of space. Any development work would have to be done on two identical dashboards or the relevant datasource reconnected before republishing each time in the two different projects.If the project is a collection of dashboard linked by URL actions then this approach would also require all URL links to be reconfigured between the two projects.

Option 2 – Date filter with the ismemberof() function

Publish the set of workbooks in a single project. The data is filtered relative to the date, based on which user is viewing the workbook and which day of the month it is. To set this up:

• Set the datasource to refresh at the start or end of the month.
• Create a calculated field called User Date:

if ISMEMBEROF('Management') or day(today())>5then dateadd('month',-1,datetrunc('month',today()))else dateadd('month',-2,datetrunc('month',today()))ENDThis could be further enhanced by counting five working days rather than just five days. For help on calculating working days, this blog by Ian Baldwin should help.

• Then create any date filter you need to use on your worksheets.

Eg. Latest Month filterdatetrunc('month',[User Date])=datetrunc('month',[Transaction Date])Eg2. Rolling 12 Month filterdatetrunc('month',[User Date])>=datetrunc('month',[Transaction Date])anddateadd('month',-11,datetrunc('month',[User Date]))<datetrunc('month',[Transaction Date])Eg3. Lastest quarter filterdatetrunc('quarter',[User Date])=datetrunc('quarter',[Transaction Date])

• Add the filters to you worksheets then sign in to the server to test (top menu bar, Server, Sign in..). Once you are signed in you will see your username on the bottom right of the screen. To see what other users will see you can click on your user name then search and select another user or group.

The main advantage of this option is that it allows any design development to be implemented in a single set of workbooks rather than two. Errors and inconsistencies can occur when a tableau designer is required to apply the same changes to two workbooks. It is also very tedious, repetitive work. One disadvantage could be that the datasource connection does not run as fast because the workbooks are connected to a larger datasource (more rows due to the extra month).So next time office politics collide with Tableau server, remember there are a couple of options depending on your requirements. Two options are separate projects or user/group filters.Dan Watt