unable to display summary information

[nikhil27]

Hi,

I have to provide a report to my company's VP. The data in the table, against which I have to generate the report, is like this:

invoice# customer# amount
ABC 9999 123
ABC 4567 123
DEF 5678 100
DEF 5678 100
XYZ 4567 300
XYZ 6789 400

I have to display the data in the report like this:

customer# invoice# amount
4567 ABC 246
4567 XYZ 700
Total:- 946
5678 DEF 200
Total:- 200

The logic which I am using is:

1. First do a group by on invoice#
2. Then select the minimum account number and sum of amounts
3. Do a sort on customer account number
After this the data in the report looks like following

customer# invoice# amount
4567 ABC 246
4567 XYZ 700
5678 DEF 200

But I am not able to provide the summary by account numbers, because I haven't done a grouping on account number.
Any pointers rearding this will be of great help.

Thanks,
Nikhil

 

[MrBillSC]

Group 1 should be customer number and group 2 should be invoice number. subtotal in both group footers.

 

[nikhil27]

Thanks for the reply but..

I can not do a group on the customer number because first I have to calculate the sum of invoice amounts irrespective of customer account number. Please refer to the provided example.

 

[MrBillSC]

you may need to create a subreport grouped on invoice number and pass the invoice total to the main report.

Group the main report on customer and add sort by invoice number. Link to subreport by customer number and invoice number. Suppress the detail section of main report and in the invoice footer of main report display the invoice number and invoice total from the subreport.

Since an invoice can have more than one customer, each customer will show the total of the invoice. This means each invoice total would be repeated for each customer of that invoice.

Clear as mud?

Hope it points you in the right direction.

MrBill

 

[lbass]

Another approach would be the following. I'm assuming that you used topN/group sort to sort groups by "minimum of {table.customer#}". You can create a faux group footer by doing the following:

Place your current group summaries in the group footer, if they are not already there, and drag the invoice # into the group footer. Suppress the details section. Then create two formulas:

//{@calcsum} to be placed in the group header:
evaluateafter({@displ});
whileprintingrecords;
stringvar x;
numbervar y;

if instr(x, minimum({table.customer#},{table.invoice#})) = 0 then
(x := minimum({table.customer#},{table.invoice#});
y := sum({table.amount},{table.invoice#})) else

if instr(x,minimum({table.customer#},{table.invoice#})) > 0 then
(x := x + minimum({table.customer#},{table.invoice#});
y := y + sum({table.amount},{table.invoice#}));

//{@displ} to be placed in the group header AND in the report footer:
whileprintingrecords;
numbervar y;

You can suppress {@calcsum}. Then go to the section expert->group header->suppress->x+2 and enter:

whileprintingrecords;
numbervar y;

groupnumber = 1 or
y <> sum({table.amount},{table.invoice#})

-LB

 

[nikhil27]

Thanks lbass!!
This worked like a dream..U r genius !!

I am still trying to decipher your code

once again, thanks a ton..