Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] OVER, SUM, ACROSS & BY
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] OVER, SUM, ACROSS & BY
 Login/Join
 
<José Andrés Vargas Aguilar>
posted
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>,
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
<José Andrés Vargas Aguilar>
posted
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!
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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


Francis


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
<José Andrés Vargas Aguilar>
posted
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.
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] OVER, SUM, ACROSS & BY

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.