SSRS 2005 column chart: show series label missing when data count is zero

Question

I have a pretty simple chart with a likely common issue. I've searched for several hours on the interweb but only get so far in finding a similar situation.

the basics of what I'm pulling contains a created_by, person_id and risk score the risk score can be:

1 VERY LOW 2 LOW 3 MODERATE STABLE 4 MODERATE AT RISK 5 HIGH 6 VERY HIGH

I want to get a headcount of persons at each risk score and display a risk count even if there is a count of 0 for that risk score but SSRS 2005 likes to suppress zero counts.

I've tried this in the point labels =IIF(IsNothing(count(Fields!person_id.value)),0,count(Fields!person_id.value))

Ex: I'm missing values for "1 LOW" as the creator does not have any "1 LOW" they've assigned risk scores for.

*here's a screenshot of what I get but I'd like to have a column even for a count when it still doesn't exist in the returned results.

@Nathan

Example scenario:

select professor.name, grades.score, student.person_id from student
inner join grades on student.person_id = grades.person_id
inner join professor on student.professor_id = professor.professor_id
where student.professor_id = @professor

Not all students are necessarily in the grades table.

I have a =Count(Fields!person_id.Value) for my data points & series is grouped on =Fields!score.Value

If there were a bunch of A,B,D grades but no C & F's how would I show labels for potentially non-existent counts

Answer 1

In your example, the problem is that no results are returned for grades that are not linked to any students. To solve this ideally there would be a table in your source system which listed all the possible values of "score" (e.g. A - F) and you would join this into your query such that at least one row was returned for each possible value.

If such a table doesn't exist and the possible score values are known and static, then you could manually create a list of them in your query. In the example below I create a subquery that returns a combination of all professors and all possible scores (A - F) and then LEFT join this to the grades and students tables (left join means that the professor/score rows will be returned even if no students have those scores in the "grades" table).

SELECT 
    professor.name
  , professorgrades.score 
  , student.person_id
FROM
(
  SELECT professor_id, score
  FROM professor
  CROSS JOIN
   ( 
     SELECT 'A' AS score
     UNION
     SELECT 'B'
     UNION
     SELECT 'C'
     UNION
     SELECT 'D'
     UNION
     SELECT 'E'
     UNION
     SELECT 'F'
   ) availablegrades
 ) professorgrades
INNER JOIN professor ON professorgrades.professor_id = professor.professor_id
LEFT JOIN grades ON  professorgrades.score = grades.score
LEFT JOIN student ON grades.person_id = student.person_id AND 
              professorgrades.professor_id = student.professor_id

WHERE professorgrades.professor_id = 1

See a live example of how this works here: SQLFIDDLE