Report Writer Total / Total Count

[SundancerKid]

I am working on a custom statement, in one field is the Invoice Amt and the other the Payment Amount. I need an Average number of invoice (SLS) and a Average number of Payments (PMT). I Created a Calc field to sum (SMS), I created a Calc field to count (SMS), I create a Calc field to sum (PMT), and I created a field to count (PMT). these fields are summarized into Footer Field. I can not divide the Footer Fields to get an average. i.e.
SUM(SLS) / COUNT(SLS) = Average (SLS)
SUM(PMT) / COUNT(PMT) = Average (PMT)

Any suggestions?

Thanks in Advance

Rick P.

 

[dgillz]

What report writer are you using? Crystal Reports or something built into GP?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[SundancerKid]

I am using Great Plains 8.0 Report Writer.

I do use Crystal alot as well but I am modifing the Userdefined Statement.

 

[dgillz]

Can't help you there, maybe theres a GP guru here somewhere.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[winthropdc]

Have you tried using the AVERAGE option? In the same way you can select DATA, SUM or COUNT, you can select AVERAGE.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer
MBS Support - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[SundancerKid]

Yes I have,

The only problem is it counts every transaction to average by, I want to average each code seperately.

Example.

There are a total of 13 transactions a mixture of SLS and PMT. 7 SLS and 6 PMT

SLS Total 7000 / 7 = 700 as an average
PMT Total 6000 / 6 = 600 as an average

 

[winthropdc]

Try this.

Create 4 conditional calculated fields.

1) SLS Amount
Expression: Type = SLS
True: Transaction Amount
False: 0

2) SLS Count
Expression: Type = SLS
True: 1
False: 0

3) PMT Amount
Expression: Type = PMT
True: Transaction Amount
False: 0

4) PMT Count
Expression: Type = PMT
True: 1
False: 0

Add this fields to the Report footer and set the type to SUM.

The create 2 more calculated fields and use the report fields added to the Report footer to calculate the averages.

1) SLS Average
Expression: RF_SUM_SLS_AMOUNT/RF_COUNT_SLS_COUNT

2) PMT Average
Expression: RF_SUM_PMT_AMOUNT/RF_COUNT_PMT_COUNT


Then add these to fields to the Report Footer and be sure to change the field type from Last Occurrence to Data.

This should do what you want.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer
MBS Support - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[SundancerKid]

Hi Winthropd,

Thank you so much that worked out great.

I have not been using Report Writer that much.

I had everything correct except for using the RF_ cal files.

Thanks again.