2366608 - How to use @variable('BOUSER') in Crystal Reports

SAP Knowledge Base Article - Public

2366608 - How to use @variable('BOUSER') in Crystal Reports

Symptom

  • When using @BOUSER variable in Crystal Reports reporting off from datasource, various error messages displayed.
  • When reporting off Oracle database, "DBS: ORA-00936:missing expression" error is displayed
  • When reporting off MS SQL database, "Must declare scalar variable "@Variable". SQL State: 42000 Native Error: 137" error displayed

Environment

  • SAP Crystal Reports  2011
  • SAP Crystal Reports  2013
  • SAP Crystal Reports  2016

Cause

  • Behaviour is by design. The @Variable('BOUSER') is evaulated when reporting off from universes. 

Resolution

Crystal Reports has own field for holding the Enterprise username called: CurrentCEuserName. This is filled out with the current enterprise user name when the designer is connected to the repository. When the designer is off-line, this field has an empty value.

Solution 1. 

  • Records can be filtered on Crystal side using parameters, when small amount of data is returned in the report. This solution may has an impact on the performance.
  1. Create a new report off from oracle database
  2. Add your sql command as a data source
  3. Logon to BI Platform, since when you work online the  CurrentCEuserName will be evaulated, when you not connected to the platform, the  CurrentCEuserName will be empty
  4. Create a new formula (name used: f-username) and enter the formula text:  CurrentCEuserName

    bouser_1.png

  5. Save the formula
  6. Go to select expert and set up a local data filter based on the formula created at step 4 (f-username)

 

Solution 2.

  • Using subreports to report off datasource will solve the problem to passing the current username to a stored procedure or an sql command. In the main report (which the formula CurrentCEuserName will be evaulated, and will be passed to the subreports parameters)

 

  1. Create a new report without datasource
  2. Logon to BI Platform, since when you work online the  CurrentCEuserName will be evaulated, when you not connected to the platform, the  CurrentCEuserName will be empty
  3. Create a new formula (name used: username) and enter the formula text:  CurrentCEuserName

    bouser_1.png

  4. Save the formula
  5. Add a subreport which repors off from the requested datasource
  6. Add your sql command as a data source with parameter which requires current username value (eg BOUSER)

    bouser_2.png

  7. Now link the main report username formula with the parameter of the subreport

    bouser_3.png

 

 

Keywords

crystal, cr, bouser, @bouser, variable, ORA-00936, Missing expression, Must declare scalar variable, 42000, , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016