topic Re: Initial Selection Based on Section Access in QV Server (Hide tabs) in QlikView

Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Mon, 10 Oct 2016 18:50:18 GMT

Hello,

I've implemented one of the simpler Section / Sheet access examples I found on here. It appears to work on desktop but not on Server.

I've set the Document Properties to Lock the NTNAME (As USERID) field and reduce Sheets (Conditionally Show) based on Section Access.

Section Access;

/* CHANGE THE NTNAME, USER, OMIT VALUES in xls file TO REFLECT YOUR OWN USERS */

SECURITY:

LOAD ACCESS,

NTNAME,

USER,

OMIT

FROM

BillingUserSheetAccess.xls

(biff, embedded labels, table is UserRoles$);

///* CHANGE THE NTNAME AND USER VALUES TO REFLECT YOUR OWN USERS */

Section Application;

LOAD NTNAME,

USER as ROLE

Resident SECURITY;

inner Join

//SHEET_ACCESS:

LOAD USER as ROLE,

Main as SH01,

[Main - Management Team] AS SH06,

Contribution AS SH02,

[Contribution - Management Team] AS SH07,

Deployment AS SH03,

Profitability AS SH04,

[Revenue - Management Team] AS SH08

FROM

BillingUserSheetAccess.xls

(biff, embedded labels, table is SheetAccess$);

Rename field NTNAME to USERID;

DESKTOP:

QV SERVER:

Any help would be much appreciated!

Thanks,

Channing

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Mon, 10 Oct 2016 20:06:28 GMT

Hi Channing,

I think you need to simplify things a little.

First up, your section access chunk wants to be something like:

Section Access;

LOAD
ACCESS,

USERID,

ROLE

FROM

BillingUserSheetAccess.xls

(biff, embedded labels, table is UserRoles$);

Section Application;

The spreadsheet needs to contain ADMIN or USER in the ACCESS column, the AD Usernames in the USERID column and one of the roles in the ROLE column.

You then need to load the role table, with the sheet access;

RoleAccess:

LOAD

USER as ROLE,

Main as SH01,

[Main - Management Team] AS SH06,

Contribution AS SH02,

[Contribution - Management Team] AS SH07,

Deployment AS SH03,

Profitability AS SH04,

[Revenue - Management Team] AS SH08

FROM

BillingUserSheetAccess.xls

(biff, embedded labels, table is SheetAccess$);

You don't need to do any joins or anything else here.

Straight after you reload you should see the RoleAcess table in it's entirety, and the section access table will not show (it is never visible in the data model).

Make sure that you select to have Initial Data Reduction and Strict Exclusion on in the Document Properties and save. When you open the document up again (I would do this without closing the first) you should see just the row in the Role Access table associated with your user.

The code for the show and hide criteria for each sheet will need to be:

sum(SH01) > 0

You need to be careful with Section Access, as you can lock yourself right out of your application, you may want to read this blog post:

https://www.quickintelligence.co.uk/help-ive-locked-myself-out/

Let me know how you get on.

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Mon, 10 Oct 2016 20:41:54 GMT

Steven,

Thank you for the very helpful information to simplify my application. However, when I attempt to distribute this it's failing ("Can't open the file"). I noticed this issue comes with Strict Exclusion. EDIT: I take that back, I hadn't updated the excel file. Testing now!

Okay, I was able to successfully distribute the application. Now, one of my accounts is working as expected, mine (cworkman), the others (MGMTTEAM, OWNER) are not working and I'm getting this pop-up.

Any thoughts?

Thanks,

Channing

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Mon, 10 Oct 2016 21:23:48 GMT

Hi Channing,

That is exactly the message they should receive if they have no access whatsoever.

Sorry, you should replace the USERID column with NTNAME, and that should match with the user account of the user logging in.

If you are not sure of the NTNAME of the user logging in, create a simple application with no Section Access with a text box on the front sheet with the following expression:

=OSUser()

This will show you what you need to have in the Section Access table.

Note that to reload and distribute using Publisher you will also need to include the Service account of the Publisher Service in the Section Access list.

Hope that helps.

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Tue, 11 Oct 2016 17:47:42 GMT

Steve,

I've created a text object to see what the OSUser is while I'm in the document (Logged in as cworkman).

It appears that the Publisher's Service account is the OSUser at the time of script loading, but once in the application cworkman takes the role. Image below:

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Tue, 11 Oct 2016 19:58:22 GMT

That's correct, so both need to be in the Section Access list, along with

any other users who need access.

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Tue, 11 Oct 2016 20:04:48 GMT

By both you mean the service agent and me? Because both are included in the list. However, it's not reading into section access correctly. It's reading it as the agent upon loading, but when I'm logged in OsUser changes to me, but that's not being reflected. If i'm misunderstanding please clarify!

Thanks,

Chan

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Tue, 11 Oct 2016 20:27:46 GMT

I'm not sure I follow. Can you post a copy of your load script as it is now?

=OSUser() in a text box will always show the current user who has opened the document - regardless of who reloaded it.

I'm not sure how that OSUser table box is getting populated.

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Tue, 11 Oct 2016 20:51:11 GMT

Steve,

This is my script. I included the OSUser() method to see the side-by-side view of NTNAME and OSUSER.

Any thoughts?

//Section Access;

/* CHANGE THE NTNAME, USER, OMIT VALUES in xls file TO REFLECT YOUR OWN USERS */

//Controls access to specific fields of data

LOAD ACCESS,

NTNAME,

ROLE,

OMIT,

OSUser() as OSUser

FROM

BillingUserSheetAccess.xls

(biff, embedded labels, table is UserRoles$);

///* CHANGE THE NTNAME AND USER VALUES TO REFLECT YOUR OWN USERS */

SECTION Application;

RoleAccess:

LOAD USER as ROLE,

Main as SH01,

[Main - Management Team] as SH06,

Contribution as SH02,

[Contribution - Management Team] as SH07,

Deployment as SH03,

Profitability as SH04,

[Revenue - Management Team] as SH08

FROM

BillingUserSheetAccess.xls

(biff, embedded labels, table is SheetAccess$);

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Thu, 13 Oct 2016 19:12:15 GMT

OSUser() in the load script will always be the user doing the reload (in this case the server) rather than the user opening the document.

At the moment you have Section Access commented out, so this will not be applying any security.

Are there fields you are wanting to remove from some users? If not remove the OMIT column from section access. Similarly, I would not put the OSUser() code in your section access table - this will only confuse things.

I notice that in the second table you have USER as ROLE. I would have thought that this should read just ROLE, so that the role from the one table applies to the other.

If the only thing you are using this for is to show and hide sheets then I would not use section access at all.

Simply load your SheetAccess table and then on each sheet do something like this:

Sum({<USER={'$(=OSUser())'}>}SH01) > 0

You will need to ensure that the USER in the table matches exactly, case and all, OSUser. You may be safest to do an upper on both, e.g.

RoleAccess:

LOAD

Upper(USER) as USER,

Main as SH01,

[Main - Management Team] as SH06,

Contribution as SH02,

[Contribution - Management Team] as SH07,

Deployment as SH03,

Profitability as SH04,

[Revenue - Management Team] as SH08

FROM

BillingUserSheetAccess.xls

(biff, embedded labels, table is SheetAccess$);

And

Sum({<USER={'$(=Upper(OSUser()))'}>}SH01) > 0

Hope that helps.

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Thu, 13 Oct 2016 19:19:29 GMT

Steve,

I commented the section access out so I could see what data was being produced with that script. I included the OSUser() to see if NTNAME and OSUser() would match once deployed on the server.

NTNAME was QVSERVICEAGENT and OSUser was cworkman.

Omit is there because I must hide fields and that is working perfectly fine. The issue is the fact that my distribution server isn't recognizing the User logging in.

Thanks,

Channing

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Fri, 14 Oct 2016 07:08:25 GMT

Hi Channing,

The OSUser at reload time is of no relevance to Section Access, as long as that account has the rights to open and reload the app - you will get a QMC fail if this is not the case.

OSUser when an app is open on the server will be the AD account that is used to log in to the web server. On a local network this will usually pick up directly from the machine login, and therefore be the same as it is when the app is open in Desktop.

I don't understand what you mean by the Distribution Server not recognising the user logging in. This server just reloads and saves the document.

Do you still have users who are unable to open the document post reload?

Have you tried going into the app as them when the Section Access has been commented out and seen what they have as their OSUser()?

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Tue, 18 Oct 2016 18:02:53 GMT

Steve,

If I remove section access and strict exclusion I get the right User(). However, if I keep strict exclusion it won't allow the MgmtTeam and Owner users. My name (cworkman) works with Section Access and Strict Exclusion or Section Access alone. I keep coming back to Strict Exclusion as the issue.

Is there something I'm doing wrong on the Server side? (Adjust QMC)

Thanks,

Channing

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Tue, 18 Oct 2016 22:31:41 GMT

Are MgmtTeam and Owner users or groups? Have you tried using individual users instead of groups?

With the Section Access part commented out, does the section access table link correctly to the rest of the data on the field you are matching on?

When you select MgmtTeam does it select the correct matched data (unless you are using *, in which case it won't).

Are all field names being matched by Section Access upper case? Are all field values in these fields also in upper case?

If you create a text box with the following expression in, what does it return when you log in, and what does it return with MgmtTeam logs in?

=maxstring({<NTNAME={'$(=Upper(OSUser()))'}>}NTNAME)

Hopefully we will get this fixed soon for you.

I can't see that there is anything that will need to change on the QMC side, if it works for your user and the other accounts are also users.

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Tue, 01 Nov 2016 19:56:35 GMT

Steve,

I have it working the way I'd like it to now, but I think I've definitely executed a work-around.

So, I left strict exclusion unchecked, but Section access blocks the appropriate data dependent on the User (My data is hidden safely away).

However, I've renamed the fields in my SheetAccess table so that nothing is excluded (all users all roles and their specific flags). I then set up a trigger that selects the User from that table dependent on the User logged in and locked the field upon Opening.

This hides the appropriate sheets while maintaining section access's data exclusion.

Does this make sense and if so is this a "safe" way to go about things?

Thanks,

Channing

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

stevedark — Tue, 01 Nov 2016 21:31:48 GMT

Hi Channing,

The main thing to ensure is that rows are removed using Section Access, so that data is secure. Manipulating the UI, by hiding sheets etc., doesn't requite to be part of Section Access, and a data island with this will work fine.

I would be wary of not having Strict Exclusion checked - in what way does it fail if this is ticked? Sometimes you need to add extra rows to your Section Access table so that all permutations exist in that table - to define the scope of what the * symbol refers to.

Generally, I would look to avoid triggers. From what I understand of why you are using these, a bit of Set Analysis may work better. Something like this on the Sheet Visible property:

=maxstring({<SheetUser={'$(=Upper(OSUser())))'}>}SH03Allowed) = 'Yes'

This way you don't need to apply the selection with the trigger and there is no risk of someone clearing a selection, or that selection appearing in the Current Selections box.

Steve

Re: Initial Selection Based on Section Access in QV Server (Hide tabs)

Anonymous — Thu, 03 Nov 2016 14:35:16 GMT

Steve,

My issue is any user not assigned as an "Admin" getting completely locked out when Strict Exclusion is checked. Thank you for the tip on Set Analysis. I can see how that would be more effective for the UX.

Thanks,

Channing