Complex Authorization using generic keys and NULL values

[Tole1808]

Hi there,

I have issues with my Section Access. Based on @hic 's article concerning complex authorizations with generic keys I have added an autorization based on two variables (department and category 1). It worked perfectly. Since there are no NULL values for both variables I had no problems.

Then I added a third variable and now I experience issues. The third variable has some NULL values in the dataset. As an admin with access "<ANY>|<ANY>|<ANY>", I do not see all values of the dataset, but only the ones where the third variable has no NULL values.

Can someone help me fix it?

BR

Timm

[marcus_sommer]

I assume the only solution is to check the dataset and to replace the NULL's with real values like 'NULL' or '#NV' or ' ' or any other more appropriate value. Depending on the use of this field within the application you may duplicate it for the section access to keep the original field untouched.

- Marcus

[Tole1808]

Thanks for the quick response. If duplicate it for the section access, I still need a link between the original variable and duplicated variable. The link would not work for the NULL values right?!

[marcus_sommer]

NULL isn't a value else it means that there does nothing exists - nothing was stored. And therefore NULL couldn't be linked or selected in any way and needs to be replaced with real values if they should be directly accessed.

- Marcus

[hic · Accepted solution]

NULL is not selectable. This means that if the reducing fields have NULLs, these records will be excluded no matter the value of the reducing field.

You need to convert all NULLs in all linking fields (keys and reducing field) to something else, e.g. blank or the string 'NULL'. Use one of the following:

Coalesce(<Field>,  'NULL' ) as <Field>,

If(IsNull(<Field>),  'NULL', <Field> ) as <Field>,

[Tole1808]

Thanks for the help; now it works!!

However, obviously I see the string "NULL" in all my tables/filter panels etc. for the reducing field in the front-end.  Is there a way to clean this in the script or do I have to exclude this value in the front end?

[marcus_sommer]

I wouldn't try it in this way else like above already suggested using a duplicated field for the section access. The visible and accessible fields within the UI mustn't be mandatory those which are applied within the section access or those which are used in various calculations/selections, for example if the selections are made per island tables and/or the user selects string-values but the matching behind them happens on numeric values and/or there are additionally dimension values to calculate own partials sums and/or ...

- Marcus

[hic]

If you want NULL to link between two tables (key or reducing field) then you need to replace NULLs with a value. This value will be visible and selectable.

If you choose to have NULL, the tables won't link these records. There are no other options: Either a value, or a NULL.

What you could do, is to assign a value which is less "visible", like a blank space:
Coalesce(<Field>, ' ' ) as <Field>

[Tole1808]

Understood. Many Thanks!