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

[Anonymous]

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

[stevedark]

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

[Anonymous]

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

[stevedark]

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

[Anonymous]

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:

[stevedark]

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

any other users who need access.

Steve

[Anonymous]

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

[stevedark]

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

[Anonymous]

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$);

[stevedark]

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