Crystal Reports Count 0 doesn't appear


I'm trying to build a Crystal Report that shows the number of element linked to a certain ID.

Here is what I try to do
Col1         Col2
ID1               5
ID2               0
ID3               3

Here is what I get
Col1         Col2
ID1               1
ID1               2
ID1               3
ID1               4
ID1               5
ID3               1
ID3               2
ID3               3

I've searched over the internet, but didn't found the appropriate answer.

Col1 belongs to Table1 and Col2 to Table2 and Col2 is a reference to Col1
Who is Participating?
LinInDenverConnect With a Mentor Commented:
Ah okay, i would modify to this then:

If isnull({Table2.Col1}) or totext({Table2.Col1})=''
then 0
else 1

This is basically a manual running total for counting... You would still insert SUM on this field into your Group Header or Group Footer.

Table1.PK    Table2.Col1    Formula Returns
ID1                5                   1
ID1                3                   1
GF for ID1                         2 <---SUM of Formula

ID2               NULL             0
GF for ID2                         0 <---SUM of Formula

ID3                 3                1
ID3                 2                1
ID3                4                 1
GF for ID3                        3 <---SUM of Formula

Do a left join between Table1 and Table2. This will give you at least one row for each entry in Table 1 (ID1 ,2, and 3), even if there is not a matched entry in Table 2.
LefarfadaisAuthor Commented:
Hello LinInDenver

Thank you for your fast reply, however, it didn't changed. I still get the same result.

To clarify a bit more, here's the Table2 structure

Col1 Col2  Col3    Col4
x1     y1     z1     refa
x2     y2     z2     refa
x3     y3     z3     refa
x4     y4     z4     refa
x5     y5     z5     refa
xx1   yy1   zz1   refc
xx2   yy2   zz2   refc
xx3   yy3   zz3   refc
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Can you please provide:
1) Structure of Table 1
2) Your joins
3) SQL statement generated by Crystal Reports (or through your command statement).
LefarfadaisAuthor Commented:
Sure, data is not shown for security, however I made them match to ensure comprehension.


IDCol   Col1       ...
refa   Valuex    ...
refb   Valuex    ...
refc   Valuex    ...
...          ...

IDCol is a primary Key
Col4 is a foreign Key to IDCol

 SELECT "table1"."IDCol",  "table2"."col4"
 FROM   (("DB"."dbo"."table2" "table2" LEFT OUTER JOIN "DB"."dbo"."table1" "table1" ON "table2"."col4"="table1"."IDCol")

I just want to link table1.IDCol to Count(table2.col4), and want to show the 0 if the situation happens.

i think the left join is backwards between the two tables.

To show all values from Table 1 regardless of activity in Table 2, the left join needs to be placed from Table 1 to Table 2, like below... give this a try.

 SELECT "table1"."IDCol",  "table2"."col4"
 FROM   (("DB"."dbo"."table1" "table1"  LEFT OUTER JOIN "DB"."dbo"."table2" "table2" ON "table1"."IDCol"="table2"."col4")

LefarfadaisAuthor Commented:
I assume a Right join would work if it's inverted...

but then again, I still get the same result.
Maybe my showing is wrong. I have a running total of the table2.col4 resetting every table1.IDCol

I have 2 fields on my report (that I want to show, once done, the remaining field get auto-filled)

P.S. Doing the Count(table2.col4) gives me the count of all the records, not the one they associated with.
in the detail of the report, place on your PK from Table 1, and the counting value from Table 2...

is it showing a row for each option in Table 1, with blanks where there is not Table2 reference? If not, then there is still an issue with the join.

If it IS showing this, try to insert a GROUP on Table1.PK  (from Insert Menu, choose Group, then choose the proper field from drop down).
If you still have your counting field from Table 2 in detail, right click on it and insert SUM.

This would hopefully provide the numbers you are looking for....

One more option you should change to make sure you get a 0 instead of a NULL, is from File | Report Options. Check the boxes for Convert Database NULL values to Default, and Convert Other NULL Values to Default.
LefarfadaisAuthor Commented:

Sorry for a little late answer. Tried a couple things
I did changed the option for DataBase Null to Default and Other Null as well.

Now all fields appear, but with a 1 instead of a 0. The other numbers are alright.
I've created a group on Table1.IDCol and it seems to be showing ok.
Perhaps your database has 1 set as a null default then...

You might want to uncheck those boxes then, and create a formula for your summary.

If isnull({Table2.Col1}) or totext({Table2.Col1})=''
then 0
else {Table2.Col1}

You would then insert sums on this new formula.
LefarfadaisAuthor Commented:
I don't think that would do.

I try to count the number of rows in table2 related to table1.IDCol = table2.col4
if there is no rows, then show 0.
LefarfadaisAuthor Commented:
Thanks alot!

Now it's working!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.