Hi,

we have requirement, we need to pivot the rows data in to column.
Our data is in the following format
colA ColB
A 01/01/2010
B 08/03/2010
C 04/02/2010
C 08/01/2010
A 04/01/2010
A 04/02/2010

and we want prepare the report in the below format
COLA JAN FEB MAR
A 2 1 0
B 0 0 1
C 1 1 0

we want to fetch all the records for the year and report it monthly for each item.

Thanks for your help in advance!

This message has been edited. Last edited by: NaviC,

DEFINE FILE yourfile
 JAN/D9 = IF yourdatfield FROM 20100101 TO 20100131 THEN 1 ELSE 0
 FEB/D9 = IF yourdatfield FROM 20100201 TO 20100228 THEN 1 ELSE 0
 MAR/D9 = IF yourdatfield FROM 20100301 TO 20100331 THEN 1 ELSE 0

....you get the point...
END


FILE TABLE yourfile
SUM JAN FEB MAR
BY COLA
ON TABLE PCHOLD FORMAT HTML
END

Even better is to use a function to retrieve the month from the date file ( depending on your date-format ).

This should do the trick....

G'luck

You might convert your dates to the desired output format and use SUM ACROSS. This assumes ColB is a Smart Date.

DEFINE FILE filename
 MONTH_NUM/M   = ColB ;
 MONTH_ABBR/MT = ColB ;
END
-*
TABLE FILE filename
 SUM amount_field
 BY ColA
 ACROSS MONTH_NUM NOPRINT
 ACROSS MONTH_ABBR
END