Showing results for 
Search instead for 
Did you mean: 

Optional Prompt Not Working

Former Member
0 Kudos

Dear Experts ,

In my report I am using Following Command ,

Select  field name  from table name where

   Company_code='{?Company_Code}' and     branch_code='{?Branch_Code}' and    location_code='{?Location_Code}' and    job_no='{?Job_No}' and


and also in Select Expert -> Record -> if HASVALUE ({?Vehicle_No}) then {CR_21061.VEHICLE_NO} in {?Vehicle_No}

In Value Option Screen , Vehicle_no parameter I am selected Optional Prompt Value = TRUE .

In my job I have 2 vehicles ,if Vehicle Parameter is empty , I want to retrieve all vehicle for that particular job ,

Now I am getting Empty report if vehicle parameter is Empty , Please check my image file .

Accepted Solutions (1)

Accepted Solutions (1)

Active Contributor
0 Kudos

Hi Sri,

The Record Selection Formula should be:


Not(HasValue({?Vehicle_No})) OR

{CR_21061.VEHICLE_NO} IN {?Vehicle_No}



Active Contributor
0 Kudos

hi Sri,

let us know if you've got lots of records coming back from your sql command.

if you don't then no problem and please ignore this reply. if you do then you may wish to change your report a bit as using a Record Selection Filter against a command does not pass the filter to the database. this can cause some performance issues against big databases.

if you do have a lot of records consider doing this as a test...

1) create a new report off of a command object

2) create the parameters / prompts inside the command object

3) use syntax similar to below by adding the parameter to a where clause

SELECT `Customer`.`Customer Name`, `Customer`.`City`, `Customer`.`Contact Position`
FROM   `Customer` `Customer`
WHERE (`Customer`.`Customer Name` IN {?Name} OR 'All' IN {?Name})

4) note that a default of "All" is being used in an OR statement...this will be used instead of having optional parameters, which are not possible when you've created the prompt / parameter inside of the Command object

5) after writing the command, edit the parameter / prompt so that you've added "All" as the default value

the above will ensure that you've added the where clause to the report.



Answers (1)

Answers (1)

Former Member
0 Kudos

Sri, you just need to add the 'else' part like:

else true