Parameter in Section Access & Loadscript

[#1]

Hi to everybody,

in the last few days I'm having this problem and I really don't know how to figure it out.

I have my qvw file with Section Access where users are defined with Windows Single Sign On Account.

In the Section Application I defined a table with two columns (UNI_BU and LANG) who execute data reduction.

The first one is related to other columns in the model data (so actually it goes in join with these columns) and it works fine, executing the right data reduction according to the user who has access to the system.

The second column is a parameter that I want to use in the loadscript in a where clause.

I tried two different approaches to the problem:

1. I defined a variable (let's call it VAR) who catch the value of LANG according to the user who had access.
   But if I want to use $(VAR) in the Loadscript I don't get any result in the query. I checked the log and I saw that the variable is replaced with corresponding text ("=LANG").
   I also tried to use antoher variable so defined: SET VAR1 = $(VAR). VAR1 keeps the right value, but still I can't use it within the where clause.
2. I tried to define a variable using fieldvalue('LANG',0) function applied to the reduction table. My hypothesis was that, since I access with my own Windows SSO account, this function would have returned to me just one row with the LANG value of my account.
   But I noticed that the value returned is always the first one in the table independently to the access executed.

I attach a qvw file with the example of the structure over explained.

Can please anyone help me?

Thanks in advance,

Mario

[#2]

Up!!

[Miguel_Angel_Baeyens]

Hello,

I don't know if I got your issue right. Section access works on reload, and reduces data when the user logs in. Taking a look at your script, it seems you want to load different data when user logs in instead, which is not possible. When the user logs in, the reduction is done on he records already existing in the document, and the user actually cannot reload the document (unless specified otherwise in the Document Security settings).

That said, and according to your script, the line

LET Linguaggio2 = FieldValue('LANG', 1);

Will always return the first value of LANG. Since table Cono_dati has not been reduced in load time, and never will be, it will always return "2", which may not be correct.

What you can do is add the LANG field to your datamodel, so you don't need to reload the document and when reduction takes place just after the user credentials are passed on to QlikView, it will only show those correspoding to the section access.

Besides, you will need to check the "Initial Data Reduction Based on Section Access" box in the Settings menu, Document Properties so the reduction happens.

You will not need to use that WHERE when the user has already reducing data when logging.

Take the following example based on your script:

SECTION ACCESS;
LOAD * INLINE [
    ACCESS, USERID, PASSWORD, LANGUAGE
    USER,  A, A, 20
    ADMIN, B, B, 2
    ADMIN, ADMIN, ADMIN
];
SECTION APPLICATION;
TAB_UNIT:
LOAD * INLINE [
Field1, Field2, LANGUAGE
1, AAA, 1
2, BBB, 2
3, CCC, 20
4, DDD, 20
5, EEE, 2
];

When user B logs in, it will only see records 2 and 5, since the rest of the data is reduced. Likewise, when user A logs in, only records 3 and 4 are available. When ADMIN logs in (from the Desktop and local file, not from the Server) all data is available (he has and empty value for LANGUAGE in section access).

This is how reduction happens in QlikView using section access.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Hello Miguel Angel,

first of all thanks for your reply.

In the past message I forgot to say that the qvw file is scheduled to be reloaded every xx hours.

So actually, I don't try to reduce data with user login but I correctly reduce data (but only for column UNI_BU) with the scheduled reload.

I agree with your suggestion to introduce the column LANG in the model data, but this is not an easy task for the way my model is done.

What I'm trying to understand is if I can pass LANG value (the right one) and use it in the WHERE clause of the loadscript without modifying the model data.

Like I said in my first message:

I tried two different approaches to the problem:

1. I defined a variable (let's call it VAR) who catch the value of LANG according to the user who had access.
   But if I want to use $(VAR) in the Loadscript I don't get any result in the query. I checked the log and I saw that the variable is replaced with corresponding text ("=LANG").
   I also tried to use antoher variable so defined: SET VAR1 = $(VAR). VAR1 keeps the right value, but still I can't use it within the where clause.

According to your experience is there a chance to pass the value of variable VAR in the WHERE clause of the loadscript?

If not, do you have any other suggestion to solve this problem?

Thanks for you patience,

Mario

[Miguel_Angel_Baeyens]

Hi,

I'm assuming you are using Publisher, is that right? Anyway, the LET variable above should be enough provided there is one and only one possible value for UNI_BU or LANG for each user, and use then in the WHERE.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

Hi,

no actually I'm not using Publisher.

The reload of the report is done with a Windows Scheduled Task where I execute a Partial Reload of the report itself.

I defined a variable called VAR that contains the right value of LANG depending of the user accessing.

But when I use it in the WHERE clause of the loadscript, $(VAR) is not evaluated and I see in the log file the string containing the corresponding expression instead of the variable value.

I get:

WHERE Column = =LANG (Expression of VAR)

instead of:

WHERE Column = 20 (Value of VAR)

Thanks again,

Mario

[Miguel_Angel_Baeyens]

Hello Mario,

Are you using the LET statement for the variable instead of the SET you were using as I mentioned above?

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#8]

Hello Miguel,

I tried both:

1. when I use SET statement the behaviour is the one I described above.
2. when I use LET, in the query log I have no evaluation of the variable, so the WHERE clause looks like this:
   WHERE Column =
   And no value appears at the place of $(VAR).

This is really strange!

Thanks once more,

Mario

[Miguel_Angel_Baeyens]

Hi Mario,

FieldValue starts counting on 1. On your example above, you are using 0 instead. Are you using 1 so FieldValue with LET returns the first value for the field specified?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#10]

Hi Miguel Angel,

I tried your suggestion but if I use:

LET Linguaggio = FieldValue('LANG','1')

I got the wrong value of LANG: 2 instead of 20.

Like I said in my first message, it seems like data reduction is not seen from this function and it always returns the first value in the table independently from the user who has access.

Have you ever experienced this kind of problem?

I think that the closest street to the solution is the one who use VAR and $(VAR), but there's something wrong...obviously!!

Have you please any other suggestion for me?

Thanks once again,

Mario