Changing the labels at the top of the Bars in a Bar Chart

[cwillard]

I have a bar chart embedded in a report.  I am displaying rates for counties in a bar chart and I have the chart labels turned on so the value for each county shows at the top of the Bar.  Now I am looking to customize the values shown at the top of the bars so counties that have a certain number in another data item called Stability will show and NA instead of the zero.

Is there a way to do this and if so what is the best way to do it.  I have tried creating a data item to show the NA or the value converted to text then added that as the text label but it still only displays the numeric values. I also tried adding the if then statement as a report expression in the text label but it returns an error when the chart runs.

Here is the expression I am using which does run ok when viewing the query in tabular view.

if ([Prevalence Per 10000 Birth Rto] = 777777) then ('0.0') else

if ([Prevalence Per 10000 Birth Rto] = 888888) then ('NS') else

(cast([Prevalence Per 10000 Birth Rto] as  varchar(8)))

I have attached a sample chart image.

Any suggestions or help would be greatly appreciated.

[Lynn]

How about setting the "Zero Value Character" property of the data format?

[cwillard]

Thank you for the suggestion but unfortunately I need to show two different values, depending on the condition, so this would not.

[Lynn]

Ok, so how about sticking with numbers instead of text and using null for one of your conditions and zero for the other. Then use data format to specify both missing value characters and zero value characters.

Code Select Expand


case
  when [Prevalence Per 10000 Birth Rto] = 777777 then 0
  when [Prevalence Per 10000 Birth Rto] = 888888 then null
  else [Prevalence Per 10000 Birth Rto]
end


[cwillard]

I updated the data item to create the null and then updated the data format for the series so the zero value characters are set to NA and the missing value characters to NS.  When I run the report the zero values still show zero and the nulls or blank on the chart.  I also tried adding a custom label with the same configuration and still got the same results.

Thanks,
Chris