COUNTs and Calculations

[Former Member]

CR Xi

SQL 2005

I've created several Running Total fields such as #CountOpen; #CountCloseWon; #CountALL

#CountOpen has logic to check for if the opportunity is open {Table3.Status}='Open'; #CountClosedWon has logic to check for if the opportunity is closed and won {Table3.Status}='C-Won'; #CountAll is setup to count all rows (COUNT not Distinctt Count).

So, I've set these up, setup Formula fields for calculations but all I get are 0's in the Footer1 area.

@ClosingPercent={#CountClosedWon}*100/{#CountAll}

@ClosingRatio={#TotalSales}*100/#TotalOutstanding

Assume the #TotalSales and #TotalOutstanding work as these fields are on the report (Footer1) and have the correct data.

What am I doing wrong on the ratios and calculations? I've inserted the fields into the Detail section and Suppressed them so they don't print.

Seems like an easy solution.

Is there a way to step through the processing as a report runs?

TIA!

[former_member11696 · Accepted solution]

Hi Total,

Check for IsNULL in your formulae, CR stops processing them when it gets a null value.

Example: if is NotNull( ) then do the rest...

And to help debug simple create 3 new formula for each calculation and put them in the detail section to see what each row is returning.

Then use one at a time in the footer to see if they match.

Don

[Former Member · Accepted solution]

Don,

Thanks for the hints and suggestions. I went through and found a counter that would work and started making adjustments to that. Your input was instrumental in ultimately determining the logic for the solution.

[Former Member]

Hi

Right click on the formula and go in edit, on topy of your formula editor you will find one option with a drop down 'Exceptions for Null' click on the dorop down and select Defaut Values for Nulls. Now check whether you are getting values for your formulas.

Thanks,

Sastry

[Former Member]

HI Sastry,

I looked into the situation that you described and the Formula Editor had the defaults already there so I didn't change anything. Your idea gave me a different path which ultimately led to the solution!

Thanks again!