How can I make empty groups of a bar chart visible in SSRS?

Question

I have a bar chart that displays # of Work Orders on the X axis and groups them into 3 categories on the Y axis (Overdue, 0 to 7 days, 8 to 28 days). The report is setup for the user to select a parameter (in this case an NYC boro) in order to run the report. In some cases, the borough selected does not return values for all 3 of the groups on the Y axis. How do I force SSRS to display all of the categories on the Y axis even when those groups are empty. In the example image included, the "0 to 7 Days" category is not showing up.

Answer 1

You need to create a table with a list of your categories in and then left join your current query to that. Without seeing your current query I can;t give the best answer as you may already have all the categories in a table that you could already use but this will still work...

DECLARE @cat TABLE(CategoryName varchar(20))
INSERT INTO @cat VALUES
    ('Overdue'),
    ('8 to 28 days'),
    ('0 to 7 days')

SELECT 
    c.CategoryName, q.*
    FROM @cat c
        LEFT JOIN (SELECT * FROM YourOriginalQuery) q
            ON c.CategoryName  = q.myOriginalCategoryName

As you are now left joining from a list of categories, each category name will be present in your dataset (unless of course your WHERE clause filters it out).

If you edit your question showing your current query, there may be a much better way to achieve the same result.