Author |
Message |
junkbox
Newbie
Joined: 07 Nov 2012
Online Status: Offline
Posts: 17
|
Topic: Wonky Running Total Workaround Posted: 05 Dec 2012 at 7:40am |
here's the basic setup of each column of the report (strike through means it's suppressed):
Group Header - Day
Detail - table.covers Group Footer - #table.covers <-- Running Total which sums with the below evaluate formula Report Footer - #table.covers <-- Running Total which sums with same conditions but never resets
Because of the way the application tracks totals throughout the day by 'periods' (breakfast, lunch, dinner) the table.covers ends up ends up repeating in the details band. To alleviate that from interfering with the summary, the running total uses a simple evaluate formula: {table.covers}<>Previous{table.covers} and has 'Default values for NULL'. The group one resets on change of day and the report footer one never resets. That works great:
(unsurpressed view) Date Covers 180 180 180 01/01/2012 180
Except...(you knew it was coming) on the rare occasion where the exact same number of covers happened the day before.
Date Covers 180 180 180 01/01/2012 180 147 147 147 01/02/2012 147 147 147 147 01/03/2012 0 <----
Now in my mind, I would think that reset on group (day) would start the entire evaluation over but apparently the {table.covers}<>Previous{table.covers} evaluation criteria in the #running total does not respect the change of group. It may be that Previous() and Next() in and of themselves don't take group into consideration.
So my question is, now can I make Previous() group aware if at all? Or alternatively how can I evaluate the running total on only distinct values? I can't rap my skull around how the classic 3 formula (reset,calc,display) would work in this situation either, I tried it and maybe my formulas were wrong but it didn't work either.
Any Ideas?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Dec 2012 at 8:08am |
so you always have 3 rows of data per day that are always the same and you want to show the sum (one row) per day and also the sum of all days.
your group level running total can just be a max of the field reset per group
the total can be a sum of the field
with an evaluate = on change of group (day) and reset=never.
Edited by DBlank - 05 Dec 2012 at 8:09am
|
IP Logged |
|
junkbox
Newbie
Joined: 07 Nov 2012
Online Status: Offline
Posts: 17
|
Posted: 05 Dec 2012 at 8:56am |
well there's not *always* only 3 there could be as many as 5, but yes you are correct, hide the details, daily total in the GF and a Grand Total. I thought of this, but got stumped on the grand total. Max works fine for Group totals, but my Grand Total was still off using sum, evaluate {table.covers}<>Previous{table.covers} (I have to have that evaluation in there otherwise each multiple field is totaled), reset never. Like I say though the sum was off. Which is likely another issue all together...wait...lemme look at that... Well, hell...that explains alot. The above would work except some of the details are staggered. i.e. Date Covers 180 0 <-- makes the Previous() evaluation false 180 0 01/01/2012 360 <--- that's why my grand is bad 147 147 147 01/02/2012 147 147 147 147 01/03/2012 0 <-- the original issue So really, it's not a different issue but the inherently the same in the end. I need a way to rtotal/summarize ONLY unique {table.covers}. First thought on the above would be to just sort by {table.covers} so Previous() would pick it up but I'm already sorting by another field due to the same issue and it blows up the rest of the working report. Any more ideas on being able to summarize only unique values? My google prospects are beginning to run thin... Hamaday offered an interesting solution which I may have not applied correctly, I'm going to revisit that (and will post if successful). In the meantime any pointers or solutions would be greatly appreciated.
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 05 Dec 2012 at 9:09am |
actually DBLANK was right, if you just look at the max, it doesn't matter how many records are in the details, just make a running total or sql expression that will just show the max value in the group footer, not a sum.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Dec 2012 at 9:11am |
for your report total do a descending sort on the 'covers' field
use a runningtotal
name=whatever
field to summarize=covers
type=sum
evalaute=on change of group(day)
reset=never
|
IP Logged |
|
junkbox
Newbie
Joined: 07 Nov 2012
Online Status: Offline
Posts: 17
|
Posted: 05 Dec 2012 at 10:19am |
Originally posted by comatt1
actually DBLANK was right, if you just look at the max,
it doesn't matter how many records are in the details, just make a
running total or sql expression that will just show the max value in the
group footer, not a sum. Right that works fine for the group total but for a grand total, It'll only show the maximum of largest group wide. i.e. 100 0 100 100120 120 0 120120
Originally posted by DBlankfor your report total do a descending sort on the 'covers' field
use a runningtotal
name=whatever
field to summarize=covers
type=sum
evalaute=on change of group(day)
reset=never Right, like I said in my second post, that'd be optimal, but I can't sort by {table.covers} as it affects other fields I'm sorting by to alleviate the same. Mean to say this is a recurring problem with this database schema and the way it stores records (multiples of everything) dunno, guys I'm stumped.
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 05 Dec 2012 at 10:28am |
Originally posted by junkbox
Originally posted by comatt1actually DBLANK was right, if you just look at the max,
it doesn't matter how many records are in the details, just make a
running total or sql expression that will just show the max value in the
group footer, not a sum.Right that works fine for the group total but for a grand total, It'll only show the maximum of largest group wide. i.e.100 0100 100120120 0 120120
Originally posted by DBlankfor your report total do a descending sort on the 'covers' field
use a runningtotal
name=whatever
field to summarize=covers
type=sum
evalaute=on change of group(day)
reset=never Right, like I said in my second post, that'd be optimal, but I can't sort by {table.covers} as it affects other fields I'm sorting by to alleviate the same. Mean to say this is a recurring problem with this database schema and the way it stores records (multiples of everything)dunno, guys I'm stumped.
to get the grand total create 3 formulas
1 - instantiate a variable to store the total
2 - populate the max group value with the variable
3 - show the value in the report footer
1. func1 (put in report header)
shared numbervar tot1:=0;
2. func2 (put in group footer)
shared numbervar tot1:=tot1;
tot1:=tot1+{RUNNING TOTAL of MAX in GROUP FOOTER};
3. func3 (put in report footer)
shared numbervar tot1:=tot1;
tot1
--- Don't think you NEED to declare the variable in each area, but I do as a habit.
Edited by comatt1 - 05 Dec 2012 at 10:28am
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Dec 2012 at 10:30am |
correct.
I would then use 3 variable formulas
first sets x:=0 (in the report header)
second adds x:= x + maximum(field,group) placed in group footer
third displays final x result in the group footer
Edited by DBlank - 05 Dec 2012 at 10:33am
|
IP Logged |
|
junkbox
Newbie
Joined: 07 Nov 2012
Online Status: Offline
Posts: 17
|
Posted: 05 Dec 2012 at 11:38am |
[insert Legend of Zelda found secret sound]
Awesome. Like I said in my first post:
Originally posted by junkbox
I can't rap my skull around how the classic 3 formula (reset,calc,display) would work in this situation either, I tried it and maybe my formulas were wrong but it didn't work either.
I knew what I needed to do but couldn't get it quite right, the grouping was blowing my mind. Works flawless, thanks for both of your time and expertise.
I only ask for help when I'm really stuck, and end up here from google searches more often than not, but only recently joined up. I think I'll stick around, this is a great resource. I'll help where I can.
Again, thanks I spent too many hours trying different approaches now it's just a matter of formatting and scheduling and the dishes are done.
-jb
Edited by junkbox - 05 Dec 2012 at 11:38am
|
IP Logged |
|
|