Solved

Crystal Reports Count 0 doesn't appear

Posted on 2010-09-07
12
351 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Lefarfadais
  • 6
  • 6
12 Comments
 
LVL 14

Expert Comment

by:LinInDenver
Comment Utility
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.
0
 
LVL 3

Author Comment

by:Lefarfadais
Comment Utility
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
0
 
LVL 14

Expert Comment

by:LinInDenver
Comment Utility
Can you please provide:
1) Structure of Table 1
2) Your joins
3) SQL statement generated by Crystal Reports (or through your command statement).
0
 
LVL 3

Author Comment

by:Lefarfadais
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:LinInDenver
Comment Utility
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")

0
 
LVL 3

Author Comment

by:Lefarfadais
Comment Utility
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 14

Expert Comment

by:LinInDenver
Comment Utility
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.
0
 
LVL 3

Author Comment

by:Lefarfadais
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:LinInDenver
Comment Utility
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.
0
 
LVL 3

Author Comment

by:Lefarfadais
Comment Utility
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.
0
 
LVL 14

Accepted Solution

by:
LinInDenver earned 500 total points
Comment Utility
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
0
 
LVL 3

Author Closing Comment

by:Lefarfadais
Comment Utility
Thanks alot!

Now it's working!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
This is about my first experience with programming Arduino.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now