topic How to filter documents from the user id(Authorization) in QlikView

How to filter documents from the user id(Authorization)

Tue, 03 Aug 2010 08:53:57 GMT

I have table documentsTable

idDoc, Sum, Date, idUser - these are columns. I make this table in qvd format for example.

So how can i do, that user login in qlikview - and if his idUser = 1 than he can see reports and documents which are only with idUser = 1 from table documentsTable? Some kind of authorization.

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Tue, 03 Aug 2010 09:21:17 GMT

Hello Evgeny,

You may define a section access table where, besides asking the user login and password, use that login as filter, enabling "Strict Exclusion" in your Document Properties. Be very careful when doing this and backup your file, as if you fail to login (your section access does not behave as it is supposed to) you may lose your document.

Regards

How to filter documents from the user id(Authorization)

Tue, 03 Aug 2010 09:28:05 GMT

Hi,

You can implement the security through section access. You should include the section access details in the script and enable the data level security

The sample script is as follows:

Section Access;
Load * inline
[ACCESS,USERID,PASSWORD
ADMIN, A,X
USER,U,Y ];
Section Application;
Load... … from... …

You can find more details in the Qlikview reference manual.

-Haneesh

How to filter documents from the user id(Authorization)

Tue, 03 Aug 2010 09:31:07 GMT

Thank u Miguel for ur answer! But could ur write some example script of section access.. and what controls in qlikview are for login of user, and how this info stored in qlikview? or where can i read about it? in tutorials it is er few info about this..

How to filter documents from the user id(Authorization)

Tue, 03 Aug 2010 09:34:53 GMT

see a real example described previously

http://community.qlik.com/forums/p/32211/124570.aspx

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Tue, 03 Aug 2010 09:38:15 GMT

Hello Evgeny,

Haneesh sample code might do, but please download and check this document in order to understand better the way section access works. Once the user has logged, the data will be reduced according to the user id or whatever field you want to use, so charts will only display info linked to that user.

Regards

How to filter documents from the user id(Authorization)

Tue, 03 Aug 2010 09:49:54 GMT

Thank u Miguel, and Thank u All!! i will study links that u give me.. and than it would be more concrete questions i guess..)

How to filter documents from the user id(Authorization)

Tue, 03 Aug 2010 09:55:37 GMT

Thanks Miguel! this document is very helpful to understand the security implementation.

-Haneesh

How to filter documents from the user id(Authorization)

Tue, 03 Aug 2010 16:10:35 GMT

I have table in excel:

USERID PASSWORD ACCESS GROUP
ADMIN 123 ADMIN
User1 1 USER GROUP1
User2 2 USER GROUP2
User3 3 USER GROUP3
User4 4 USER GROUP4

than, i'm loging through for example user2.

i have another table in excel

GROUP Position_ID
GROUP1 397
GROUP2 74
GROUP3 482
GROUP4 350

So, when i'm logging through user2 it gets that user2 position_id is -- 74.

No i must load data from qvd file something like:

[dm_Sales]:
LOAD
dm_Sales_id
, dm_Sales_SK_Date_ID as SK_Date_ID
, dm_Sales_SK_Distributor_ID AS SK_Distributor_ID
, dm_Sales_SK_SKU_ID as SK_SKU_ID
, dm_Sales_SK_Position_ID as Position_ID
, dm_Sales_Doc_ID
, dm_Sales_Amount
FROM dm_Sales.qvd(QVD)
where Position_ID = ?

How can i filter sales table by Position_ID ?
what should i write in query?

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Tue, 03 Aug 2010 17:09:58 GMT

Hello Evgeny,

First, I'd check that

STAR IS *;...ADMIN 123 ADMIN *

adding asterisk means that admin is allowed to see everything.

Second, you don't have to check anything in your select statement, as QlikView will reduce data following the logged in user. It's like when you select one value in Position_ID and then go to File -> Data Reduction -> Keep possible values, which will erase all values not linked to the selected Position_ID. For that, you will need to check Strict Exclusion in your document properties once you are sure your section access is working properly.

Regards

How to filter documents from the user id(Authorization)

Wed, 04 Aug 2010 14:30:48 GMT

Thank u very much, now i can filter documents by id of logging users.
GROUP Position_ID
GROUP1 397
GROUP2 74
GROUP3 482
GROUP4 350
for example if user with userId = 350 logged in, than
documents filter by Position_ID = 350. It's ok.
But..
i want to filter by more than one positions, for ex 350 and 351(this second is a child position from first position)
I tried to do this:

GROUP Position_ID
GROUP1 397
GROUP2 74
GROUP3 482
GROUP4 350
GROUP4 351

and this:

GROUP Position_ID Position_ID
GROUP1 397
GROUP2 74
GROUP3 482
GROUP4 350 351

but it still filters only one - N 350 position.
How can i filter by more than one position?

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Wed, 04 Aug 2010 17:38:18 GMT

Hello Evgeny,

One workaround is to create a link table between the GROUP fields and all possible Position_IDs so when a user logs in and linked to the group, the group links with all possible positions.

Hope that helps

How to filter documents from the user id(Authorization)

Wed, 01 Sep 2010 15:10:07 GMT

Miguel A. Baeyens, Hello, can u send me some info with security date (access control list) info stored not in excel but in database table.O what the differiences between excel and db table?

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Wed, 01 Sep 2010 15:30:09 GMT

Hello Evgeny,

The only difference between the samples above is the method of loading the database, so instead of using INLINE as above, you may use something like

SectionAccess:LOAD ACCESS, USERID, PASSWORD, GROUP; SQL SELECT ACCESS, USERID, PASSWORD, GROUP FROM USERTABLE;

but anyway, it's the same way you do to get your data for the application.

Hope that helps.

How to filter documents from the user id(Authorization)

Thu, 02 Sep 2010 09:03:46 GMT

Thank u Miguel it works good))

But now i want to make aliases to loading fields.

So ho can i write something like this:

LOAD

[GROUP]

, SK_Employee_ID;

SQL SELECT Subject, Object FROM [admin].[EmployeesACL];

It doesnt work because it can't find field [GROUP], but in Qlikview i want this field to call as [GROUP],

but in database it calls Subject...

so can i write something like this:

LOAD

[GROUP]

, SK_Employee_ID;

SQL SELECT Subject as [GROUP], Object as SK_Employee_ID FROM [admin].[EmployeesACL];

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 09:10:08 GMT

Hello Evgeny,

I prefer to rename fields on the load part, rather than on the select statement, so it should look like

LOAD Subject AS GROUP, Object AS SK_Employee_ID;SQL SELECT Subject, Object FROM [admin].[EmployeesACL];

Regards.

How to filter documents from the user id(Authorization)

Thu, 02 Sep 2010 09:14:12 GMT

Wow thaank.u )) it also works. Do u have skipe? or email?)) From what country u are?)

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 09:37:16 GMT

Hello Evgeny,

I'm Spanish. You can contact me starting a conversation in my profile 🙂

Regards.

How to filter documents from the user id(Authorization)

Thu, 02 Sep 2010 10:36:45 GMT

I've replace login data from excel into two tables in db

It seems that everything the same, but userid doesn't works - it asks me for it for 3 times and than qlikview fails. but this userid exists in table.

but userid -admin - is working ok.

may be u met this problem before?

How to filter documents from the user id(Authorization)

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 11:05:38 GMT

Evgeny,

First, make sure that in your document properties as admin, you don't have checked "Strict Exclusion" in the "On Open" tab unless you are completely sure your section access works. I rather check, when testing, the "Initial selection based on section access" just to see that users accessing the document have records related.

Make sure as well that your passwords doesn't contain spaces or characters that may vary depending on the keyboard layout.

But if it asks you for the password means that either user or password are not correct. You may have done some changes to your document, then you need to reload for those changes to take effect.

Hope that helps.