cancel
Showing results for 
Search instead for 
Did you mean: 

Displaying values in specified order (including nulls) in a bar chart.

Former Member
0 Kudos

Hello,

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.

Suggestions, please?

Accepted Solutions (1)

Accepted Solutions (1)

former_member260594
Active Contributor
0 Kudos

Evan,

Try creating a crosstab with departments as the columns and status as the rows and the count as the summarized value. Apply your specified order to the crosstab.

Then right click on the crosstab and select insert chart. As the crosstab chart is reading the columns and rows it won't "skip" the null values.

Former Member
0 Kudos

Thanks, that worked great!

The specified order for the status feilds wasn't working, but I was able to get around it by renaming them with a number in front. (e.g. 1: Received, 2: Available, etc.)

Now my chart looks exactly like I wanted it to!

Answers (0)