Crystal reports graphing/charting question

Hello!
I have a graph that shows how many samples were positive for each titer group.  There are 8 possible results, group titer 0 through group titer 7.

Right now, the graph only shows the groups that actually have results in them... imagine that.
However, the techs want the graph to show all 8 groups, even if nothing is there!  Is there an easy way to do this?
what graph/results look like now
CAHFSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vastoCommented:
You need to include the table , which contains the list of groups and left joint with the result table. The returned data with contain record for each group ( no matter if there is  result o not)
0
mlmccCommented:
Agree.  Crystal has to have a record to work with

mlmcc
0
CAHFSAuthor Commented:
But...  that's the same table.  That sub-report only has the one table, a view actually.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

mlmccCommented:
General method of doing this is to add a table to the universe that has all the possible values.  IN your case a table with 8 records.

NewTable
TiterValues
   1
   2
   3
   4
   5
   6
   7
   8

Your report will then use that table in addition to the existing one

NewTable  -->   Existing Table   Left Ouer Join on NewTable.TiterID = ExistingTable.TiterId

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CAHFSAuthor Commented:
Ok, great! Thank you.  I was hoping to stay away from the table-making/changing part of it but I'll get it done.
0
CAHFSAuthor Commented:
It's still not working.  I have a table that has 0-7, added it in, left outer join, updated the graph so that it says On Change of my number field, it counts ID's.  still no value for the ones that have 0 ID's.  What am I missing?
0
James0628Commented:
Is the Left Outer Join from the new table to the old one?  It has to be in that direction, and not the other way around.

 And I think the "On Change Of" needs to be on the field in the new table, not the field in the old table.

 James
0
mlmccCommented:
You probably should have split the points on this question.

Vasto gave you the answer of adding a new table.
James answered your follow-on question.


My comment which you accepted as the answer simply explained how to implement Vasto's comment.  

The question can be reopened if you wish to allow you to more fairly award the points.

mlmcc
0
James0628Commented:
Well, we don't actually know yet if my post helped.

 James
0
CAHFSAuthor Commented:
Yes, you're right mlmcc, sorry to the others, i'll adjust the points.

Unfortunately, it's still not working. I've been playing with the link trying every combination, have gotten some interesting results, but not the right ones...
Right now i have
new table.number -- left outer join to old table.titer_id

the chart has:
On Change of:
new table.number

Count
 old table.sample_id
0
James0628Commented:
If you're not doing a distinct count, how about doing a count of "new table.number"?

 "old table.sample_id" will be null when there are no records, which may prevent you from getting a count.  I'm not really sure.

 You could also try going to File > Report Options and checking the "Convert database null values to default" option.  Again, I'm not sure if that will help, but it's easy to try.

 James
0
mlmccCommented:
What are you getting as the result?

Do you want to reopen the question?

mlmcc
0
CAHFSAuthor Commented:
James, thank you. I tried all that but my graph just looks like the original one, bars only on the ones that have >1 id.  
I've done this on another report and it worked so I'm not sure what is going on here. The other report didn't have a graph though so I just added a group for the new table.number and it's not showing up either as a group header for the ones that have 0 values so at least now i know it's not just a graph thing.  So it has to be a linking thing...  right?
0
CAHFSAuthor Commented:
I just deleted the graph and started over just for kicks.  Same result
0
James0628Commented:
If you're checking fields in the old table in your record selection formula, CR may be changing the Join to Inner.  You can go to Database > "Show SQL Query" and see what type of Join is in the query that CR is sending to the server.

 James
0
mlmccCommented:
What data does your table have in it?

mlmcc
0
mlmccCommented:
I created a very simple report and data.  It worked first try.

mlmcc
MissingGroups.rpt
MissingGroups.xls
0
CAHFSAuthor Commented:
thanks. i'm going to compare yours to mine to see what's different.
0
CAHFSAuthor Commented:
it's something with this darn sub-report.  i've created my own, from scratch and it works fine but when i apply the very same things to this report.  it ignores my left outer join.
I'm in the process of re-creating the sub-report to see if anything changes.  If it doesn't, I think I may have to tell the tech it can't be done.  My absolute last resort...
0
vastoCommented:
If you have a field from the left joined table in the where clause this will turn the left join to inner join. James mentioned this few posts above. In other words if you have a sql like this

SELECT * FROM a LEFT JOIN b ON a.Field1=b.Field1
WHERE b.Field2=X

it will work as an INNER JOIN because you have table b addressed in the where clause
0
CAHFSAuthor Commented:
so how do we get around it? is there a way?  someone here mentioned creating a sub-report just for the graph but this is already a sub-report.
0
vastoCommented:
You can get around this by moving the where clause to the join . For example for the SQL
SELECT * FROM a LEFT JOIN b ON a.Field1=b.Field1
 WHERE b.Field2=X

you nee to do

SELECT * FROM a LEFT JOIN b ON a.Field1=b.Field1AND b.Field2=X

This will require to edit the SQL, which is not possible in Crystal. However , you can create a view in your database and use it for the report instead of tables a and b or you can use a command and write the SQL manually
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

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.