Running Totals - any advice?

[FluffyKitty]

here's what I have...

These 2 calculated fields are located in the group footer which is grouped by USER_NAME...

[TOTAL MINUTES]
NumberVar TotalSec := datediff("s", minimum({qry_Efficenies.STATUS_DT},{qry_Efficenies.USER_NAME}), maximum({qry_Efficenies.STATUS_DT},{qry_Efficenies.USER_NAME})) ;
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600)*60;
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
Hours + Minutes

[TOTAL COST]
${TOTAL_MINUTES}*1.49

These 2 calculations work "perfectly"...it's when I attempt to create the forumula sum({TOTAL_COST}) in the REPORT FOOTER, that I get an error message "The summary/running total field could not be created.

Anyone? Thank you

 

[Reebo99]

Fluffy,

The minimum and maximum statements within TotalMinutes are summaries, and you can't create a summary from a summary.

I'm fairly sure this same query came up not so long ago and KenHamady gave a good answer as to why it doesn't work and how to get around it. Sorry I don't know the Thread number, but a search might help. Reebo
Scotland (Going mad in the mist!)

 

[mtownbound]

Fluffy,

If you find that thread that reebo is talking about, please let me know because I have the same issue. I'm trying to create a chart using a summary field that uses two other created summary fields. I'll keep my open eyes open too.

 

[FluffyKitty]

To: mtownbound...

It's Thread: 149-182

Good Luck and if you have any good feedback/shortcuts please post them. This is a common problem for many and so far this is the 'best' place to get help.

Thanks!

 

[lbass]

Here's a workaround:

1-Add an alias table (qry_Efficiencies_1)

2-Group by {qry_Efficiencies.USER_NAME}

3-Add {qry_Efficiencies.Status_DT} to group header and sort descending; add {qry_Efficiencies_1.Status_DT} to group header and sort ascending.

4-Create a formula {@DiffMaxMin} and place in group header:

datediff("n",{qry_Efficiencies_1.Status_DT}, qry_Efficiencies.Status_DT}) //gives the difference in minutes--you will need to adjust this to your desired result

5-Create a running total (#GrandTotalDiff) based on the sum of {@DiffMaxMin}, evaluate on change of group (USER_NAME), reset never.

6-Create a formula {@Cost}:
#GrandTotalDiff*1.49 //and format as currency

-LB

 

[mtownbound]

Fluffy,

Thanks, I'll try this and let you know!! Thanks again!!

 

[synapsevampire]

A quick and dirty fix is to add another variable in the group footer to sum all Total Minutes (never reset it) and display it in the report footer as the grand total...

-k