Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to OMIT complete Dimension table to few Users?

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.

3 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.