topic AW:Re: AW:Re: Section Access with MySQL Database in QlikView

Section Access with MySQL Database

Mon, 08 Nov 2010 14:20:54 GMT

Hi,

I would like to create a section access for my QlikView document.

But it does not work...

Here is my source code:

----------------

Section Access;
SQL SELECT ACCESS,
NTNAME,
USER_NAME
FROM `qvr_vgv`.`tbl_section_access`;

Section Application;

user_allocation:
SQL SELECT USER_NAME,
SAPUSER
FROM `qvr_vgv`.`tbl_user_allocation`;

----------------

I think the problem are the [ that have to be around the fields access and ntname.

I have tried to rename the db fields with the brackets as well as renaming the fileds within the sql statement (ACCESS AS [ACCESS]) but this does not work either.

If you have any idea please answer.

Thank you

Kind regards

Simon Koenen

Section Access with MySQL Database

pover — Mon, 08 Nov 2010 16:43:52 GMT

What part doesn't work exactly? The script doesn't run, the user can't login or the data is not filtered properly?

With NTNAME make sure you include the domain or include the NTDOMAINSID field. Also, a step that many developers miss when filtering data with section access is ticking the checkbox to enable Initial Data Reduction based on Section Access in the Opening tab in Document Settings.

Regards.

Section Access with MySQL Database

Miguel_Angel_Baeyens — Mon, 08 Nov 2010 19:46:57 GMT

Hello Simon,

First of all, I'd give a try to

Section Access;LOAD UPPER(ACCESS) AS ACCESS, UPPER(NTNAME) AS NTNAME, UPPER(USER_NAME) AS USER; // USER is required to ask the user for additional login, otherwise, leave it as USER_NAME //UPPER(PASSWORD) AS PASSWORD // Uncomment it if you are going to use the USER field, otherwise, delete it SQL SELECT ACCESS, NTNAME, USER_NAMEFROM `qvr_vgv`.`tbl_section_access`; Section Application; user_allocation:NOCONCATENATE LOAD *;SQL SELECT USER_NAME, SAPUSERFROM `qvr_vgv`.`tbl_user_allocation`;

Hope that helps!

AW:Re: Section Access with MySQL Database

Tue, 09 Nov 2010 07:46:52 GMT

First of all thank you for your tips I have tried to change my statements in the way Miguel A. Baeyens has mentioned but it still does not work.

Here are a few more details. I hope this will help you understanding my problem:

The MySQL Database looks like this:

tbl_section_access:

ACCESS NTNAME USER_NAME
ADMIN VAILLANT\skoenen SKOENEN
ADMIN VAILLANT\bjbraune BJBRAUNE

tbl_user_allocation:

USER_NAME SAPUSER
SKOENEN KOENENS
BJBRAUNE BRAUNEB

My main objective is, that the user opens the document and if he has a valid authorization he will only see the data he is authorized for. In my case I need the connection between the Windows User and the SAP User. If I login the system has to limit the user allocation table to my sapuser and if my collegue logs in he will only see his sapuser.

If I use an Excelfile containing the necessary data (both tables) it works but if I use the same table structure within a mysql database the fields are empty and after closing the qv file I am not able to open it anymore because the message appears that I am not authorized. I think this problem has something to do with the brackets around the field names but if I change my sql statement within the section access and put a load statement in the head of it this does also effects the authorization error.

I hope this makes everything a bit easier to understand.

AW:Re: Section Access with MySQL Database

Tue, 09 Nov 2010 09:47:11 GMT

Hi,

You need to reorganize the Section Access table like to contain only fields NTNAME and the desired restriciton field, ex SAPUSER .

Later on, one table contains a field called SAPUSER with uppercase field name, and uppercase content


Section Access;

LOAD
 UPPER(t1.NTNAME) as NTNAME,
 UPPER(t2.SAPUSER) as SAPUSER;
SQL select .. from `qvr_vgv`.`tbl_section_access` t1 inner join `qvr_vgv`.`tbl_user_allocation` t2 on t1.USER_NAME = t2.USER_NAME


Section Application;
user_allocation:
 ..
 Supper(SAPUSER) as SAPUSER
 ..

-Alex

AW:Re: Section Access with MySQL Database

Miguel_Angel_Baeyens — Tue, 09 Nov 2010 17:53:48 GMT

Hello Simon,

I see some things here. What Alexandru says in the post above seems to be related. If you are not able to log into the document, I assume you are using "Reduce Data..." and "Strict Exclusion", then it's not a user/password issue, since it's not asking for the password three times, but avoiding you to open the document. If that's the case, be sure that a SAPUSER exists in your datamodel further than just the Auth table.

My guess is that SAPUSER field is not present in any other table in the datamodel, so when it asks for login and password, then QlikView reduces according to that user, the document becomes empty, so you cannot access it.

If you want your users only see some information (records) but not all of them, then you should add in some tables SAPUSER as field. Take the following example. Say your users are sales agents, that are only allowed to see information about their own clients, but not other sales agents' clients. SAPUSER field would correspond to field "Sales Agent" in "Clients" table, so any client will have a field with that information, so any user will be able to login.

Is that right?

AW:Re: AW:Re: Section Access with MySQL Database

Wed, 10 Nov 2010 14:49:09 GMT

Hi,

I have removed the section access and the tables are connected by the field user_name in the data model. So a connection between the tables is possible..

Furthermore I have added some more tables with dummy data and deactivated the strict exclusion but I still get the no access message on restarting the document. I have replaced the user_name with the sapuser to remove the connection table (user allocation). What confuses me is the fact that it works if I use a csv-flatfile or an internal table with the same data structure.

My source code looks like this:

ODBC CONNECT TO QV_VGV_MYSQL;

Section Access;
SQL SELECT *
FROM `qvr_vgv`.`tbl_section_access`;

Section Application;
SQL SELECT region,
SAPUSER,
value
FROM `qvr_vgv`.`tbl_auth_dummy_data_1`;

SQL SELECT costs,
SAPUSER
FROM `qvr_vgv`.`tbl_auth_dummy_data_2`;

and my table content:

[access table]

ACCESS NTNAME SAPUSER
ADMIN VAILLANT\bjbraune brauneb
ADMIN VAILLANT\skoenen koenens

[dummy table]

SAPUSER costs
brauneb 7343464
koenens 34349976

[dummy table]

SAPUSER region value
koenens de 432433
koenens en 7575
brauneb de 76731
brauneb nl 654687

AW:Re: AW:Re: Section Access with MySQL Database

pover — Wed, 10 Nov 2010 15:33:30 GMT

Hello Simon,

It is wierd that everything works from a flat file and not SQL so try putting using the trim() function around the fields you are importing from SQL to make sure you don't have an extra character causing problems.

Section Access;
LOAD UPPER(trim(ACCESS)) AS ACCESS,
UPPER(trim(NTNAME)) AS NTNAME,
UPPER(trim(USER_NAME)) AS USER_NAME
SQL SELECT * FROM `qvr_vgv`.`tbl_section_access`;

Regards.

AW:Re: AW:Re: Section Access with MySQL Database

Miguel_Angel_Baeyens — Wed, 10 Nov 2010 20:00:10 GMT

Besides that, and following my code example above and as suggested by Karl in the previous post, note that all fields in section access must be uppercase (both name and values).

Regards.

AW:Re: AW:Re: AW:Re: Section Access with MySQL Database

Thu, 11 Nov 2010 09:18:35 GMT

I have changed the fields to capital letters (upper) and removed the empty spaces (trim) as Karl has written and it works.

Thank you very much for your help.