Hide/Show Columns in a Report based on User Permission

Imagine that you have some reports that contain sensitive or confidential data that should show certain columns basing on the users who view the report. You can create different reports for each user group but that could be tedious when you have to duplicate your updates on several reports.

Holistics provides a seamless solution for that. What we need is a combination of if-else condition syntax in our query and the ability to return filter value depending on user group.

Use case:

We have report Users that have these columns ID, Name and Email. But only users in group Confidential should be able to see column Email.

Assume that we already have group Confidential with the right people in it. Please refer this link on how to do this.

1. Create a dropdown filter

In your report edit page, create a dropdown filter named show_email with value yes, no.. Please refer this link on how to create a dropdown filter.

2. Make the column not show by default

Under Options, set default value to no.

3. Make the filter return the right value

In Permissions, include the syntax to show your chosen columns to group Confidential.

permissions:
- if_group: Confidential
  then_value: 'yes'
- else:
  then_value: 'no'

This sets the filter value to yes when the users who view the report is in group Confidential. Anyone who is not in any of these group will get value no.

The final result of that dropdown filter will look like this:

Then Save this filter.

4. Write query with if-clause

Now we'll use Holistics' Query Condition to decide whether to show the column or not. This allow you to include If conditions into your SQL queries, which means you can conditionally render text blocks.

Your query should look like this:

select id as "ID", first_name || ' ' || last_name as "Name"
  {{#if show_email == 'yes'}} ,email as "Email" {{#endif}}
from ecommerce.users

Here, as written in the original query, if the condition in the if-clause is no, the final query that runs will be:

select id as "ID", first_name || ' ' || last_name as "Name"
from ecommerce.users

Please refer to this link for more use-cases of our if-clause.

Then save the report and we're done!

Result

This is what users in group Confidential see:

This is what users who do not belong to Confidential see: