So I guess this is the reason why it doesn't work ?
Any other suggestions ...?
The star symbol is not allowed in information files. Also, it cannot be used in
key fields, i.e. fields used to join tables.
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?
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??
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
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 ];
SectionTest.qvw 139.0 K
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):
project_country as COUNTRY,
project_country & '|' & employee_country as securityKeyProjectTable
employee_country as COUNTRY,
project_country & '|' & employee_country as securityKeyProjectTable
ps of course you will need to add the securityKeyProjectTable to your project table.
keep me posted if you come up with another solution.
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.
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.
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.
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:
Load * inline
[ACCESS, USERID,PASSWORD, SEC_CODE
ADMIN, ADMINISTRATOR, , SEC_ALL
USER, USMANAGER, , SEC_US];
star is *;
load * inline
[SEC_CODE , COUNTRY
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];
SUBFIELD(SEC_ID, '-') AS COUNTRY
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.