>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