sorting in cross-tab report

[tanyasharma]

Hi I am making a cross-tab report in which I taking months as report's columns.My problems are -
a) When my report gets values for 12 months it prints 12 columns and when values are for 5 months it shows 5 columns. Is there any way to fix the number of coluns to 12.
b) Second is very important problem. I want to allow user to sort the cross-tab report on the basis of rows as well as Row total column. I tried by passing ORDER BY clause with SQL string but it couldn't do any sorting. Please help me in this regard
Thanks

 

[AndrewBaines]

The sorting can be done in the Cross-Tab format - group options - in CR8 and later - not sure about 7.
To get the years there are several options:
easiest I find is to edit the SQL to add some dummy rows with UNIONs. For example, I'm using the XTreme sample database for 1996 only:
SELECT
Customer.`Customer Name`,
Orders.`Order Date`,
Orders.`Order Amount`
FROM
`Customer` Customer INNER JOIN `Orders` Orders ON Customer.`Customer ID` = Orders.`Customer ID`
WHERE
Orders.`Order Date` BETWEEN {ts '1996-01-01 00:00:00.000'} AND {ts '1996-12-31 00:00:00.000'}
UNION
SELECT 'Dummy',0,{ts '1996-01-01 00:00:00.000'}
FROM `Customer` Customer
UNION
SELECT 'Dummy',0,{ts '1996-02-01 00:00:00.000'}
FROM `Customer` Customer
UNION
SELECT 'Dummy',0,{ts '1996-03-01 00:00:00.000'}
FROM `Customer` Customer
UNION........

This gives an entry for each month in 1996 whether there were any sales or not. Andrew Baines
Chase International