Editing Record and Group Selection Formulas

[PANZON]

I am interested in dynamically changing the SQL statements as the report is refreshed each time. Many of our reports have 10+ parameters for the user to respond to. Sometimes they want to ignore a certain parameter like Order # and have the report return all Order #'s. To accomplish this, I have done the following:

If isnull(?order#) then true
Else table.field = ?order#

This works quite well but it doesn't update the where clause when you go to Show SQL Query. In some cases I have noticed the report runs very fast and in others it takes a long time. I assume it is pulling all records and then executing the record selection formula when it takes a long time. Is there any way to avoid this.

Lastly, I am not familiar with the Edit Group Selection Formula. What is involved there and what impacts does it have. [sig][/sig]

 

[kenhamady]

The record selection formula determines which records get into the result set.
Parts of it that can be converted into SQL will make it into your "where clause" and get processed on the server. Other parts (particularly parts that use Crystal functions, "or" or "If-then-else" logic)have to be processed by the client. Sometimes there are different ways to accomplish the same rule, and you will want to experiment to see which generates the most efficient SQL.

if you can use a SQL expression, instead of formula fields, then you will speed things up even more, because these are always processed on the server.

The Group Selection formula is only active if you create a rule that is based on a Crystal Subtotal. This rule would select which groups qualified to print on the report. This however must return all records from the database, since Crystal does the grouping and subtotaling on the client [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Taining by Ken Hamady</a><br>[/sig]

 

[PANZON]

Is there a way to write a SQL expression based on the user parameters? Specifically to be able to allow the user to leave the prompt blank if they want to basically ignore that restriction. Almost all of the where clause is generated off user input rather than pre-determined exclusions or limits. [sig][/sig]

 

[kenhamady]

SQL expression fields in Crystal can't include parameter fields, which makes what I described not very effective in your situation. This is because most of your rules use If-Then-Logic which won't be passed to SQL. But Crystal can pass some rules to SQL even if they use a parameter. As long as that rule doesn't use a Crystal function, &quot;OR&quot; or &quot;If-Then&quot;, or a formula field that uses one of these.

To really control the SQL you would have to create your own parameter window in the application, assemble the SQL statement in manually based on user input, and then pass the full SQL to Crystal when the report is run.

You also might want to see if the slow queries are slow because of the indexing of the database. If the SQL statement looks OK and the query is slow, you may have a database issue. [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Taining by Ken Hamady</a><br>[/sig]

 

[olympian]

... This works quite well but it doesn't update the where clause when you go to Show SQL Query. In some cases I have noticed the report runs very fast and in others it takes a long time. I assume it is pulling all records and then executing the record selection formula when it takes a long time. Is there any way to avoid this...

You don't say what database is at the back-end (?) but if it's SQL Server then 10+ parameters is crying out for stored procedures! I agree with front-end app. having it's own window for entering parameters and passing these. [sig][/sig]

 

[PANZON]

Thanks for your input. As an FYI to olympian, we use Oracle 8i but are considering a move to SQL Server 7. In addition, we are using Seagate Info 7 rather than Crystal Developers kit which makes using a front end app impossible unless we went the Seagate Info Developers Kit which seems like it would be more hassle than it is worth just to pass the query directly. [sig][/sig]