topic Re: Show and Hide Sheets based on Section Access in QlikView

Show and Hide Sheets based on Section Access

qlikwiz123 — Sat, 16 Nov 2024 00:07:28 GMT

Hi,

How can I show one sheet for everyone irrespective of their Section Access Presence (They are not in the Section Access script at all) and hide another sheet for such users?

Example:

SECTION ACCESS;

Users:
LOAD * INLINE
[ACCESS,ID
ADMIN,197
ADMIN,464
ADMIN,387
ADMIN,999
];

All the IDs listed in Section Access should see both the Sheets but Users who are not in Section Access should only see Sheet1 but not Sheet2.

Re: Show and Hide Sheets based on Section Access

jwjackso — Tue, 18 Aug 2020 15:05:43 GMT

In our case, we have an application that has both financial data and salary data. All users should see the financial data, but only users that manage employees should see the salary data. The financial and salary data are in 2 different tables. Access to employee data is based on the user's department in the section access.

To allow all users access to the application and see the finance data, I created a dummy department and concatenated all users the section access table with the dummy department. This allows all users access to the application, but they cannot see salary data unless they have a legitimate department in the section access table.

I hide the salary sheet by adding the following expression to the "Show Sheet" conditional expression.

If (count(employee) > 0,1,0)

Only users that have a legitimate department in the section access table will have a count greater than 0.

Re: Show and Hide Sheets based on Section Access

eliran — Tue, 18 Aug 2020 15:12:30 GMT

Hi,

When using section access, you need to include ALL the users that needs to use it.

You can handle show/hide sheets quite easy, when adding additional data to the script as follows

SECTION ACCESS;

Users:
LOAD * INLINE
[ACCESS,ID,ShowSheet
ADMIN,197,1
ADMIN,464,1
ADMIN,387,1
ADMIN,999,1
USER,XXX,0
];
Section Application;
LOAD * INLINE [
ShowSheet
1
0
]

What I have done, is add another column with the value 1 inside, now when a user is connecting to the app, if he is matched with that row, he will have 1 as value, if not he will have 0.

Now inside the sheet conditional, you can use the following -

=Sum(ShowSheet) - It's suppose to return one to those who have access.

BR,

Eliran.

Re: Show and Hide Sheets based on Section Access

qlikwiz123 — Tue, 18 Aug 2020 15:39:27 GMT

Hi @eliran @jwjackso

Thank you. I thought about this approach as well. But the caveat here is, I still need to add all the users in Section Access table and there are thousands. I can easily identify users who should have access to both Sheet1 and Sheet2 but there will be thousands who should have access just to Sheet1 but not Sheet2

Re: Show and Hide Sheets based on Section Access

eliran — Wed, 19 Aug 2020 07:25:30 GMT

Well, when working with section access, you can't avoid adding all the users.

What you can do, if you got access to all those users, is load them with the value 1 in section access field, and then concatenate the rest with 0 in the section access field as follows.

Section_Temp:
load UserID as USERID,
'ADMIN' as ACCESS,
1 as ShowSheet
from allusers.qvd/dbo.allusers/allusers.xlsx;
concatenate
LOAD * INLINE
[ACCESS,ID,ShowSheet
USER,XXX,0
];

SECTION ACCESS;

load
USERID,
ACCESS,
ShowSheet
resident Section_Temp;

drop table Section_Temp;

Section Application;
LOAD * INLINE [
ShowSheet
1
0
]

Re: Show and Hide Sheets based on Section Access

Brett_Bleess — Wed, 02 Sep 2020 14:47:38 GMT

@qlikwiz123 You do realize you can use Security Groups if you are using NTNAME system field in Section Access? I would recommend you review the following posts, they may be helpful too:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Section-Access/ba-p/1465766

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Security.htm

The only other way to do this would be to split the application into two different apps, one for the Section Access folks and the other that would not have Section Access at all.

Regards,
Brett

Re: Show and Hide Sheets based on Section Access

colinodonnel — Tue, 18 Jul 2023 15:33:13 GMT

Hiya,

This is the key idea and worked a treat

Section Application;
LOAD * INLINE [
ShowSheet
1
0
]

This can be tailored for more than 2 groups and then use something along the lines of

=if(min(ShowSheet) = 2, 1, 0)