Author |
Message |
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Topic: GF Summary Question Posted: 12 May 2009 at 4:24pm |
Running CR v10
Since I just posted a question this morning I feel pretty silly posting another but I'm very close to completing a report and I really do appreciate all the help!
Anyway, I'm trying to summarize totals for various running totals for each "rep" at the group footer level (GF #1 = tblReps.RepName).
Sample data may prove helpful here:
Rep: Invoice: Material Cost:
DannyS 162 $500.00
DannyS 175 $1,500.00
JohnD 150 $450.00
Material Cost is a running total which summarizes tblInvoices.PostedCost
formula below is evaluated
{tblInvoiceDetail.Type} = ["S", "O", "M", "A"]
and resets with {tblInvoices.InvoiceNumber}
Is it possible to summarize totals for each rep if "Material Cost" is a running total, if so your insight on how I go about doing this would be welcomed. If not, what must I do in order to make this happen?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 May 2009 at 7:14pm |
If I understand your question correctly you can not really summarize as a sum the running total field but you can create another running total using the same criteria ({tblInvoiceDetail.Type} = ["S", "O", "M", "A"])and reseting it at group1 instead of at the tblinvoices.postedcost group level. Place that on GF1. It should give you same result as a SUM of the lower level RTs.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 13 May 2009 at 6:58am |
Since I don't use running totals, I would defer to DBlank, but if that doesn't work (but I don't see why it wouldn't), you could create your own running totals using shared variables.
you would set one of your variables to 0 when the group starts and increment it every detail line. If you want you can increment your summar counter at the same time. In the footer for the group you would display the correct counter.
Same as a running total.
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 13 May 2009 at 8:23am |
DBlank,
That's what I thought but for a reason I've yet to figure out it's not working, calculation is way off. The detail data in the report reflect a much lower number than what the running total is reporting. For example one rep shows the total should be $15,476 but the RT shows $201,880.00. Also, the running total does not reset after each rep.
I know it's difficult to diagnose what could be causing this issue but any ideas you may have are welcomed.
Lockwelle,
I'm not familar with using shared variables. I'm certain the information you provided is crystal clear but I'm a rookie w CR and don't know how to carry out your instructions.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 May 2009 at 8:32am |
First, are you conditionally rows at all?
A RT will still include these in calculations (although i would think it would also nbegatively impact your other calculations that look correct).
In your Rep total RT under Reset it should be set to "on change of group" and "group#1:xxxxx" and placed on the Group1 footer. Also you have the evaluate as Use a formula with your conditions in there? Is this how you have it set up?
Edited by DBlank - 13 May 2009 at 8:33am
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 13 May 2009 at 9:44am |
DBlank,
Not sure what you are referring to with your question regarding "conditional rows". Please excuse my ignorance.
In regards to the RT, it is setup just as you described. Field to summarize is tblInvoices.PostedCost, evaluate is configured to use the formula: {tblInvoiceDetail.Type} = ["S", "O", "M", "A"], and reset is configured as "Change of Group" use Group#1:tblReps.RepName - A and has been placed in GF#1 tblReps.RepName - A.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 May 2009 at 9:52am |
Sorry, I should proof read better before I post
I meant are there any rows that are being conditionally suppressed (or just suppressed) that would impact your group level totals? Suppressing records has no impact on a RT, it will still include them in any calculations.
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 13 May 2009 at 10:21am |
Oooo, yes, GH#3 tblInvoices.InvoiceNumber has this suppression configured: IsNull ({tblAccounts.AccountName}). I removed the suppression but it didn't make a difference.
Maybe this helps, the detail fields are listed under GH#3 instead of the "Details" section to allow for individual invoice listing per rep.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 May 2009 at 11:50am |
So is this your set up?
GH1: Rep Name
GH2: Invoice Number
GH3: AccountName (suppressed if NULL)
Details: (suppressed)
GF3: no running total
GF2: Correct RT if {tblInvoiceDetail.Type} = ["S", "O", "M", "A"]
GF1: Incorrect RT if {tblInvoiceDetail.Type} = ["S", "O", "M", "A"]
Edited by DBlank - 13 May 2009 at 11:51am
|
IP Logged |
|
psalm19
Groupie
Joined: 19 Feb 2009
Online Status: Offline
Posts: 48
|
Posted: 13 May 2009 at 12:05pm |
Close.
GH2: AccountName
GH3a: InvoiceNumber (suppress if AccountName is NULL)
GH3b: Various Fields Display Here (suppress if AccountName is NULL)
GF3: Hidden
GF2: Line
GF1: Rep Totals
|
IP Logged |
|
|