0

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?

Jon295087
  • 731
  • 1
  • 8
  • 28
  • Filter out data that returns no value in your source proc. – Snowlockk May 31 '17 at 15:54
  • @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 May 31 '17 at 16:07
  • 1
    Did you try hiding them in the chart filter option. Right click - Chart Properties - Filters – Snowlockk May 31 '17 at 16:23
  • 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 Jun 01 '17 at 09:09
  • 1
    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 Jun 01 '17 at 09:52
  • @iamdave - Thanks for your advice. I tried Chart Filter as suggested, but still couldn't make this work. However, I've managed to resolve it by rebuilding the chart from scratch using a different field to derive the chart values. My guess is perhaps the original configuration of the chart was preventing the filters and visibility settings from working how I wanted them to. – Jon295087 Jun 02 '17 at 15:05

1 Answers1

0

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.

SuperSimmer 44
  • 964
  • 2
  • 7
  • 12