Solved

Running Total by group 2

Posted on 2011-03-07
26
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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 35

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 35

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 35

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 35

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 35

Expert Comment

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

 James
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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