XI Suppress Zero Value Data Labels 1

[pb100]

Hello,
Using CR Developer XI and SQL 2005, I have created a stacked bar chart with data labels. The stacks in each bar are based on 6 different formulas that return a value if certain criteria are met (if criteria=true then table.field). So my Show Values in the Chart Expert has 6 formulas. Up to 6 stacks are possible, but not every bar shows all the stacks due to the criteria in my formulas. Everything works well, except with data labels showing, I get 0 values at the top and bottom of every bar. How can I suppress those values?

I researched the forums and found thread767-1169695 which suggested the data be checked. There are no zero values in my data. Any help is greatly appreciated!!
Have a great day!
-pb100

 

[lbass]

If your formulas result in numbers, then 0 values will be the default when your criteria are not met. Try this method (a Ken Hamady trick). Create a formula {@null} by inserting a formula and saving and closing without placing anything in the formula area. Then change each of your conditional formulas to:

if {table.criteria} = true then {table.field} else
tonumber({@null})

-LB

 

[pb100]

Thank you!! It works great! Just what I needed.

 

[pb100]

I now have the same issue in a different chart where the Summary Operation for my values is DistinctCount. The zeros are back...any ideas?
TIA

 

[lbass]

The same method should work. Distinctcount will not count nulls. For my help you will need to provide more information.

-LB

 

[pb100]

Hi LB,
Thank you for your great tips! I am again creating a stacked bar chart with data labels. The stacks in each bar are based on 6 different formulas that return a value if certain criteria are met (if datatype=true then table.field) else tonumber({@null})). Then, I want to count how many for each datatype as the value for each stack in the bars. The table.field is actually the id field since I just need a count instead of a value. In the Chart Expert Show Values section, I use DistinctCount for the Summary Operation for each of the 6 formulas. Before my formulas included the "else tonumber({@null{)" piece, I was getting values for every type in each stack, which was not correct. Each stack should only show the sections where there is a value. Adding the "else...null" corrected that issue but I am still getting 0s at the top and bottom of each bar.

What do you suggest?
Thanks so much!!
-pb

 

[lbass]

I'm sorry, I don't see a way of removing the zeros from the data labels. It might help the display if you add the label to the value, so that the viewer can see what the zero references.

-LB

 

[pb100]

Hi LB,
Thanks again. In showing the labels, I can see that what is happening is that the DistinctCount is counting the null values as zeros and displaying those in the bars. I might try to create formulas to count the values outside of the Chart Expert and then set the Chart Expert to "Don't Summarize". Not sure how complex that would be, I'll save for later at this point.
Have a great day!
-pb

 

[pb100]

I've come back to this issue and still have not been able to resolve getting rid of the zero data labels. I tried using a formula outside of my chart: if distinctcount({fieldname}) = 0 then tonumber(@null) else distinctcount({fieldname}), then setting the Chart Expert to "Don't Summarize" but the counts show up with incorrect values. Am I missing something simple or is this just not possible with Crystal XI (hopefully it will be soon!!) My client will not be happy...

Thanks for any advice!