Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Slow report - using several subreports Post Reply Post New Topic
Author Message
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Topic: Slow report - using several subreports
     Posted: 27 Sep 2013 at 9:58am

I had to recreate a report that used the cross tab format to calculate several distinct counts. The user insists getting the information in EXCEL. Crosstab was no an option due to all of the formatting issues when exporting to EXCEL.

Two groups: Sales Terr and Clients.
Need distinct counts for each client and the total count for all the clients under a sales terr.
Need the above for each month in the last year as well as the grand total for the whole year.
                            Jan         Feb     Total (Jan + Feb)
Sales terr 1          3             2         5       
      Client A          1              0         1
      Client B          2              2         4
Sales terr 2          1               0        1
       Client C         1              0         1
     GRAND TOTAL 4              2          6
 
There is a subreport for each of the distinct counts that need to get calculated. I do have subreport links set up for each of them. The report works fine, but it takes forever to run.
 
The Main report has a selection criteria for the records for the whole year. Each of the subreports has a selection criteria for the Month that they need to return the counts for.
 
I am sure that there is an easier and more efficient way to do this. OR is there a simple way to improve performance on the existing report? 
 
Any suggestion is appreciated!!!
 
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 27 Sep 2013 at 10:32am
try instead of subreport formulas like
Jan
if date_field in date(2013,1,1) to date(2013,1,31) then distinctcount_field
create a summary for each month
distinctcount(jan)
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 03 Oct 2013 at 5:22am
and there is always my favorite...
make a stored procedure. It will be difficult because of the number of columns being unknown...

of course with that caveat, what the stored proc could do is take the data, return the distinct counts, and then let the report pivot the data into the crosstabs...
either way it would be simpler, as the stored proc has already 'cleaned' the data

it's thought, though the solution is not for everyone.
IP IP Logged
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Posted: 04 Oct 2013 at 4:32am
I provided the data for this month. I am going to have some time to redesign the report next week. I am going to try out both solutions. Thanks for the suggestions. I will let you know what I ended up doing.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 04 Oct 2013 at 5:09am
just a warning on Kostya's suggestion, I could be wrong but my experience with Crystal is this will not work.
Even though there is no "Else" for the if-then, it will use the default value for the else and that value is then included in as a value in distinctcount, increasing your results by one (anytime the grouping has any row that used the else). There is a trick of creating a NULL formula field you can call in for the else but it requires everything to us string types.
if you can format the overall 'grid results' to give the sales terr group totals at under the client totals you could use running totals with evaluate formulas. The evaluate formulas can use conditions against today's date if needed.
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum