Formula not working - Help please

[boeloe]

Hi,

the following formula is not returning the expexted result.
I'm using Crystal Reports 9 with MySql as the database.

***********************************************************
whileprintingrecords;
numbervar intMonths:=0;

if Sum ({@Period01}, {SLA_Summary.Metrics}) <> 0 then
intMonths := intMonths + 1;
if Sum ({@Period02}, {SLA_Summary.Metrics}) <> 0 then
intMonths := intMonths + 1;
if Sum ({@Period03}, {SLA_Summary.Metrics}) <> 0 then
intMonths := intMonths + 1;
if Sum ({@Period04}, {SLA_Summary.Metrics}) <> 0 then
intMonths := intMonths + 1;
if Sum ({@Period05}, {SLA_Summary.Metrics}) <> 0 then
intMonths := intMonths + 1;
**********************************************************

In the report i've got a 12 month array containing a summarized Running Total per metric per month. For months we haven't got any for yet it displayes a blank field (null value) as well as where the the summary is a real zero (0).
The formula above simply needs to calculate the number of months with valid values, including the zero values which are shown as a blank field.

What am i doing wrong or simply miss?

Oh yes, how do i get the summary running total fields to display the "0" when it really is a zero and not a Null. The normal Crystal reports options to convert other null values to default shows all the blank fields with a "0.00"

Any help will be appreciated.

Thanx
JPS

 

[Katy44]

I don't think I understand the problem, you say "The formula above simply needs to calculate the number of months with valid values, including the zero values ", which is fine, but then your formula excludes zeros.
Also, if you think nulls may be causing your problems, you can use "if isNull(Sum...)" or "if not isNull(...

 

[boeloe]

Thanx, maybe i wasn't clear enough

The report currently displays as follows:
P1 P2 P3 P4 ------ P12 YTD
Metric-A 90.92 98.5 94.7

Problem 1: P2 has got a "0" value but report shows blank.
Problem 2: P4 to P12 needs to stay blank (null values in db) but have to show the "0.00" value in P2.
Problem 3: YTD must calculate the average but currently only calculates it with P1 & P3 and ignores P2 which it needs to do.

Report is supposed to display as follows:
P1 P2 P3 P4 ------ P12 YTD
Metric-A 90.92 0.00 98.5 63.14

Hope this makes it more understandable.

 

[Katy44]

I see what you mean now, sorry. So if you select the P2 value out of the database, do you get a zero? I don't understand why the report would display that as a null.

 

[boeloe]

Yes, i get the zero when getting the value from the database. However, on the report i group per Metric and have an average running total displayed in the group footer which is the values appearing per month.
In this running total i evalute each value that is not a zero. This could be my problem but at the moment i don't have an alternative due to Crystal returning a lot of zero values when populating the 12 month array in the details section (This section is suppressed on the report).