SQL Expressions and Parameter fields..

[kutoose]

When you look at the SQL Expression fields you cannot see
parameter fields. I need to do some calculations in
SQL expression field using the parameter passed
to the report.

How can this be acheived ?

kutoose@yahoo.com

 

[fredp1]

With SQL Expressions you cannot in v8.5
I think this may be true in 9 and 10... but Crystal has a 'commands' option to write the sql that can have prompts (V9 and above).
Database, database expert, ... Add command
I've never tried it but it looks simple.



Cheers
Fred

 

[gj0519]

Fred you are correct on the Add command. When you are in the Database expert just double click on add command. Now you can add in your SQL, and also set your parameters up here and add them into your SQL if you want. I use this all the time in V9.

Hope this helps

Glenn

 

[kutoose]

The reason I cannot add SQL command is because I am using server side processing. Most of the SQL expressions are DECODE and case statements -
Cannot add SQL command because if I use it the report will not be processsed in server.

kutoose@yahoo.com

 

[fredp1]

Since I've only used 8.5, i not speaking with any confidence, but the 'add command' will be pushed to the server(according to the help).
Where I can see you having a performance hit, is when you are taking advantage of the grouping and the SQL that gets generated with server side processing i.e. only summarised values are returned, not the details (assuming that all the criteria for server side processing is met).
Besides using stored proc's which may give you marginal improvement, I'm not aware of any other techniques in Crystal.
It comes down to you balancing the report requirements and performance.
For complex queries, I've remodeled the data in a datamart/datawarehouse which will give you the best of both worlds (if thats possible in your situation). That data is then processed/updated daily.

Cheers
Fred