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
ID UID Role
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!