?
Solved

Replace Null value with "" in Formula Field

Posted on 2006-05-16
12
Medium Priority
?
765 Views
Last Modified: 2008-02-01
Hi All,

I just  cant seem to get this one right.

I have a db field that returns NULL values.  I would like to create a formula in the Formula Field that will replace that Null value with "".

How can this be done.  I've tried using:

IIF( IsNull({sp_ParticipantEnroll_rpt;1.line_two}),"",({sp_ParticipantEnroll_rpt;1.line_two}));

but it doesnt work for me.

0
Comment
Question by:GoldenJag
  • 6
  • 5
12 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1800 total points
ID: 16693821
Hi GoldenJag,
The formula should be:

if  IsNull({sp_ParticipantEnroll_rpt;1.line_two}) then
""
else
{sp_ParticipantEnroll_rpt;1.line_two}



Pete
0
 

Assisted Solution

by:jaysin144
jaysin144 earned 200 total points
ID: 16693857
Technically you can't do this, NULL is the abssence of a value while "" is an actual value...You can however use the Field Properties (right click on the field in your report, properties, Common Tab, Display String) and paste this formula

if isnull({tablename.fieldname}) then
""
else
{tablename.fieldname}

It will replace the NULL values with "" and display the actual value for all other instances
0
 

Author Comment

by:GoldenJag
ID: 16693978
I tried what you both said and it doesnt work.  I still get the NULL value in my report.

I am using Crystal XI and reporting in .NET on the web. (Not sure if that makes a difference)

Do you have anymore suggestions?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 77

Expert Comment

by:peter57r
ID: 16694002
What is telling you that you have a null value in your report?

Pete
0
 

Author Comment

by:GoldenJag
ID: 16694014
when i run the report the null values shows up in the web report.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16694027
peter57r,
Try something silly first to be sure the formula is running:

 if  IsNull({sp_ParticipantEnroll_rpt;1.line_two}) then
"XXX"
 else
 {sp_ParticipantEnroll_rpt;1.line_two}


Pete
0
 

Author Comment

by:GoldenJag
ID: 16694148
Peter, i tried your suggestion and the Null values are still visible on the report.

???
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16694167
You are using the formula field in your report and not the original field aren't you?

The formula field is a new field.  It does not affect the value in the original field.

Pete
0
 

Author Comment

by:GoldenJag
ID: 16694241
Pete,

This is what i am doing.

In the Field Exploer ...Under Database Fields...I select the feild from my stored proc (this field is dragged on the report design) ...on the report design i right click that field...select format editor.....under common i click the formula button for Display String....i get the formula workshop screen...under Report custom functions ..formatting formulas...Details...display string ... i see the function you gave me:

if  IsNull({sp_ParticipantEnroll_rpt;1.line_two}) then
"XXX"
else
{sp_ParticipantEnroll_rpt;1.line_two}


is this the way i am suppose to use it?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16694346
No.
You cannot format a field to a different value.

You choose Formula Fields and click the New button on the Field Explorer toolbar; then Formula Editor.
The formula goes in the formula pane.
Save and close.
Use this field instead of the original in your report.

Pete
0
 

Author Comment

by:GoldenJag
ID: 16694529
Pete,

I tried it and i still get the null values.

This should work unless the formula is incorrect.  I have used the formula field in this way before and i am currently using it to concatinate two string and it works fine.


??
0
 

Author Comment

by:GoldenJag
ID: 16694642
Got it....for some weird reason the value was actually "NULL" so the isNull function didnt pick it up
so both of you guys were right... i appreciate the time you spent helping me Peter.


Thanks!.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question