?
Solved

Null values and conditional showing

Posted on 2003-03-24
13
Medium Priority
?
604 Views
Last Modified: 2009-12-16
Hi everybody. I assume it's easy... I hope I'm not wrong :)

I would like to have function that will act like this:
 IF IsNULL({@Field}) Then
  formula = " "
 else
  formula = {@Field}
 End IF

 Something like scaled down version of COALESCE function, with just one parameter. If the field is null, I want string that has something (not empty string, so my formating still show), or field itself.
 Reason is that Crystal doesn't like NULLs, so my fields are not shown at all. In most cases, that's exactly what I want, so option Convert Nulls to Default is not checked on reports. But sometimes, I want to show them, underline empty ones, color them etc. Only sometims. So, instead of changing that option, can anybody tell me how to make this visible on all reports? I'm using CR 9 Advanced.
 I tried to make it custom function, but got an error that ISNull() can not be used in custom functions...

Any ideas?
Thanks.
Lady Linet
0
Comment
Question by:ladylinet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 8199628
You are very close.  The @field must be a text type

IF IsNULL({@Field}) Then
 " "
else
 {@Field}
End IF

mlmcc
0
 
LVL 7

Author Comment

by:ladylinet
ID: 8204641
I tried it in custom function, and error I'm getting is:
 "This function can not be used in custom functions".
 with cursor pointing to ISNULL.

 Reason I want custom functin is that I have to do check for lot of fields, and I was hoping to at least move logic into function, instead of writing if...else... end if all the time...

Thanks.
Linet
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8207137
Try this

IF IsNULL(@Field) Then
 ' '
else
 @Field
End IF


mlmcc
0
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
LVL 101

Expert Comment

by:mlmcc
ID: 8207146
When you say function are you referring to formulas or is funstion a new feature in CR9?

mlmcc
0
 
LVL 7

Author Comment

by:ladylinet
ID: 8208457
Yes, I think it's new in CR9. They are called custom functions - way to share your common functions, encapsulate your logic instead of repeating etc.
 I've already have that if isnull()...then...else but I'm tired of it, and tought to encapsulated just that part into custom function...

Lady Linet
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8212346
How do you build a custom function?  If what you are showing is the entire function then whatyou want can't be done.

I suspect there is more to the function than that.

mlmcc
0
 
LVL 7

Author Comment

by:ladylinet
ID: 8240811
No, that's all - if value is null, I would like to replace it with whatever default value I pass as second parameter. Error I'm getting is with IsNULL function - looks like ISNULL is not applicable, even if it's not on list of not applicables functions.
Why do you think this can not be done?

Thanks.
Lady Linet
0
 
LVL 7

Author Comment

by:ladylinet
ID: 8350164
Looks like another unanswerable question...
Thanks to mlmmc for trying...

Lady Linet
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8351999
I think I have been very stupid.

Try

IF IsNULL(?Field) Then
' '
else
{?Field}
End IF


@ is for formulas  ? is for parameters

mlmcc
0
 
LVL 7

Author Comment

by:ladylinet
ID: 8374984
I'm getting an error for ISNULL function.

Lady Linet
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 80 total points
ID: 8377614
Unless IsNull doesn't work with parameters I don't know.

mlmcc
0
 
LVL 7

Author Comment

by:ladylinet
ID: 8546102
Thanks for trying, looks like it's impossible...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8550549
If you tried what I had given you there was a problem

Should have been

IF IsNULL({?Field}) Then
   '  '
else
  {?Field}


or

IF IsNULL({?Field}) Then
   "  "
else
  {?Field}


Use Crystal Syntax.  I just built that as a formula.  Not sure how to do it as a function since that is a new feature in CR9

mlmcc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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