Using Command Object to modify Crystal query

[sirlansa]

I am running CR 10 Enterprise with a SS 2005 database.
I have a simple report [running from a single table] that runs and shows all groups. I want to modify it to show ONLY groups that have at least 2 detail records. In MS Access I would add a HAVING COUNT(*) > 1 clause in the underlying SELECT query.

I tried using a Command object (with the SELECT/HAVING SQL command embedded in it) to modify the results. Although it looked like it should work (and the Database Expert treated the Command as a table and automatically linked up the result field of the Command to the other tables in the database, using INNER JOIN), when I exited Database Expert, I received a message: "More than one datasource or Stored Proc has been used in this report...".

In addition, the result of "Show SQL Query" shows the original table query and the SQL from the command object, but they are NOT linked in any way, ie, they don't reflect the linkage I saw in Database Expert!

When I run the report, it runs for a very long time, then gives the error "Not Supported".

 

[lbass]

The link is occurring locally, rather than on the server. You would be better off using a command as your entire datasource, so that it all passes.

Instead of using the command, you could just use group selection. Go to report->selection formula->GROUP and enter:

count({table.field},{table.group}) > 1

-LB