Formula to Sum a Summary Field

[omoralez]

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

 

[dgillz]

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[omoralez]

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

 

[lbass]

Please show sample data that also shows the results you would like to achieve with that sample.

-LB

 

[dgillz]

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[omoralez]

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

 

[lbass]

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

 

[omoralez]

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

 

[lbass]

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

 

[omoralez]

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

 

[lbass]

//{@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

 

[omoralez]

Thank you, after a few modifications I got this to work. OM