Ordering columns in a matrix

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 29 Jun 2005 23:42 GMT

Is is possible to order the columns in a matrix? In this case, the number of
columns is always the same.

So, for example, if the columns were Car, Truck, SUV, and Bike, can you code
MSRS to report them in the order Truck, Car, SUV, Bike?

Thanks,
Andy

Reply to this Message

Dan Christjohn - 30 Jun 2005 00:42 GMT

Put the order by in the query and it should work fine.

SELECT
myfield1, myfield2, myfield3
FROM
mytable
WHERE
mycriteria
ORDER BY
myfield2, myfield1, myfield3

Good luck!

Dan

> Is is possible to order the columns in a matrix? In this case, the number
> of
[quoted text clipped - 6 lines]
> Thanks,
> Andy

Reply to this Message

Andy - 30 Jun 2005 16:49 GMT

Thanks for your reply. It helped me find a solution. Unfortunately in this
case, the order by doesn't work because the column names are not sequential.

ORDER BY gives me the order of Bike, Car, Truck, SUV and ORDER BY DESC
obviously gives the reverse.

To get it in the order I needed, I did the following:

SELECT
CASE WHEN VehicleType = 'Car' THEN '1'
WHEN VehicleType = 'Truck' THEN '2'
WHEN VehicleType = 'SUV' THEN '3'
ELSE '4'
END AS SortCol
VehicleType,
Count(*)
FROM mytable
WHERE mycriteria
ORDER BY SortCol

Basically, you create a dummy sortable column that you never display and,
like you said, once you sort it in the query it will sort in the report.
Thanks again.

> Put the order by in the query and it should work fine.
>
[quoted text clipped - 21 lines]
> > Thanks,
> > Andy

Reply to this Message