How to read null values as "0" not blanks 1

[Tread42]

I am new to CR and am trying to get a null value to read as zero instead of blank. I'm reading values form a field in the database. Below I've posted the code I use to get all records from yesterday that contain a Product_code of "promo". But if there were none the report just says Promo:

Instead of Promo: 0

Thanks

CurrentDate-1 = {CASES.COMPLETION_DATE_TIME} and trim ({CASES.PRODUCT_CODE}) = "PROMO" Regards,
Tread42

 

[mbarron]

Go to File/Options... On the Reporting tab check the Convert NULL Field Value to Default option. Mike
If you're not part of the solution, you're part of the precipitate.

 

[Tread42]

Maybe I mis-typed when I said Null. Basically there would be not entries for "Promo" under that field. So I don't know if it's null or just not existent. But I changed the setting recommended and it still is only Promo: Regards,
Tread42

 

[dgillz]

Please give an exact sample of the data in the field now, and a sample of how you want it to read. Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[mbarron]

What does it show when there are "Promos"?

Try this to check to see if any of the {CASES.PRODUCT_CODE}'s are nulls or just blanks.
Create the following formula:

If isnull({CASES.PRODUCT_CODE}) then "null" else
if {CASES.PRODUCT_CODE}="" then "Blank" else
{CASES.PRODUCT_CODE}

Don't use the " and trim ({CASES.PRODUCT_CODE}) = "PROMO" part of your select criterea.

Mike
If you're not part of the solution, you're part of the precipitate.

 

[Tread42]

First off. Thanks for all the help.

Now, I'll attempt to answer the questions.

dgillz- Are you talking about the data in "Promo" Well it will be text value. But I'm just counting how many times it shows up so it is normally generating like Promo:3 if there were 3 cases whose product_code="Promo" But if there are none that ="Promo" that's where I would like it to report Promo: 0

mbarron-Should I just put and after my current statement and then
If isnull({CASES.PRODUCT_CODE}) then "null" else
if {CASES.PRODUCT_CODE}="" then "Blank" else
{CASES.PRODUCT_CODE}

or are you saying take out the trim part and replace it with this? When I tried to just put and "then the code" after my current statement I get a boolean error.

Thanks again everyone. Regards,
Tread42

 

[mbarron]

What I meant was to create a formula using what I had posted to check to see what kind of field you had... that is Null or Blank. Then place the formula in your report. Run the report without filtering for the PROMO. Check to see if you have "NULL"s or BLANK fields.

After further review, you don't need to know if they are blanks or nulls.

You can get your total by using the following 3 formulas.

//Count header\\ (put this one in your report header and then you can suppress it):
whileprintingrecords;
shared numbervar counter:=0

//counter\\this on goes in your details and can be suppressed too.

whileprintingrecords;
shared numbervar counter;
if trim({CASES.PRODUCT_CODE})="PROMO" then counter:=counter+1 else counter

//count sum\\ This one goes where your current field/forumula that gives your current count of PROMOs is located

whileprintingrecords;
shared numbervar counter
Mike
If you're not part of the solution, you're part of the precipitate.

 

[Tread42]

mbarron-right now my statement resides in the Report->Selection Formulas->Records area. From your post, I take it this is the wrong section? And I should delete this out and use ones you provided? If so where do I put those in at as seperate Formula Fields?

Thanks for your patience? And sorry if these are elementary questions. Regards,
Tread42

 

[mbarron]

Keep what you have in the records selection area.

Go to Insert/Fields Object...
This will bring up the Field Explorer. This is where you can create the formula I have above.

If you need, I can try to "walk you though" creating a formula, but I would need to know your CR version. (Hopefully your using 7 or 8.X)

Mike
If you're not part of the solution, you're part of the precipitate.

 

[Tread42]

Great! It worked. Thanks mbarron and everyone else who contributed...

Now, I just must deal with formatting the main file that ties together all of the sub-reports correctly. I have a summary report that links to seven reports like the Promo example above.

One problem, I'm having is when I export to HTML only the first three reports show? I thnk I'll add another post to address this though.

By the way I am using v9 of CR. Regards,
Tread42

 

[Tread42]

mbarron- I just noticed that no matter if there is an entry or not the report is always reading "0".

For example. I've put the code into a reason called "ACHINQ" and it pulls up "0" when there should be 314 of them.

I used the code from above but with the following edits.

//Count header\\ (put this one in your report header and then you can suppress it):
whileprintingrecords;
shared numbervar counter:=0

//counter\\this on goes in your details and can be suppressed too.

whileprintingrecords;
shared numbervar counter;
if trim({CASES.REASON_CODE})="ACHINQ" then counter:=counter+1 else counter

//count sum\\
whileprintingrecords;
shared numbervar counter

Any thouhts? Regards,
Tread42

 

[mbarron]

Did you change your select expert from PROMO to ACHINQ? Mike

 

[Tread42]

Yes, and that's the thing. Two days ago I had some entries for "Promo" and it still reads "0" as well. Is there something I should check to see if it's either null or blank? Or should that not matter? Regards,
Tread42

 

[mbarron]

Would you be able to send me a copy of the report?

mbarron"@"arkwright.com Mike

 

[Tread42]

Yes, I can send the report but not the database it's tied to. I'lls end the report out now. Regards,
Tread42

 

[mbarron]

Rats... I should have caught it before.

You're using CR9 and I can't open the files, I only have access to CR7 and 8. And CR9 can't save as an earlier version.

One thing does come to mind. Is the field "PROMO" or can it be "Promo" If the case is different, make the following change to the formula:


FROM:
if trim({CASES.REASON_CODE})="PROMO" then
TO:
if trim(ucase(({CASES.REASON_CODE}))="PROMO"


Mike