Filter a unique user from report if any row referencing them contains a value

Hi, in our database we store our users and information in a table with a unique UID that relates to their in app roles in another table. They can have multiple roles in our app and each one is a recorded as a new row. We need to easily filter out any user who has the role ‘test’ in this table for multiple reports. We need this to be easy to do for anyone creating a report who does not have technical knowledge. What would be the best practice for doing this in Holistics?

Here is an example use case. I have created a report using another table that tracks usage in app and relates that usage back to the same UID it is called the ‘visit_data’ table. The request is to show a count of daily unique users (so count unique UID’s in visit_data table) where the user has the role of ‘guardian’ in the roles table.

I have set up relationships of
One to many - users to visit_data
One to many - users to roles

I can easily create this report using these settings

But now I also need to filter out any users who have a role of ‘test’ from this data. If I add a filter of ‘is not test’ that will not change the data, because it still counts, for example, UID 1 who has both ‘test’ and ‘guardian’ roles.

Example, in the below data I would only want a unique count of actions for user_2 & user_3 because user_1 has the test role. So the distinct count should return ‘2’
visit_data Table
id uid action
1 1 some action
2 1 some action
3 2 some action
4 2 some action
5 3 some action

Users Table
uid user_name
1 user_1
2 user_2
3 user_3

Roles Table
1 1 guardian
2 1 test
3 2 guardian
4 3 guardian

Is there a simple way to achieve this in Holistics within filters or do I need to modify/aggregate the tables?

Thanks for any help!

You need to aggregate all the roles of a user into 1 record in the table:

Roles Table
ID UID Roles
1 1 guardian, test
2 2 guardian
3 3 guardian

and use filters ‘contains guardian’ and ‘does not contain test’ to achieve that.

Thanks @Nui_Ta that’s what I figured but is this easy to achieve within Holistics at the reporting level? Or is it something we will need to do in our warehouse?

Welcome to the community, Adam!

We understand that you are looking to “filter out users who have the role ‘test’ in the table for multiple reports” . At the moment, this is not easy to implement in Holistics’s reporting layer; hence, we would recommend you to transform your data in the warehouse beforehand.

Our apologies for any inconvenience this may cause and really appreciate your patience. We are constantly improving our reporting layer and will be sure to keep you updated with any changes that we make.

If you have any further questions or comments, please do not hesitate to reach out!

Holistics Support Team