13 Replies Latest reply: Jun 16, 2011 8:35 AM by Bruno Decruynaere RSS

    Section Access - logical OR on different fields

    Bruno Decruynaere

      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
          Peter Rieper

          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
              Bruno Decruynaere

              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
                  Patrick Laredo

                  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
                      Bruno Decruynaere

                      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
                      Deepak Kurup

                      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
                          Bruno Decruynaere

                          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
                              Patrick Laredo

                              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
                                  Bruno Decruynaere

                                  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
                                      Stefan Wühl

                                      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).

                                       

                                      Section.PNG

                                      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
                                          Bruno Decruynaere

                                          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...

                                           

                                          ResultAsAdministratorWithStar.bmp

                                          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
                                              Peter Rieper

                                              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
                                                  Peter Rieper

                                                  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
                                                      Bruno Decruynaere

                                                      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.