Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Division by Zero 5
- Thread starter tdiboll
- Start date
Have you tried using a running total for {@BAC}? Set it up to sum {Virtual RRM.rrmTDlrs} and evaluate using a formula where {Virtual RRM.rrmValueType} = 'S' then resetting for each group. Change your {@test} formula to be:
{@BCWP}/{#RTotal}
{@BCWP}/{#RTotal}
My formula for @BAC is:
IF {Virtual RRM.rrmValueType} = 'S' THEN {Virtual RRM.rrmTDlrs} ELSE 0
my formula for @BCWP is:
IF {Virtual RRM.rrmYm} <={?ReportMonth} AND {Virtual RRM.rrmValueType} = 'P' THEN {Virtual RRM.rrmTDlrs} ELSE 0
It calculates them correctly in the report but when I try to divide one into the other...I get 0's.
IF {Virtual RRM.rrmValueType} = 'S' THEN {Virtual RRM.rrmTDlrs} ELSE 0
my formula for @BCWP is:
IF {Virtual RRM.rrmYm} <={?ReportMonth} AND {Virtual RRM.rrmValueType} = 'P' THEN {Virtual RRM.rrmTDlrs} ELSE 0
It calculates them correctly in the report but when I try to divide one into the other...I get 0's.
-
1
- #23
Ok, I'll jump in here...something has been bugging me about this problem (and this may be a red herring, so please forgive me if it is):
Generally, when you have grouping in the report, and you want to do a calculation in the group footer, you would base your calculation on the sums (or some other summary) of the detail records for that group. For example your formula (without the null checks, mind you), should look something like
sum({@BCWP},{table.task#})/sum({@BAC},{table.task#}
Now, if your group only has one record, or you're only interested in the last record of the group, then putting say, one of synapse's formulas in the group footer (as he always gets this stuff write given the correct problem description) should work. Do you only have one record per group?
On the other hand, if you have multiple records per group, there is no way your calc is going to be correct, regardless of your trapping routine. You'll only show the results for the very last record in the group.
Generally, when you have grouping in the report, and you want to do a calculation in the group footer, you would base your calculation on the sums (or some other summary) of the detail records for that group. For example your formula (without the null checks, mind you), should look something like
sum({@BCWP},{table.task#})/sum({@BAC},{table.task#}
Now, if your group only has one record, or you're only interested in the last record of the group, then putting say, one of synapse's formulas in the group footer (as he always gets this stuff write given the correct problem description) should work. Do you only have one record per group?
On the other hand, if you have multiple records per group, there is no way your calc is going to be correct, regardless of your trapping routine. You'll only show the results for the very last record in the group.
FV et. al,
I've tried something similar on a dummy report (name all my reports after me) and you are correct that it gets trapped on the last record (i.e. it's always true and set to 'x' or false and set to 0) so the final value (even though the logic is correct) will give a division by zero or 100 as the answer. Using running totals for the two base formulas ignored null and 0 values then I inserted a formula to calculate ({#Rtotal0}/{#Rtotal1})*100 and it returned the correct values. All of you have been at this longer than me, so if I'm completely off-base or out of line, let me know and I'll bow out but it seems like running totals may do the trick.
I've tried something similar on a dummy report (name all my reports after me) and you are correct that it gets trapped on the last record (i.e. it's always true and set to 'x' or false and set to 0) so the final value (even though the logic is correct) will give a division by zero or 100 as the answer. Using running totals for the two base formulas ignored null and 0 values then I inserted a formula to calculate ({#Rtotal0}/{#Rtotal1})*100 and it returned the correct values. All of you have been at this longer than me, so if I'm completely off-base or out of line, let me know and I'll bow out but it seems like running totals may do the trick.
FV Trainer,
No, I do not have one record per group. I am extracting data for a given month for the BCWP (it's cumulative to date) and the BAC basically sums all the data per group to give me total budget.
midearth,
The running total method almost did the trick...I am getting data, however the % complete is not correct except for the very first record. The calculation compares the running totals, not the total for each record. Can I write another formula to subtract #R-BCWP#2 from #R-BCWP#1 to extract BCWP#2?
No, I do not have one record per group. I am extracting data for a given month for the BCWP (it's cumulative to date) and the BAC basically sums all the data per group to give me total budget.
midearth,
The running total method almost did the trick...I am getting data, however the % complete is not correct except for the very first record. The calculation compares the running totals, not the total for each record. Can I write another formula to subtract #R-BCWP#2 from #R-BCWP#1 to extract BCWP#2?
Are you resetting the RT on each group? The way I did it (using your syntax) was:
{#RTotal0} --> sum {Virtual RRM.rrmTDlrs}--> evaluate using x+2 where {Virtual RRM.rrmYm} <={?ReportMonth} AND {Virtual RRM.rrmValueType} = "P" --> reset for each group {TASK}
{#RTotal1}--> sum {Virtual RRM.rrmTDlrs}--> evaluate using x+2 where {Virtual RRM.rrmValueType} = "S" --> reset for each group {TASK}
I then created a formula,{@%}, which was ({#RTotal0}/{#RTotal1})*100
I placed both RT's and the {@%} formula in the group footer and it worked fine. My data in the dummy report had 0's, Null and two different logics similar to {Virtual RRM.rrmValueType}, "W" or "S".
{#RTotal0} --> sum {Virtual RRM.rrmTDlrs}--> evaluate using x+2 where {Virtual RRM.rrmYm} <={?ReportMonth} AND {Virtual RRM.rrmValueType} = "P" --> reset for each group {TASK}
{#RTotal1}--> sum {Virtual RRM.rrmTDlrs}--> evaluate using x+2 where {Virtual RRM.rrmValueType} = "S" --> reset for each group {TASK}
I then created a formula,{@%}, which was ({#RTotal0}/{#RTotal1})*100
I placed both RT's and the {@%} formula in the group footer and it worked fine. My data in the dummy report had 0's, Null and two different logics similar to {Virtual RRM.rrmValueType}, "W" or "S".
So are you saying {Virtual RRM.rrmTDlrs} is a cumulative field (i.e., the value in each successive record is the total amount recorded to date)?
For example, if you had four records for a task, and they were sorted by date, you would see something like this in the detail:
Date rrmTDlrs
---- --------
10/1/03 100
10/2/03 125
10/3/03 150
10/4/03 200
(of course rrmTDlrs could be null or zero until there is an initial amount recorded...)
If using running totals is helping to resolve this issue, then there is something that is not obvious from the way the problem has been presented to us. That's not to throw blame at anyone, but this problem does not sound difficult...there has to be a missing piece to this puzzle. Also, knowing what fields are being placed in the details and what the detail data looks like would be helpful.
For example, if you had four records for a task, and they were sorted by date, you would see something like this in the detail:
Date rrmTDlrs
---- --------
10/1/03 100
10/2/03 125
10/3/03 150
10/4/03 200
(of course rrmTDlrs could be null or zero until there is an initial amount recorded...)
If using running totals is helping to resolve this issue, then there is something that is not obvious from the way the problem has been presented to us. That's not to throw blame at anyone, but this problem does not sound difficult...there has to be a missing piece to this puzzle. Also, knowing what fields are being placed in the details and what the detail data looks like would be helpful.
The formulas for @BAC and @BCWP:
@BAC = IF {Virtual RRM.rrmValueType} = 'S' THEN {Virtual RRM.rrmTDlrs} ELSE 0
@BCWP = IF {Virtual RRM.rrmYm} <={?ReportMonth} AND {Virtual RRM.rrmValueType} = 'P' THEN {Virtual RRM.rrmTDlrs} ELSE 0
...are located in the details field.
In the group (WBS) footer field is 'Sum of @BAC' and 'Sum of @BCWP'. I also used a parameter so I could specify which month to cum data to, called ?ReportMonth, which is only necessary in the @BCWP formula.
@BAC = IF {Virtual RRM.rrmValueType} = 'S' THEN {Virtual RRM.rrmTDlrs} ELSE 0
@BCWP = IF {Virtual RRM.rrmYm} <={?ReportMonth} AND {Virtual RRM.rrmValueType} = 'P' THEN {Virtual RRM.rrmTDlrs} ELSE 0
...are located in the details field.
In the group (WBS) footer field is 'Sum of @BAC' and 'Sum of @BCWP'. I also used a parameter so I could specify which month to cum data to, called ?ReportMonth, which is only necessary in the @BCWP formula.
And 'Sum of @BAC' and 'Sum of @BCWP' are Running Total fields along the lines of what MidEarth created?
If so, that makes sense, assuming you want to show the total of {@BAC} without regard to the month (or up to whatever date your record selection allows).
If so, that makes sense, assuming you want to show the total of {@BAC} without regard to the month (or up to whatever date your record selection allows).
synapsevampire
Programmer
Running Totals aren't required, but of course they will also work.
This simple task has turned into too much sillyness, glad that you got something working.
-k
This simple task has turned into too much sillyness, glad that you got something working.
-k
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question