Passing CurrentCEUserName value into SQL variable for security reasons

[Former Member]

Hi All,

I'm new to Crystal but I have to fairly complex reports that require me to know the user's ID from within SQL for security reasons.

My reports are to be run from within InfoView. Both reports run as subreports of a single report which allows the user to enter full/partial values into any of 9 different prompts. This then generates a dynamic SQL statement which returns records meeting the users criteria.

The two subreports (on which security is required) are on-demand subreports which run off of the resulting row of the top-level report by passing a parameter, called material_id, from the top-level report to the subreport.

If possible, I need to also pass the CurrentCEUserName value along with the material_id parameter passed into the subreport.

I cannot use row by row security to display or not display a row (or rows) as the table relating the user id to what rows to display are held within the database as another table.

I talked with a corporate BO guy and he told me to build a universe to handle this but it's late in the process and I don't know if I can get more IT support to build a universe (or buisness view)

If possible, could I get very specific instructions on how to get the value for CurrentCEUserName and pass it into a variable within SQL?

All help is appreciated,

Thanks

Phil

[Former Member]

Thanks for your response Raghavendra.

Because I need security for both report levels (main report and subreports), I'm thinking that I have to prompt the user for their User Id then verify it and then pass the parameter to the subreport.

I will still use your response as I have other reports to run requiring security only at the subreport level.

Regards,

Phil

[Former Member]

Hi Phil,

I think if you want to pass the CEUserName to sql then you must have subreport and create some command level parameters like {?User Name} and create a formula in the main report like

@UserName:

CurrentCEUserName

Now right click on subreport and go to subreport links and add this formula to link with sub report parameter ({?UserName}) which will pass the username to your command and get the related output in subreport.

Hope this Helps!

Raghavendra

[Former Member]

Hi Raghavendra,

I know you can use the CurrentCEUserName can you also pass the group or folder the user is in from InfoView?

Thanks,

Rick

Edited by: Rick Phillips on Oct 17, 2008 4:26 PM

[Former Member]

Hi Rick,

We cannot pass the group name or folder in which the user belogs to. I think you can use Business views row level security and create the report using business views.

Regards,

Raghavendra

[Former Member]

How exactly would you pass the CurrentCEUserName to a SQL storedprocedure without prompting?

[Former Member]

Insert your report which was created using stored procedure and insert this report as a subreport in another new report and in the main report create a formula like this

CurrentCEUserName

Now right click on the subreport and go to change subreport links and add this formua from main report and link it with subreport stored procedure parameter.

Hope this helps!

Raghavendra