BO user to a command line

[Former Member]

I am writing a report in CR2011 on BO 4.0 platform.  SQL server DB

I have some row level security built into a table.  So I need to pass the BO user to the command line. So I need in my SQL to say something like this

where BO user = Security User.

[Former Member]

Hi Brian,

Just as an option, you could make good use of the security table in a universe structure. This way, the 'Manage Access Restrictions' feature of the Designer will be very handy and easy to use.

The @variable('BOUSER') can be applied at object level in the SQL whereas the 'Manage Access restrictions' can be applied to the users and groups.

THis way, you would not have to code this on a report-to-report basis and no need to have a subreport for every crystal report that you create.

-Prathamesh

[Former Member]

Prathamesh,

I agree that solution would work.  But all my universes are .unx and would cause me to have to re-write all my Crystal reports in CRE.  I just did not want to have to re-write all my reports.  Since all my reports are Crystal off command.

Also I wanted to use @BOUSER to my users would not be prompted for user name. 

[abhilash_kumar]

Hi Brian,

In the Record Selection formula you can write something like this:

{database_field} = CurrentCEUsername

-Abhilash

[Former Member]

I was thinking that way.  But was curious if there was any way of pushing it into the command's SQL itself instead of report level filtering

[abhilash_kumar]

Hi Brian,

Could you try this:

1) Create a parameter in the Command Object called 'User'

2) Use the prompt in the where clause of the command object so it looks like this:

where {database_field} = {?User}

3) Insert this report as a Subreport

4) In the Main Report create a formula with this code:

CurrentCEUsername

5) Right-click the Subreport > Change Subreport Links > Move the formula to the Pane on the right > From the drop-down at the left-bottom that says 'Subreport parameter field to use' choose the

{?User} prompt

This way when you run the report, the user name of the user person logged in should be passed to the Subreport's prompt which in-turn filters the records at the Command Level.

P.S: If this doesn't work, you could try creating a Business View with the filter at the Data Foundation. The report will then need to created against the Business View.

-Abhilash

[DellSC]

Actually, if the user ID is a string (which I suspect it is), you'll probably have to put quotes around in in the where clause - something like this:

where {database_field} = '{?User}'

-Dell

[abhilash_kumar]

Thanks for pointing that out Dell - yes the double quotes have to be there.

-Abhilash