Sort order in crosstab columns

rbuser

Hi there,

I have a problem with logical ordering of my crosstab columns. In some
crosstabs I have buildings and floors as column headers. But RBuilder
naturally sorts them with an alphanumerical sort order, e.g. for the
buildings "Hall 1", "Hall 10", "Hall 2" which should be "Hall 1", "Hall 2",
"Hall 10". The same with floors (I don't have the english abbreviations to
hand): "1. OG", "1. UG", "2. OG", "EG" which should be "1. UG", "EG", "1.
OG", "2.OG".
So I need to implement a logical ordering for the columns. Where would I do
that?
It certainly does not work with an SQL Statement because it sorts
alphanumerical too. It has to be somewhere inside the crosstab

regards

Andreas Kr?gersen

digitalmetaphors

You can trick the crosstab into ordering the data alphabetically but still
in the natural order by prefixing each data field value with aaa, aab, aac,
aad,... The the natural order of the field will be preserved. Then when
the crosstab goes to render the cell, you can change the text by trimming
the prefixed characters off of the value. Use the
TppCrossTab.OnGetValueText event to trim the text.

Cheers,

Jim Bennett
Digital Metaphors

rbuser

I tried that but I must do something wrong. Look at the following source
code. In the report's BeforePrint event I call this procedure to change the
caption strings that will later be shown in the column headers. Then I
checked TppCrossabEngine.Calc but the new values are not used. Instead the
old strings are used. I guess it doesn't work by modifying the data fields
themselves. But how would I do it then?
Note: I cannot add a prefix manually but have to calcutate it in code
because every customer has his own set of data that must not be modified.

procedure Test(dbPipe: TppDBPipeline);
var
strField: string;
val: Integer;
varField: Variant;
begin
dbPipe.First;
while not dbPipe.EOF do
begin
strField := dbPipe.GetFieldAsString('GEB?UDE');

//some string manipulation here

varField := strField;
dbPipe.SetFieldValue('GEB?UDE', varField);

dbPipe.Next;
end;

end;

regards

Andreas Kr?gersen

digitalmetaphors

Ok, create an integer calculated field on the dataset which has only the
number extracted from the field which is dispaying 'Hall xx' Use this field
to create the crosstab definition so that it orders by xx and then add the
'Hall ' prefixed to the cell's text in the OnGetValueText.


Cheers,

Jim Bennett
Digital Metaphors