| Author |
Message |
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
   Topic: Sum total hours
Posted: 20 Oct 2013 at 9:48pm |
|
Hi,
I'm trying to get the total of {Job_Operation_Time.Act_Run_Labor_Hrs} based on a filter which uses date parameters. The total hours is based on a relationship between {Transaction_Detail.Job} and {Job_Operation_Time.Act_Run_Labor_Hrs}.
I then need to take that value and display it in relation to the date parameters selection I have created.
I was wondering if anyone could help me with this please? I appreciate there may be more detail required but being a complete novice at this I'm unsure what.
Thanks as always :)
Edited by shabbaranks - 20 Oct 2013 at 9:52pm
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 21 Oct 2013 at 5:06am |
|
I would think that you select your tables, link them together, then in the Report/Selection Formulas/Record section you would filter the data based on if the date of the record in the database is IN the range of the parameters.
something like:
{table.field} in {?startDate} to {?endDate}
I might be off in my syntax, check Help for the Ranges and follow the data example
HTH
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 22 Oct 2013 at 4:54am |
Thanks, Lockwelle. Im struggling to get the report to sum value {Job_Operation_Time.Act_Run_Labor_Hrs} based on the date parameter selection I have setup.
Would I need to create the formula to work out the sum and then apply that to the report? Or is there a different\proper way to achieve it?
I created the formula and when I add it to the report all my values (work date and text.1value) disappear. If I remove the totalhours formula from the report they come back again :S
Thanks for you help, its appreciated :)
Edited by shabbaranks - 22 Oct 2013 at 5:20am
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Oct 2013 at 5:08am |
|
It all depends on the data. If you want the sum of all records in a group or the report, then sum should work just fine...but you will need to filter the data to just what you want.
If you have more data that appears on your report than you want to sum, then a running total or formulas that utilize shared variables would be applicable.
Running totals confuse me, for some reason...DBlank is the master of them.
Shared variables are easy to me...they tend to come in sets of 3 formulas: reset, increment, display
reset: usually group header
shared numbervar x:=0;
"" // will hide the 0 displaying on the report
increment: usually in details
shared numbevar x;
if {table.field} = something then
x := x + {table.field2};
"" //again will hide the running total
display: usually in a group footer
shared numbervar x
HTH
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 22 Oct 2013 at 5:27am |
Is it bad practice to store the complete report in the detail pane? This is how I have setup my "testing" report. I think I need to display rather than reset or increment.
The query (as I have it in my head) is show total hours for projectcode x. This will show the total hours per projectcode per day based on the date field.
Also, is it possible to create a formula if total hours is >=0 then put some text in a field?
Thanks :)
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Oct 2013 at 5:38am |
|
1) if the complete is in the detail pane, are you filtering for projectcode x...if you set up a group, then you can subtotal by each projectcode.
2) sure.
shared numbervar x;
if x >= 0 then
"some text"
else
"some other text" //or "" or don't even bother with the else
HTH
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 23 Oct 2013 at 10:08pm |
Hey lockwelle, just wanted to say thanks again - Im actually making progress!! Managed to get the majority of values onto my report. Its strange because Ive imported my tables the same way every time. When I start to drop the values on to the report I get the expected results.
The only difference being that instead of designing the whole report within the details section, Im using the headers etc - and amazingly it works :)
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 24 Oct 2013 at 2:57am |
Originally posted by lockwelle
It all depends on the data. If you want the sum of all records in a group or the report, then sum should work just fine...but you will need to filter the data to just what you want.
If you have more data that appears on your report than you want to sum, then a running total or formulas that utilize shared variables would be applicable.
Running totals confuse me, for some reason...DBlank is the master of them.
Shared variables are easy to me...they tend to come in sets of 3 formulas: reset, increment, display
reset: usually group header
shared numbervar x:=0;
"" // will hide the 0 displaying on the report
increment: usually in details
shared numbevar x;
if {table.field} = something then
x := x + {table.field2};
"" //again will hide the running total
display: usually in a group footer
shared numbervar x
HTH
Me again!
Still working my way through this one but I am making progress. I managed to create my code based on your above formula, but what I need to do for another field is take it a step further.
I have a range of codes within a table some are numeric "1234" and others are alphanumeric "ABC123".
How would I create a formula which will show codes ABC123 but supress the numeric ones? I've read up about null values but obviously they aren't null. Thanks!
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 24 Oct 2013 at 4:52am |
|
are you suppressing the whole row or just the field?
the strategy is the same, the where to apply changes.
in either the Section Expert of the Format Object, find Suppress, click the x-1, enter:
isumeric({table.field})
if the field is a number, this will result in a true, and the item will be suppressed.
There is a caveat, at least in SQL, a number like 123E4 can be considered numeric as it might be in scientific notation...just something to be aware of.
HTH
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 24 Oct 2013 at 10:16am |
Worked like a charm!! Whens your book out - thanks 
|
|
IP Logged |
|
|
|
