To subreport or not to subreport, that is the question 1

[anationalacrobat]

I think I can do all this in subreports, the question is whether that's the best solution or a bad one.

The report we're trying to generate pulls data from many many different places. What we're looking to put all on one page:

1. Customer name
2. Customer addresses, business and personal (in the database, customer name and some personal data is on one table, addresses are all stored in a second table, a third table links customer name to addresses. One customer could have dozens of addresses)
3. To the right of all this will be payment columns that will sum the yearly transaction volume.

So, it'll look something like this:

name, ID number 2008 2007 2006
bus address home address $x $y $z

Then and that's it for one record, it loops to the next record.

Now I've been scratching my head and the way I see it, this is all about multiple queries and multiple result sets so that means pretty much every field here is going to be a sub-report, the only exception being the customer's name and ID number.

So, is there a smarter way or is this it?

 

[lbass]

The only thing that you might need a sub for is the addresses. Can you please show how you would want multiple customers to display?

-LB

 

[anationalacrobat]

Absolutely.

The dashes represent group breaks, then I would keep the groups together with the feature enabled for preserving groupings at page breaks.

name, ID number 2008 2007 2006
bus address home address $x $y $z
----------------------------------------------------------
name, ID number 2008 2007 2006
bus address home address $x $y $z
----------------------------------------------------------
name, ID number 2008 2007 2006
bus address home address $x $y $z
----------------------------------------------------------
name, ID number 2008 2007 2006
bus address home address $x $y $z

You say I wouldn't have to use subs except for the address. Would that be using a summation field for the payments then?

With example payments of...

2008 $5
2008 $10
2007 $3
2007 $4
2007 $5
2006 $2

The summation would be sum2008="add if year=2008" and we print that in the appropriate area, then sum2007 would pull by 2007, etc?

name, ID number 2008 2007 2006
bus address home address $15 $12 $2

Like so?

 

[lbass]

You could handle this by inserting a group on customer and then using two Group Footer sections. In GFa you would place the customer name and the subreport containing the addresses. You would format the GFa section to "Underlay following sections". Then you would insert a crosstab in GFb that uses only the date field as the column->group options->on change of year, and the amount as the summary field (no row). Then go to the customize style tab->format grid lines->uncheck "show grid lines".

You didn't actually show samples of the business and home address, but I'm guessing they are multiple lines. In the subreport you would have to limit the records to the desired addresses (not sure what fields you are working with in order to limit this). Insert a group on a field that relates to each label (address type?), and then format the group footer to "print at the bottom of the page." Suppress all but the detail sections where you have placed the address fields (detail a,b,c). In the section expert, format the detail section to "format with multiple columns"->layout tab->set the width and gap->across then down. Link the subreport to the main report on the Customer ID.

-LB

 

[anationalacrobat]

Ok, I will have to try that out.

As for the addresses, yes, multiple lines.
joe blow
123 street
anywhere, NA 12345

The controls I have are whether the address is business, personal, and primary. There should be no more than 1 primary address of each type for each customer.

 

[lbass]

Then I would add that as a selection criterion in the subreport, e.g.:

{table.primary} = "Y"

-LB