Using the same data within the Main report and subreports

[phellis]

I have a crystal report (v10) which contains a subreport. Both the Main report and Subreport contain the same data, just presented differently.

As it stands the report queries the database for the main report and then queries it for each time it needs to load the subreport.

Rather than doing this is there a way that the subreport can reuse the existing data from the main report so it doesn't have to requery the database each time?

 

[Madawc]

Do you mean it asks for a parameter? You can overcome this by passing a value using Edit > Subreport Links.

If that's not it, then it's in the nature of subreports to do an independent database access each time the subreport is called.

Sometimes a Crosstab or Mock-Crosstab can be used instead of a subreport.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[phellis]

The issue is that the subreport is using exactly the same data as the main report. However the subreport is held at a group footer level so it calls the subreport and hits the DB each time the group changes.

Your suggestion of a cross tab may work however to add to the problem the grouping level, through a passed in parameter, can change ie it could be grouped by customer or product or location which means that I'd have to find a way to limit what is included in the crosstab.

Hmmm...I shall go and experiment.

 

[phellis]

I don't think cross tabs are going to do it.

Here's an example of what the data looks like coming from an SP.

Portfolio, Client, Completed, Completed within Deadline, Within 10 Days, Within 20 Days
PortfolioA, ClientA, 20, 10, 5, 5
PortfolioA, ClientB, 10, 1, 6, 3
PortfolioB, ClientC, 20, 10, 5, 5
PortfolioB, ClientD, 10, 1, 6, 3

The report is laid out as follows

Grouped By Portfolio

Portfolio A

Client Completed Within Deadline Outside Deadline
------------------------------------------------------------
ClientA 20 10 10
ClientB 10 1 9

Group Footer - Show Break down
Client Within 10 Days Within 20 Days
-------------------------------------------
ClientA 5 5
ClientB 6 3

<Page Break>

Portfolio B

Client Completed Within Deadline Outside Deadline
------------------------------------------------------------
ClientC 20 10 10
ClientD 10 1 9

Group Footer - Show Break down
Client Within 10 Days Within 20 Days
-------------------------------------------
ClientC 5 5
ClientD 6 3


The "break down" is currently done in a subreport but the more portfolios its using the slower the report as it hits the database each time to return the same data.

The cross tab can be used to produce the client list in the grouping areas but I don't know how to set up the cross tab so that it displays the columns I want.

 

[Madawc]

If crosstabs won't do it and you have to show the data at group footer level, then I can't see any better solution than what you've got.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[phellis]

This is why I was hoping there may be a way around having to hit the DB each time for the subreports.

Is there a way of passing the information into the subreport using arrays. The idea being that I could make the hit to the DB in the subreport simpler so that it runs and links but the detail reported on is in an array.

 

[Madawc]

I've never done it, but I think it is possible. Try SEARCH because the answer is probably in an existing thread. Failing that, start a new thread asking about passing arrays.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

A crosstab would do the job if you have deadline date field and a completed date field so that you could set up a formula that resulted in # of days. You could then create a formula that divided them into the two categories and use that in a crosstab as the column field.

-LB