3 Replies Latest reply: May 11, 2015 12:03 AM by jagan mohan rao appala RSS

    How to OMIT complete Dimension table to few Users?

    Shivendoo Kumar

      Hi Guys,

       

      I have application where we have more that 5 dimensions and one Fact.

       

      For few of the users we don't want to show any fields from Dimension1 which is having 5 fields (SalesName, DOB, SalesID, Project, JoiningDate). I don't want to write case statement in Section access for each 5 fields.

       

      If I write OMIT then I have to write for each 5 fields in section access. There is any simple way to hide complete fields from a Dimension only for few users.

       

      Note: Few of the fields we have used in List box from Dimension1 and I have got expression (=Count( {<$Field={aFieldName}>} $Field)=1) to hide listbox if field is OMITed.

       

      Make sure to revisit this setting: Document Properties-->Security--> Uncheck Show All Sheets and Objects

       

      If Show All Sheets and Objects is checked then show and Hide will not work.

       

      Want to Hide List box when Field used in it is OMITed for few users.

        • Re: How to OMIT complete Dimension table to few Users?
          jagan mohan rao appala

          Hi,

           

          Try OMIT option in Section Access

           

          FROM Qlikview Help file:

           

          QlikView and QlikView Server support a feature by which some of the data in a document can be hidden from the user based on the section access login.

          First of all, fields (columns) can be hidden by the use of the system field OMIT.

          Secondly, records (rows) can be hidden by linking the Section Access data with the real data: The selection of values to be shown/excluded is controlled by means of having one or more fields with common names in section access and section application. After user login QlikView will attempt to copy the selections in fields in section access to any fields in section application with exactly the same field names (the field names must be written in UPPER CASE). After the selections have been made, QlikView will permanently hide all data excluded by these selections from the user.

          In order for this procedure to take place, the option Initial Data Reduction Based on Section Access on the Document Properties: Opening page must be selected. If this feature is used in documents that are to be distributed by other means than via QlikView Server, the option Prohibit Binary Load on the same page of the Document Properties must be selected in order to maintain data protection.

          Note!
          All field names used in the transfer described above and all field values in these fields must be upper case, since all field names and field values are by default converted to upper case in section access.

          Example:

          section access;

          load * inline [

          ACCESS, USERID,REDUCTION, OMIT

          ADMIN, ADMIN,*,

          USER, A,1

          USER, B, 2,NUM

          USER, C, 3, ALPHA

          ];

          section application;

          T1:

          load *,

          NUM AS REDUCTION;

          load

          Chr( RecNo()+ord(‘A’)-1) AS ALPHA,

          RechNo() AS NUM

          AUTOGENERATE 3;

          The field REDUCTION (upper case) now exists in both section access and section application (all field values are also upper case). The two fields would normally be totally different and separated, but if the Initial Data Reduction Based on Section Access option has been selected, they will link and reduce the number of records displayed to the user.

          The field OMIT in section access defines the fields that should be hidden from the user.

          The result will be as follows:

          User A can see all fields, but only those records connected to REDUCTION=1.

          User B can see all fields except NUM, and only those records connected to REDUCTION=2.

          User C can see all fields except ALPHA, and only those records connected to REDUCTION=3.

           

          Regards,

          jagan.

            • Re: How to OMIT complete Dimension table to few Users?
              Shivendoo Kumar

              Jagan,

               

              Hope you have read my question completely. I am fetching Section Access data directly from AD tables like below.

               

              SELECT DISTINCT

                'USER' AS ACCESS,

                'Domain\'+''+ UPPER(USERNAME) AS NTNAME,

                EMPLOYEEID,

                'Field1' AS OMIT

              FROM [DBO].[ADUSERS_TANLE]

              WHERE GROUP='Group-1'

               

               

              So my question was how can I OMIT more than one columns. I don't want to use UNION set for each field like below sample query.

               

              SELECT DISTINCT

                'USER' AS ACCESS,

                'Domain\'+''+ UPPER(USERNAME) AS NTNAME,

                EMPLOYEEID,

                'Field1' AS OMIT

              FROM [DBO].[ADUSERS_TANLE]

              WHERE GROUP='Group-1'

              UNION

              SELECT DISTINCT

                'USER' AS ACCESS,

                'Domain\'+''+ UPPER(USERNAME) AS NTNAME,

                EMPLOYEEID,

              'Field2' AS OMIT

              FROM [DBO].[ADUSERS_TANLE]

              WHERE GROUP='Group-1'

              UNION

              SELECT DISTINCT

                'USER' AS ACCESS,

                'Domain\'+''+ UPPER(USERNAME) AS NTNAME,

                EMPLOYEEID,

              'None' AS OMIT

              FROM [DBO].[ADUSERS_TANLE]

              WHERE GROUP NOT IN ('Group-1')

               

              and So on....

               

              I hope It is clear now....

               

              Any user from Group-1 should not see fields 1,2,3,4,5 but users from groups other than Group-1 should see everything. Fields 1,2,3,4,5 are from one dimension table

                • Re: How to OMIT complete Dimension table to few Users?
                  jagan mohan rao appala

                  Hi,

                   

                  Try like this, maintain two tables

                   

                  Security:

                  USER,     NTNAME, EMPLOYEEID

                  1,abc, abc

                  2, xyz, xyz

                   

                  FieldAccess:

                  EMPLOYEEID, OMIT

                  abc, Field1

                  abc, Field2

                  abc, Field3

                  xyz, Field1


                  Security:

                  SELECT DISTINCT

                    'USER' AS ACCESS,

                    'Domain\'+''+ UPPER(USERNAME) AS NTNAME,

                    EMPLOYEEID,

                    'Field1' AS OMIT

                  FROM [DBO].[ADUSERS_TANLE]

                  WHERE GROUP='Group-1';

                   

                  LEFT JOIN(Security)

                  LOAD

                  *

                  FROM FieldAccess;

                   

                  Hope this helps you.

                   

                  Regards,

                  Jagan.