Qlik Community

QlikView Administration

Discussion Board for collaboration on QlikView Management.

cancel
Showing results for 
Search instead for 
Did you mean: 
imac
Contributor II
Contributor II

Section Access filtering at three different levels

Hi all

My organization has requirement to filter at three levels - geographic(region / branch), product, and salesperson. The QVW is using data reduction based on section access. A sales person can belong to only one branch but may sell one or more products.

The idea being there are region heads who should see all branches in their region, branch managers who should see everything in their branch, product managers who should see everything for a specific product globally, and of course, the individual sales people who should see their own data.

Have been trying to read through how this can be implemented in section access, and the way I am attempting to do this is to:

- create all possible combinations of branch, product and salesperson in my section access with access of admin and ntname of the QVS service account, as an example

ACCESS     NTNAME     BRANCH     PRODUCT     SALESPERSON

ADMIN        QVS             B1                P1                  S1

ADMIN        QVS             B1                P1                  S2

ADMIN        QVS             B1                P1                  S3

ADMIN        QVS             B1                P2                  S1

ADMIN        QVS             B1                P2                  S2

ADMIN        QVS             B1                P2                  S3

ADMIN        QVS             B2                P1                  S4

ADMIN        QVS             B2                P2                  S4

ADMIN        QVS             B2                P2                  S5

and so on... and:

- specific user level access rows for branch, product and sales person.

ACCESS     NTNAME     BRANCH     PRODUCT     SALESPERSON

USER          U1                B1                *                    S1

ADMIN        U2                *                   P1                  *

ADMIN        U3                B2                *                     *

and so on...

The second part is not a lot of data, but the problem is, for the first part, each time there is a new sales person or a new product, I have to go back and make sure all the new combinations are included otherwise the initial reduction does not let those data rows come through

I wrote a quick and dirty QVW to load the a list of all sales persons and products from an excel spreadsheet, do joins to create all the combinations and then use a table box to export it to another excel file, which has a master copy of the second part of the section access, which I then read into my main QVW as the section access (if I try to direct 'store' the resulting join table to a QVD or CSV for some reason it wont store the '*'),

it has cut down the time a lot in keeping this updated, but I still have to manually keep doing this each time there is a new sales person or product

There must be a better way of doing this right?

Any help appreciated.

1 Solution

Accepted Solutions
Or
Master III
Master III

You should be able to get this information from the actual fact data, e.g.

AUTHENTICATION:

Load Distinct 'ADMIN' as ACCESS, 'QVS' as NTNAME, BRANCH, PRODUCT, SALESPERSON

Resident FactTable;

JOIN

Load (Specific user level access)

Keep in mind this may slow down the data reload if you have a large volume of data in the fact table.

View solution in original post

3 Replies
Or
Master III
Master III

You should be able to get this information from the actual fact data, e.g.

AUTHENTICATION:

Load Distinct 'ADMIN' as ACCESS, 'QVS' as NTNAME, BRANCH, PRODUCT, SALESPERSON

Resident FactTable;

JOIN

Load (Specific user level access)

Keep in mind this may slow down the data reload if you have a large volume of data in the fact table.

imac
Contributor II
Contributor II
Author

Thanks Or. Works like a charm. I have to do a bunch of joins to generate all the combinations but with that, this works pretty cleanly

Thanks again!

Or
Master III
Master III

Happy to help. I made a mess of the same question before I got there eventually, so better if I can save someone else the trouble.