Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Formula Based Running Total Post Reply Post New Topic
Author Message
KathleenD
Newbie
Newbie


Joined: 19 Mar 2009
Location: United States
Online Status: Offline
Posts: 5
Quote KathleenD Replybullet Topic: Formula Based Running Total
     Posted: 19 Mar 2009 at 8:15am

Hello Everyone,

 

I’m having a problem with a Running Total based on a formula.  I’m trying to sum {tasks.hours} when

 

{@Child Count Total} = 0

and {@Child Record} = FALSE

 

I have {@Child Count Total} in group footer 2 along with {@Child Record} so I can see their values. Even though group 2 does not fit the criteria the Running Total is still capturing the {tasks.hours}

 

AARGH! What am I doing wrong?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Mar 2009 at 10:35am
What are your Formulas?
I think you may be trying to use a Summary Count in {@Child Count Total} formula and then use that in the running total. Not sure if you can do this as the running total may be reading the records before the summary is happening hence the first part of your running total condition is not being met. Can you explain the data and then how you want it conditionally summed?
IP IP Logged
KathleenD
Newbie
Newbie


Joined: 19 Mar 2009
Location: United States
Online Status: Offline
Posts: 5
Quote KathleenD Replybullet Posted: 19 Mar 2009 at 11:43am

We track the number of hours technicians have worked on a project.  Some projects (parents) have multiple entries (children), which may have differing technicians.  Each child has hours associated with them that roll up to the project parent.  If there are no children then the project parent will have its own hours associated with it.  

 

We are trying to capture the total number of hours that a technician worked during a given month without double counting the project parents without children and project parents with children.  For example the data would like something like -

 

Record 1) Tech: X01 Parent: 1234  Child: 1234 Hr: 1.5

Record 2) Tech: X01 Parent: 1234  Child: 2345 Hr: 1

Record 3) Tech: X02 Parent: 1234  Child: 3456 Hr: .5

Record 4) Tech: X01 Parent: 5555  Child: 5555 Hr: 1

 

I have several formula fields –

 

{@Child Count} stored in Detail Section -

whilereadingrecords;

Global NumberVar ChildCt;

if {@Child Record} = TRUE

then ChildCt := ChildCt + 1

 

{@Child Count Total} stored in Group Footer

whilereadingrecords;

Global NumberVar ChildCt;

ChildCt;

 

{@initval} stored in Group Header

Evaluateafter ({@Child Count Total});

Global NumberVar ChildCt := 0;

 

The Project Parent Running Total Evaluates on the following placed in the “Use a formula”

 

{@Child Count Total} = 0

and {@Child Record} = FALSE

 

and Resets on change of group

 

If a project parent has children I do not want to add in the hours found on the parent record. So in my example above, Tech: X01 would get hours credited for

Record 2 and 4, 2-hrs. and Tech: X02 would get credit for Record 3, .5-hrs.

 

What is happening my Project Parent Running Total is picking up ALL Project Parent hours even if the {@Child Count Total} = 0, so Tech: X01 is getting credit for 3.5-hrs.

 

I hope that helps . . . and any help would be greatly appreciated!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Mar 2009 at 1:28pm
Yuck...This is a fun one.
Again I am pretty sure your totals are not working because yuor formulas are hapening "while printing records" and therefore the ultimate count or soem do not exist when the running totals happen so it isn't working.
Seems like it might be better handled in a view or stored procedure but this might work...
Group on ParentID# for Group1
Group on Tech for Group2
Do a Count Summary function on ParentID# and place in GF1.
Select the SummaryCount field and and clcik on Select Expert.
It should default to the Count of field and the Group Selection toggle button should be activated.
For now just put in Is Greater than 1.
Click on Edit formula (make sure you are in Group Selection)
adjust the fomrula to be:
 
if Count ({table.ParentID#field}, {table.ParentID#field}) > 1 then {table.ParentID#field}<>{table.ChildID#field}
 
Hopefully this will drop out the Parent if there are any children and leave it when there are not any and now let you just SUM the hours per worker grouping.
Let me know if it works.
IP IP Logged
KathleenD
Newbie
Newbie


Joined: 19 Mar 2009
Location: United States
Online Status: Offline
Posts: 5
Quote KathleenD Replybullet Posted: 20 Mar 2009 at 5:20am

Thanks so much for your suggestions . . . I altered my grouping setting as you suggested and added the Count Summary function on the ParentID# and placed it in GF1.  I received “This function cannot be used because it must be evaluated later” message when trying to adjust the formula.   Working through my frustration, I stumbled onto something else and now maybe you can help me with this . . .

 

My original grouping is first by Tech and then by Parent.

I was able to isolate my Parent Hours by creating variables both are in GF2. 

 

whilereadingrecords;

Global NumberVar ParentHrs :=0;

 

If ({@Child Count Total}) = 0 then

ParentHrs := ParentHrs + {TASKS.HOURS};

 

My problem is how can I get a running total of the ParentHrs?  I tried creating another variable -

 

Global NumberVar ParentHrs;

Global NumberVar ParentHrTotal;

 

ParentHrTotal := ParentHrTotal + {@ParentHrs}

 

But it is not totaling just what is set in the variable at the time that ParentHrs prints.  It is capturing the total hours found on ALL parent records. 

 

I feel that I’m so close to a solution, but have run out of ideas!  Please HELP!!!
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 20 Mar 2009 at 6:26am
your variable is doing what you told it to...don't you hate that.  It sounds like you want it to reset to 0 at some point (probably in a group header).
 
Regardless of location, as long as you know where you want it start over in its count, create another formula (mine are usually called reset) like:
Global NumberVar ParentHrTotal := 0;
""   //I put in the quotes so that nothing is displayed on the report
 
then drop the formula where you want the counter to be reset.
 
Hope this helped
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Mar 2009 at 6:37am
Thanks for the help Lockwelle.
Hopefully that will clear it up going doen that path..
As another option, with my previous suggestion to use the Group Selection criteria there are some specifics that you have to follow for that to work. From what I can figure out, in order to use a group condition (like a count) you must use the Insert Summary function to get your count. When you do this it will appear as an option to use in the select expert. If you just create a summary formula field you cannot use it. If you try to just type in the summary into the selection you will get the "evaluate later" error. To avoid this use the summary function and create a Count. Grab that Summary from the list of report fields in the Select Expert and make it >1. Save it. Go back into the Formula editor (make sure you have it set to Group Selection) and Edit it by adding the additional "If-Then" noted above around the existing selection criteria and you should not get the error.
Hope that helps.


Edited by DBlank - 20 Mar 2009 at 6:40am
IP IP Logged
KathleenD
Newbie
Newbie


Joined: 19 Mar 2009
Location: United States
Online Status: Offline
Posts: 5
Quote KathleenD Replybullet Posted: 20 Mar 2009 at 7:46am

Well that makes sense, but wouldn’t I want to initialize the ParentHrTotals at GH1, which is my Tech level; since I want a total of all parent hours by Tech.  Using this example,

 

Tech: X01

Record 1) Parent: 1234  Child: 1234 Hr: 1.5

Record 2) Parent: 1234  Child: 2345 Hr: 1

           

My variables are picking up -    ParentHrs à 0.00 ParentHrTotal à 1.5

 

 

Tech: X01

Record 3) Parent: 5555  Child: 5555 Hr: 1

 

My variables are picking up -    ParentHrs à 1.00 ParentHrTotal à 2.5

 

 

For the Parent 1234 I was expecting that the ParentHrTotal = 0.00 and then when it hit Parent 5555 = 1.00.  In other words, adding 0 to ParentHrTotal when ParentHrs is 0, not simply adding the hours from the parent hrs.

 

What am I missing?

 
IP IP Logged
KathleenD
Newbie
Newbie


Joined: 19 Mar 2009
Location: United States
Online Status: Offline
Posts: 5
Quote KathleenD Replybullet Posted: 20 Mar 2009 at 8:20am
Hi Again . . . I figured out my problem! I needed to make my ParentHrTotal variable a shared variable! I knew it was something simple!
 
DBlank and Lockwelle thanks so much for spending the time trying to help me.
 
Kathleen
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.