Null value in the Section application reduction field

[#1]

section access fields :

ACCESS	USERID	CITY
ADMIN	MAIN	*
ADMIN	MAIN
USER	USER_1	X
USER	USER_2	Y

section application fields :

CODE	CITY	sales
A	X	100
B	Y	200
C		300

using the above fields in a qlikvies report , i want user 'MAIN' to be able to view the data belong to CODE 'C' for which the CITY is null.

Note - CITY is the data reduction field

Can anyone help me please..

Regards,

Sankar

[phcaptjim]

I have a similar problem.

My fact table is limited on a field called USERID.  Data reduction works well with my current setup, however I have added some summary records to my fact table that I identify using a new field called RecordType.  The detail rows are RecordType = 'D' and the summary records are 'S'.  The summary records have a NULL value in the USERID column so I can summarize the facts by manager and organization so each employee can see the overall averages and totals for their manager and organization.

Section Access:

ACCESS, NTNAME, USERID

Facts:

USERID, MANAGERKEY, DIRECTORKEY, DEPT, DIVISION, NUMERATOR, DENOMINATOR, RECORDTYPE

The problem I have is the Summary records are being eliminated by the data reduction.  Is there a way to keep these records in the data set with a blank or NULL USERID column?

I have found it very difficult to find any information on keeping summary data when using section access.

Any help would be greatly appreciated!

[prabhuappu]

Hi Sankar,

As per your section access table admin user can able to see all the data.

normally

1) if you put * in Reduction field then the admin user can able to view all the values listed in the section access table (example1.qvw)

2) if you put "null" then the admin user can able to view all the values in that particular field (including the values not listed in the section access table). Only in the case where the reduction field doesn't have any null value (example2.qvw).

In your case City has null value. So the values X, Y and Null will be visible for the ADMIN user (example3.qvw)

If you want the admin users to see only CODE C data. Then remove the ADMIN user entry with * in the section access table (example4.qvw)

=====

Hi Phcaptjim

Do you want the summary records can be viewed by all the users?

If yes create a link table wuth two fields USERID and RECORDTYPE

USERID RECORDTYPE
A            S
B            S
C            S

Now All users can see summary records

I have attached a file for reference Section Access_Consolidated.qvw.

regards
PRABHU APPU

[phcaptjim]

Thanks for the response Prabhu.

I love your example but is the synthetic key going to be an issue with this current data model?  Below is the data model for my application, it's a little more involved than the example I posted but the concept is the same.  EPMFacts stores my details and summary records with the RecordType column used to distinguish between the two.  SECURITYID is the field that links to the Section Access table for each user from the Security table.  I could add two records for each user in the security table (one for RecordType 'S' and one for 'D' but then a synthetic key would be created between the two tables.

Do you know how I should handle this?

(I have no idea why the image shows the calendar table twice.....that is odd)

[prabhuappu]

Hi Phcaptjim,

This could be solved by using "generic keys". I have done it for a simple scenario. Modify this as per your requirement.

Create a new table with two fields GEN_SECURITYID, SECURITYID

and instead of SECURITYID in the fact table use GEN_SECURITYID. Change the GEN_SECURITYID for the summary records as ANY.

The section access table will look like this

* INLINE [
    ACCESS, USERID, SECURITYID
    USER, A, 1
    USER, B, 2
    USER, C, 3
    ADMIN, X, *
];

Now any user will be able to see the summary record. (Refer the attachment for more)

Regards,

PRABHU APPU