InNull formula not working 1

[dcanfield]

I recently took a class from New Horizons for Crystal Reports - 10.

One of the resources that they gave us as part of the class was a workbook and a CD with some sample files.

I have a report here at work that is almost exactly the same as one of the lessons in the class workbook:

When I use the files that came with the workbook, and complete the lesson, I can get it to work exactly as shown in the lesson, which is what I am trying to accomplish.

When I try to use the same logic on my report from my office, it does NOT work. I have even tried copying the formula that I created from this lesson (which works on the files that were provided), pasting into my formula editor, and then just changing the table/field names….but no luck.


What I am trying to do, is create a report that summarizes the balances of each Financial Class (FC) in my database. I have pasted both the example formula that I got from my lesson, that works, as well as my formula that I am actually trying to use, that does not work.

I have also pasted my Record Selection formula below.

In the Record Selection formula, when I use the DAYS = 61 to 90, there is a value for FC= X, which is displayed. When I use the DAYS = 91 to 120, there is No value for X, (as there are no records that match that criteria), and I want it to display “No Balance”. Instead, it is just blank, and all groups below then move up in the report.

Is there anything else that controls the display if the value is Null? Why does the “No Balance” not display on my report when there are no records that match my criteria? Any ideas? In my report, I have gone into “FILE / REPORT OPTIONS / and set the “Convert Null Field Values to Default” the same as in the lab created report, but it still just displays a blank instead of “No Balance”. I have also checked, and the formatting of the fields (currency) appears to be exactly the same.

Let me know if you need more information.

Thanks for your assistance!

David


Example from workbook (WORKS)–

If IsNull (Sum ({Credit.Amount},{Customer.Customer ID})) then "No Credits"
else
ToText (Sum ({Credit.Amount},{Customer.Customer ID}), 0)


From my actual report (does NOT work) -

If IsNull (Sum ({T_MASTER_ATB.BALANCE},{T_MASTER_ATB.FC})) then "No Balance"
else
ToText (Sum ({T_MASTER_ATB.BALANCE},{T_MASTER_ATB.FC}), 0)



My Record Selection Formula:

{T_MASTER_ATB.BALANCE} > $0.00 and
{T_MASTER_ATB.ATB DATE} = {?ATB Date Selection} and
{T_MASTER_ATB.DAYS} in 91 to 120 and
{T_MASTER_ATB.FACILTIY} = {?Facility} and
if {?Facility}= "BUSINESS A" then
{T_MASTER_ATB.FC}= "B" or
{T_MASTER_ATB.FC}= "E" or
{T_MASTER_ATB.FC}= "F" or
{T_MASTER_ATB.FC}= "I" or
{T_MASTER_ATB.FC}= "J" or
{T_MASTER_ATB.FC}= "K" or
{T_MASTER_ATB.FC}= "L" or
{T_MASTER_ATB.FC}= "M" or
{T_MASTER_ATB.FC}= "O" or
{T_MASTER_ATB.FC}= "P" or
{T_MASTER_ATB.FC}= "Q" or
{T_MASTER_ATB.FC}= "T" or
{T_MASTER_ATB.FC}= "U" or
{T_MASTER_ATB.FC}= "W" or
{T_MASTER_ATB.FC}= "X

 

[MisterMo]

Although It might not be displayed because sometimes a zero value is suppressed, Zero is not the same as NULL Value
try and use something like
If (Sum ({Credit.Amount},{Customer.Customer ID})) <= 0 then "No Credits"
else
ToText (Sum ({Credit.Amount},{Customer.Customer ID}), 0)




Mo

 

[dcanfield]

Hi Mo,

Thanks for the quick response. While your tip DID work (it did display "No Payments" when the selection criteria was <=0), it gave some other results that distorted the data that I am trying to report on. I have a report total field that counts the number of records that are reported, and using your formula, it counted all of the records found, even if they would be excluded in my group totals.

I have also tried to modify my formula as shown below, with the same results (null values are showing as blanks, and not as Zero. I have checked, and have turned off "Suppress Zero", in the field format. My sample report works, but my working report still does not.

Any other ideas?

Thanks, and best regards,

David



Does NOT Work – MY working Report

If IsNull (Sum ({T_MASTER_ATB.BALANCE},{T_MASTER_ATB.FC})) then 0.00
else
(Sum ({T_MASTER_ATB.BALANCE},{T_MASTER_ATB.FC}))


WORKS – WORKBOOK LAB EXAMPLE

If IsNull (Sum ({Credit.Amount},{Customer.Customer ID})) then 0.00
else
(Sum ({Credit.Amount},{Customer.Customer ID}))

 

[dgillz]

Test for nulls at the detail level:

if isnull({T_MASTER_ATB.BALANCE}) then 0 else {T_MASTER_ATB.BALANCE}

Then sum this formula instead of the database field:
Sum({@MyFormula},{T_MASTER_ATB.FC})

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[dcanfield]

Hi dgillz,

I tried this, and the records that are null are still not displaying, and not being replaced by a 0, as I would like them to be.

Any more thoughts?

Thanks!

David

 

[Turkbear]

Hi,
Try this:
With your report open in CR Designer, check under the 'Report Options' tab to be sure the 'Convert NULL values
in database to Default' is checked ( the other one about other NULL values should be checked as well)..
The Default for a String field is "" and for Numerical data it is 0, IIRC..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dcanfield]

Hi Turkbear,

I have tried that, but it did NOT make any difference.

I have posted a more detailed inquiry as a new thread, titled: "Record Selection Problem and IsNull formula", that does a better (I hope) job of discussing what I am trying to do.

Thanks,

David