ReportSmith/Crystal 8.5 - SQL Help needed

[gtarrant]

I guess my question should be directed to Chris Cook, since he knows both very well, but if someone else also has this answer, please let me know.

In ReportSmith, I have an option to control my selection criteria with sql (since adjusting the sql outside the selection criteria doesn't let you undo after modification-"Edit SQL&quot. And I'm working on Oracle 8i that uses effective dates and effective sequences.

Example: (used on the selection criteria line)

any_tbl.effdt is equal to formula(select max(b.effdt)
from any_tbl
where b.nbr = any_tbl.nbr)

All this was done in the selection criteria and I could do this field-by-field in ReportSmith and control/modify my sql statements without adjusting anything in the main sql text window.

In the near future, I will have to convert ReportSmith to Crystal (over 200 rpts).

Here's my question:
In Crystal 8.5, how can the above mentioned example be acheived? I see alot of simularities between ReportSmith & Crystal and have tried to use Crystal's "Select Expert" to duplicate this SQL statement but was unsuccessful(they also have formulas too, but it doesnt seem to accept sql statements).
There has to be another way to control sql outside of Crystal's "Show SQL Query" window...right?

Sorry for the Crystal question in a ReportSmith forum, but I didn't want this buried in Crystal forums because I dont know if Chris Cook reads all of those (there's alot!).

 

[CharlesCook]

"There has to be another way to control sql outside of Crystal's "Show SQL Query" window...right?"

You would think so but if there is I am yet to find it. "Show SQL Query" is where I add my effdt code.


CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[prhld]

Charles/gtarrant,

I have to re-write the reports off of ReportSmith into Crystal. I have almost 400 reports, is there an easy way get this through ?

Have you get a chance to find a way to do this with ease in past 2 years ?

Your help will be a lot to me, if there is a way out.

Thanks,
Prahlad.

 

[ReportSmithing]

prhld,

Alas, there's no easy way in doing this since it's very frustrating that Crystal comes out with a new version of their application almost every year - I believe Business Objects owns them now - and they tend to take away anything thats user friendly from their apps so they can charge you support and consulting fees.

However, if you are using crystal 8.5 or 9.0, you can just cut and past the sql into the window menu and with a little messaging, you can get it to work. If you became good at ReportSmith macros and your reports have these, you will need to use VB along with Crystal to reproduce thoses in case you had front ends and such.

I don't envy the task ahead of you. I remember Crystal's formula language which is absolutely pathetic and is hard on the eyes! There seems to be no easy rollout when considering Crystal.

 

[prhld]

Thanks for the information.
I'm using Crystal 9 currently. Can you please help me understand little more on the cut and paste the SQL statement into the window menu? How can I do that?

Thanks,
Prahlad.