Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula to Sum a Summary Field

Status
Not open for further replies.
omoralez

omoralez

Programmer
May 1, 2006
23
0
0
US
Using CR XI with TCAccess Views (SQL Views) where there are multiple records for each Auth within each District by year. Each record has the total for the Auth amount field for the district, but only one District total amount is to be summed per Auth.
I have grouped Roll; District; Year; Auth
I can get the following formula to work at the Year group level, but when I try to use Summary or Running Totals it errors can't sum a summary.
When I place the formula in the higher groups I only get the last occurrance.

local numberVar AuthTAC := 0;
local numberVar AuthCount := 0;
local numberVar AuthTot := 0;


AuthTAC := sum({ViewTA04.SUM_TACHANGE}, {ViewTA04.AUTH_CODE});
AuthCount := DistinctCount ({ViewTA04.AUTH_CODE}, {ViewTA04.YEAR});

AuthTot := AuthTAC * AuthCount;

AuthTot;

Any suggestions!!!!

 
Sort by date Sort by votes
No need for the init formulas at all. In the group footer, try this:

WhilePrintingRecords;
Numbervar AuthTAC := AuthTAC+sum({ViewTA04.SUM_TACHANGE}, {ViewTA04.AUTH_CODE});
Numbervar AuthCount := AuthCount+DistinctCount ({ViewTA04.AUTH_CODE}, {ViewTA04.YEAR});

Then a 3rd formula to do the multiplication.

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

"What version of URGENT!!! are you using?
 
Upvote 0 Downvote
I need to total at the Roll level not the Year. If I place the formula in the Year GF I get the correct total. If I place the formula in either group prior I still get the last occurance. OM
 
Upvote 0 Downvote
Please show sample data that also shows the results you would like to achieve with that sample.

-LB
 
Upvote 0 Downvote
Do you still have the initialization formulas?

local numberVar AuthTAC := 0;
local numberVar AuthCount := 0;
local numberVar AuthTot := 0;

If you have them where are they placed? What report section?

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

"What version of URGENT!!! are you using?
 
Upvote 0 Downvote
I wanted to give you a good sampling of the data. I am grouping Roll, Tax Dist, Year, Auth Code. I need to sum at the GP1 Roll level and Grand Total the SUM_TaxChange. I need to only total one TAC per AUTH CODE. Plese note that the MILLAGE_12 wraps in the display.

MI
TAX_ SUM_ SUM_ AUTH_ LLAGE
ROLL DIST YEAR TACHANGE MILLAGE_1 CODE _12
PP 0010 2005 1052.26 0.064259 0101 0.031513
PP 0010 2005 1052.26 0.064259 0101 0
PP 0010 2005 1052.26 0.064259 0101 0.008628
PP 0010 2005 1052.26 0.064259 0101 0
PP 0010 2005 1052.26 0.064259 2998 0.011577
PP 0010 2005 1052.26 0.064259 2998 0.000872
PP 0010 2005 1052.26 0.064259 2998 0.001577
PP 0010 2005 1052.26 0.064259 2998 0.000395
PP 0010 2005 1052.26 0.064259 2998 0
PP 0010 2005 1052.26 0.064259 2998 0
PP 0010 2005 1052.26 0.064259 2998 0
PP 0010 2005 1052.26 0.064259 2998 0
PP 0010 2005 1052.26 0.064259 2999 0.001
PP 0010 2005 1052.26 0.064259 3006 0.00588
PP 0010 2005 1052.26 0.064259 3006 0.00222
PP 0010 2005 1052.26 0.064259 4528 0
PP 0010 2005 1052.26 0.064259 4712 0.000532
PP 0010 2005 1052.26 0.064259 4713 0.000065
PP 0012 2005 183.8 0.064259 0101 0.031513
PP 0012 2005 183.8 0.064259 0101 0
PP 0012 2005 183.8 0.064259 0101 0.008628
PP 0012 2005 183.8 0.064259 0101 0
PP 0012 2005 183.8 0.064259 2998 0.011577
PP 0012 2005 183.8 0.064259 2998 0.000872
PP 0012 2005 183.8 0.064259 2998 0.001577
PP 0012 2005 183.8 0.064259 2998 0.000395
PP 0012 2005 183.8 0.064259 2998 0
PP 0012 2005 183.8 0.064259 2998 0
PP 0012 2005 183.8 0.064259 2998 0
PP 0012 2005 183.8 0.064259 2998 0
PP 0012 2005 183.8 0.064259 2999 0.001
PP 0012 2005 183.8 0.064259 3006 0.00588
PP 0012 2005 183.8 0.064259 3006 0.00222
PP 0012 2005 183.8 0.064259 4306 0
PP 0012 2005 183.8 0.064259 4528 0
PP 0012 2005 183.8 0.064259 4712 0.000532
PP 0012 2005 183.8 0.064259 4713 0.000065
PP 0015 2005 1689.39 0.064259 0101 0.031513
PP 0015 2005 1689.39 0.064259 0101 0
PP 0015 2005 1689.39 0.064259 0101 0.008628
PP 0015 2005 1689.39 0.064259 0101 0
PP 0015 2005 1689.39 0.064259 2998 0.011577
PP 0015 2005 1689.39 0.064259 2998 0.000872
PP 0015 2005 1689.39 0.064259 2998 0.001577
PP 0015 2005 1689.39 0.064259 2998 0.000395
PP 0015 2005 1689.39 0.064259 2998 0
PP 0015 2005 1689.39 0.064259 2998 0
PP 0015 2005 1689.39 0.064259 2998 0
PP 0015 2005 1689.39 0.064259 2998 0
PP 0015 2005 1689.39 0.064259 2999 0.001
PP 0015 2005 1689.39 0.064259 3006 0.00588
PP 0015 2005 1689.39 0.064259 3006 0.00222
PP 0015 2005 1689.39 0.064259 4528 0
PP 0015 2005 1689.39 0.064259 4620 0
PP 0015 2005 1689.39 0.064259 4712 0.000532
PP 0015 2005 1689.39 0.064259 4713 0.000065
PP 0128 2005 953.15 0.092789 0501 0.039787
PP 0128 2005 953.15 0.092789 0501 0
PP 0128 2005 953.15 0.092789 0501 0.011788

 
Upvote 0 Downvote
I didn't mean actual data. We don't know what millage_12 is, and I don't know what we are seeing on the right. I meant for you to show a sample across groups, so we could see how the groups relate to the calculation. I was ready to respond earlier, but then realized you were referencing summaries from different groups in your formulas, so wasn't sure what your overall intention was. Try showing an abstract sample that shows us what's is being summarized at what level, and that shows the calculation you expect to see.

-LB
 
Upvote 0 Downvote
I am grouping at Roll, Tax District, Year, Auth Code. I can get this to work at the Year GP3, but not in either of the two upper groups.
WhilePrintingRecords;
numberVar FAuthTAC := 0;
numberVar FAuthCount := 0;
numberVar FAuthTot := 0;

FAuthTAC := sum({ViewTA04.SUM_TACHANGE}, {ViewTA04.AUTH_CODE});
FAuthCount := DistinctCount ({ViewTA04.AUTH_CODE}, {ViewTA04.YEAR});

FAuthTot := FAuthTAC * FAuthCount;

FAuthTot;



0101       1052.26
2999       1052.26
3006       1052.26
4528       1052.26
4712       1052.26
4713       1052.26
6 * 1052.26 = 6,313.56 District Total 0010

0101       183.80
2998       183.80
2999       183.80
3006       183.80
4306       183.80
4528       183.80
4712       183.80
4713       183.80
8 * 183.80 = 1,470.40 District Total 0012

0101       1,689.39
2998       1,689.39
2999       1,689.39
3006       1,689.39
4528       1,689.39
4620       1,689.39
4712       1,689.39
4713       1,689.39
8 * 1689.39 = 13515.12 District Total 0015
Roll Total 21299.08
This is the total I need to create at the Gp1 level, then
I need to create a Grand Total = Total of all Rolls GP1's
 
Upvote 0 Downvote
Started to respond again and then realized we don't know how the inner groups vary. Is the year always the same value throughout? Your last sample implies the year group is irrelevant.

-LB
 
Upvote 0 Downvote
The year(s) is a user mainframe parameter selection, but most of the test data I have seen has at least three years (2005, 2006, 2007).
 
Upvote 0 Downvote
//{@Accum} to be placed in the [red]auth group[/red] section:
WhilePrintingRecords;
Numbervar AuthTAC := AuthTAC+ [red]maximum[/red]({ViewTA04.SUM_TACHANGE}, [red]{ViewTA04.AUTH_CODE}[/red]);
Numbervar AuthCount := AuthCount+DistinctCount ({ViewTA04.AUTH_CODE}, {ViewTA04.AUTH_CODE});
Numbervar authtot := authtac*authcount;
Numbervar rolltot := rolltot + authtot;
Numbervar grtot := grtot + authtot;

//{@reset} to be placed in the roll group header:
WhilePrintingRecords;
if not inrepeatedgroupheader then
rolltot := 0;

Then in the roll group footer, use a formula:

//{@displrolltot}:
WhilePrintingRecords;
Numbervar rolltot;

In the report footer use:

//{@displgrtot}:
Whileprintingrecords;
Numbervar grtot;

-LB
 
Upvote 0 Downvote
Thank you, after a few modifications I got this to work. OM
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Corinne30
  • Question
Division of currency formula with/without rounding
Replies
7
Views
338
dgillz
dgillz
Cpreston
  • Locked
  • Question
Select Top 2 rows where field = 2,3,4 and 5
Replies
1
Views
82
Cpreston
Cpreston
john432
  • Locked
  • Question
OLE Objects in your formula 1
Replies
2
Views
100
john432
john432
Tichaona
  • Locked
  • Question
Row numbering formula not working
Replies
4
Views
100
lbass
lbass
john432
  • Locked
  • Question
Crystal Report formula (finding with key words) 1
Replies
2
Views
98
john432
john432

Part and Inventory Search

Sponsor

Back
Top