topic Re: Row Level filter with a table linked to many reports in SAS Visual Analytics

Row Level filter with a table linked to many reports

gemi — Sun, 20 Nov 2022 16:54:53 GMT

Hi, I have this situation:

User1 has the authorization to view just 2 reports, named REPORT_A and REPORT_B. Of course, the authorizations are not assigned directly to the user but to the groups to which he belongs (GROUP_REPORT_A and GROUP_REPORT_B).

Both reports are built with few tables and they have a table in common, named TABLE_X.

This TABLE_X has a lot of data, columns, rows.. but has a column identifying city names, named NAMENATION.

For example, this column has values like "Paris", "Milan", "New York"...

I would set authorizations so that when User1 opens REPORT_A , he will see the report without any filters. But when he opens REPORT_B, he will see the report filtered by NAMENATION.

I have setted authorizations on TABLE_X in this way:
GROUP_REPORT_A: ReadInfo --> Grant ; Select --> Grant

GROUP_REPORT_B: ReadInfo --> Grant ; Select --> Row-Level Grant (NAMENATION = 'Paris').

Unfortunately in this way, User1 always sees, for both records, all the data without filters.. where am I wrong?

Re: Row Level filter with a table linked to many reports

Sajid01 — Thu, 24 Nov 2022 01:32:22 GMT

I believe you are using Visual Analytics. The users permission for Report__A that makes the user see unfiltered table. Please have a look here.

https://documentation.sas.com/doc/en/vacdc/7.5/vaag/n0hopehj2j9vzun1jm9rl56bpnrh.htm

Re: Row Level filter with a table linked to many reports

gemi — Thu, 24 Nov 2022 09:45:30 GMT

Yes, I know that the permission for Report_A makes the user see unfiltered table... Infact I would find a solution. What you linked I already know..
Is it so difficult to say:
When User1 open Report_A view data with this filter and when User1 open Report_B view data with another filter (or unfiltered).. ?
I know that the problem is because both report are built with same table..

Re: Row Level filter with a table linked to many reports

Sajid01 — Thu, 24 Nov 2022 10:10:40 GMT

One approach is to create a view or another table with filtered data.

use this table to create the second report_B.

Re: Row Level filter with a table linked to many reports

gemi — Thu, 24 Nov 2022 10:25:49 GMT

Unfortunately, this is a wrong approach in my case.. the table ha 2,2M of rows and as many columns.
Moreover, we a lot of users that have to view reports filtered by different values.. so I can't replicate that table for each users..

Re: Row Level filter with a table linked to many reports

Sajid01 — Sat, 26 Nov 2022 21:26:19 GMT

In many cases what the analytics users see as a table is actually a database view. Creating another view should not be an issue. However I see that this is not your preference.
As and when your resolve the issue, please do share it.

Re: Row Level filter with a table linked to many reports

HunterT_SAS — Mon, 28 Nov 2022 15:51:12 GMT

If User 1 is part of two groups GROUP_REPORT_A and GROUP_REPORT_B, and those two groups have different permissions on the same CAS table, then User 1 is going to end up with both applied anytime the user accesses that table. The only way around this is to have separate tables like this:

Report A -> uses Table X

Report B -> uses Table Y (which is really a View or Copy of Table X)

Then for permissions:
GROUP_REPORT_A -> Table X -> ReadInfo --> Grant ; Select --> Grant

GROUP_REPORT_B -> Table Y -> ReadInfo --> Grant ; Select --> Row-Level Grant (NAMENATION = 'Paris').

I don't think you'd need a separate table for each user but maybe I'm misunderstanding what you're doing.

Otherwise I'm not sure if there's another way around this.

Re: Row Level filter with a table linked to many reports

Sajid01 — Thu, 01 Dec 2022 02:13:58 GMT

Hello @HunterT_SAS
@gemi has been doing what you have suggested. As can be seen from the documentation, I referred above and the screen capture below, the outcome of @edimauro is what is expected.

(Please correct me if I have mis-understood the documentation)

There could be many approaches to resolve this issue, a separate table may not be needed, but faced with a similar situation, I would create a view of the original table and use it.

Re: Row Level filter with a table linked to many reports

gemi — Thu, 01 Dec 2022 07:42:44 GMT

@Sajid01 do you think is possible to create a view of table directly on SAS Environment Manager ? Or do I have to create the view on my DBS and then import it on SAS ?

Re: Row Level filter with a table linked to many reports

HunterT_SAS — Thu, 01 Dec 2022 16:40:23 GMT

@Sajid01 - I think my response was misunderstood. It sounded like this thread was winding down with no solution so I wanted to jump in and re-iterate some key points including the documentation you noted and expected results, AND that the only way this works is if Report A and Report B used separate views or tables. My example uses separate tables (which I don't think is already being done by @gemi ?) with RLS rules on each table for the two different groups. But you're right it doesn't have to be separate tables, it could be a view too. The point is the reports cannot use the exact same table in this scenario.

As far as creating a view, I don't believe Environment Manager can do this. Maybe SAS Data Studio can, but I'm not familiar with it enough to know for certain.

Re: Row Level filter with a table linked to many reports

Sajid01 — Fri, 02 Dec 2022 13:35:05 GMT

I am sorry @HunterT_SAS I must have misread your post. We both have the same approach in mind.
I agree @gemi is not creating a separate table.
In the event I have the option to create the said report and have the option to use SAS code in place of Visual Analytic, I would prefer using SAS code.