Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: How do I create a Sum total for multiple fields? Post Reply Post New Topic
Page  of 3 Next >>
Author Message
Repent34
Newbie
Newbie


Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
Quote Repent34 Replybullet Topic: How do I create a Sum total for multiple fields?
    Posted: 07 Mar 2011 at 10:42am
How do I create a Sum total for multiple fields?

I have a DB with the following tables/fields:

Table=GLAccount
Field=GLAccountNumber

In the field value, there are 7 different account numbers that I want to sum up (there are many more, but only want to sum seven of them). The account numbers represent different product lines.
They are:

410000.00
412500.00
415000.00
417500.00
420000.00
421000.00
431000.00

Each of these account numbers has a value associated with it that represents a sales dollar figure.

I need a formula that will add up the values represented by the account numbers and place that value in the report footer next to a field I've labeled as "Balance per GL".

Can someone point me in the right direction?

thank you all;
chris
Edit/Delete%20Message
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Mar 2011 at 10:57am
use a running total with an evaluate formula of
GLAccount.GLAccountNumber in (410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00)
IP IP Logged
Repent34
Newbie
Newbie


Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
Quote Repent34 Replybullet Posted: 07 Mar 2011 at 11:25am
Originally posted by DBlank

use a running total with an evaluate formula of
GLAccount.GLAccountNumber in (410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00)


I found where to create a new running total but not sure what to put where?  When I selected GLAccount.GLAccountNumber as the "Field to Summerize" and selected "Use a formula" under "Evaluate" and plugged in the text you suggested I only get errors about the formula.  Can you help me further?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Mar 2011 at 11:29am

sorry, original "in" formula had parenths () but should be brackets[]

create a RT
name = whatever you want
field to summarize = GLAccount.GLAccountNumber
type of sumamry = SUM
evaluate = use a formula
click on the formula box and add your condition formula here
{GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
reset = never
place in your report footer
 


Edited by DBlank - 07 Mar 2011 at 11:30am
IP IP Logged
Repent34
Newbie
Newbie


Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
Quote Repent34 Replybullet Posted: 07 Mar 2011 at 12:00pm
Originally posted by DBlank

sorry, original "in" formula had parenths () but should be brackets[]

create a RT
name = whatever you want
field to summarize = GLAccount.GLAccountNumber
type of sumamry = SUM
evaluate = use a formula
click on the formula box and add your condition formula here
{GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
reset = never
place in your report footer
 


Dan;

I realized I left part of the puzzle out when I got an answer of $779 BILLION dollars in sales!!

Further explanation:
My (Sales) report reflects sales for different products. The products are represented by various "account numbers" There are maybe 100 account numbers. Some of the account numbers, those I listed, need to have the dollar values they represent summed up so that I can plug that answer into a box on the form (Balance Per GL).

Also, this form does not currently show the account numbers nor does it need to, however, the account numbers I mentioned all relate to a field called "MonthBeginBal". If I drop the 'GLAccount.GLAccountNumber" field and the 'GLAccount.MonthBeginBalance" onto the report, I can look down through the account numbers and in the second field I dropped onto the form('GLAccount.MonthBeginBalance"), I can see the dollar amount involved.

It is this dollar amount of the seven account numbers that I want to sum. There are several instances of duplication of the account numbers and the same dollar value because in the DB there could be several sales of that product. I only need it to sum once, see below:

410000.00 $500

410000.00 $500

410000.00 $500

412500.00 $800

412500.00 $800

415000.00 $200

415000.00 $200

The formula should only sum each account number dollar value once, so the answer to the above would be $1500.00

Thank you for looking at this Dan.


IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Mar 2011 at 12:03pm
try:
{GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
and
next({GLAccount.GLAccountNumber})<>{GLAccount.GLAccountNumber}
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Mar 2011 at 12:08pm
hopefully you also figured out the field to SUM is your $500,$800,$200 field
if the previous() function does not work, make a formula field
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
then  in your RT change it as
name = whatever you want
field to summarize = new Formula field
type of summary = SUM
evaluate = on change of field (GLAccountNumber)
reset = never
place in your report footer
 


Edited by DBlank - 07 Mar 2011 at 12:11pm
IP IP Logged
Repent34
Newbie
Newbie


Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
Quote Repent34 Replybullet Posted: 07 Mar 2011 at 12:43pm
Originally posted by DBlank

hopefully you also figured out the field to SUM is your $500,$800,$200 field
if the previous() function does not work, make a formula field
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
then  in your RT change it as
name = whatever you want
field to summarize = new Formula field
type of summary = SUM
evaluate = on change of field (GLAccountNumber)
reset = never
place in your report footer
 


Sorry Dan, I'm new to alot of this.  The formula field mentioned above didn't work.  Where do I create this "formula field" and is it replacing the Running Total field created at the beginning of this?


IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Mar 2011 at 4:24am
The formuila field will be used with the Running Total.
You create the formula field in the field explorer under formula fields
right click and select new
name it whatever you want, i will call it 'Amount_W_0s'
add in the formula you want it to do
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
replace the blue portion with the actual field from your db
save it.
drag and drop it onto your report canvas next to your other value so it should look something like:
Acct#                    AMount         'Amount_W_0s'
410000.00           $500                $500
410000.00           $500                $500
410000.00           $500                $500
412500.00           $800                 $800
412500.00           $800                 $800
412325.00           $900                 $0
412325.00           $900                 $0
412325.00           $900                 $0
415000.00           $200                 $200

Now update your RT as

name = whatever
field to sumamrize = @Amount_W_0s (your new formula field)
type of summary= SUM
evaluate = on change of record, select the GLAccountNumber field
reset = never
 
you can place it on the detail section to see how it calcualtes row by row
IP IP Logged
Repent34
Newbie
Newbie


Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
Quote Repent34 Replybullet Posted: 08 Mar 2011 at 8:44am
Originally posted by DBlank

The formuila field will be used with the Running Total.
You create the formula field in the field explorer under formula fields
right click and select new
name it whatever you want, i will call it 'Amount_W_0s'
add in the formula you want it to do
if {GLAccount.GLAccountNumber} in [410000.00,412500.00,415000.00,417500.00,420000.00,421000.00,431000.00]
 then {GLAccount.amount_field} else 0
replace the blue portion with the actual field from your db
save it.
drag and drop it onto your report canvas next to your other value so it should look something like:
Acct#                    AMount         'Amount_W_0s'
410000.00           $500                $500
410000.00           $500                $500
410000.00           $500                $500
412500.00           $800                 $800
412500.00           $800                 $800
412325.00           $900                 $0
412325.00           $900                 $0
412325.00           $900                 $0
415000.00           $200                 $200

Now update your RT as

name = whatever
field to sumamrize = @Amount_W_0s (your new formula field)
type of summary= SUM
evaluate = on change of record, select the GLAccountNumber field
reset = never
 
you can place it on the detail section to see how it calcualtes row by row


Dan;

We are soooo close....I plugged in everything as described.  I'm still getting a HUGE total amount but only because the report is summing up ALL instances of these account numbers, even though the amount is still the same within each account number.  Is there a way to have the report/formula only count one instance of the account numbers in the formula?  I think that would be the finishing touch.

Before I think I mentioned that each account number represents a different product when apparently each account number represents a different "line" of products, thusly in the report, there can be dozens of the same account number, because the account numbers represent a line of products.

How do I get the formula to stop counting/summing up each account number after it finds the first one?

chris


IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.