topic Re: Section Access through Database in QlikView

Section Access through Database

victor5995 — Wed, 26 Nov 2014 23:26:19 GMT

Hi Folks,

I have requirement to implement section access through database user table that contain USERID. I have to set up this in a way that all the user within this table should able to access the application.

Appreciate any help.....

Thanks

Re: Section Access through Database

jagan — Thu, 27 Nov 2014 02:51:57 GMT

Hi,

Try like this

Section Access;

SQL SELECT

USERID,

PASSWORD,

'USER' AS ACCESS

FROM TableName;

Section Application;

star is *;

Hope this helps you.

Regards,

Jagan.

Re: Section Access through Database

jagan — Thu, 27 Nov 2014 02:54:46 GMT

Hi,

If you don't have password then try this

Hi,

Try like this

Section Access;

SQL SELECT

USERID,

'USER' AS ACCESS

FROM TableName;

Section Application;

star is *;

Hope this helps you.

Regards,

Jagan.

Re: Section Access through Database

victor5995 — Mon, 01 Dec 2014 16:08:07 GMT

Hi Jagan.

Thanks for your quick response.

It is working as intended but by doing this I think all have access as USER only. If I want to give some one ADMIN as access then how can I do that?

Again Thanks.

Re: Section Access through Database

datagrrl — Mon, 01 Dec 2014 16:27:25 GMT

I have a user SQL table that doesn't include my service accounts or developer ID. I load an inline table first then my SQL table.

I do mine like this:

LOAD * INLINE [
USERID, ACCESS

,DOMAIN\ADMIN,ADMIN];

Ideally I would probably write a view with a union statement to pull my admin accounts, as that seems easier to maintain but this solution works.

Re: Section Access through Database

victor5995 — Tue, 02 Dec 2014 13:55:43 GMT

Hi,

I have exact same situation that you described like the database table do not contain the Developer ID's.

I am using the code provided by Jagan Mohan above. Please let me know with that where I can add the inline table which contain the Developer ID's.

Code :

Section Access;

SQL SELECT

USERID,

'USER' AS ACCESS

FROM TableName;

Section Application;

star is *;

Thanks

Re: Section Access through Database

victor5995 — Tue, 02 Dec 2014 14:01:17 GMT

I Solved it by concatenating main USER table with inline table of Developer's ID.

But one step further now I have to reduce the data according to databases. Here all data including user info coming from different databases so if some USERID is existed in particular database then that user should see only data associated with that database only. I have the key here that representing the different database of user but I am not getting how can I implement that key in section access.

Appreciate any Help.

Thanks.

Re: Section Access through Database

datagrrl — Tue, 02 Dec 2014 14:01:57 GMT

Section Access;
SA_Table:
LOAD * INLINE[
USERID,ACCESS
ADMIN,ADMIN
SVC1,ADMIN
];

SA_Table:
SQL SELECT

USERID,

'USER' AS ACCESS

FROM TableName;

Re: Section Access through Database

victor5995 — Tue, 02 Dec 2014 14:13:14 GMT

But here how can I reduce the data ?

Re: Section Access through Database

datagrrl — Tue, 02 Dec 2014 14:17:05 GMT

I used this blog to figure somethig similar out:

http://www.resultdata.com/qlikview-section-access-and-document-authorization/

Re: Section Access through Database

Peter_Cammaert — Tue, 02 Dec 2014 14:51:48 GMT

Usually by adding a Link field to your section access table that connects to your data. Imagine that you want to reduce on Region, and your region field is called ... er Region, then add a column to your database table wherein you specify the allowed region for each user. Load the column together with the userID and you will be set.

If you want to be able to specify multiple regions per user, add a link table to Section Application wherin you expand each LINK value (e.g. NS for North+South, or SEW for South+East+West) into multiple records, each with a different applicable region.

A few very helpful community resources (by Henric of course):

http://community.qlik.com/blogs/qlikviewdesignblog/2014/05/26/section-access

http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/09/methods-for-data-reduction

Best,

Peter

Re: Section Access through Database

victor5995 — Wed, 03 Dec 2014 01:18:01 GMT

Hi,

Can some one give me the sample script that I can see how I can use the reduction field with the section access. Below is the sample code that right now I am using.

Section Access;
SA_Table:
LOAD * INLINE[
USERID,ACCESS
ADMIN,ADMIN
SVC1,ADMIN
];

SA_Table:
SQL SELECT

USERID,

'USER' AS ACCESS

FROM TableName;

Section Application;

star is *;

Thanks

Re: Section Access through Database

jagan — Tue, 09 Dec 2014 04:31:49 GMT

Hi,

Check this sample script for REDUCTION

Section Access;

LOAD * INLINE [

ACCESS, USERID, PASSWORD, REDUCTION

ADMIN, Admin, password, *

USER, Sales, password1, 1

USER, Market, password1, 2

];

Section Application;

star is *;

Departments:

LOAD * INLINE [

REDUCTION, Dept,

1, Sales

2, Marketing];

Sales:

LOAD * INLINE [

Dept, Sales

Sales, 100

Sales, 200

Sales, 4646

Sales, 755

Sales, 422

Sales, 42

Marketing, 2

Marketing, 453

Marketing, 54

Marketing, 45

];

When you login as Admin, you can see all data in the dashboard, when you login as Sales then you can see only Sales data and when you login as Market you can see only Marketing data.

Hope this helps you.

Regards,

Jagan.

Hope this helps you.

Regards,

Jagan.

Re: Section Access through Database

jagan — Fri, 26 Dec 2014 02:00:23 GMT

Hi,

Please close this thread if you got the answer, it helps others in finding answers easily.

Regards,

Jagan.

Re: Section Access through Database

Anonymous — Fri, 26 Dec 2014 04:49:45 GMT

What is the meaning of,

star is *;

regards,

Re: Section Access through Database

jagan — Fri, 26 Dec 2014 05:19:03 GMT

* is wildcard character.

Regards,

Jagan.

Re: Section Access through Database

victor5995 — Fri, 26 Dec 2014 13:56:16 GMT

Hi Jagan,

As I am new to qlikview, When you said close the threat exactly what you want me to do ?

Thanks,

VIpul

Re: Section Access through Database

SunilChauhan — Fri, 26 Dec 2014 15:23:50 GMT

mark it as helpful or correct among above reply if you found suitable reply

Re: Section Access through Database

jagan — Mon, 29 Dec 2014 02:15:42 GMT

Hi,

Just click on the Helpful and Correct Answer buttons to the posts which helps you most in finding the solution.

Regards,

Jagan.