Hello, i'm having some trouble for using OVER with SUM, ACROSS & BY.
When I place OVER next to the fields I want to be over the rest all of the report gets scooped and looses the correct position according to the headers.

Can anyone tell me where I should place the OVER or how should I rearrange my fields for them to work with OVER please?

You'll see that only to columns are shown in the report: "Monto" & "Cant.". "Monto" should be OVER "Cant.".

Thanks a lot!

This is the code to my fex:


DEFINE FILE SQLOUT
MONTOS/D20.2 = MONTO;
CONMORA/D20.2 = IF DIAS GT 0 THEN MONTO ELSE 0;
MORA30/D20.2 = IF DIAS GT 30 THEN MONTO ELSE 0;
CANTIDADES/D20 = CANTIDAD;
CANTMORA/D20 = IF DIAS GT 0 THEN CANTIDAD ELSE 0;
CANT30/D20 = IF DIAS GT 30 THEN CANTIDAD ELSE 0;
MORA/A60V = IF MOROSIDAD EQ '0 - 0' THEN 'MAYOR' ELSE MOROSIDAD;
DIA/A60V = IF DIAS EQ 0 THEN 'AL DIA' ELSE MORA;
INTERES/A60V = IF INTERESES EQ '0% - 0%' THEN 'MAYOR' ELSE INTERESES;
SALDO/A60V = IF SALDOS EQ '0 - 0' THEN 'MAYOR' ELSE SALDOS;
PLAZO/A60V = IF PLAZOS EQ '0 - 0' AND DIASPLAZO GT 0 THEN 'MAYOR' ELSE IF PLAZOS EQ '0 - 0' THEN 'AL DIA' ELSE PLAZOS;
END

TABLE FILE SQLOUT

SUM
MONTOS NOPRINT
CONMORA NOPRINT
MORA30 NOPRINT
CANTIDADES NOPRINT
CANTMORA NOPRINT
CANT30 NOPRINT

SUM
MONTOS NOPRINT
CONMORA NOPRINT
MORA30 NOPRINT
CANTIDADES NOPRINT
CANTMORA NOPRINT
CANT30 NOPRINT

BY
MONEDA AS 'Moneda'

BY &wf_agrupa

SUM
MONTOS AS 'Monto'
CONMORA NOPRINT
MORA30 NOPRINT
CANTIDADES AS 'Cant.'
CANTMORA NOPRINT
CANT30 NOPRINT

BY
MONEDA AS 'Moneda'

BY &wf_agrupa

ACROSS MORAMIN NOPRINT
ACROSS DIA AS 'Dias de Mora'
COMPUTE TVAL/D12.2 = C7; AS 'Total'
COMPUTE TCAN/D12.2 = C10; AS ''
COMPUTE PVAL/D12.2% = 100*C7/C1; AS 'Concentracion'
COMPUTE PCAN/D12.2% = 100*C10/C4; AS ''
COMPUTE CONM/D12.2% = 100*C8/C2; AS 'Mora'
COMPUTE CANM/D12.2% = 100*C11/C5; AS ''
COMPUTE MOR3/D12.2% = 100*C9/C3; AS 'Mora 30'
COMPUTE CAN3/D12.2% = 100*C12/C6; AS ''

ON MONEDA SUBTOTAL AS 'Total por Moneda'



Thanks a lot!

This message has been edited. Last edited by: <José Andrés Vargas Aguilar>,

Hi Jose,

I passed this one to our technicals and was suggested that the code runs fine, so can you please provide some more details on what you are looking for? To be more specific, can you please provide an example of what you are getting and what you would like to see?

Cheers,

Kerry

Yes, the code works just fine.

If you see I have two columns per each across detail: Monto & Cant.

So What I would like to do is to place Monto over Cant. So the output would be this:

MONTO A B C D ...
CANT. A B C D ...

And not this:
MONTO CANT.
A A
B B
C C
D D
... ...

Thanks for the support!

Here's one idea, extract the data into two hold files and then concatenate them with the MORE command:

-*-- across6.fex

SET STYLEMODE=FIXED
TABLE FILE CAR
SUM
COMPUTE MEASURE_DESC/A10 = 'WEIGHT';
MEASURE_AMT/D10 = WEIGHT;
BY COUNTRY
BY SEATS
ON TABLE HOLD AS H_WEIGHT
END
-RUN

TABLE FILE CAR
SUM
COMPUTE MEASURE_DESC/A10 = 'HEIGHT';
MEASURE_AMT/D10 = HEIGHT;
BY COUNTRY
BY SEATS
ON TABLE HOLD AS H_HEIGHT
END
-RUN

TABLE FILE H_WEIGHT
SUM

MEASURE_AMT
BY COUNTRY
BY MEASURE_DESC AS ''
ACROSS SEATS

ON COUNTRY SUBFOOT
" "

MORE
FILE H_HEIGHT
END
-RUN

Result:

 PAGE     1
 
 
                         SEATS
                                     2              4              5
 COUNTRY
 ---------------------------------------------------------------------
 ENGLAND     HEIGHT                 98             53             54
             WEIGHT              5,676          4,000          4,200
  
 FRANCE      HEIGHT                  .              .             57
             WEIGHT                  .              .          2,860
  
 ITALY       HEIGHT                147             56              .
             WEIGHT              8,325          2,590              .
  
 JAPAN       HEIGHT                  .            108              .
             WEIGHT                  .          4,269              .
  
 W GERMANY   HEIGHT                  .             56            336
             WEIGHT                  .          2,400         11,471

I'm not sure how this kind of approach will work with your COMPUTEs, but it uses OVER to achieve the desired result of one measure over another.

DEFINE FILE CAR
 TOTHW/D8 = HEIGHT + WEIGHT ;
END
-*
TABLE FILE CAR
 SUM HEIGHT
 OVER WEIGHT
 OVER TOTHW AS 'Total'
 BY COUNTRY
 ACROSS SEATS
 ON TABLE ROW-TOTAL AS 'Total'
END

Thanks Dan. I've tried your suggestion before.
The fex does run but the data gets all scrambled up on HTML.

About Francis' option, I'm still testing.