Problems with running total and charts

[SBpsc]

I am having some problems with running totals and charting and I hope someone can help me.
I am using MySQL 4.1.5 and Crystal Reports XI.


Trying to chart the actual cost per month.

Currently, I have the following formulas:

ActualCost:If type = 'a' then CCur(mycost) else CCur(0);


I also have a running total formula called OverallActualCost where I summed the ActualCost.
For Evaluate (using the formula option): mydate <= givendate
For Reset (using the formula option): mydate > givendate

For example, I have the following information:

mydate | ActualCost| OverallActualCost | givendate
March 2006 | 100 | 100 | may 2006
April 2006 | 100 | 200 | may 2006
May 2006 | 150 | 350 | may 2006
June 2006 | 100 | 450 | may 2006
July 2006 | | 450 | may 2006


(type,mydate, and mycost are fields in the database)



So, now my chart displays:
100 for march, 200 for april, 350 for may, NULL for june, and NULL for july. (This is exactly what I want).

However, if I have no records that match type 'a' then
my chart displays 0 for march, 0 for april, 0 for may, 0 for june, and 0 for july. So, now it looks like work has been done in the future.
(Why does it display 0 and not NULL for june and july?). Any ideas on what I am doing wrong?

Hope I explained this well. Thanks for your time.

- SB

 

[lbass]

I'm not sure whether this will address the issue, but it might be worth a try. Create a formula {@null} by choosing formula->new and then saving the formula with nothing in it. Then in your original formula, use:

If type = 'a' then CCur(mycost) else CCur(@null})

This should at least result in a null versus a 0 on the chart--not sure that was your issue though.

-LB

 

[SBpsc]

Thanks, LB. It did not fix the problem by itself but I added on a few more formulas with it and was able to get the charts to display correctly.

Thanks for the advice.
- SB