how to implement bind variables in sap crystal report oracle as a database

[Former Member]

Hi,

i have one report in that main data is coming from sql command there are three dynamic parameter are declared in command the report is running little bit slow i need to improve the performance of report for that i need to implement bind variables then how should i do this for that i need to write stored procedure or something else for workaround what should i do to improve the performance of the report

[JWiseman · Accepted solution]

hi Ganesh,

anything that is in a command object is being processed by the database server. so using a stored proc isn't going to get you any performance gains if the stored proc is running the same query.

1) is there a record selection formula being used? i.e. go to Report > Selection Formula > Record. this will affect performance negatively as this filter (a record selection on a command based report) will not be processed by the database but in crystal reports. to fix this, add anything that is a record selection / filter to the command's WHERE clause.

2) is the command object joined to another command or table or stored procedure in the Database > Expert. this too will affect performance negatively. all records from the command and then the table or other command or stored proc will be brought back to the crystal reports designer and then processed.

3) is the command object bringing back a lot of records to begin with? i.e. are there millions of records coming back? if so use an aggregate / group by command if your report is not showing details but just groups and summaries.

4) are there a lot of subqueries in the command object? if there are, consider using a SELECT WITH directive instead. more info on the syntax for this here. the example though is on missing data, but it shows how to use a SELECT WITH.

i hope this helps,

jamie