>>I'm trying to make a cross-tab report. It is to show the number of times an order for a report was requested - by month and state.
>>
>>For example:
>>---USAGE REPORT---
>>Worker's Comp.
>>State Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
>>AK
>>AL
>>AZ
>>CA
>>NY
>>OH
>>PA
>>WY
>>
>>with the appropriate frequency counts in each row/col.
>>
>>I thought I could do this with one SQL, but it's not working like I thought. Here's the SQL
>>
>>SELECT;
>> Orderitems.report_nam,;
>> Orderitems.state,;
>> COUNT(MONTH(Orderitems.closed)==1) as January,;
>> COUNT(MONTH(Orderitems.closed)==2) as Febuary;
>> COUNT(*) as Total;
>> FROM ofax!orderitems;
>> WHERE NOT EMPTY(Orderitems.state) = .T.;
>> AND NOT EMPTY(Orderitems.closed) = .T.;
>> GROUP BY Orderitems.report_nam, Orderitems.state;
>> ORDER BY Orderitems.report_nam, Orderitems.state
>>
>>The first 2 COUNT()'s give me the same value as the Total count for that report for that state.
>>
>>Is there any way to get the count for each month and the total?
>>Can it be done in one REASONABLE SQL? or will I need one for each month?
>Hi Richard,
>(waiting authorization for ICQ) could you postpone month naming ? I think this would work :

SELECT;
>  report_nam,;
>  state,;
>  COUNT(*) as MonthCnt ;
> FROM ofax!orderitems;
> WHERE NOT EMPTY(state) ;
>   AND NOT EMPTY(closed) ;
> GROUP BY report_nam, state, closed
>DO (_GENXTAB) WITH 'XTAB',.t.,.t.,.t.,,,,.t.,0,.f.
>select xtab
>browse

Cetin
Ooops. Months gone !

SELECT;
  report_nam,;
  state,;
  monthname(closed) as Month ;
  COUNT(*) as MonthCnt ;
 FROM ofax!orderitems;
 WHERE NOT EMPTY(state) ;
   AND NOT EMPTY(closed) ;
 GROUP BY report_nam, state, closed ;
 into cursor tmpCrs
DO (_GENXTAB) WITH 'XTAB',.t.,.t.,.t.,,,,.t.,0,.f.
select xtab
browse

function monthname
lparameters tnMonth
return cmonth(ctod(str(tnMonth)+"/1/1998")) && mdy assumed to keep short

Cetin