x
Solved

# Running Total by group 2

Posted on 2011-03-07
Medium Priority
323 Views
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
Question by:GeorgeSalet
• 10
• 9
• 6
• +1

LVL 101

Expert Comment

ID: 35063752

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

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 101

Expert Comment

ID: 35065935
What are @TimeWork and @Travel?

mlmcc
0

LVL 35

Expert Comment

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

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

//@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

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.

George
0

LVL 101

Expert Comment

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

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 101

Accepted Solution

mlmcc earned 1200 total points
ID: 35072530
Try this

WhilePrintingrecords;
Global NumberVar GroupsWithTime := 0;
""

//@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

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 101

Expert Comment

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

Yes use the WhilePrintingReccords

mlmcc
0

LVL 35

Assisted Solution

James0628 earned 800 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 101

Expert Comment

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

mlmcc
0

LVL 35

Expert Comment

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

James
0

Author Comment

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

George
0

LVL 35

Expert Comment

ID: 35106303
:-)
0

Author Comment

ID: 35117249
I am having some problems with Mimcc: code
//@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 101

Expert Comment

ID: 35117685
Max should be Maximum

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

mlmcc
0

Author Comment

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

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 101

Expert Comment

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

mlmcc
0

Author Comment

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 101

Expert Comment

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

mlmcc
0

LVL 35

Expert Comment

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

ID: 35118774
Wow
Thanks for all the help!!!
I split the credit because Mimcc: formulas would not work without James input.
Thanks Again
George
0

LVL 35

Expert Comment

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

James
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Watch the software video of Kernel Import PST to Office 365 tools which can easily import PST and OST files to Office 365 for bulk mailboxes. The process of migration is simple and user can map source and destination mailboxes and easily import data…
Watch the video which demonstrates the easy migration process from GroupWise to Outlook with the help of Kernel Novell GroupWise to Outlook software. User can export single or multiple mailboxes either by Online mode or via Command line mode with ea…
###### Suggested Courses
Course of the Month5 days, 5 hours left to enroll