I have a bar chart in which I am displaying a count of our database grouped first by department and then by status. There are five departments and five possible status categories. I have my chart set up so that on change of department and status I show value count of status. I have set specified orders for both my departments and my statuses, but the specified order only works if all statues are present in the first department to be displayed.
For example. If department 1 contains statuses 1, 3 and 5, and department 2 contains statuses 1 through 5, the order of bars in my chart and in my legend will be 1, 3, 5, 2, 4.
The problem seems to be the null values for statuses 2 and 4 in the first department. What I would like to have is a blank space on the chart for null values in the first department like there are for departments further down the list. Then I could have a logical progression to my statuses and keep the same order and color scheme from day to day
After reading other similar problems I've tried converting my database and other NULL values to default with no results. I've also tried a left outer join linking to a database table in which all statuses are present, but that hasn't worked either.