Forum Discussion
RLS with comparitives
Hello, I have a report with circa 300 low level users, 10 mid level (regional type roles) and 20 top level users (corporate type roles). I have RLS instituted and it works fine. No one can see anything they are not supposed to. What I would like to do is at the bottom level, serve up regional comparitves to those users. I know I can use a separate summarised fact table to generate regional and national comparitives but that seems to my mind a massively inefficient way of achiving this. I also know that i cant DAX my way around it with ALL's and PATH isnt a solution in this case as it wont give me comparitives either. Whats the solution? There must be one but I am a bit stumped.
no sensible, efficient solution with the current programme. a pity since it seems a typical sort of thing users would want to achieve.
5 Replies
Thanks for the suggestions but these are variants on the dynamic security I already have. They limit the lowest level to seeing only the lowest level. I want each level of user to see the detail for there area of responsibility AND summarized data from the next level up.
For instance the lowest level would see the prices they have sold a bunch of products for and who they sold them to in great detail but would also see the average regional sales price for comparison benchmarking reasons. That average is made up of their peers sales prices withtin their region.I dont really want to create a regional level table since its just a repitition of the same data and seems a very inefficient solution.
Anyone got a solution?
Hi samdthompson
if you want fool proof control of which data a user can access, RLS is the only option. And when using RLS, there is no way of achieving what your are describing without having some additinal tables.
It is possible to write measures which uses the username-function as part of the filter. You can then create to sets of measures, one set for detailed views and one set for higher level views. This is less secure, and whether you can use this approach will depend on how your users can access the report. E.g. with the Q&A it is possible to list out an entire table if it is not protected by RLS. If users have access to the model, they will be able to see everything.
If they only have access to the report through an app, and Q&A is turned off, I think you can use this approach. But it has been a while since I tested this, so not 100% sure about this.
Cheers,
Sturla
no sensible, efficient solution with the current programme. a pity since it seems a typical sort of thing users would want to achieve.
