Series colours are different to legend colours in chart

Question

I'm building an incident report with the below query:

SELECT Incident_Logged
      ,Location_Name
      ,Incident_Type
      ,Body_Part_Name
FROM Incident
INNER JOIN Location ON Location_ID = Incident_Location_ID
INNER JOIN Body_Part ON Body_Part_ID = Incident_Body_Part_ID
WHERE Incident_Logged BETWEEN @StartDate and @EndDate
AND Incident_Location_ID IN (@Location)
ORDER BY Incident_Logged DESC

I've also created a chart, however for some reason the colours in the chart don't line up with the legend. Any ideas?

score 0 · Accepted Answer · answered Oct 04 '17 at 23:48

Turns out this is a ~~feature~~ ~~bug~~ known issue that happens if you use the COUNT() aggregate. I resolved this issue by adding a pseudo-column with the value of 1 in the query:

SELECT Incident_Logged
      ,Location_Name
      ,Incident_Type
      ,Body_Part_Name
      ,1 Incident_Sum
FROM Incident
INNER JOIN Location ON Location_ID = Incident_Location_ID
INNER JOIN Body_Part ON Body_Part_ID = Incident_Body_Part_ID
WHERE Incident_Logged BETWEEN @StartDate and @EndDate
AND Incident_Location_ID IN (@Location)
ORDER BY Incident_Logged DESC

Once you do this, change the value to the Sum of the new column. Once you do that, your colours should line up.

Source: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0dc8aad4-b846-476d-a429-f8fc2312c585/ssrs-2008-chart-legend-colours-not-matching-series-colour?forum=sqlreportingservices - look for "softworks phil smith"'s answer.

Series colours are different to legend colours in chart

1 Answers1