| Author |
Message |
HEMREDDY
Newbie
Joined: 31 Jul 2013
Online Status: Offline
Posts: 38
|
  
Topic: Summarised field totals
Posted: 11 Feb 2014 at 6:54am |
Hi,I have the following data.
|
CMINV
|
ARDATE
|
AR_BALANCE
| |
XYZ
| | | |
17,271
|
06/12/2012
|
-30.30
| |
21,893
|
10/03/2013
|
-125.59
| |
COSTRITE LTD
| | |
150,744
|
01/03/2014
|
2,714.05
| |
152,604
|
01/05/2014
|
5,132.80
| |
154,953
|
01/08/2014
|
6,739.20
| |
154,954
|
01/08/2014
|
2,560.53
| |
175,744
|
02/05/2014
|
8,952.90
| |
175,745
|
02/05/2014
|
3,294.40
|
The AR balance filed values are summarized field values per invoice. I am trying to get a report to show for each customer the totals for each month and total for all months by customer and overall totals as a page footer. Can you help me with this. Reddy
|
 IP Logged |
|
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 11 Feb 2014 at 7:55am |
|
add 2 groups
one for costomer_id
one for ardate
when you add the group for ardate click options select evaluate for each month
then create summaries
right click on he field >>insert>>summary >>group 1
right click on he field >>insert>>summary >>group 2
|
| IP Logged |
|
HEMREDDY
Newbie
Joined: 31 Jul 2013
Online Status: Offline
Posts: 38
|
Posted: 11 Feb 2014 at 8:54am |
Hi, I already have the grouping by customer and Invoice date. What i have in AR balance is the sum of individual line items for that invoice on various dates. The AR date is Min date of the indivual line items dates on an invoice number. The back ground detail data is like this. Customer Cosrite Invoice number 150744 Date AR balance 01/03/2014 1,501.25 01/15/2014 - 501.25 02/01/2014 1,714.15 MINAR date 01/03/14 AR Bal Summary Total 2714.15 This is the case with almost all the individual lines. Now i need the total for each month based on Min Ar Date by Customer and total for Customer and Total for all the customers. Reddy
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 11 Feb 2014 at 12:05pm |
|
into member group footer place a
formula for minimum date per member
minimum(date,memberid)
and formula for sum by member
sum(balance,member)
for all the members
sum(balance)
Edited by kostya1122 - 11 Feb 2014 at 12:06pm
|
| IP Logged |
|
HEMREDDY
Newbie
Joined: 31 Jul 2013
Online Status: Offline
Posts: 38
|
Posted: 12 Feb 2014 at 1:48am |
Hi, Can not create the formula minimum({ARMaster.ARDATE},{ARMaster.CMACCTNO}) i am getting a message "there must be a group that matches the field". Reddy
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 12 Feb 2014 at 3:23am |
|
whatever field you used for customer group use it in the formula.
as you said
"I already have the grouping by customer and Invoice date"
minimum({ARMaster.ARDATE},customer)
|
| IP Logged |
|
HEMREDDY
Newbie
Joined: 31 Jul 2013
Online Status: Offline
Posts: 38
|
Posted: 12 Feb 2014 at 7:49am |
Kostya, Used cross tab and got the desired results. Created formulas and group by invoice number and sum by invoice and filtered all the items with sum totals not equal to 0 and the used that for summarization. Thanks Reddy |
| IP Logged |
|
|
|
