data reduction leeds to unintended missing data.

[#1]

Hi,

I'm working on a report that shows mobile phone numbers and links them to the departments in our organisation.

I've gotten everything in place and all is working.

The problem arrises when I start using data reduction with Section Access. Im using data reduction based on the organisational units (OE) (a number)

Users can only see the phone numbers that belong to their own OE. I got everything set up, even the users that have an exception to this rule (some users are required to see all phone numbers).

Now the problem:

Because restrictions are given on OE level, phone numbers that are not linked to persons can't be viewed. I see them when reloading, but after saving and reopening the data reduction is effective and I lose those numbers.

How can I make these numbers without a person linked to them visible to some users?

My tables:

1. Phonenumbers (Link to persons is right(phonenumber, 8))

2. Persons (Link to OE_LINKTABLE is PERS_NR)

3. OE_LINKTABLE (Link to OE is OE_NR)

4. OE

Data reduction is taking place on table 3 (OE_LINKTABLE) on OE_NR_2 (I made a second key for this link, but the value is the same as OE_NR)

If you need more info please ask. I'll be going home now, but will be answering and viewing this topic tomorrow.

P.S. I can't upload the QV report as there is sensitive information in it.

[erichshiino]

Hi,

I think you should use OE_NR_2 with a samll difference.

This field will have a default value when OE_NR is null. You can achieve this during load with the following line

Load OE_NR, if( isnull (OE), 'N/A', OE) AS OE_NR_2

FROM.....

Then, all the users must be associated with this second (default) value (every users will have a second line in the section access).

Your section access would look like this:

Section Access;

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, OE_NR_2

    ADMIN, A, A, A

    ADMIN, B, B, B

    ADMIN, A, A, N/A

    ADMIN, B, B, N/A

];

Section Application;

Hope this helps,

Erich

[#3]

That was a push in the right direction. This however only solves the problem partial, as now only the persons that have a mobile number, but no OE show up with OE N/A.

There are still phone numbers without person linked to them. And those still come up withouot an OE.

I've already tried the isnull on the PERS_NR in table 2, but without luck and also tried an if(len(PERS_NR) = 0, , )

[#4 · Accepted solution]

OK, I've managed to get this working. I don't think it will win a beauty contest, but maybe you guys can explain why this is working and the before mentioned isn't.

What I did:

1. Left join table 1 and 2 and name this as a temp table.

2. Resident load the temp table and in the resident load performed the if(isnull(PERS_NR), '0000', PERS_NR)

3. Create an inline table with the same name as table 3 with the value of PERS_NR = '0000', OE = '0000'

4. Create an inline table with the same name as table 4 with the value of OE = '0000'

If someone could explain another (better/neater) way to achieve what I did here I'd be very gratefull.