Sum total hours
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20136
Printed Date: 03 May 2024 at 1:44am
Topic: Sum total hours
Posted By: shabbaranks
Subject: Sum total hours
Date 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 :) |
Replies:
Posted By: lockwelle
Date 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 |
Posted By: shabbaranks
Date 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 :)
|
Posted By: lockwelle
Date 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 |
Posted By: shabbaranks
Date 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 :)
|
Posted By: lockwelle
Date 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 |
Posted By: shabbaranks
Date 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 :)
|
Posted By: shabbaranks
Date 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!
|
Posted By: lockwelle
Date 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 |
Posted By: shabbaranks
Date Posted: 24 Oct 2013 at 10:16am
Worked like a charm!! Whens your book out - thanks 
|
Posted By: shabbaranks
Date Posted: 24 Oct 2013 at 10:42am
|
Ok this next question is going to take some explaining.
A table holds custom values for multiple other tables. These tables are linked as in the image. Now the two fields I have circled are also linked - the {User_Values.Text3} holds custom values for {Transactional_Detail.Job} If I add the {User_Values.Text3} to my report its empty but I get the {Transactional_Detail.Job} entries fine. Am I right in thinking there must be a "middle man" table which links these two tables together, and that's the only way the values can be joined? Thanks 
|
Posted By: lockwelle
Date Posted: 25 Oct 2013 at 6:55am
|
doesn't have to be. depending on the structure of the tables, TransactionalDetail could join to UserValues directly, or there could be a middle table. And again depending on the usage of the custom values the join could be either inner or outer.
there are so many ways to skin a cat |