Returned Information from Subreport based on Selection Criteria

[trialbyfire]

I have a report where in the main report I am breaking down sales dollars and pieces by sales rep then by month, then by billing customer. (Billing customer is the main billable account. Ex Walmarts billing customer would be Walmart Coorporate and the Store would be Wal-Mart Store.)
The grouping by billing customer allows me to report total sales for the month without having to show all the store detail. Anyway this works fine. I then have a subreport in report footer b that has the same detail section as the main report and has one group which is by billing customer. What I am trying to do is once the entire report is run and they look at the sales by rep,month,cust. is give them a Subtotal of sales by customer. Anyway when I set the selection criteria in the subreport up it pulls the correct $ totals but it is displaying the customer number and name for the last detail record for that billing customer in the main report. Is there anyway to get the subreport to show the same billing information as the main report?

 

[synapsevampire]

Rather than a text description, try posting something more technical, such as version information, where in the report you're using the subreport, how you're linking it, some example data, and expected output.

Since the main report is at the last record for it's details, if you're linking by that column, that would makes sense.

-k

 

[trialbyfire]

Alright here is an example. Anything in Caps is the subreport data that I am looking to return.
Group 3 Contains Subreport Custname. This is linked from the Invoiceheader.billing-cust-key to the Custname.cust-key file in the subreport. Each customer record has both a customer code and a customer key. Each customer has a billing customer key that corresponds with the main accounts customer key. Please see example below.

-Report Title
-Sales by Rep, By Month, By Customer

Rep Month Customer#/Name Sales $

6511 (group1)
9/2003 (group2)
5109 WALMART (group3) $7929
6789 COSCO $532
7652 HIBBETTS $14987
Month Total: $23448
10/2003
5109 WALMART $32000
Month Total: $32000
Rep Total: $55448
6513
9/2003
4624 TARGET $123876
2470 KMART $898
(Group2 Totals) Month Total: $124774
(Group1 Totals) Rep Total: $124774


{Report Footer:}
(SubReport CustSales)
2470-987 KMART STORE #987 $ 898
5109-504 WALMART STORE #504 $ 39929
4624-556 TARGET DC #556 $123876

Now in the Report footer is another subreport Custsales. The first subreport CSTNME works because the billing-customer-key on the invoice header corresponds with a customer-key in the customer name file. The Subreport then in turn just lists the customer code and name that corresponds with the customer-key that is = to the billing-customer-key on the invoice header.
CSTNME SELECTION CRITERIA
?PM-ARINV.SYSTEM-ID = CUSTNAME.SYSTEM-ID
?PM-ARINV.BILLING-CUST-KEY = CUSTNAME.CUST-KEY

Now the second subreport, CUSTSALES, has the same invoice detail as the main report. Suppressed in both reports. I am then grouping by billing-cust-key in both the main report (group3) and the subreport (group1).
SELECTION CRITERIA SECOND SUBREPORT AS FOLLOWS:
?PM-ARINV.SYSTEM-ID = ARINV.SYSTEM-ID
?PM-ARINV.REF-DATE = ARINV.REF-DATE

Now here is where I run into problems. If I put in the subreport link
?PM-ARINV.BILLING-CUST-KEY = CUSTNAME.CUST-KEY
Like the subreport CSTNME above, the subreport CUSTSALES displays the billing cust# and name information correclty but will only display the very last customer record and dollar amount in the main report.

If I do not include this subreport link I get the information returned to me in the example above. Now the dollar totals are correct and all customers show up but the cust# and Name are displayed like the example above. It is grabbing the very last customer code and name from the very last ar record for each billing-cust group in the subreport.

Make any sense? Does this help at all?

 

[synapsevampire]

I do appreciate you supplying some of the required information.

It may be that you misunderstand what a report footer is, it fires after the entire report is done, and one time only.

Again you have no example data and expected output, no version information (versions have different capabilities) rather just an example of what you don't want.

It's fairly likely that you don't need these subreports at all based on what I've read, but my time is too short to spend it through a lengthy discovery process because you haven't shared technical information.

I can say that you should consider a Crosstab in lieu of the final subreport, I'm not sure if the other subs can be replaced, but given your approach they likely could be.

A good rule of thumb is to post the specifics I outlined before, some example data, and what the output should be, not how you're trying to do it and an example of what's wrong. Think of it like designing a spec for an architect, there are lots of great coders here, perhaps one of them can assist you in the design phase as well.

-k