Customize an LOV in BV - Urgent Help

building reports crystal reports
1

CR XI Oracle
What I want to do is that when the report runs, the dynamic cascading prompt would only show the list of organizations or personnel that the user has access to. For example if User “A” logs on, the LOV will only populate with the organizations or personnel that’s link to his user_id. The user_id is stored in an Oracle database table. The user will run the report via an user interface and that interface will pass the user_id but I don’t know how to get the user_id to filter the LOV with the list of organization or personnel that the user is only allowed to see. Has anyone ever done this?

My data foundation in BV for my LOV is:

select distinct pa.person_assignment_id, sp.user_id, o.org_structure_long, p.last_name || ’ ,’ || p.first_name user_name
from persons p,
person_assignments pa,
orgs o,
session_privileges sp,
users u
where pa.person_assignment_id = p.checked_in_reporting_id
and p.person_id = pa.person_id
and pa.org_organization_id = o.org_id
and pa.org_organization_id = sp.organization_id
and sp.user_id = u.user_id
and u.user_id = ‘1055624’

The ‘1055624’ is hard-coded to test the command but I would like to replace it with {?user_id} and pass a value this prompt parameter.

cookiejar (BOB member since 2008-09-17)

2

In your Data Foundation, set a filter on the user_id column and you can set it equal to the generic variable {CurrentCEUserName}.
If you set the filter up this is a standard option in the dropdownlist in the Data Foundation Filter.

chesl73 (BOB member since 2008-05-15)

3

Is the CurrentCEUserName the only variable that I can use? The user_id that we need to filter the data by is one that is created by a web application, not Crystal’s user information? Is there another way?

cookiejar (BOB member since 2008-09-17)

4

Ah, I see.
I’m afraid there’s no out-of-the-box solution to this.
You could pass the web application username via a http header or something similar and then have som custom JSP that stores this and uses the BOE XI SDK to call the viewer passing in this as a parameter. Not that easy though.

chesl73 (BOB member since 2008-05-15)