SSRS chart, how to prevent horizontal axis categories displaying when value is 0

0
votes

I've placed a simple bar chart into an SSRS report which is designed to display a series of Consultant Names (along the x axis), with a corresponding count of patients who are flagged as being in the category ">18 Weeks" (on the y axis), based on the following expression:

=SUM(IIF(Fields!RTTWaitGroup.Value=">18 Weeks",1,0))

The chart renders fine. However, a number of the Consultants listed in the chart have a zero count, and therefore are listed across the x axis but with no vertical bar displayed, as you'd expect.

enter image description here

I'd like to configure the chart so that it only displays those consultants that have a count of 1 or more patients.

I'm not overly familiar with the syntax, but I've tried using the following expression in both the Series Properties and Chart Properties 'Visibility' options, to try and suppress x axis categories that are zero, but it doesn't have any effect on the chart:

=iif(Fields!LastConsultant.Value ="",True,False)

Please can anyone advise on correct syntax and appropriate expression field to enter it?

1
reporting-servicesssrs-2008-r2reportbuilder3.0reportbuilder
Filter out data that returns no value in your source proc. – Snowlockk
@Snowlockk I'm using the same data source behind another element in the same SSRS report, so I'd have to generate a separate data source to filter out these records as you suggest, which is certainly doable, but I'm interested to learn if there's a way to achieve this through the front end viz layer instead. – Jon295087
Did you try hiding them in the chart filter option. Right click - Chart Properties - Filters – Snowlockk
I've tried a number of different expressions in the 'Series_Properties/Visibility/Show_or_hide_based_on_an_expression' option, but to no avail, including =IIF(count(Fields!LastConsultant.Value) = 0,True,False) =IIF(IsNothing(Fields!LastConsultant.Value),True,False) =iif(Fields!LastConsultant.IsMissing,True,False) – Jon295087
Don't try to hide the chart labels, just make your dataset not return any consultants that have a 0 value. You can either do this in your source dataset or in the Chart Filter. Actually apply what Snowlockk is telling you and go through Chart Properties -> FIlters instead of Series_Properties/Visibility/Show_or_hide_based_on_an_expre‌​ssion. – iamdave

1 Answers

0
votes

Presumably your consultant names will be in the category group, if so right mouse click and select category group properties.Select Filters, in the expression enter count(fields!patients.value)... for example. Change type to integer , operator to greater than and the value to 0.