Record Selection Question

[EVM]

Hi all,
In order to use dynamic selection criteria, I type the following coding in the Record Selection Formula Editor:

{DatabaseField1} = {? Parameter1}
and
(
if {?parameter2} <> &quot;&quot;
then {DatabaseField2} = {? parameter2}
else 0=0
)

Although it can work, I don’t know whether I use the best method.
Do I use a correct method?? Is there any other method??
Thanks!!!

 

[kenhamady]

Describe the way you want it to behave and we can tell you if this is the best way.

You aren't launching the report from a VB application are you? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[EVM]

I am not launching the report from a VB application but viewer for ActiveX on the Web.
For example, user can input the keyword in the browser. Then the report is launched and the keyword will be passed into the report as a parameter.
If the user doesn't input the keyword, all records will be displayed. Otherwise, the record selection will depend on the keyword.

so I use.....
if {?parameter2} <> &quot;&quot;
then {DatabaseField2} = {? parameter2}
else 0=0

Can you give me some advise? Thanks!!!

 

[kenhamady]

I like the following, which isn't much different, but clearer. What I don't understand is why you have 2 paramaters and how you want them to behave in relation to each other?

if {?parameter2} = &quot;&quot;
then True
else {DatabaseField2} = {? parameter2} Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[EVM]

Kenhamady, Thanks for your help!!
There is another relative question.

Report 1:
if {?parameter2} = &quot;&quot;
then True
else {DatabaseField2} = {? parameter2}

Report 2:
{DatabaseField2} = {? parameter2}

In the case {?parameter2} <> &quot;&quot;, I found that the time used for Report 1 is much longer that Report 2.
Report 1 seems to get all records from the database before executes the code in formula editor. Is that right??
Thanks!!

 

[kenhamady]

Yes, a selection formula that uses If-Then logic will be processed at the client because it isn't passed to the WHERE clause.

If you want to experiment, you can try different selection formulas and each time show the SQL statement and check the Where clause that CR is generating. You may find a formula that generates a more efficient SQL statement, but I doubt it.

You might also try making this a stored procedure with a parameter, which should push the processing to the server.

Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

Or, if you are using V8.5, you can rewrite your record selection formula so that conditional record selection formulas get processed at the server. This is not possible in previous versions though. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[EVM]

Do you mean the selection formula that uses If-Then logic will also be processed at the server for V8.5??
In my case, the report joins more than 5 tables and all record selection formula use If-Then logic. It takes more than 5 minutues to generate the report.
Is there any method to decrease the time used other than using store procedure??
(I am using V8.0)
Thanks for your help!

 

[kenhamady]

Use Database - Show SQL Query and see if your If-Then criteria make it into the WHERE clause. I doubt it. If you can tweak the WHERE clause to do what you want, it will probably improve performance. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

V8.5 has some limited capability to process conditional formulas on the server. One report I was working on took over 77 minutes in V8, and 1:18 minutes in V8.5 because of that. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[EVM]

Thanks!!
oh....77 minutes??!!!
User told me to speed up a report which took over 5 minutes.....

 

[jasminum]

Hi all,

I have a similar question here.

How do I change the SQL Statement to use a parameter in the WHERE clause?

I tried:

... WHERE TB_PERSON.ID_NO = {?idNo}

which gives the following error:

ODBC error: [Oracle][ODBC][Ora]ORA-24374: define not done before fetch or execute and fetch

This field is the main selection criteria which will filter out many irrelevant records. If this selection is done in Edit -> Select Expert, the final output might be the same, but processing time would be much longer.

 

[kenhamady]

You can't stick a parameter into the SQL in CR. You can add a parameter to a SQL statement written in the SQL designer, and run the report off of that. Or you can write a stored procedure with a parameter field, and report off of that. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

I keep seeing references to passing multiple values to the server when using iif, but I can't repeat it, nor have I seen an example, does anyone have one? (Malcolm???)

 

[kenhamady]

I am not sure if this will help, but my last newsletter had a note that shows an interesting behavior in relation to if-then logic and SQL statements.

http://www.kenhamady.com/news0204.html

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

Thanks, Ken, this didn't directly resolve the problem, but it did make me go attack it again because it appears to work sometimes...if you hold your head just right and mutter enough...

Anyway, I have reasonable performance now.

If I could just figure out how to use the CR parameter screen to pass multiple values to a stored procedure in a user friendly fashion, I'd be flying...

Right, I'm getting kinda greedy...

-k