Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

how to show null count as zero

See sql below.  I have loaded this into the Command and am displaying in Crystal Reports detail section.
When count is nothing, the field is blank.  How can I make it show zero so that the report looks better.?

Thanks!



SELECT Month("CL_MAST"."Delete_Date"), Count("PR_MAST"."Emp_No") as ChicoEmpCount
 FROM
"CL_MAST" "CL_MAST" INNER JOIN "PR_MAST" "PR_MAST" ON "CL_MAST"."Loc_No"="PR_MAST"."Loc_No"
WHERE
"CL_MAST"."Loc_No" >= 1000
AND
"CL_MAST"."Loc_No" < 2000
AND
"CL_MAST"."Loc_No" <>  1009
AND
"CL_MAST"."Delete_Date">={d '2005-01-01'}
AND "CL_MAST"."Delete_Date"<={d '2005-12-31'}
AND
"PR_MAST"."Term_Date"  =  "CL_MAST"."Delete_Date"

GROUP BY
Month("CL_MAST"."Delete_Date")
0
KeithMcElroy
Asked:
KeithMcElroy
1 Solution
 
gopatincCommented:
There are a number of things that could be done here

1. Modify the query to return 0 if no count
    Looks like you have a SQL database, in that replace Count("PR_MAST"."Emp_No")  with ISNULL(Count("PR_MAST"."Emp_No"),0)

2. At the front end,in Crystal, create a formula which checks for null and replaces it with 0
    Create a forumla called ChicoEmpCount
    In the formula editor enter
      if isnull({field with ChicoEmpCount}) then 0 else {field with ChicoEmpCount}

   
0
 
MSuchoffCommented:
This is off the top of my head... Try going to Report Options and look for the database section. Then select the option to Convert Null Field Values to Default. I think I've done this in the past to display zeros

HTH
Mike
0
 
mlmccCommented:
Both good ideas.  Problem with the second idea, is all NULL fields will now show the default value which may not be what you want for some fields.

mlmcc
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now