0

I inserted a pie chart in my SSRS 2008 report. When there is no data coming from sql, it basically shows "no data available", but what I would like it to display is an empty chart instead. I could not find a way to provide this. Any help would be appreciated

BalkDal123
  • 13
  • 9
  • 1
    You can use the "No data available" expression to show an image instead. – JohnHC Oct 12 '16 at 12:54
  • I am using it right now, but I am supposed to show an empty chart instead "no data available" – BalkDal123 Oct 12 '16 at 13:03
  • Add a column to the dataset that is a count of records (excluding the count row), and so it always has one row. Add an image of an empty chart, visibility dependent on this new value, i.e. -=IIF(Recordset!RowCounter=0,False,True). Then the actual chart has the oppisite visibility property =IIF(Recordset!RowCounter=0,True,False) – Jerry Ritcey Oct 12 '16 at 20:54

1 Answers1

1

This is how I solved the problem for myself, though I was not using a pie chart. For my dataset query, I'd add a column called ShowRow. For any valid data, the ShowRow value would be 1. I would union that data with a row that matched the original data and if we had valid data, then showRow would be zero, else 1.

WITH ChartData as
(
SELECT Sales, Month, Year, 1 as ShowRow
)
SELECT * FROM ChartData
UNION
SELECT NULL,NULL,NULL, 
CASE WHEN (SELECT COUNT(*) FROM ChartData) > 0
    THEN 0
    ELSE 1
END as ShowRow

Then in the dataset properties, I added a filter to only show rows where ShowRow = 1. This nicely showed my legend values and chart frame without showing any data.

malckier
  • 1,052
  • 2
  • 14
  • 22