Optimizing performance

[synapsevampire]

This is an offshoot of an ongoing theme wherein I'm trying to get reasonable performance from Crystal products.

The scenario involves reports based on multiple values being passed via a parameter, or as one of the choices, *All*, which should eliminate this field from the selection criteria, or set it to return all rows.

Since this does not work within Crystal Reports/Crystal Enterprise without the use of external code to build the SQL, has anyone figured out if it's possible to customize ePortfolio (using CSP and/or other tools) to build the SQL and pass in a recordset to a report?

Does this approach sound logical?

Our intent is to do as little customization to Crystal Enterprise as possible, allowing our clients to add in reports later and use the canned features.

Thanks for any thoughts you might have on this.

 

[jdemmi]

I am not following you....what do you mean by "The scenario involves reports based on multiple values being passed via a parameter, or as one of the choices, *All*, which should eliminate this field from the selection criteria, or set it to return all rows". I have many reports with multiple parameters which allow for "All" values (rows). I may be able to help.

 

[synapsevampire]

Then it sounds as though you can help, thanks for the response.

By multiple values, I mean that a user may select many from a picklist (which brings up how to automate updating the picklist too...), with one of the choices being all, which of course means all records will be returned.

My initial problem is how to pass through SQL to the server efficiently, which I may have the solution for, but it seems to be a trial and errorororor proposition.

Can you give me an example of your selection criteria, providing it passes all of the SQL to the server?

Mine involved using an if then else, with the then portion being properly passed, but not the else. Logically I can live with this for now, but I'd really like to know how and when Crystal constructs and passes down SQL.

-k

 

[jdemmi]

As far as updating the picklist automatically....I am afraid I can't help. This is a serious limitation of Crystal, and it drive me batty!

Now, back to the "ALL" question. I think I am doing it the same way you are. I have one report in which I allow for the user to select a FROM and TO. (The values for these fields in my DB are ALPHA -NUMERIC)

The FROM Parameter uses the following formula...
If {?To Document Type} = '^' then
' '
Else {?To Document Type}

The TO Parameter uses the following formula...
IF {?To Document Type}='^' THEN
'ZZZZZ'
ELSE
{?To Document Type}

 

[synapsevampire]

Thanks for the response.

What I'm trying to do is assure that SQL is passed through to the server, the *All*, etc, I handle just fine.

It seems 3 parts voodoo, and one part stewpud luck...

<slamming my head into the monitor while trying to look composed>

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rhinok]

The only way you can get 'All' to pass through the server is to use a wildcard that represents 'All'. In order to accomplish this, you need to:[ol][li]Set up your parameter to allow for multiple entries. Remember, if you don't allow users to modify their parameter entries then you must include a wildcard (*) as an available parameter in your pick list. Typically, it would be the first parameter[/li][li]Use the 'like' statement in your Record Selection Criteria (ex: {table.customer_id} like {?Customer_Param})[/li][/ol]The downside to this solution is that records where the criteria field ({table.customer_id}) is Null will not be returned. This, too, has a workaround.

If you're reporting from a SQL-based database (Oracle, SQL Server, etc...) you can create a SQL Expression that evaluates the Criteria field ({table.customer_id}) for a Null and returns a default value. Following is an example of a SQL Expression that was written using the CR Oracle8 V3.6 ODBC Driver:

//Customer_ID
{fn IFNULL(table.&quot;customer_id&quot;,&quot;NULL&quot}

Once you've created a SQL Expression--which is always processed server-side--you can modify your Record Selection Criteria as follows:

{%Customer_ID} like {?Customer_Param}

In the above example, the User could select one, many or all Customers even if the Customer_Id field was Null (it never would be in a perfect world, but we don't live there). Furthermore, this Record Selection Criteria should be passed to the server in the Where clause of the CR-generated SQL Statement. The end result is that this report should now process more quickly because it has been optimized for performance and efficiency.

 

[synapsevampire]

I never really had an *All* issue. I used the text &quot;All&quot; as the first choice (I could have used anything that isn't in the column), so if they choose nothing, it will default to &quot;All&quot;, and my logic will appropriately pass doodly squat to the server. That wasn't the poblem to begin with, the problem was getting it to pass the SQL when the user made other choices, because you should pass nothing if you want &quot;All&quot; (although you may lose performance on some indexes, but that's a somewhat different issue). My original attempts had all failed, so I'd written off Crystal SQL pass through as unreliable, hence this first post.

This was purely for performance reasons, and I've finally nailed down when Crystal passes SQL.

Some tricks are to match data types (i.e., use datetimes when comparing against database datetimes, etc.), I had poor luck when using SQL Expressions to convert the data types, so I used the same types in Crystal. Also, construct your record selection Ifs to include else if conditions.

Example:

(if {?Classification}[1] <> 'All' then
({SOMEDATA.CLASSIFICATIONNAME} in {?Classification})
else
if {?Classification}[1] = 'All' then
true)

and

(if {?Contact Type}[1] <> 'All' then
({SOMEDATA.CONTACTDESC} in {?Contact Type})
else
if {?Contact Type}[1] = 'All' then
true)

Once I do this, I can reliably get Crystal to pass the SQL for all 9 parameters. Note that the SQL that's constructed only worked reliably if it was based on the first If Then, not on the Else If then.

Adding to the complication was that one of the date parameters was a selection of Today, Yesterday, Last Week, Last Month, etc.

In order to get Crystal to reliably pass this through I had to construct these in separate formula fields (one for start date, one for end date), and NOT use any variables (i.e., numbervar, stringvar) in the formula.

If this theory holds, I'll write up a FAQ on how to optimize for pass down.

It'll appropriately include a good martini recipe.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Jayday97]

synapsevampire,

I just wanted to thank you for the previous post. We have attempted millions of times a way to get a statement like the one you provided to appear in the SQL statement. Thank you thank you thank you thank you thank you!

I am interested in that FAQ if you ever create it.

Jayday97

 

[synapsevampire]

JayDay: No worries, just passing it on, as you undoubtedly discovered, Crystal is very finicky about constructing pas thru SQL, I even managed to get this method to fail in one example, but I managed to create a work around then, too.

I believe that Ken Hamady is going to publish something along these lines soon, if he doesn't, or I have something to add, I'll do so.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com