cancel
Showing results for 
Search instead for 
Did you mean: 

Would like to use @variable('BOUSER')

Former Member
0 Kudos

Hi All,

I am working with

--> Business Objects XI R2 (View Crystal Report using InfoView)

--> Business Views XI R2 ( access the data using Oracle 10g ) building LOV location table with the

proper access to the location(s) they have to.

--> Crystal Reports XI R2 (Database connection is the Business View)

I need to pass the BO User ID of the person running the report to the Oracle db.

Any suggestions would be greatly appreciated.

====================================================

I am working with 2 tables

--> LOCATION_SECURITY.SECURITY_ALL_LOCATIONS

--> LOCATION_SECURITY.SECURITY_FOLDER_LOCATION

If they exist in the LOCATION_SECURITY.SECURITY_ALL_LOCATIONS table then give them access to all locations also I want to display ...ALL value at the top of the LOV.

If they exist in the LOCATION_SECURITY.SECURITY_FOLDER_LOCATION table then give them access to all only the locations they have access to I DO NOT want to display the ...ALL value at the top of the LOV.

Proposed Oracler View


Select @variable('BOUSER'), 99999 as Folder, '...ALL' as Location, 'All Locatiions' as Description
From dual 
where (select Q.User_ID from LOCATION_SECURITY.SECURITY_ALL_LOCATIONS 
          where Q.User_ID = @variable('BOUSER')) Is not null
UNION
Select C.User_ID, 99999 as Folder, B.Location, B.LocationName
From medgate_v60_app.MT_TBLLOCATION  B left join
     LOCATION_SECURITY.SECURITY_ALL_LOCATIONS C on C.User_ID Is not null and 
     C.User_ID = @variable('BOUSER')
UNION
Select D.User_ID, D.Folder_ID, D.Location, E.LocationName
from LOCATION_SECURITY.SECURITY_FOLDER_LOCATION D Left Join 
       medgate_v60_app.MT_TBLLOCATION E on D.Location = E.location
WHERE  
  D.Folder_ID in (SELECT O.REAL_FOLDER_ID FROM LOCATION_SECURITY.FOLDER_MAPPING O)  
  and @variable('BOUSER') 

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

What I am asking is how do I use BOUSER

Former Member
0 Kudos

Hi,

Do you need to use an SSO authentication?

Do you need to verify that you BOUSER matches a user stored in a table?

Can you detail the workflow you want to do with BOUSER?

Didier

Former Member
0 Kudos

Do you need to use an SSO authentication?

NO I am using an Oracle UserID for everyone with no prompt for password

Do you need to verify that you BOUSER matches a user stored in a table?

YES the user either belongs in either the SECURITY_ALL_LOCATIONS table or the SECURITY_FOLDER_LOCATION table

Can you detail the workflow you want to do with BOUSER?

I know that in Business Views I can use CurrentUserName in a filter to select the person I want by using I would like to pass to the Oracle SQL View the user running the BO USER running the report.:

Here is what the original Oracle View looks like.

Select A.User_ID, 99999 as Folder, '...ALL' as Location, 'All Locatiions' as Description From LOCATION_SECURITY.SECURITY_ALL_LOCATIONS A

UNION

Select C.User_ID, 99999 as Folder, B.Location, B.LocationName From medgate_v60_app.MT_TBLLOCATION B left join LOCATION_SECURITY.SECURITY_ALL_LOCATIONS C on C.User_ID Is not null

UNION

Select D.User_ID, D.Folder_ID, D.Location, E.LocationName From LOCATION_SECURITY.SECURITY_FOLDER_LOCATION D Left Join medgate_v60_app.MT_TBLLOCATION E on D.Location = E.location Where D.Folder_ID in ( SELECT O.REAL_FOLDER_ID FROM LOCATION_SECURITY.FOLDER_MAPPING O )

Edited by: Rick Phillips on Jan 29, 2009 3:56 PM

amrsalem1983
Active Contributor
0 Kudos

you use @variable('BOUSER') in your universe to restrict the rows or data level for some users and to manage it between users,

i dont see that ur oracle view has any problem

all you need is to create a derived table in the universe builder and put your query

or even you can use the @variable('BOUSER') at the joins between ur tables, it will work fine.

BOUSER will work from the universe builder, im not sure if its gonna work form business view or not.

but i used it many times before in the universe builder creating some universes to restrict data on row level.

good luck

Amr

Answers (0)