.Net ramblings
# Friday, 04 August 2006
Crystal Reports: Suppress doesn't work for Count(x) = 0
I picked up a tip on the Internet to display a "No records" message if the report contains no records, rather than leaving the user with an empty screen.  Simply Format the text field and enter a formula next to 'Suppress' with something like
Count({Table.Field}) > 0
This will suppress (hide) the message if there are records in the report.

I tried using a similar approach to hide a text field when there are no records, so it is essentially the same thing in reverse.  You would think a simple formula on the 'suppress' property this would achieve the desired effect:
Count({Table.Field}) = 0

But apparently the value returned by Count can be null if there are no records.  so you have to use:

IsNull(Count({Table.Field})) OR Count({Table.Field}) = 0

This is just another crystal reports annoyance, of which there are many.

Friday, 04 August 2006 11:26:38 (GMT Daylight Time, UTC+01:00)  #    Comments [9]  .Net General | Asp.Net | Windows Server

Wednesday, 12 November 2008 20:09:12 (GMT Standard Time, UTC+00:00)
thank you sir, this helped me out today :)
Wednesday, 03 June 2009 20:24:35 (GMT Daylight Time, UTC+01:00)
This helped me out as well. Thank You!
Tuesday, 22 September 2009 21:09:16 (GMT Daylight Time, UTC+01:00)
Me too :)
Thursday, 05 November 2009 22:55:07 (GMT Standard Time, UTC+00:00)
You are awesome! Thank you very much :)
Tuesday, 12 January 2010 04:16:39 (GMT Standard Time, UTC+00:00)
Thank you so much...
You help me alot...
Thursday, 14 January 2010 15:17:04 (GMT Standard Time, UTC+00:00)
Thanks for the tip, that's what I was looking for!
Tuesday, 04 May 2010 07:22:54 (GMT Daylight Time, UTC+01:00)
Thanks a lot , it helped me very much
Thursday, 02 September 2010 04:22:26 (GMT Daylight Time, UTC+01:00)
This is great.

Just an additional note as to why you needed to cover both bases...

The report options will allow a user to specify that a null value should be changed to the default value.

So, you therefore sometimes will see a report, where =null, and other times, =0 because 0 is the default value when said setting is set to True.

Kyle McAdam
Tuesday, 01 March 2011 19:29:22 (GMT Standard Time, UTC+00:00)
Hi I've tried using the following to suppress my message if records are returned but the message doesn't show even if nothing is returned. Can you see where I'm going wrong?

Not IsNull(Count({Table.Field})) OR Count({Table.Field}) > 0

Any help would be appreciated - thanks!

Please login with either your OpenID above, or your details below.
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview