Solved

Crystal Reports Count 0 doesn't appear

Posted on 2010-09-07
12
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 14

Expert Comment

by:LinInDenver
ID: 33618663
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
ID: 33619001
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
ID: 33619035
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:Lefarfadais
ID: 33619111
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
ID: 33619220
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
ID: 33619293
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
 
LVL 14

Expert Comment

by:LinInDenver
ID: 33619358
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
ID: 33620198
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
ID: 33620239
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
ID: 33620263
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
ID: 33620435
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
ID: 33620566
Thanks alot!

Now it's working!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

752 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