Running Total - zero count not displayed 2

[asrisk]

Hi All,

I have a Running Total field performing conditional count in my Group Footer which counts (some of) the Detail records subject to a formula, and resets on change of Group.

All well and good, but if there are no detail records which match the criteria, the count field is blank. How do I make it display zero?

I have tried:
- "Suppress if Zero" is cleared
- No other suppression formula
- I tried creating a formula which says

   if isnull({#CountField}) then 0 else {#CountField}

and using that instead, but that comes up blank too!

Any suggestions appreciated, many thanks in advance.

 

[bdreed35]

HAve you tried formatting the field on the report to show the Default value as 0? Not sure if this will solve the problem but it is worth a try.

Right Click the field on the report.
Choose Format Field.
Click the Number tab.
Click the Customize button.
In the drop down list on the right under "Show Zeros Values as:", choose 0.

You could could also try creating some manual running totals that might give you better results, if the above doesn't work.

Create the following formulas:

@Init - Place this in your Report Header if you are not reseting on change of group, or place it in your group header if your are resetting on change of group

WhilePrintingRecords;
numberVar CountField := 0;

Right Click, Format Field, Suppress

@Accumulate - Place this in your Detail Section. This does a simple running total but you would want to replace it with what ever logic you have in your {#CountField} Running Total. Suppress if need be.

WhilePrintingRecords;
numberVar CountField;
CountField := CountField + 1;

@Display - Place this in your Group Footer Section.

WhilePrintingRecords;
numberVar CountField;
CountField := CountField + 1;

~Brian

 

[asrisk]

Brian

Thanks for that. The problem was fixed by using the trio of little functions your provided. Plenty of scope for customisation too, I think these 3 formulas will be useful in the future.

Much appreciated!

Andy.

 

[brek2]

I have the same problem as Andy (blanks where there is no data for a field that doesn’t match the criteria) and tried the trio of little functions, but they did not work for me. The @Display formula that Brian gave is the exact same as the @Accumulate formula. Is that right? How did you get this to work?


Thanks.

 

[lbass]

The display formula should be:

WhilePrintingRecords;
numberVar CountField;

-LB

 

[chelseatech]

The reason the running total field didn't display a zero was that the value was actually NULL. There is a a report option in CR9 and later, "Convert Other NULL Fields to default values" that will force it to be a zero.

The 3 formula technique doesn't generate NULL values so works as expected. It has been around a long time, but still avoids being forced into retirement.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[brek2]

I have Crystal 8.5 so can't use chleseatech's option.

The three formula technique works (sort of) with one condition (as noted above, it doesn't force the zero). When I add a second condition, it ignores it. If I reverse the conditions, it works with the first and ignores the second. Here is my Accumulate formula


If (Table.Group = “Nursing” and Table.OfferDate in LastYearYTD) then
CountField := CountField + 1;


My original formula was

If {Table.OfferDate} in LastYearYTD And

{Table.Group} = "Nursing" Then

1

else

0

Again, I only get the first condition and it doesn't give me the zero value.

Thanks in advance for your help.

 

[brek2]

Looking at the report again, I see I am getting the SECOND condition only. I need both conditions to be met and I need a zero for every record that doesn't meet the conditions.

I hope this is possible.

Thanks!

 

[lbass]

You can find the option in 8.5 in file->report options->convert null fields to default values.

Please explain your report structure and where you are placing the formulas.

-LB

 

[brek2]

Thank you, lbass!!

That one simple change fixed my report. I now have zeroes in every field that doesn't meet the conditions. Also, strangely, both conditions are now working as they should.

Such a simple solution---you are a lifesaver!!!