Author |
Message |
Repent34
Newbie
Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
|
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
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Repent34
Newbie
Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 07 Mar 2011 at 11:25am |
Originally posted by DBlankuse 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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Repent34
Newbie
Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 07 Mar 2011 at 12:00pm |
Originally posted by DBlanksorry, 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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Repent34
Newbie
Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 07 Mar 2011 at 12:43pm |
Originally posted by DBlankhopefully 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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
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 Logged |
|
Repent34
Newbie
Joined: 03 Mar 2011
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 08 Mar 2011 at 8:44am |
Originally posted by DBlankThe 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
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 Logged |
|
|