Series Order in TDBChart with CrossTab

[kimmerkc]

Hello,

I have a TDBChart containing (at design time) a single TBarSeries
whose DataSource is a TDBCrossTabSource. I'm setting the Query and
other aspects of the CrossTabSource at Run Time. This generates a
number of series displayed at run time on the graph. So far, so good.

Here's a partial code sample of how the CrossTab is currently
configured:
---
GraphQry.SQL.Add(
'SELECT a.pick_area_cd as LabelMe, a.pick_stat_cd as Status, '
+
' l.pick_stat_tx as GroupMe, count(a.ord_id) as CountMe
' +
'FROM ...
GraphQry.SQL.Add('GROUP BY 1,2,3');
GraphQry.SQL.Add('ORDER BY LabelMe, Status');

GraphQry.Open;

Series1.DataSource := DBCrossTabSource1;
Series1.XLabelsSource := '#SORTASC#LabelMe';
Series1.YValues.ValueSource := '#SUM#CountMe';

DBCrossTabSource1.DataSet := GraphQry;
DBCrossTabSource1.GroupField := 'GroupMe';
DBCrossTabSource1.ValueField := 'CountMe';
DBCrossTabSource1.LabelField := 'LabelMe';
DBCrossTabSource1.Series := Series1;
DBCrossTabSource1.Active := TRUE;
---

Here's the problem. The series generated from the crosstab don't
appear to be in any particular order. I would like to dictate the
order in which they display in the Legend and in which the bars appear
in the graph. The Order By in the query seems to properly order the X
Values on the graph, but does not seem to effect the order of Series
(which are labelled by the "GroupMe" field). I have tried using
"GroupMe" as the secondary sort, instead of "Status" (the former is a
text lookup value for the later) with no apparent difference.

Any ideas?

[kimmerkc]

Responding to my own message:
Turns out the ORDER BY in the query *is* dictating the order of
series. The problem is that if my first X value does not have a
corresponding value record for all of the possible series groups, they
are not encountered in order and are created in the order in which
they *are* encountered.

I can partially fix this with an outer join, but the Null values in
the X value field are causing trouble.

So I'm still open to alternate suggestions.

Thanks!
Kimberly