SAP Knowledge Base Article - Public

2241699 - Optional Prompt = false is not considered in Crystal Reports 2011/2013 when using BEX query with optional prompt

Symptom

  • In Crystal Reports 2011/2013 reporting off from BEX query and the BEX query input variable such as 0SALESORG (VKORG) is designed as optional and variable represented as Selection option
  • In Crystal Reports 2011/2013 when executing the report these variables can be set to null, or can be left empty independently in prompt property Optional Prompt = false
  • Crystal Reports needs to be forced to do not display any data when these variables are empty or set to null value

Environment

  • SAP Crystal Reports 2011/2013

Reproducing the Issue

  1. Launch Crystal Reports Designer
  2. Create a new report based on SAP BW BEX query having optional prompt
  3. Execute the report and prompt window displayed
  4. In the prompt window you may notice, that the variable value can be set to null

Cause

  • Crystal Reports 2011/2013 works as designed in the BW, since on the BW side the variable is optional or variable represents as Selection option.
  • The BI Client tools (Crystal Reports 2011/2013, Crystal Reports for Enterprise, Web Intelligence, and SAP BusinessObjects Dashboards) are creating range variables for a selection option variable from a BEx Query and when a SAP BEX variable is optional, Crystal Reports creates a parameter which is optional and the user can set it to null).
  • There is no make sense if you set the property Optional Prompt = false in the prompt edit window of Crystal Reports 2011/2013 Designer.

Resolution

  • The BEX query has to be modified and variable from optional has to be changed to mandatory.

Workaround

  • If not possible to modify the prompt on the BW BEX Query, the following steps can be applied Crystal Reports Designer to force not displaying any data when input variable is empty or set to null value
  1. Create a formula with the followings:
    if isnull({?[PARAMETER NAME]}) then true else false;
  2. Go to Section expert -> Select details section and in the line "Suppress no drill down" press the "x+2" button and bring your formula there.
  3. Repeat step 2 for each section
  4. Save your report and test

See Also

Keywords

CR, CRD, Crystal reports, Mandatory prompt, optional prompt, null value, null , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013