User Access Rights in Hierarchy Nodes BEx Query

Hi experts,

have been researching for a while, but seems a bit difficult to find the answer to my issue. So I have a query which uses hierarchical reporting and I would like to restrict different users from looking at different node values in diffierent levels for ex.

Cars (Level 1)

Model X (Leve 2)

Type X1 (Level 3)

Model Y (Level 2)

Type Y1 (Level 3)

So user X should see only Model X and its child nodes. User Y should see only Model Y and its child nodes. Finally, User Z should se the whole hierarchy like it is above.

I know one solution is to restrict the hierarchy in the query, save the query and publish it to the Role that this user is assigned. However this is not flexible if there are 100 users and each of them has to access a specific node value in the hierachy. If following the above approach, this would mean to create 100 Roles and 1 user for each role.

Could anyone please suggest any possible solution for the above scenario?

With Best Regards,

Regys