Hi all,i have one requremnt like the below report here the difficulty is the model is not there in database and each country is seprate column (Measure)in the table, i tried with OVER but i didn't get the structure like belo report.

quote:

quote:


Model Country Cost Sales
Model1 Country1 $10000 $50
Country2 $15000 $40
Country3 $20000 $20
Model2 Country1 $20000 $30
Country2 $25000 $20
Country3 $10000 $10
Total Country1 $30000 $80
Country2 $40000 $60
Country3 $30000 $30

Hi Kalyan,

Please try the following suggestion:

Focexec:

DEFINE FILE MYTEMP                                       
CNT/I1 =CNT +1;                                          
IVAL/I1=EDIT(EDIT(COUNTRY,'$$$$$$$9'));                  
FLAG/I1=IF CNT  LE 1         THEN 1 ELSE                 
        IF IVAL LE LAST IVAL THEN 1 ELSE 0;              
XFLAG/I1=IF CNT  LE 1        THEN 1 ELSE                 
         IF IVAL LE LAST IVAL THEN XFLAG +1 ELSE XFLAG ; 
XMODEL/A8= 'MODEL' | EDIT(XFLAG);                        
END                                                      
TABLE FILE MYTEMP                                        
PRINT COUNTRY  COST SALES                                
BY XMODEL                                                
END

Data:
COUNTRY11000050
COUNTRY21500040
COUNTRY32000020
COUNTRY12000030
COUNTRY22500020
COUNTRY31000010
COUNTRY13000080
COUNTRY24000060
COUNTRY33000030

Master:
FILENAME=MYTEMP, SUFFIX=FIX,
FIELD=COUNTRY, ALIAS=ID, FORMAT=A8,A8,$
FIELD=COST , ALIAS=LN, FORMAT=P12Mc,A5,$
FIELD=SALES , ALIAS=FN, FORMAT=P12Mc,A2,$


Hope this helps.

Cheers,

Kerry