0

I'm trying to build a bar chart that displays Events per Year. Each year divided into 4 quarters. The problem arises in case that one, or more, quarter doesn't have any events in any year. In this case the legend key for this quarter doesn't appear.

For example, if no events took place in 1st quarter, then the legend show keys for 2nd, 3rd and 4th quarters only.

I need to make the legend always shows all keys, even if one, or more, doesn't have any values.

How can I accomplish this??

A similar question is here but the answer doesn't solve the problem.

Any help is appreciated.

Applying @alejandro answer, I get this result. The missing quarter, Quarter 3, is renamed to Series1 and located at the first place. Any suggestions?

enter image description here

Community
  • 1
  • 1
Dr. MAF
  • 1,853
  • 1
  • 27
  • 45

1 Answers1

2

There is no way to build the legends and labels based on data that is not present in your fields.

You can create a table or a CTE with all quarters, then use LEFT JOIN operator to relate each row in your data with the corresponding quarter, if a quarter doesn't match any row in your data it will return null for each column in your data but will include the quarter, which lets SSRS build the legend.

WITH quarters 
     AS (SELECT 1 [Quarter] 
         UNION 
         SELECT 2 
         UNION 
         SELECT 3 
         UNION 
         SELECT 4) 
SELECT a.[quarter], 
       b.* 
FROM   quarters a 
       LEFT JOIN YourDataTable b 
              ON a.[quarter] = b.[quarter]

After that you can use Quarter field in your Chart and it will show all quarters in the legend even if there is no data in one or more quarters.

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Thank you. It works. But the name of missing quarter is renamed to "Series1" and appears at the first place. Any work around is appriciated. Thank you any how. – Dr. MAF Oct 25 '16 at 16:23
  • @Dr.MAF, Where does "Series1" appears, in the legend or is it a label? Do you have multiple series in your chart? – alejandro zuleta Oct 25 '16 at 16:47
  • @Dr.MAF, Share the Chart Data settings to see what fields are you using to build your chart. It seems some series are producing Null. – alejandro zuleta Oct 26 '16 at 12:51