Parsing an input parameter for the where clause or record select value

[Former Member]

In my limited CR experience, I've always used a command database connection so that I can write my own SQL. However, now I have to parse a pipe delimited parameter to get my value for the where clause, so I'm selecting several tables and joining them through the Database Expert Links tab. All works fine, but after doing that and then parsing the parameter with the below formula in the Select Expert, I notice that there is no where clause in the SQL query, and although the report eventually displays the proper values, it runs through thousands of records first. Here is my Select Expert - Record formula:

StringVar array Parm1;

Parm1 := Split({?DATA_AREA}, "|");

{SO_ORDERS.CASE_ID} = Parm1[2]

If I change "Parm1[2]" on the last line to a valid Case ID, then there is a where clause in the SQL and the report generates immediately.

It seems like the record select formula is applied AFTER all of the records (without a where clause) are searched when I use the parsed parameter value, but when I hard code a valid value, it places that into the where clause BEFORE the sql is executed. Is there a way to get the parameter parsed first and then use that parsed value in the SQL where clause?

Thanks.

Bill

[Former Member · Accepted solution]

Yes crystal will run the query first to get 100% data and then applies record selection condition. To increase the performance you need to pass the where condition at the command level instead of report level. So you need to create a report using add command like this

select * from tablename where field={?Parameter}

{?Parameter} is a command level parameter.

Now insert this report as a subreport in another report which has no connection but has a parameter

{?DATA_AREA} and create a formula like this in the main report

Split({?DATA_AREA}, "|")[2]

Now right click on the subreport and go to change subreport links and add this formula from main report and link this to sub report parameter {?Parameter} without linking any database field from the subreport.

Now your subreport runs with the where clause to get the data.

Regards,

Raghavendra

[Former Member · Accepted solution]

Perfect! I struggled with it for a while because it dawned on me that I needed a second parameter, but just duplicated what I had done for the first, created a new formula in the main report and associated it with the other parameter and it worked fine. Thanks!