1 of 1 people found this helpful
I would create a calculated field called FilterInclude as follows;
Username() = [Store Manager ID] or Username() = [Supervisor ID] or Username = [Clerk ID]
That field will return true or false - drag it onto the filter shelf and set it to true.
Just gave this a try with a quick CSV copy/paste and a Parameter instead of the actual UserName(), and it worked perfectly. Props, Tom W.
I find getting this to work from a single line with just direct true/false checks really clever. If I'm thinking through this correctly, that calculated field will check through all the values in [Store Manager ID] as a first step, and if it finds a match return True and move on. If it doesn't find a match, it'll set to a "temporary" False and move on to check through all of [Supervisor ID], and the same thing happens - if there's a match, True and stop, or continue with the "temporary" False as it runs through [Clerk ID].
Is this why it's filtering properly through the Hierarchy levels? It makes the order of the OR statements very significant if so.
It will evaluate the criteria in the method you've suggested. The order isn't really important though, it's not actually enforcing a hierarchy through the order of the conditions. If you reversed the order of the conditions it would return exactly the same result.
It's filtering 'properly' through the hierarchy levels because the data is structured in such a way that I can use the OR to run the test on multiple fields and if any of them are true I know I'm good to include that data for the results. It's partially driven by the fact this is a really simple hierarchy. The same approach wouldn't necessarily work on a more complex hierarchy.
Wow, Tom W... I've spent far too many hours than I'd like to admit today over engineering multiple cascading calculations and filters to achieve the outcome that your recommendation achieves. My thanks for your response... you've saved me hours of over thinking this weekend.
Sometimes the simplest solutions are the best! I'm glad I could help out.