Solved

Running Total by group 2

Posted on 2011-03-07
26
312 Views
Last Modified: 2012-05-11
Well I guess I jumped the gun without checking my results

I am trying to count the number of groups for a condition, if one of the records in the group is true the group will be true. I am using this formula then summing the formula with a running total. when there are more than 1 record and the last record is equal to 0 the formula result is 0 instead of 1

If({@TimeWork}+{@Travel})> 0 Then 1

group
  Record-1 true
  Record-0 false
returns value 0    Need value of 1

Thanks
George
0
Comment
Question by:GeorgeSalet
  • 10
  • 9
  • 6
  • +1
26 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35063752
Try changing your formula to

If({@TimeWork}+{@Travel})> 0 Then
    1
Else
    0


Your formula returns NULL for the value and hwen NULL is added to anything the result is NULL

mlmcc

0
 

Author Comment

by:GeorgeSalet
ID: 35065898
Mimcc
my problem is that the formula returns result for each record, If one record in the group is true the group should be true. My current  expression allows the last record to control the group.
Thanks
george
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35065935
What are @TimeWork and @Travel?

mlmcc
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 34

Expert Comment

by:James0628
ID: 35068039
 mlmcc,

 In my experience, if you don't include an "else" part in a formula like that, you just get the default value of the same data type as the value in the "then" part (0 in this case).  I've used countless formulas like that with no "else" and never had a problem.


 George,

 If the {@TimeWork} and {@Travel} formulas operate on the record level, then the result from that formula in a group footer will be the result from the last record in the group.  You mentioned something about using the formula with a running total, but didn't provide any details.

 If you just want to know if that formula produced 1 for any of the records in the group, you could use

Sum ({your formula}, {group field}) > 0


 James
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35068095
Create a new formul called @Null do this by opening a new formula nd closing without entering anything in the formula editor

Change your existing formula to
//@TimeTotal
If({@TimeWork}+{@Travel})> 0 Then
    1
Else
    ToNumber({@Null})

Then use this in your running total

Field to summarise -> @TimeTotal
Type of Summary -> DistinctCount
Evaluate -> On change of group 1
Reset -> Never





0
 

Author Comment

by:GeorgeSalet
ID: 35071549
I am not sure I explained what I am trying to do very clearly<GRIN>

Timework and travel represent time. I am trying to count the number of groups(Employee Dispatches) that have "positive time" some groups have no time and should be ignored.
each group could have from 1 to 20 records in it,  Simply put I want to know how many dispatches(groups) have time in them.

Thanks for all your help
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35071597
What are the 2 formulas?

When they have no time is that a NULL value or 0?

Where is the formula you are using in the report?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35071725
I am using this formula to figure the time for each record:
DATEDIFF ('s',DateTime(cdate({DispTech.DispDate}), time({DispTech.TimeOn})), DateTime(cdate({DispTech.DateOff}),time({DispTech.TimeOff})))/3600

I then have a running total at the group level R-TotalHrs that sums the records for each group

and travel running group is R-Travel

I tried using R-TotalHrs and R-Travel in the formula but CR Rejected that

George
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 300 total points
ID: 35072530
Try this

In the report header add a formula
WhilePrintingrecords;
Global NumberVar GroupsWithTime := 0;
""

Add your time testing formula
//@TimeTotal
If({@TimeWork}+{@Travel})> 0 Then
    1
Else
    0

In the group footer add a formula
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Max({@TimeTotal},{GroupField}) then
    GroupsWithTime := GroupsWithTime + 1;
""

In the report footer to display the count of groups with time
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
GroupsWithTime
   
mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35074533
Mimcc
I want to make sure I understand this, 4 separate formulas?

1st in the header.. resets memory variable?
2nd in details to populate the  variable
3rd in group footer to total count
4th in footer to display results

Do I name them the same? and is that while printing records?
Sorry to be so lame
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35074994
Yes, 4 formulas.  Give each its own name related to what they do.

Yes use the WhilePrintingReccords

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 200 total points
ID: 35081689
You still haven't said what's in the @TimeWork and @Travel formulas.  mlmcc's formulas are assuming that those formula operate at the record level.  If @TimeWork and @Travel actually use the running totals that you mentioned, his formulas won't work, because they try to take the maximum of a formula (@TimeTotal) that uses those formulas, and you can't do a summary (maximum) of a summary (running total).

 Assuming that that's not a problem, mlmcc's 3rd formula (for the group footer) should be:

WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Maximum({@TimeTotal},{GroupField}) > 0 then
    GroupsWithTime := GroupsWithTime + 1;
""

 I replaced Max with Maximum, and added "> 0".

 I think that should be ">0", or "= 1".  Right, mlmcc?


 > and is that while printing records?

 Reports are executed in multiple passes.  WhilePrintingRecords is a function that tells CR when to evaluate a formula.  Include that in each of these formulas so that they're all evaluated during the "printing" phase.  Otherwise, they might be evaluated during different phases, and (I think) a variable that's set in one phase won't be set in a different phase.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35085332
Yes it should.
That's what happens when you type slower than you think

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 35087183
That's why I try to think slowly.  :-)

 James
0
 

Author Comment

by:GeorgeSalet
ID: 35090849
And My brain is going a mile a minute just trying to keep up with your typing

George
0
 
LVL 34

Expert Comment

by:James0628
ID: 35106303
:-)
0
 

Author Comment

by:GeorgeSalet
ID: 35117249
I am having some problems with Mimcc: code
To answer James questions 1st
//@Timework
DATEDIFF ('s',DateTime(cdate({DispTech.DispDate}), time({DispTech.TimeOn})), DateTime(cdate({DispTech.DateOff}),time({DispTech.TimeOff})))/3600

//@travel
DATEDIFF ('s',DateTime(cdate({DispTech.DispDate}), time({DispTech.DispTime})), DateTime(cdate({DispTech.DispDate}),time({DispTech.TimeOn})))/3600

I created 4 formulas and recieved two errors.... not sure where to go from here

//@inv-1
WhilePrintingrecords;
Global NumberVar GroupsWithTime := 0;
""
Placed in header at runtime recieved error "A running total cannot refer to a print time formula"

//@TimeTotal
If({@TimeWork}+{@Travel})> 0 Then
    1
Else
    0
Placed in detail No errors

//@inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Max({@TimeTotal},{GroupField}) then
    GroupsWithTime := GroupsWithTime + 1;
""
Placed in group footer Recieved error: Max was highlighted "a number, currency, time is expected"

//@inv-3
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
GroupsWithTime

Placed in report footer
Thanks
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35117685
Max should be Maximum

Do you have a running total that uses inv-1?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35117720
Mimcc:
I did however since I saved the file  the only error is with the Max highlighted. When you said Max should be Maximum did you mean that literally to change it to maximum??
0
 

Author Comment

by:GeorgeSalet
ID: 35117814
Mimcc:
I changes max to maximum, now I am getting another error

//inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Maximum({@TimeTotal},{GroupField}) then
    GroupsWithTime := GroupsWithTime + 1;
""
Groupfield is highlighted with the following error "field name not known"
should I ad a number variable?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35117848
GroupFIeld is the name of you field for the group.

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35117919
Mimcc:
Well were getting close.
I tried inserting the groupname with little sucess, could be I don't know what I am doing<LOL>

here is how I changed it
//inv-2
WhilePrintingrecords;
Global NumberVar GroupsWithTime;
If Maximum({@TimeTotal},GroupName ({Dispatch.Dispatch})) then
    GroupsWithTime := GroupsWithTime + 1;
Thanks
George
""
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35118084
No, Just select the field from the field list in the formula editor

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 35118452
IOW, if your group is on {Dispatch.Dispatch}, that's what you use to replace {GroupField}.  Not GroupName ({Dispatch.Dispatch}).  Just the field name.

 James
0
 

Author Closing Comment

by:GeorgeSalet
ID: 35118774
Wow
Thanks for all the help!!!
I re-read and re-read all the info and it finally clicked
I split the credit because Mimcc: formulas would not work without James input.
Thanks Again
George
0
 
LVL 34

Expert Comment

by:James0628
ID: 35136083
You're welcome.  Glad I could help.

 James
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
I do a File --> Open and Crystal just sits there 5 62
How to add a total 6 39
Conditional Page Breaks Based On Grouping 1 30
business objects to connect to MSSQL 6 72
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now