Solved

Running Total by group 2

Posted on 2011-03-07
26
310 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
Comment Utility
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
Comment Utility
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
Comment Utility
What are @TimeWork and @Travel?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes it should.
That's what happens when you type slower than you think

mlmcc
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:James0628
Comment Utility
That's why I try to think slowly.  :-)

 James
0
 

Author Comment

by:GeorgeSalet
Comment Utility
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
Comment Utility
:-)
0
 

Author Comment

by:GeorgeSalet
Comment Utility
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
Comment Utility
Max should be Maximum

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

mlmcc
0
 

Author Comment

by:GeorgeSalet
Comment Utility
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
Comment Utility
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
Comment Utility
GroupFIeld is the name of you field for the group.

mlmcc
0
 

Author Comment

by:GeorgeSalet
Comment Utility
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
Comment Utility
No, Just select the field from the field list in the formula editor

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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
Comment Utility
You're welcome.  Glad I could help.

 James
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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. …
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

10 Experts available now in Live!

Get 1:1 Help Now