Two RLS settings for the same file based on report type

[Anonymous]

I have a data model with a sales table with the following information:

I also have two types of reports in the same file:

1. Reports with information historic sales perfomance
2. Reports with information about current sales performance

When sales staff is acessing the report, they are only supposed to see their own data:

RLS on "Sales AmountSales Person (at time of sale)" in the historic section:

- Joe Black, Tim Green and Matt Gray will each see their one sale

and RLS on "Sales Person (current)" in the current section:

- Joe Black sees two sales, Tim Green one sale and Matt Gray no sales

Is it possible to set up dynamic RLS for this scenario without duplicating the sales table or creating two seperate data models? 

[tex628]

I have an idea. It's quite complicated byt it might work. 

Assuming that you have a Sales Person table that you're applying the RLS on we want to change the connection from Sales person <-> Sales person to Combination <-> Combination.

Essentially we want to give every combination of current and historic sales person a numeric ID. Taking your example we would like to convert it to the following. 

Create a mapping table, containing only distict ID's. And finally create a new column in your sales table, mapping the combinations of sales people into the same ID's that we already created in the Sales person table.

I'm not completly certain this works, but i think this is as close as you can get. Let me know how it goes and if you need any help!

/ J

[Anonymous]

Nice idea, thanks for the suggestion. Let me see if I can find a way to set it up like that to test it.