Another way to set the restrictions described above is by a subquery. Instead of directly setting the limit on the STATE column, limit the values in the STORE_ID column in the STORES table. The constraint operator would be IN, and the constraint values field might look something like this:
(SELECT S.STORE_ID FROM STORES S WHERE S.STATE = 'OH')
Now, no matter what limit the user sets in the STORES table, they will always be constrained to the set of store IDs that are allowed based on their group memberships and their own user name. Even if a city outside of the allowed state is chosen, such as a city that exists in more than one state, any stores that other city has will not show up in the results.
Using a subquery can be useful when incorporating existing security systems into the row‑level security feature of Interactive Reporting. When constructing constraints of this type, it is especially important to know SQL. For example, to specify a subquery, it helps to know that a subquery is always enclosed in parentheses. It is also important to know how EPM Workspace generates SQL and to follow its naming conventions to make sure the syntax generated is appropriate.