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
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>,
April 29, 2010, 01:03 PM
Kerry
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
Kerry Zhan Focal Point Moderator Information Builders, Inc.
May 12, 2010, 01:22 PM
<José Andrés Vargas Aguilar>
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!
May 12, 2010, 01:33 PM
Francis Mariani
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
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 13, 2010, 05:18 PM
Dan Satchell
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
WebFOCUS 7.7.05
May 13, 2010, 07:30 PM
<José Andrés Vargas Aguilar>
Thanks Dan. I've tried your suggestion before. The fex does run but the data gets all scrambled up on HTML.