Stacked Bar Graph data sort 1

[bobs67]

Is there any way to sort data on the stacked bar graph in a specified order? Lets say I have the following stacked field values on each bar:

baseball
foot
football
game
hand

and want it to appear in the following order:

foot
hand
baseball
football
game

I tried to sort the field in question in "specified order" but its not working for some reason. Thanks for the help in advance!

 

[lbass]

For the specified order to work correctly for stacked bar charts, there needs to be a value for each stacked group per x-axis value. If some values are null, the order doesn't work.

There was a recent post where the OP added 0 values (which wouldn't contribute to the stack, but allowed the values to sort correctly), but I can't remember the specific technique.

-LB

 

[bobs67]

lbass - thanks for the quick response!

I didnt know null values messed up the specified order. I was partially successful using a cross-tab to sort the above mentioned data properly and basing the stacked graph on it, but it generated 0 counts that, even after suppressing them on the cross-tab, I could not suppress on the resulting graph. Based on your post, the 0 values were what made the sort work properly.

 

[lbass]

They have to be real zero values in the data, not just represented as zeros in the crosstab. I guess you could use a command as a datasource for the chart where you add zero values in a union all:

select table.sport, table.number, table.otheronchangeoffield
from table
union all
select table.sport, 0, table.otheronchangeoffield
from all

-LB

 

[bobs67]

Interesting idea - unfortunately I cannot modify table/view structure of this database. As I mentioned before, I was sort of successful using a cross-tab, but if the counts of baseball, foot, etc.. were 0, then the 0's actually showed up on the graph - I am wondering if there is a way to suppress it on the graph, same as suppressing it on the crosstab?

 

[lbass]

Using a command doe not modify the table/view structure of the database. If you see "add command" above your table list in the database expert, you can write a command.

I didn't think zero values would appear in a stacked bar chart. You are saying that they do? Where? In the data labels?

-LB

 

[bobs67]

They appear as Data Points/Show Value as counts of each stack in the bar graph when it is based on a crosstab.

 

[lbass]

I don't think there is a ready fix for this--except to not show the values in the chart.

-LB

 

[bobs67]

Got it - thanks much for all the help!