Display entries equal to ZERO 1

[Scott5150]

Using Crystal with MSCRM (Crystal Pro v 9.2.x.x) and trying to report items with a zero value. This is for project stage comparison purposes. Am I missing something extremely simple in getting these items to report?

Thanks in advance,
Scott

 

[lbass]

You might have null values. Try:

if isnull({table.number}) then 0 else {table.number}

Use this in your report instead of the field {table.number}. You might also check to see if your field is formatted to prevent zeros. Right click on it->format field->numbers->customize and uncheck "suppress if zero".

-LB

 

[Scott5150]

I think I need to clarify a bit, I tried your suggestion and wasn't ab;e to get far...

I am using Crystal with MSCRM. We are reporting on sales stages, and would like to display in a graph ALL the stages, even if there are no items that are in that stage. When I create a group that will provide the information for the graph, the report doesn't return stages with no records, so Contract = 0 just doesn't appear. How can I get Crystal to report this correctly?

 

[lbass]

You can't report on data that isn't there. If you have a table that contains all stages, you could add a left join FROM that table to a table that contains the data you want to report on (as long as you make no selections on the second table that would undo the left join). Then you could use a field from the master table for your group field (on change of) in the chart.

-LB

 

[mdwyer]

A dba friend once received a (mngmt) request for a report of everything that wasn't in the database.


Not trivializing your requirement, Scott. Just a bit of Dilbertesque humor. Lbass has the right idea, and if you don't have such a table, you'll have to create one.

 

[Scott5150]

I certainly mis-typed that last time, there are table values representing the stages, but no records that are associated to the stages yet. Where I was wrong was trying to report on the zeros rather then the stages I guess...

very new to SQL...
Scott