Performance w/ Parameter-Based Grouping

[bobhagan]

I have a report where I need to select for and display running totals on a group level for Worker, Supervisor, Boro, and City. I thought that changing the group with a parameter was the ideal solution (otherwise I need to maintain multiple versions of the report). However the run goes from 2/3 minutes for a field-based group to 20/30 minutes for the selectable version.

Peck says "You may pay a slight performance penalty .... Since the sorting or grouping will be done with a formula ... the ORDER BY clause won't use this formula, requiring Crystal to actually sort the data once it arrives ...."

Is there any other approach that avoids the "slight" performance problem?

Thanks
Bob Hagan

 

[bobhagan]

Oops. I meant to post this in the main Crystal forum.
Ignore this.

Bob Hagan

 

[MJRBIM]

You can try creating groups and sub-totals on each possible level -

EG : Group #1 - City
Group #2 - Boro
Group #3 - Supervisor
Group #4 - Worker

Then supress the groups depending on the selected parameter (ie. PARAMETER = "City" then supress the display of Header and Footer for Group #2, #3 & #4.

Let me know if that improves performance...

 

[rhinok]

In general, I wouldn't be happy with a report that takes 2/3 minutes unless I've exhausted all other options. Can you please post your record selection criteria and your SQL (Database|Show SQL Query). There may be issues here as well.

Also:

How many records are returned from the database?
How powerful is the machine used to run the report?
Is this a distributable report through Enterprise or Info , or is this 20/30 minutes on a developer machine?

 

[bobhagan]

I'm actually taking in quite a lot of records because in a main report I'm looking at a status table to determine what clients were open at the beginning of the month, opened during or closed during and still open at the end of the last month. Then I'm doing a sub-report to see if required contacts were made.

I tried creating the other groups and suppressing all but one. It appears the problem now is the query. I'd created a parameter to choose the report type: All Boros, 1 Boro, Unit, Worker. In the selection criteria, I put:

IF {?report_type} = "All Boro" THEN
general query
ELSE IF {?report type} = "1 Boro" THEN
Client.site_cd = {?Boro} AND
general query
ELSE IF ...

I'm running on a development machine against the production database. None of the Selection Criteria is in Show Query. If I comment out all but the first IF THEN the report runs in a little more than a minute, the query is the WHERE clause in Show SQL, and all records selected are used. With the additional ELSE IFs uncommented, for the same All Boros query, over 20,000 records are pulled up in about 30 minutes to use about 1,000.

So is there any way to select the version of a query and still have it run on the server?

Bob Hagan

 

[rhinok]

Based on your sample you might be able to use a Switch or IIF statement. You might also be able to use a combination of parenthesized Boolean If-Then-ElseIf statements. Can you post your whole selection criteria? We'll take a look at it for you.

 

[bobhagan]

I looked at more answers here and finally ended up eliminating the report_type param and setting all other parameters to default to "*" and changing the selection to this:

{Client.site_cd} LIKE {?Borough} AND
{Client.mgr_key} LIKE {?Unit} AND
{Client.wkr_key} LIKE {?Worker} AND
{status.status} IN

 AND
{status.status_dt} >= {?Start_LastMonth} AND
{status.status_dt} <= {?End_LastMonth}

That runs in under 30 seconds.

However I'd like see your other ideas if you're still willing.  The original query was:

IF {?report_type} = &quot;All Boro&quot; THEN 
  general query as above
ELSE IF {?report type} = &quot;1 Boro&quot; THEN
  Client.site_cd = {?Boro} AND
  general query
ELSE IF {report_type} = &quot;Unit/supervisor&quot; THEN 
  {Client.mgr_key} LIKE {?Unit} AND
ELSE IF {report_type} = &quot;Worker&quot; THEN
  {Client.wkr_key} LIKE {?Worker} AND
  general query

Thanks much
Bob Hagan

 

[rhinok]

OK, I have a few questions regarding your Selection Criteria:[ol][li]Your current Record Selection Criteria is more flexible than the original because it will allow more dimensions of data. Do you really want a report that only shows you one dimension at a time?[/li][li]Do you need partial input searches ({?Worker} LIKE 'A*') or are you just trying to allow for 'All' Workers? I only recommend using LIKE if you absolutely have to allow partial searches. It isn't efficient and it doesn't allow for Null values in the field being queried.[/li][/ol]Here's a modified Record Selection Criteria that is based on two assumptions:[ol][li]More flexible reporting is better. The Report Type doesn't need to be explicitly stated.[/li][li]You have populated picklists of Boroughs, Units and Workers and don't allow partial wildcard searches[/li][/ol]

//'General Query' - this is consistent regardless of the Report Type selected
{status.status} IN

AND {status.status_dt} >= {?Start_LastMonth}
AND {status.status_dt} <= {?End_LastMonth}
AND

[COLOR=green]//Report Type Selection
//Be sure to set the Default Value for {?Borough}, {?Unit} and {?Worker} to 'All'
//The following Boolean If-Then-Else If statements return [b]ALL[/b] values by default unless a specific value is
//selected.  If you want to allow multiple selections for each parameter, simply change the = to IN[/color]
(
  IF {?Borough} <> 'All'
  THEN {Client.site_cd} = {?Borough}
  ELSE IF {?Borough} = 'All'
  THEN TRUE
)

AND

(
  IF {?Unit} <> 'All'
  THEN {Client.mgr_key} = {?Unit}
  ELSE IF {?Unit} = 'All'
  THEN TRUE
)

AND

(
  IF {?Worker} <> 'All'
  THEN {Client.mwkr_key} = {?Worker}
  ELSE IF {?Worker} = 'All'
  THEN TRUE
)

 

[bobhagan]

<<Your current Record Selection Criteria is more flexible than the original because it will allow more dimensions of data. Do you really want a report that only shows you one dimension at a time?>>

Yes. I'm displaying only one summary footer at a time. I would like to be able to drill down, but I had to use running totals for some of the functions because they were dependent on the results of other functions.

<<Do you need partial input searches ({?Worker} LIKE 'A*') or are you just trying to allow for 'All' Workers? I only recommend using LIKE if you absolutely have to allow partial searches. It isn't efficient and it doesn't allow for Null values in the field being queried.>>

I don't want partial matches. I want to show the results for only one worker, supervisor, etc. at a time. I used LIKE because it worked. I'd seen the &quot;If blah then true&quot; setup but didn't understand it. I saw your example of switch in another thread today as well.

I had to stop working on this for a few days, so now I've got some new ideas to try out.

Thanks
Bob Hagan