cancel
Showing results for 
Search instead for 
Did you mean: 

remove null & empty values

Former Member
0 Kudos

I created a report with multiple fields. I would like to create a generic formula in which it evaluates all the values and if it found a null or empty value it will be replaced by a 'N/A'. I was searching in the forums and I found the following formula

if (isnull() or ( ='')) then

"Display the required text"

else

when I tried to use it i had an error that the value must be boolean. Is there is a way where I can change all the fields as string?? and a assign "N/A"

or

how can I assign a "N/A"to a boolean field??

also I have approx. 140 fields to evaluate, does the formula can be generic or I will need to create a formula for each variable??

thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Usually when Crystal runs across a field that is NULL, it immediately stops executing the formula, unless the field is enclosed within IsNull(). However, I'm not sure if that is the case for a parameter being passed to a function. So, you may be able to create a function like (Formula Workshop -> Create Custom Functions -> Add; basic syntax):


function DisplayString (inVal as string) as string
if (isnull(inVal) or (inVal ='')) then
  DisplayString = "N/A"
else
  DisplayString = inVal
end if

If Crystal does stop when passing a NULL value as a parameter, then you could code a similar function (without the isnull()), and check the Convert NULL Database Values to Default option on the File -> Report Options panel.

You would need to create one such function for each data type that you want to show "N/A" for, and for data types other than string, convert the value to a string when it is not null.

These functions could then be used in the Display String formula for the field on the report.

The only other way I can think of achieving this would be to base the report(s) on an SQL Command and convert NULL values to "N/A" there. It might be a bit easier to do in an SQL Command, but probably not a whole bunch.

Or, educate your users that when nothing is printed, it means that it's not applicable!

HTH,

Carl

Former Member
0 Kudos

I can export the data base and assign an "N/A"for each Null value. The problem that I'm having is that if I do that the columns that only contains number doesn't shows any value in crystal report.

ex.

variable 1

1 report 1

2 report 2

3 report 3

N/A report4

when I perform the report in crystal report it shows the following

report 1 variable1

report2 variable 2

In other words there is no data displaying in the report. If I can resolve that I can do the replacement of the NULL value.

Former Member
0 Kudos

I don't understand your last post...

What do you mean by "export the database"? Do you mean dump the data out of the database, translating NULLs to N/A, and into another file?? (Don't go there... Drive the report directly from the database...)

And I don't understand how

variable 1

1 report 1

2 report 2

3 report 3

N/A report4

would ever become

report 1 variable1

report2 variable 2

as the two sets of data seem unrelated. (Where did "variable 2" come from??) Also, is the first set of data from the database, or what was "exported"? If the former, what logic was used to "export"?

Could you please clarify?

Carl

Former Member
0 Kudos

The data that I'm exporting doesn't come from a traditional database, it comes from a Distributed Control System (DCS, provided by Emerson). What I do is to define the modules that I want to export with its variables. This is converted to a txt file, I use excel to export the data. Since all module doesn't contains the same information, when I do the export empty spaces will be fill with a NULL value. Once I export the txt file to excel I use crystal report to organize the data.

I can use the replace function in excel in order to replace the NULL value with a N/A, but as i mentioned previously when there is data that contains number and N/A crystal report doesn't shows anything.

I hope this clarify my previous message.

Former Member
0 Kudos

when there is data that contains number and N/A crystal report doesn't shows anything.

Is it because the number is being treated as a number, but N/A is a string? Can you export all of the values for the column as a string?

HTH,

Carl

Former Member
0 Kudos

I have been trying to change the data type of the exported data but I don't know how to do it. For me it will be more convenient to change all the data to string, in this way it will be more easy. When I did the export, cyrstal report never gave me an option to change the data type (ex. boolean, string etc).

Do you now a way to do it??

Former Member
0 Kudos

Is the report based off of the Excel file? I think if you change the format of the column within Excel to Text, Crystal will read it as such.

I think...

HTH,

Carl

Former Member
0 Kudos

Yes, the data comes from Excel. I changed all the cells to text in excel. I did a scan with crystal report but still i don't have the values.

if it have number in excel crystal report doesn't show any number but if it have a N/A within the same column crystal report shows the N/A.

Former Member
0 Kudos

Hummm... That doesn't make sense to me...

Try creating a new report against the Excel spreadsheet, and just drop all of the fields in the Detail format. Do you see all of the values?

Does the current report have any suppression logic?

HTH,

Carl

Former Member
0 Kudos

I found what was the problem. I was changing the data (to text) after it was exported using the format cell option within excel. For some reason excel keep the data properties and remains as a number.

What i had to do is when exporting the data in excel during the exporting dialog i need to click each column and specify that it is a text. When I did that crystal report shows the value (<NULL> or numeric values).

Answers (0)