×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

How to get sum for each customer?
5

How to get sum for each customer?

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)

Visual FoxPro articles, tips and downloads

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)

Visual FoxPro articles, tips and downloads

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
Read Events

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
				.Header1.Caption = "Name"
			ENDWITH

			WITH This.Column2
				.Width = 90
				.Header1.Caption = "Amount"
			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()
			Local Array laCalc[1,1], laTAmount[1], laDBCCriteria[1,1]
			
			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
			APPEND FROM array laDBCCriteria
			
			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
				.Column2.Header1.Caption = "Name"

				.Column3.Width = 90
				.Column3.Sparse = .F.
				.Column3.Header1.Caption = "Amount"
				.Column3.Text1.Inputmask = "999,999"

				.Column4.Width = 90
				.Column4.Sparse = .F.
				.Column4.Header1.Caption = "Running Total"
				.Column4.Text1.Inputmask = "999,999"
				
				.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

PROCEDURE Load
	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" wink

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 wink - 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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close