creating and linking SQL commands to tables

[fancyface]

has anyone come across a good tutorial or documentation on how to do this properly? I find the manual a bit pathetic in this regard. SQL commands is such a new concept when you're used to editing the SQL yourself or using subselects in subreports. I'm really struggling trying to find a good resource that walks you through how to do this properly. I would appreciate any direction.

 

[Madawc]

Which Crystal? And are you talking about Stored Procedures or something else.

In Crystal 8.5, you could not mix tables with Stored Procedures. It was possible to mix them using subreports, but better to collect all of the data in SQL.

In Crystal 10, the Stored Procedure can be treated as another table, buch better.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[fancyface]

No I am not talking about stored procedures. I'm talking about SQL commands which were introduced in Crystal 9 and up. I need some help/good documentation on how to create commands and link them up to tables in a report.

 

[elsenorjose]

Which version of Crystal are you using? I'm on XI so this is specific to XI.

Creating a command is relatively simple. If you're starting a blank report, go through the steps to create a new connection or use an existing connection via the Database Expert. Once you've selected a data source, you should see the option for 'Add command'. Double click on that and then enter your SQL command. If you need parameters, click the 'Create' button on the right hand side. Once you're done, click OK. The command now appears and can be selected as your data source. As long as the command has a field that can be linked to another table, once you've created the command, in the database expert, click the 'Links' tab and link your command to your other table(s) accordingly.

 

[fancyface]

Thanks elsenorjose. I've done exactly that however it is EXTREMELY slow so I figured I must have been doing something wrong or using the table and command together in the wrong way. How do you find performance?

 

[elsenorjose]

I've never actually done this in production, I haven't found the need to yet.

 

[lbass]

If you link a command to a table or another command, the linking occurs locally--at the client level, and so reports can be slow. Speed will be significantly better if you use one command for your entire report.

-LB

 

[fancyface]

thank you very much lbass!