Group on a Formula? 1

[DeviousDi]

Hi,

I need to group on a sumarised field basically. Now I know this isn't possible as such, but is there a way round it?!

I'm using SAP crystal, and have various fields, invoice, invoice number, dept, client number, invoice amount, and a few others.

I want to basically sum the total of the invoice amount, per client number, and then group this by using the total of the invoice amount to group it into credit and debit per dept. It would look something like this:

Dept A - Credit
ClientNo SumInvoiceAmount
123ABC -12330.00
156XYZ -5623.52
Totals: -17953.52

Dept A - Debit
ClientNo SumInvoiceAmount
123ABC 6235.22
156XYZ 521.00
Totals: 6756.22

Is it possible?

Thank you!

Di

 

[raja2008]

Hello Di,

First group on Dept , then second group on Credit,debit field then third group on Customer number.

Then you can use summary fields at the thord group level.


Hope this helps.

 

[DeviousDi]

Hi,

I don't have a credit or debit field. I want these groups to be created from the totals of the sum of the invoice amounts per client. If the there is a negative sum for the sum of invoice amount, this will go under a 'Debit' group for that department, and vice versa.

Thanks

Di

 

[raja2008]

May be you can try creaing a formula like this


if left(invoice ammount,1)="-" then "Credit" else "debit" then use that for your second group

 

[DeviousDi]

Hi,

No, it won't let me do anything like that because your still referring to the summarised field, hence me asking if anyone knew a way around the problem.

Thanks

Di

 

[raja2008]

Create the formula on the report field not the summary

 

[DeviousDi]

HI,

Its a running total field, as there are duplicate records otherwise.

Thanks

Di

 

[raja2008]

Give us some sample data.It will make it clear for us