Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
row wise initialization for session variables not working in obiee 11.1.1.7.0
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
Answers
-
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?
0 -
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.
0 -
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
0 -
And how do you use it as a filter in analysis?
0 -
DM_BRNCH.REGION_CODE = VALUEOF(V_ZONE_CODE)
0 -
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?
0 -
Yes . Even i have tried using IN operator in the filter query of the criteria tab still the same .
0 -
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?
0 -
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.
0 -
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.
0
