How to apply "OR" condition to security filters across two query subjects

[Tim86]

I have a requirement to create a unique security filter for someone.  Somehow I need to cover both of these scenarios:

• All Accounts in CostCenter "A"
• All CostCenters in Accounts "X", "Y", and "Z"
• No access to any CC/Account combinations that don't fit either of the above criteria

Accounts and CostCenters are in two separate query subjects.  I tried applying something like this in the CostCenter filter:  CostCenter = 'A' or Account in ('X', 'Y', 'Z').  But that doesn't work, because it applies the filter to only the CC list first, which gives me many more CC's than just "A".  Then the overall query ends up with many CC's and all Accounts.

Is there another way to do this?  It seems like Framework Manager only applies the security filter to the scope of that query subject, rather than applying it to the entire query. And I can't find a way to apply a filter that spans across multiple query subjects.

[cognostechie]

You are confusing filters in Framework Manager with filters in the report. In a report, one query could be combinations of unioning/joining multiple queries or just one query inside another query. In Framework Manager, every query subject is one query which again could be a combination of multiple tables in the DB depending on which fields you used from which table and whether or not it is a Database query subject or a Model query subject. Typically, in a Database query subject you would access only one table whereas in the Model query subject you could combine multiple database query subjects.

What you have to do should be done in the report. If you apply the filter in FM, you would be restricting the data for the whole population that accesses that model.

I can make the filter expression but you will have to provide the granularity of the Account and CC. Who is a parent of whom? Provide some examples.

[Tim86]

Sorry cognostechie, but I am not confusing them with filters in the report.  I am talking about "security filters" in Framework Manager.  Click on any query subject in Framework Manager, and look in the Properties.  You will see a property called "Security Filters", with a "Click to edit" option.  This is where you enter a list of groups or users, for whom you wish to restrict from seeing certain data.  If a user runs a report, and they are in the security filter list for that query subject, then their results will be limited based on the filter which was applied (it doesn't apply to all users...only the ones listed in the filter).  So this has to be done in FM, not in the report.

Accounts and CostCenters are independent attributes of the data.  Neither one is a parent of the other.  So any transaction could have any combination of Account and CC that you could imagine.  That's why my first attempt failed...because accounts X, Y, and Z can occur in all of the CC's, so it didn't really filter anything for me.

[MFGF]

accounts X, Y, and Z can occur in all of the CC's, so it didn't really filter anything for me.

I'm confused. One of your requirements is:

•All CostCenters in Accounts "X", "Y", and "Z"

but then you say above that accounts X, Y and Z can occur in all cost centres. Can you explain this?

:o

MF.

[Tim86]

Here is a list for an illustration.  In this example, the user should see all of the rows except for 2, 3, 5, and 6.  Each account can occur with each costcenter, and vice versa.  But the requirement is for the user to only see the data if the Account is in "X", "Y", and "Z", or if the CC = "A".

row  Acct   CostCenter
1      U      A
2      U      B
3      U      C
4      V      A
5      V      B
6      V      C
7      X      A
8      X      B
9      X      C
10     Y      A
11     Y      B
12     Y      C
13     Z      A
14     Z      B
15     Z      C

[cognostechie]

Sorry cognostechie, but I am not confusing them with filters in the report.  I am talking about "security filters" in Framework Manager.  Click on any query subject in Framework Manager, and look in the Properties.  You will see a property called "Security Filters", with a "Click to edit" option.  This is where you enter a list of groups or users, for whom you wish to restrict from seeing certain data.  If a user runs a report, and they are in the security filter list for that query subject, then their results will be limited based on the filter which was applied (it doesn't apply to all users...only the ones listed in the filter).  So this has to be done in FM, not in the report.

Accounts and CostCenters are independent attributes of the data.  Neither one is a parent of the other.  So any transaction could have any combination of Account and CC that you could imagine.  That's why my first attempt failed...because accounts X, Y, and Z can occur in all of the CC's, so it didn't really filter anything for me.

I understand the Data, Object and Package security in Framework Manager. I just did not notice the word 'security' in your post.

That being said, I created a table called Account with two columns- Account and CC and used the example you gave. I tried your filter and it did not work but this one worked:

[Database Layer].[Account].[CC]  = 'A'
OR
(
[Database Layer].[Account].[Account]  = 'X' or
[Database Layer].[Account].[Account]  = 'Y' or
[Database Layer].[Account].[Account]  = 'Z'
)

However, the security filters are only for one query subject, not for two so you will have to union those two query subjects and then apply the filter

[Tim86]

Thanks cognostechie.  I think the unioned query subjects is a good suggestion.  Unfortunately I'm working with an established model with hundreds of reports already written on it.  So I'm stuck with the two separate query subjects, because this security requirement needs to be applied to the already existing reports.  If I was starting from scratch on a new model, the unioned query subjects would be a great method to try.