Section Access and Null Values

[Christos500]

hello,

i have a specific problem. I'm trying to create a section access table with 2 fields , so in case of nulls in field 2 for example i just put :

AuthTableTemp:

load 

distinct

field1

from qvd1 (qvd);

join(AuthTableTemp)

load 

distinct 

if(isnull(field2), '-', field2) as field2

from from qvd2 (qvd);

AuthTable:

load * ,

'ADMIN' as ACCESS,
'INTERNAL\SA_SCHEDULER' as USERID

resident AuthTableTemp;

drop table AuthTableTemp;

concatenate(AuthTable)

load 

upper("ACCESS") as ACCESS,

upper("USERID") as USERID,

field1,

field2

from excel.xlsx ;

Section Access;
Load *

Resident AuthTable;

Section Application;

Drop Table AuthTable;

but i can't have the '-' i just put in case of nulls.

any ideas ?

[marcus_sommer]

You need to replace NULL with a real value - within the section access as well as within the data-model whereby a isnull() check may not be sufficient because it's only working for certain types of NULL - the real ones. Within the most scenarios it's better to query it in this way:

if(len(trim(FIELD)), FIELD, '<NULL>') as FIELD 

[Christos500]

the thing is that i don't have null values regarding my fields but the nulls comes from the outer join that i use in the section access section because there isn't an association between those two fields. So in order for the section access to work is to somehow convert the null values to a text, but i used the formula you gave me and it doesn't work.

An one more question, do i have to get both fields that i 'll join from the same qvd or it doesn't matter;

[marcus_sommer]

Against NULL is no access possible - you will always need real values.

In general it's possible to use multiple fields for an access-control per section access but it's not always simple because all used fields and their field-values must be in a valid relation to each other - any exclusion on one point leads to an exclusion by all others, too because everything is linked per AND - means everything within the chain needs to be to return TRUE. Further important is that the authorization worked as a white-list - means all values which should available for an user must be listed - a not listed value is an exclusion.

So the most common way to handle such cases is to combine the fields into a single field - again within the section access and within the data-model. All NULL's within the combinations needs to replaced with a real value and so it may look like:

if(len(trim(FIELD1)), FIELD1, '<NULL>') & '|' &  if(len(trim(FIELD2)), FIELD2, '<NULL>') as CombinedFIELD 

[Christos500]

actually i found the solution. It was all about the * i ve put for the users that had to see all possible values of the fields. In case of * the end user sees all non-null values of the fields, so there was a problem with the data model.

Wherever there was null value, i had data reduction as  far as some other field's values are concerned. An example is that i had sales , visit , stores and customers. A customer may have a sale and a visit to a store, but there are many cases in which the specific customer doesn't have any sale but just visits . So if i had to put in section access the field customer and the field store , with * , i was missing the case that  customers did some visits but not a sale .

So instead of * i found that i had to use an empty cell in excel ( i use an excel for all possible values for the fields that every user should see , along with the userid and access that i  later concatenate with the outer join i apply for the fields that i want to manage security) which includes null values.

[Christos500]

sorry it worked but only for the admin  and not for the users. So i 'm searching further