topic Section Access - logical OR on different fields in QlikView

Section Access - logical OR on different fields

brunodec — Mon, 06 Jun 2011 14:51:23 GMT

Hi,

I wondered if there is a way to have section access with a logical OR on two different fields. For instance, people out of different countries enter their time in a time writer application, and they book their time on projects. Each project is also linked to a country. Countrymanagers should be able to see all the time postings of their people (let's say where country = US) and also all time entries against projects from US. So they should also see the time spent from French people on US projects, and also from US people on French projects.

I tried as follows but it gives no data when user 'USMANAGER' logs on. When ADMINISTRATOR logs on, he gets everything (as expected).

Any ideas ?

Thanks !

Section Access;
Load * inline
 [ACCESS, NTNAME, SEC_CODE
 ADMIN, ADMINISTRATOR, SEC_ALL
 USER, USMANAGER, SEC_US
];
Section Application;
star is *;
load * inline
 [SEC_CODE , Employee_Country, Project_Country
 SEC_ALL, *, *
 SEC_US, US, *
 SEC_US, *, US
 ];

Section Access - logical OR on different fields

prieper — Mon, 06 Jun 2011 19:10:15 GMT

Think that the script should do. Have you tried to change the fieldnames Employee_Country resp. Project_Country in capitals?

HTH
Peter

Section Access - logical OR on different fields

brunodec — Thu, 09 Jun 2011 07:40:01 GMT

Hi,

I changed the fieldnames to capitals, but this gives the same result.
In the manual I found a close saying

So I guess this is the reason why it doesn't work ?

Any other suggestions ...?

regards,

Bruno.

The star symbol is not allowed in information files. Also, it cannot be used in

key fields, i.e. fields used to join tables.

Section Access - logical OR on different fields

pat_agen — Thu, 09 Jun 2011 08:05:36 GMT

hi Bruno,

i was intrigued when reading your first post because you said that the Administrator could see everything and it was only the US manager who failed to see what s/he should.

Was this the case? In theory the Administrator was being linked to both Employee_Country and project_country with the value "*" which would normally fail as a link.

Did you have the "Initial data reduction based on section access" and the "strict exclusion" boxes ticked?

Re: Section Access - logical OR on different fields

deepakk — Thu, 09 Jun 2011 08:36:03 GMT

HI Bruno,

You might hve not selected the initial data reduction in your application.

Please find a sample file and the screen shot of the initial data reduction feature.

Re: Section Access - logical OR on different fields

brunodec — Thu, 09 Jun 2011 09:54:29 GMT

Hi Pat,

both "Initial data reduction.." as "Strict exclusion" were selected. I tried without "strict exclusion" but this didn't change anything.

Yes as Administrator I can see everything. I will post a sample file based on Deepak's reply so you can see what happens.

First I thought that the data reduction happened row by row: for user USMANAGER first step is to reduce all data where Employee_Country is not US; second step (on the result of step 1) is to reduce the data where Project_Country is not US. But this should still give me the data for US employees working on US projects, and even these data are missing... caused by the star link??

Re: Section Access - logical OR on different fields

brunodec — Thu, 09 Jun 2011 10:16:04 GMT

Hi Deepak,

regarding the Initial Data reduction... see also my reply to Pat.

I would have loved to insert a sample file based on your file, but I can't find how to attach it here...

Anyway, it's just this little script so anyone can just copy-paste it.

If you open it as administrator, you see all hours (totalling 20 hours). As USmanager, you see no data, where I try to find a solution to see 5+6+2=13 hrs

Oh finally I found the attach button at the bottom

regards,

Bruno.

Section Access;
Load * inline
 [ACCESS, USERID,PASSWORD, SEC_CODE
 ADMIN, ADMINISTRATOR, ,SEC_ALL
 USER, USMANAGER,,SEC_US
];
Section Application;
//star is *;
load * inline
 [SEC_CODE , EMPLOYEE_COUNTRY, PROJECT_COUNTRY
 SEC_ALL, , 
 SEC_US,US, 
 SEC_US, , US
 ];
/// SEC_US, , US
//Section Application;
LOAD * INLINE [
    Employee, EMPLOYEE_COUNTRY, ProjectID, PROJECT_COUNTRY, HOURS
    John, US, PRJ_01, US, 5
    John, US, PRJ_02, NL, 6
    Kees, NL, PRJ_01, US, 2
    Kees, NL, PRJ_02, NL, 7
];

Section Access - logical OR on different fields

pat_agen — Thu, 09 Jun 2011 12:47:42 GMT

hi Bruno,

i've kicked this around a bit but always come up with the results you posted. That is the administartor sees everything whereas usmanager sees nothing.

Is it the star "*" for linking tables? I don't know because the adminstrator has this. What is certain is that your security table in section application has two fields in common with your fact (project) table. Qv creates a synthetic key to handle this, not in itself a bad thing but shows that the link between your security and the project is a combination of employee_country and project_country. QV accepts this when both parts are "star" but obviously is unhappy when one half is star and the other isn't.

One piece of information I found whilst looking at this is that since qv8.5 r3 if more than one reduction field is used in section access, if any user has more than one record and those choices are mutually exclusive the user will not get into the document.

in theory you have avoided this by only having the one reduction field in section access but maybe the fact qv introduced this restriction is a pointer to why your solution isn't working.

In our environment we have similar security considerations - why I'm interested in how this will pan out, there are some pretty qv savvy people out there who may be reading this and supply the correct answer.

We have dealt with this in two ways. Firstly is to have two documents each with different section access. so you would have a project by country doc and a project by employee doc. The reasoning being a) security is simplified and b) the document defines the analysis being made. For example what you call a country manager is in one instance being a geographical manager (overseeing all projects in the US) but in the other instance is being a people manager (overseeing all projects performed by US employees). The second way is to explicitly generate all the possible combinations of your fields.

In your case it could be solved by having a single field in section access (called say COUNTRY). then link country to a table containing the really existing combinations from your project table. The code to do that isn't terrifying and the performance implications would probably be negligeable.

something like this (added after loading of your project data):

securityLinkTable:

load

project_country as COUNTRY,

project_country & '|' & employee_country as securityKeyProjectTable

resident projects;

load

employee_country as COUNTRY,

project_country & '|' & employee_country as securityKeyProjectTable

resident projects;

ps of course you will need to add the securityKeyProjectTable to your project table.

keep me posted if you come up with another solution.

Re: Section Access - logical OR on different fields

brunodec — Thu, 09 Jun 2011 16:02:36 GMT

Thanks for this reply. It holds very usefull information, and both options are worth consideration.

The option to explicitely generate all the possible combinations also crossed my mind, but in fact I wanted to do this on 4 fields, and the example with 2 fields was only to show the basic problem. Nevertheless I will see if I can work it out in one way or the other you suggested.

Thanks again,

Bruno.

Section Access - logical OR on different fields

swuehl — Fri, 10 Jun 2011 18:33:35 GMT

Hi Bruno,

that's an interesting problem, but I also think your first approach will not work.

What makes me wonder, I see no data at all within your sample application, even as ADMINISTRATOR.

Thats what I also would expect, since I don't think the NULL or star nomenclature will work as you want.

If you want to play around a bit further, it might be useful to disable the data reduction in document property, thus you can see the effect for the users by simply selecting the SEC_CODE (without the need to log in / out).

If you then create table box with all fields, it looks like your approach doesn't filter the project table as wanted, instead added four lines to the table (i.e. appended the security table to the project table leaving the unset fields empty. If you then filter the SEC_CODE, you will always got no data for the hours).

So if you want to work further on your approach, I think you have to handle the synthetic key, which obviously works not as you want.

Regards,

Stefan

Re: Section Access - logical OR on different fields

brunodec — Wed, 15 Jun 2011 09:47:05 GMT

Hi Stefan,

your post confused me, because I had tested it before and as an administrator I was quite sure I saw all the data, but when I now retried I saw no data at all, so you were right about that. Also, your comment gives some insight in what the synthetic key does.

After some playing around, I found out that when you use the space as wildcard, the administrator sees nothing. When you use the * preceeded by the expression 'star is *', as I did in my original post, then it turns out that the administrator sees all the data (see result in the image). Don't ask me why...

Of course, back to the original problem, as USMANAGER, you still don't see any hours.

Thanks,

regards,

Bruno.

Section Access - logical OR on different fields

prieper — Wed, 15 Jun 2011 19:51:50 GMT

After having reloaded a document, you can see all the data.

You need to close not only the document, but the entire QV-session in order to see the data as per your profile.

Peter

Section Access - logical OR on different fields

prieper — Wed, 15 Jun 2011 20:11:14 GMT

After having thought a moment on this problem, think that you cannot combine the fields, but must divide them into individual pieces.

The below should work:

Section Access;

Load * inline

[ACCESS, USERID,PASSWORD, SEC_CODE

ADMIN, ADMINISTRATOR, , SEC_ALL

USER, USMANAGER, , SEC_US];

Section Application;

star is *;

load * inline

[SEC_CODE , COUNTRY

SEC_ALL, *

SEC_US, US];

Data:

LOAD *, EMPLOYEE_COUNTRY & '-' & PROJECT_COUNTRY AS SEC_ID INLINE [Employee, EMPLOYEE_COUNTRY, ProjectID, PROJECT_COUNTRY, HOURS

John, US, PRJ_01, US, 5

John, US, PRJ_02, NL, 6

Kees, NL, PRJ_01, US, 2

Kees, NL, PRJ_02, NL, 7];

SecurityCountry:

LOAD

SEC_ID,

SUBFIELD(SEC_ID, '-') AS COUNTRY

RESIDENT

Data;

HTH
Peter

Section Access - logical OR on different fields

brunodec — Thu, 16 Jun 2011 12:35:23 GMT

Hi Peter,

your solution is pretty much the same idea as pat.agen's second option. And it just works ! including a new function 'SUBFIELD' which makes it easy to generate records for each part from a given combination.

Many thanks to all of you for taking the time to share your thougths on this subject. You've been very helpful.

Kind regards,

Bruno.