Database Restrictions on Multiple Dimensions "OR"

1

I want to restrict a user in a database with multiple dimensions, using and ‘OR’ rather than ‘AND’. I’ve seen the question asked in the forum but I haven’t seen the answer.

Here’s the use case:

A divisional sales manager is responsible for the product sales in a particular product division across the company but he is also responsible for sales for his specific sales team which sales products across all dimensions.

Can I set up a database restriction which shows him only sales for his division and sales for the salesperson on his team regardless of division?

example: Division = ‘Plumbing Supplies’ OR Salesperson = ‘Bob’, ‘Sally’, ‘John’

Thanks

3 Likes
2

I spoke to my Phocas contacts and this is not currently a capability within Phocas. I will submit an enhancement request.

3

Not sure if you have Design access, but we’ve got around this by building case statements into the database sync and then filtering on that.

So:
Case
when Division = ‘Plumbing Supplies’ then ‘1’
when Salesperson in (‘Bob’, ‘Sally’, ‘John’’) then ‘1’
else ‘2’
end

Then you’d filter the user on 1 and repeat the above as needed.

2 Likes
4

Thank you - that is very helpful.

I am going to push for an enhancement because I don’t want to work around the product limitation by maintaining the SELECT statement in the extract query for a dynamic situation. In this particular case, I have multiple VPs of multiple divisions, each with their own sales teams which change members frequently. In general, I need this capability to filter databases in a similar way beyond User Database Restrictions. Filtering in this way is something that I am able to do with other BI tools and I believe it will be a useful enhancement in Phocas as well.

Thanks again and Cheers!

2 Likes
5

We’ve used case statements like jmills suggested in the past for more specific options such as catalog customer, etc.

For division access we added ‘division’ as a property to the salesperson dimension so it can be added to the Salesperson dimension filter. The logic behind it was, all sales people are apart of a division anyways and this rolls up to other reports smoothly. This exploded into a benefit across multiple other reports for our executive team to benefit from since division was right there for easy access without having to deal with nesting the report or flipping back and forth between the dimensions.

1 Like