How to get sum for each customer? 5

[Mandy_crw]

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

 

[Mike Lewis]

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

 

[Mike Lewis]

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

 

[mjcmkrsr]

Hi Mandy,

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

*!*	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

 

[Mandy_crw]

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

 

[Mandy_crw]

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

 

[mjcmkrsr]

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

 

[mjcmkrsr]

Hi Mandy,

Btw, I forgot: Thanks for the "laudatio"

MarK

 

[german12]

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

 

[mjcmkrsr]

Hi Klaus,

Vielen Dank - Thank you

Frohe Ostern - Happy Easter

MarK

 

[Mandy_crw]

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

 

[mjcmkrsr]

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

 

[Mandy_crw]

Oh i see… got it Mak… thanks again…