Link to home
Start Free TrialLog in
Avatar of Lefarfadais
LefarfadaisFlag for Canada

asked on

Crystal Reports Count 0 doesn't appear

Hello

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
Avatar of LinInDenver
LinInDenver
Flag of United States of America image

Hello,

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.
Avatar of Lefarfadais

ASKER

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

Table1

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.
Thanks!

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")

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)
table1.IDCol
Count(table2.col4)

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.
Hello

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of LinInDenver
LinInDenver
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks alot!

Now it's working!