Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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
0
CAHFS
Asked:
CAHFS
  • 9
  • 6
  • 4
  • +1
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now