Sub report accesses database for each grouping 2

[Gmaxz]

Hi I have a main report which runs of a stored procedure this is grouped and I have two other sub reports embedded in the main report, these also run of different stored procedures. The two sub reports group on the group field of the main report by using linking. When I run the report and check SQL profile I can see the SQL database being accessed for each group. ie. executing both stored procedures for the sub reports for each group. Is there a way to prevent this since the stored procedure brings back all the information in one hit. I expected the report to group and sort this on the client/server machine. Using SQL server 2000 and CR ver. 8.0
Any help is much appreciated.
Gus

 

[lyanch]

That is the nature of a subreport. Each time the subreport shows up on a report, the SQL/SP etc behind it is run. The link limits the data. You may look at combining the data from the two stored procs instead of using subreports. Crystal won't let you link SP's, but depending on your database, you may be able to use a wrapper SP that calls both and combines them.

Lisa

 

[Gmaxz]

Hi Thanks, for your response. The Main report is grouped first,Then I have the sub reports grouped based on the main report group. The problem is if the main page has say 30 groups Then the database is going to be hit 30 times for each sub report based on the change in group. If all the data is bought back during the first 'Group Hit' cannot the the sub report do all the grouping internally. Would it make any difference if I sent the data by a disconnected recordset? The stored procedures are pretty complicated and would be hard to combine..
THanx
Gus

 

[Naith]

Hey Gus,

Things would be pretty sweet if, as you suggest, each subreport brought all the data back "during the first 'Group Hit'", but alas, this is not the nature of the beast.

When you link a subreport to a group, what happens is that the grouping field is passed to the subreport, which drives it's own stored procedure based on this field. The stored procedure will not extract any information which is not related to the value of this group field at the time. Each time the group field value changes, the new value is passed to the subreport procedure, and so on and so forth, until you run out of groups. So, if you have 30 groups, and you've placed the subreport in a group section, then the subreport runs 30 times.

It seems that you would be best served by Lisa's initial suggestion: if you only need the sub-stored procedure to run once and you're using stored procedures as datasources, why have you bothered using a subreport at all? Why don't you just use a separate procedure to call the main stored procedure, and then call the procedures used by the subreports. You wouldn't need to add any additional complexity to the existing stored procedures at all.

This way, your database handles much less report traffic, the report runs quicker, and is easier to maintain.

Naith

 

[HowardHammerman]

Or, put the sub-report in the report header and hold the needed info in shared variables. Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269