SQL Server Reporting Services 2005 - How do you hide the legend label for a chart?

Question

I am reporting on data that lets say has 10 columns. Now some of my clients have data in all 10 of the columns whereas some may only use 7 or 5... you get the idea. Of course I dont want to have to build a new rdl for each possibiliy. Well in the tabular report I can hide the columns fine if no data exists by using the IsMissing property.

Well in the charts, let's say a bar graph, if there is no data for a column it will not show a bar on the graph (which is great) but all of the potential columns still show up in the series legend.

I've read that 2008 gives you that level of chart customization, but 2005 does not. Any other ideas?

Answer 1

I think I actually answered my own question (well, it will work for my scenario). I used the UNPIVOT function available in SQL Server 2005 to put the column data into rows. This gives me a dataset of two columns (Field and Result) with multiple rows now. Now in the SSRS chart, I just drop the Result into the data and the Field into the series and this works out great.

Answer 2

I'm using 2005, but I think for the bar chart that you might be able to do something like this.

Chart properties -> Data Tab -> Series Groups (edit) -> Filters -> Add the appropriate filter to remove series that you don't care about.