sum of formula field 2

[hald5]

Hi,
Would appreciate your help in getting the right total for a formula calculated field
I am using Access 2000 table with CR 7

MY REPORT HAS FOUR COULMNS GROUPED ON ITEM TYPE
TWO COULMNS FROM THE TABLE POST.DEPIT AND POST.CREDIT
GROUPED ON POST.MATER1 AND a FORMULA CALCULATED FIELDS
@a where a formula is

sum({post.depit},{post.mater1}) - sum({post.credit},{post.mater1})

now in the highlightingexpert condition if a >= 0 it display in coulmn 3; if a<= 0 it dislay result in coulmn 4

I would like to get a total in the report footer for both
coulmn 3 and 4 so far no luck ....

When I tried to get a running total I get error message *** you cannot select this field
@a as the field summarises /reset/evaluates

ANY ADVISE
HALD5

 

[IdoMillet]

In GH1, use the folowing formula {@reset_vars} to reset two variables:

NumberVar gt_debit ;
NumberVar gt_credit ;
gt_debit := 0;
gt_credit := 0;

In GF1, use the folowing formula {@accumulate_vars}:

NumberVar gt_debit ;
NumberVar gt_credit ;
IF {@a} >= 0 THEN gt_debit := gt_debit + {@a} ELSE 
gt_credit := gt_credit + {@a} ;

In the Report Footer, use 2 formulas to display the values of the two variables. The formulas simply have to declare the variable you wish to show. For example, {@show_gt_debit}:

NumberVar gt_debit ;

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

I'm guessing that you must be adding the formula twice to the group section and then conditionally suppressing it in order to create two columns. Therefore, if you want the total of both columns at the report footer level, all you really need is the grand total for one column which will include the suppressed values. Therefore, I think you could just write a formula like the following and place it in the report footer:

sum({post.debit}) - sum({post.credit})

-LB

 

[hald5]

Ido ; thank you for your time and effort ; it seems I still
have an error somewhere

I have created the formulas using the formula editor as suggested and they all appear
fine with no errors except the accumulate_vars it returns an error " a number is expected here and the cursur highlites just after the second = sign and before gt_depit"

( after the error message it looks like this )

IF {@a} >= 0 THEN gt_debit := |gt_debit + {@a} ELSE
gt_credit := gt_credit + {@a} ;

I have also placed the formulas as suggested on GH1,GF1 AND
THE RESULTS were placed at the RF
your advise will be appreciated
Hald5

 

[IdoMillet]

If you provide the full content of the formulas you used it would be easier to troubleshoot.

Cheers,
- Ido



CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[IdoMillet]

It seems like you are using gt_debit & gt_depit inconsistently.

Make sure you stick to one name... )

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

I guess I misunderstood--I thought you wanted the combined total of the two columns. Ido has you on the right track. Be sure that you are declaring the variables in every formula in which they are used, as in the first two lines of the following formula:

NumberVar gt_debit;
NumberVar gt_credit;
IF {@a} >= 0 THEN gt_debit := gt_debit + {@a} ELSE
gt_credit := gt_credit + {@a};

Also, you should not be using any reset formula (anywhere), and your display formulas in the report footer need "whileprintingrecords;", as in:

//{@grtotdebits}:
whileprintingrecords;
numbervar gt_debit;

//{@grtotcredits}:
whileprintingrecords;
numbervar gt_credit;

-LB

 

[IdoMillet]

LB is right; since you are looking for Grand Totals, you should NOT reset the values of the variables.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[hald5]

greetings to Ido and LB , here is detailes of my report
I still cannot get past the formula editor for the cumulative_vars error

as LB suggested should I use two diff variables to hold result of post.credit - post.depit( instead of one
formula @a) ??? say something like

if post.creit > post.credit then ballance1 = post.credit - post.depit else ballance2 = post.debit - post.credit

then I can sum ballanc1,ballanc2 seperatly any advise on the correct formula and its report location.

here is where I still stand after the last remarks from YOU

AND LB



Group Header 1
---------------

Group #1 Name post.mater1 (string)

detail
------
coloumn 1 post.credit (currency)
coloumn 2 post.debit (currency)


a formula
===========
Sum ({post.debit}, {post.mater1})-Sum ({post.credit}, {post.mater1})

Group footer 1
---------------
group #1 Name post.mater1
coloumn 1 sum of post.credit (currency)
coloumn 2 sum of post.depit (currency)
coloumn 3 @a highliting expert val <= 0 ( both font+background = white
/ new item ( font = black, background = white)
coloumn 4 @a highliting expert val >= 0 ( both font+background = white
/ new item ( font = black, background = white)
Report footer
-------------
coloumn 1 sum of post.credit (currency)
coloumn 2 sum of post.depit (currency)
coloumn 3 @ show_gt_credit (Number)
coloumn 4 @ show_gt_debit (number)


accumulate_vars formula
===================
NumberVar gt_debit ;
NumberVar gt_credit ;
IF{@a} >= 0 THEN gt_debit:= gt_debit + {@a} ELSE
gt_credit:= gt_credit + {@a};

error message

**** a number is required here ( cursor just after second = sign)


all report coloumns looks fine except coloumn 3, 4 totals in report footer
showing 0.000 and 0.0000


show_gt_credit formula
======================
whileprintingrecords ;
NumberVar gt_credit ;


show_gt_debit formula
=====================
whileprintingrecords ;
numberVar gt_debit ;

 

[lbass]

Try changing "numbervar" to "currencyvar" in all formulas. I think this was the first time you mentioned that debit and credits were currency datatype.

-LB

 

[hald5]

Many thanks Ido & LB the currencyVar solved the error message and the report gave me the Sum of the formula fields I was after. your fast response and directives are well appreciated by me

Hald5

img

http://www.mysite.com/images/2thumbsup

 

[jamesiv]

I just wanted to add I was having a similar problem where I was using a Running total formula in the group header area to basically sum up a Formula field since Crystal wouldn't allow me to do so. I was still having problems with the Running total then displaying the final output correctly in the report footer, but after doing a reference to the running totals variable using the code below it worked. Thanks so much!!

//{@grtotcredits}:
whileprintingrecords;
numbervar gt_credit;