Discussion Board for collaboration on QlikView Scripting.
I would like to create a section access for my QlikView document.
But it does not work...
Here is my source code:
SQL SELECT ACCESS,
SQL SELECT USER_NAME,
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.
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.
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!
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:
ACCESS NTNAME USER_NAME
ADMIN VAILLANT\skoenen SKOENEN
ADMIN VAILLANT\bjbraune BJBRAUNE
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.
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
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
Supper(SAPUSER) as SAPUSER
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?
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;
SQL SELECT *
SQL SELECT region,
SQL SELECT costs,
and my table content:
ACCESS NTNAME SAPUSER
ADMIN VAILLANT\bjbraune brauneb
ADMIN VAILLANT\skoenen koenens
SAPUSER region value
koenens de 432433
koenens en 7575
brauneb de 76731
brauneb nl 654687
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.
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`;
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).
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.