×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# How to get sum for each customer?5

## How to get sum for each customer?

(OP)
Hi everyone... may i just ask how to get the sum of the transaction amount for each cutomer and the total sum of all transaction amount? Thanks and God bless

Customer Name Trans amount
Allan 300
Allan 400
Margie 100
Allan 600
Margie 300
Kim 100
Margie 200
Allan 500

I want to have an output as this.. in the order i have done it already... my problem is how to get sum for each customer in a report.... Thanks in God bless...

Allan 300
Allan 400
Allan 600
Allan 500
total 1800
Kim 100
total 100
Margie 100
Margie 300
Margie 200
total 600

Grand total 2500

### RE: How to get sum for each customer?

In your report, create a group based on Customer Name. You do that by going to Report -> Data grouping. Click the Add button, and enter the customer name field in the Expression box. (Of course, you might already have this group, in which case there's no need to do it again.)

Now, in the group footer, and a field in the usual way. Right-click on it and select Properties. In the resulting dialogue, go to the Calculate tab. Select Sum as the calculation type. Set the "Reset based on" to the group name.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: How to get sum for each customer?

#### Quote:

in the group footer, and a field in the usual way.

I forgot to add: Set the Expression to the name if the transaction amount field.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: How to get sum for each customer?

3
Hi Mandy,

If you want to see the subtotals and the grand total in a form, please have a look at the code below

#### CODE -->

*!*	grid_calculatedcolumn.prg
PUBLIC goForm

goForm = NEWOBJECT("form1")
goForm.Show

Close all
Clear All

RETURN

**************************************************
DEFINE CLASS form1 AS form
AutoCenter = .T.
Caption = "Grid with calculated columns"
MinHeight = This.Height
MinWidth = This.Width
MaxWidth = This.Width

ADD OBJECT grid1 AS grid WITH ;
ColumnCount = -1, ;
Left = 12, ;
Top = 36, ;
Width = ThisForm.Width - 24, ;
Height = ThisForm.Height - 48, ;
RecordSource = "curTemp", ;
Anchor = 15

PROCEDURE grid1.Init
WITH This.Column1
.Width = 90
ENDWITH

WITH This.Column2
.Width = 90
ENDWITH

ENDPROC

ADD OBJECT lblTAmount as Label WITH ;
Left = 228, Top = 6, Caption = "Grand Total: ", FontBold = .T., FontItalic = .T., AutoSize = .T., Visible = .F.

ADD OBJECT cmdUndo AS CommandButton WITH ;
Left = 120, Top = 6, Height = 24, Caption = "Undo"

PROCEDURE cmdUndo.Click()
With ThisForm
.Grid1.ColumnCount = -1
.Grid1.Recordsource = "curTemp"
.Grid1.Init()
.lblTAmount.Visible = .F.
.Refresh()
ENDWITH
ENDPROC

ADD OBJECT cmdDoit AS CommandButton WITH ;
Left = 12, Top = 6, Height = 24, Caption = "Calculate"

PROCEDURE cmdDoit.Click()

SELECT SUM(iAmount) FROM curTemp INTO ARRAY laTAmount
SELECT .F. as lSwitch, cName, iAmount, iAmount as iRAmount from curTemp order by 2 into Array laCalc
SELECT .T. as lSwitch, cName, 0 as iAmount, SUM(iAmount) as iSAmount FROM curTemp ORDER BY 2 Group BY 2 INTO ARRAY laDBCCriteria

For i = 2 to ALEN(laCalc, 1)
If laCalc[i , 2] = laCalc[i - 1 , 2]
laCalc[i, ALEN(laCalc, 2)] = laCalc[i, ALEN(laCalc, 2)] + laCalc[i - 1, ALEN(laCalc, 2)]
EndIF
EndFor

CREATE CURSOR curTemp2 (lSwitch L, cName C(20), iAmount I, iTAmount I)
INDEX on cName + PADL(iTAmount, 7, "0") TAG xNaMxAm
SET ORDER TO xNaMxAm

APPEND FROM ARRAY laCalc

LOCATE

With ThisForm.Grid1
.ColumnCount = -1
.Recordsource = "curTemp2"
.SetAll("DynamicBackColor", "ICASE(lSwitch, RGB(0,255,0), RGB(255, 255, 255))", "Column")
.SetAll("DynamicFontBold", "ICASE(lSwitch, .T., .F.)", "Column")
.SetAll("DynamicFontItalic", "ICASE(lSwitch, .T., .F.)", "Column")

.Column2.Width = 90

.Column3.Width = 90
.Column3.Sparse = .F.

.Column4.Width = 90
.Column4.Sparse = .F.

.DeleteColumn(1)
ENDWITH

WITH ThisForm
.lblTAmount.Caption = "Grand Total: " + TRANSFORM(laTAmount[1], "999,999")
.lblTAmount.Visible = .T.
.Refresh()
ENDWITH
ENDPROC

PROCEDURE Destroy
Thisform.Release()
CLOSE ALL
Clear Events
ENDPROC

CREATE CURSOR curTemp (cName C(20), iAmount I)

INSERT INTO curTemp VALUES ("Allan", 300)
INSERT INTO curTemp VALUES ("Allan", 400)
INSERT INTO curTemp VALUES ("Margie", 100)
INSERT INTO curTemp VALUES ("Kim", 1250)
INSERT INTO curTemp VALUES ("Allan", 600)
INSERT INTO curTemp VALUES ("Margie", 300)
INSERT INTO curTemp VALUES ("Margie", 200)
INSERT INTO curTemp VALUES ("Chris", 500)
INSERT INTO curTemp VALUES ("Allan", 500)
INSERT INTO curTemp VALUES ("Kim", 250)

LOCATE

ENDPROC

ENDDEFINE
********************************************* 

MarK

### RE: How to get sum for each customer?

(OP)
Thanks Mike.... but Up to this day i cant seem to get the result i need... still trying.....

Thank you Mark for the code... but what i need is for the report... but i saved you code for future use... Godbless...

### RE: How to get sum for each customer?

(OP)
While reading your code Mark.... i thinking of how to be good as you or when will i will have that kind of programming skills... you are so good and if i may say best... Thank you Mark.... God bless....

### RE: How to get sum for each customer?

Hi Mandy,

If you work a lot with VFP-reports you may want to have a look at

The Visual FoxPro Report Writer: Pushing It to the Limit and Beyond Paperback – January 1, 2002
by Cathy Pountney (Author), Dave Aring (Editor)

I added a zip file "Mandy.7z" with 4 files (2) rptNames.* and (2) testgridcalc3.* You unzip it, run testgridcalc3.prg and click "Pint report". You may then hit the ESCAPE key to print the report. Later you may have a look at the report to find out how it works

hth

MarK

### RE: How to get sum for each customer?

Hi Mandy,

Btw, I forgot: Thanks for the "laudatio"

MarK

### RE: How to get sum for each customer?

mjcmkrsr

I can only confirm Mandy's "laudatio".

Your programs are also a good source for studies for me as a beginner - because they can be run immediately and can be adapted for my own purposes with small changes.
All in all, of course, the other tips here are always valuable, and since a special individual here no longer makes terror, it's a lot of fun to read in this forum.

Thanks
Klaus

Peace worldwide - it starts here...

### RE: How to get sum for each customer?

Hi Klaus,

Vielen Dank - Thank you

Frohe Ostern - Happy Easter

MarK

### RE: How to get sum for each customer?

(OP)
Hi Mark... While trying to look at your code, im trying to change the height but i coud'nt... may i ask how to make the form to make it longer in height? when i change the -48 it doesnt change the height of the form, but when i change it to positive number, it always return an "illegal value" error... thanks and God bless

### RE: How to get sum for each customer?

Hi Mandy,

Now c'mon - why would I do that. The form IS resizable in HEIGHT - just drag its upper border up or its lower border down. Or maximize it - but then it jumps to the upper left corner of the VFP Screen
However I gave its WIDTH a fixed value. Again this may be changed easily: you just comment out the line "MaxWidth = This.Width"

hth

MarK

### RE: How to get sum for each customer?

(OP)
Oh i see… got it Mak… thanks again…

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!