nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

row wise initialization for session variables not working in obiee 11.1.1.7.0

B Prakash

I have defined a session variable which is supposed to have multiple values and hence i have enabled row wise initialization for that variable. But always the variable returns only the first value in the list.

Expected output is : 805:806:810:845

but when i use VALUEOF(NQSESSION.V_ZONE_CODE) i get only 805

I need to get all the values.

Regards,

Bhuvan R

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

As row wise variables works in 11.1.1.7 would be easier if you post more details as you maybe have an issue in your init block.

So how does the init block looks like? If you test the variable in the RPD you get all your values?

B Prakash

Yes i'm getting the result in offline mode as below:

V_ZONE_CODE 805

V_ZONE_CODE 806

V_ZONE_CODE 807

V_ZONE_CODE 808

Only when i use the valueof(NQSESSION.V_ZONE_CODE) in answers i'm not getting the value as desired. Even the valueof(NQSESSION.V_ZONE_CODE) returns valued if it has only one value.

Kindly help.

B Prakash

My init block contains the following query:

select 'V_ZONE_CODE' , c.region_code

from dwhbiusr a, ur_level b, dm_brch c

where a.user_level = b.user_level

and a.id = '38900'

and a.branch_code = c.branch_code

and a.branch_code not in (select terry_code from terry_mstr)

union

select 'V_ZONE_CODE' , r.region_code

from dwhbiusr a, usr_lvl b, dm_brch c,reg_mst r

where a.user_level = b.user_level

and a.id = '38900'

and a.branch_code = c.branch_code

and a.branch_code=r.territory_code

Gianni Ceresa

And how do you use it as a filter in analysis?

B Prakash

DM_BRNCH.REGION_CODE = VALUEOF(V_ZONE_CODE)

Gianni Ceresa

I would say the ' = ' isn't the right operator when dealing with a set of values...

Did you try to use a IN and add your filter in the filter part of the Criteria tab of your analysis using the UI?

B Prakash

Yes . Even i have tried using IN operator in the filter query of the criteria tab still the same .

mac2

I think the '=' is okay, although a bit counter-intuitive. I say this because I also am on 11.1.1.7.x and have row-wise session variables that ultimately feed into row-level security groups, and we use ' = ' when setting up the row-level security. In the physical SQL, it converts it to IN(val1,val2,valn).

You are 100% sure the query in your init block returns 4 values when run directly against your database? And you are certain the query in your init block is written exactly as you posted and is not user-specific in some way?

If you look at the physical SQL that is executed when you run your report where you claim you only see 1 row instead of the expected 4 rows, are there any clues? Perhaps it is useful to post that physical SQL here?

B Prakash

The query i have sent is user-specific and if a user belongs to 'Zone-A' it would return 4 codes as mentioned and if user belong to 'Zone-B' it would return 1 row. But atleast 1 row would be returned for any user.

Also i have tested the above init block in offline mode and it returns 4 rows as below.

V_ZONE_CODE 805

V_ZONE_CODE 806

V_ZONE_CODE 807

V_ZONE_CODE 808

But when the same variable is referred in Report using VALUEOF(V_ZONE_CODE) it shows only 805. But i need to get all the 4 values.

Kindly help.

Joel Acha

So for a user who belongs to Zone A, could you go to Manage Sessions and pull out the physical SQL that OBIEE generates and paste that here.