on 01-28-2009 5:29 PM
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')
What I am asking is how do I use BOUSER
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.