H folks,
I am looking for a solution to handle a SUBFOOT with an ACROSS. I read some topics but I couldn´t find a solution yet.

Can anyone give me a hint?

Thanks

My report is:

 

TABLE FILE HX2
SUM 
VENTAS  AS 'Ventas'
VENTAS_IND_SEG  NOPRINT 
VENTAS_IND_SUBSEG  NOPRINT

COMPUTE PCT_VENTAS/D8.1 = (C1/C3)*100;
COMPUTE PCT_TOT_VENTAS/D8.1 = (C1/C2)*100;
ACROSS MONTH NOPRINT
ACROSS MONTH_REPORT AS ''
BY SUBSEG AS ''
ON MONTH SUBFOOT
"<PCT_TOT_VENTAS"
END
 

I reproduced it using CAR here:

SET ASNAMES=ON

TABLE FILE CAR
SUM SALES AS 'SALES_BODY'
BY BODYTYPE
SUM  SALES  AS 'SALES_MODEL'
BY BODYTYPE
BY MODEL
SUM  SALES 
BY BODYTYPE
BY MODEL
BY COUNTRY
ON TABLE HOLD AS H1
END


TABLE FILE H1
SUM 
SALES
SALES_BODY NOPRINT
SALES_MODEL NOPRINT
COMPUTE PCT_A/D8.1 = (C1/C3)*100;
COMPUTE PCT_TOT/D8.1 = (C1/C2)*100; NOPRINT 
ACROSS COUNTRY
-* I need it summarized, without this BY COUNTRY
BY COUNTRY NOPRINT
ON COUNTRY RECAP
TOT/I9 = C1;
ON COUNTRY SUBFOOT
"TOTAL: <TOT<PCT_TOT"
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, $
TYPE=SUBFOOT, HEADALIGN=BODY, 	$
TYPE=SUBFOOT,
	 BY=COUNTRY,
	 SIZE=9,
	 OBJECT=FIELD,
	 JUSTIFY=RIGHT, $
END
  

This message has been edited. Last edited by: Ricardo Augusto,

Any help?

Hi Ricardo,

Can you please try the below solution? It may be helpful for you.

SET ASNAMES=ON

TABLE FILE CAR
SUM SALES AS 'SALES_BODY'
BY BODYTYPE
SUM  SALES  AS 'SALES_MODEL'
BY BODYTYPE
BY MODEL
SUM  SALES 
BY BODYTYPE
BY MODEL
BY COUNTRY
ON TABLE HOLD AS H1
END

DEFINE FILE H1
FLG/A1='A';
END


TABLE FILE H1
SUM SALES NOPRINT SALES_BODY NOPRINT  SALES_MODEL NOPRINT
SUM 
SALES
SALES_BODY 
SALES_MODEL NOPRINT
COMPUTE PCT_A/D8.1 = (SALES/SALES_MODEL)*100; 
COMPUTE PCT_TOT/D8.1 = (SALES/SALES_BODY)*100;  
ACROSS COUNTRY
-* I need it summarized, without this BY COUNTRY
BY FLG NOPRINT
ON FLG RECAP
TOT/I9 = SALES;
PCT_TOT1/D8.1 = (C1/C2)*100; 
ON FLG SUBFOOT
"TOTAL: <SALES<PCT_TOT1"
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, $
TYPE=SUBFOOT, HEADALIGN=BODY, 	$
TYPE=SUBFOOT,
	 BY=COUNTRY,
	 SIZE=9,
	 OBJECT=FIELD,
	 JUSTIFY=RIGHT, $
END
  

Almost that.

I need PCT_TOT to each COUNTRY.

quote:

I need PCT_TOT to each COUNTRY.

You mean to say, you need PC_TOT in subfoot for each country? If so Subfoot will not be the right solution. Better you can go with Appending.

This message has been edited. Last edited by: narumu,

I just need it as I coded using CAR example.

Ricardo,
I guess thats not possible with subfoot.

;(

Is there any other way to show values at their right place?

  
SET ASNAMES=ON

TABLE FILE CAR
SUM SALES AS 'SALES_BODY'
BY BODYTYPE
SUM  SALES  AS 'SALES_MODEL'
BY BODYTYPE
BY MODEL
SUM  SALES 
BY BODYTYPE
BY MODEL
BY COUNTRY
ON TABLE HOLD AS H1
END


TABLE FILE H1
SUM 
SALES
SALES_BODY NOPRINT
SALES_MODEL NOPRINT
COMPUTE PCT_A/D8.1 = (C1/C3)*100;
COMPUTE PCT_TOT/D8.1 = (C1/C2)*100; NOPRINT 
ACROSS COUNTRY
-* I need it summarized, without this BY COUNTRY
BY COUNTRY NOPRINT
ON COUNTRY RECAP
TOT/I9 = C1;
ON COUNTRY SUBFOOT
"TOTAL: <TOT<PCT_TOT"
ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, $
TYPE=SUBFOOT, HEADALIGN=BODY, 	$
TYPE=SUBFOOT,
	 BY=COUNTRY,
	 SIZE=9,
	 OBJECT=FIELD,
	 JUSTIFY=RIGHT, $
END

I am little bit confused with your requirement.If you not having any problem can you please explain it in detail? So that i can explore in some other way.

Is there any work around ou suggestions to have it working?

Ricardo,
I didn't quite get what you are aming at.
Nonetheless, if you want subtotals with across, this should do the trick.

  
-* File ricardo02.fex
SET ASNAMES=ON
-*
DEFINE FILE CAR
BODYTYPE=IF MPG LT 10 THEN 'SEDAN' ELSE
         IF MPG LT 15 THEN 'HARDTOP' ELSE
		 IF MPG LT 20 THEN 'COUPE' ELSE 'ROADSTER';
END		  
-*
TABLE FILE CAR
SUM RCOST AS 'RCOST_BODY'
BY BODYTYPE
SUM  RCOST AS RCOST 
BY BODYTYPE
BY COUNTRY
ON TABLE HOLD AS H1
END
-RUN
DEFINE FILE H1
SP/A1=' ';
END
-*
TABLE FILE H1
SUM 
RCOST
RCOST_BODY 
COMPUTE PCT_TOT/D8.1 = (C1/C2)*100;  
ACROSS COUNTRY
BY SP NOPRINT
BY BODYTYPE
ON SP RECOMPUTE
ON TABLE NOTOTAL
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,
$
ENDSTYLE
END

I changed the values of BODYTYPE in order to populate the output a bit better.
However, if SUBFOOT is a necessity, give an example of what you want to achieve.

I reproduced it using CAR here:
SET ASNAMES=ON

TABLE FILE CAR
SUM SALES AS 'SALES_BODY'
BY BODYTYPE
SUM SALES AS 'SALES_MODEL'
BY BODYTYPE
BY MODEL
SUM SALES
BY BODYTYPE
BY MODEL
BY COUNTRY
ON TABLE HOLD AS H1
END


TABLE FILE H1
SUM
SALES
SALES_BODY NOPRINT
SALES_MODEL NOPRINT
COMPUTE PCT_A/D8.1 = (C1/C3)*100;
COMPUTE PCT_TOT/D8.1 = (C1/C2)*100; NOPRINT
ACROSS COUNTRY
-* I need it summarized, without this BY COUNTRY
BY COUNTRY NOPRINT
ON COUNTRY RECAP
TOT/I9 = C1;
ON COUNTRY SUBFOOT
"TOTAL: ON TABLE SET STYLE *
TYPE=TABFOOTING, HEADALIGN=BODY, $
TYPE=SUBFOOT, HEADALIGN=BODY, $
TYPE=SUBFOOT,
BY=COUNTRY,
SIZE=9,
OBJECT=FIELD,
JUSTIFY=RIGHT, $
END



If you run this code you will see what I want. I just want this but I need it summarized by country.
For each country I have a specific total to show as a SUBFOOT. How I do that?

WebFOCUS 8.1.05 / APP Studio

Hi Ricardo,

What I can suggest you, which is more work I admit but it works, built the report structure yourself.

You will need several steps:

1- extract and hold the detailed lines of your report and add a RowId to them, such as RowId = 100
2- extract and hold the subtotal rows for your report and add RowId to them, such as RowId = 200
3- merge both files together. Both files will need to have the exact same format and because the subtotal file will not have the exact same "keys" as the detailed one, you will have to assigned default values in the subtotal file.
4- other and print merged data in the proper order.

Here a sample. You will then have to play with the formatting as you need:

SET ASNAMES=ON

-* HOLD DETAILED ROWS
TABLE FILE CAR
SUM RETAIL_COST AS 'RETAIL'
BY BODYTYPE
BY COUNTRY
BY MODEL
BY TOTAL COMPUTE ROWID   /P3 = 100;
BY TOTAL COMPUTE ROWTYP  /P3 = 100;
ON TABLE HOLD AS DETDATA
END
-RUN

-* HOLD SUBTOTAL COUNTRY
TABLE FILE CAR
SUM RETAIL_COST AS 'RETAIL'
BY BODYTYPE
BY COUNTRY
BY TOTAL COMPUTE MODEL   /A24 = 'Total Country';
BY TOTAL COMPUTE ROWID   /P3  = 100;
BY TOTAL COMPUTE ROWTYP  /P3  = 200;
ON TABLE HOLD AS CNTRYTOT
END
-RUN

-* HOLD SUBTOTAL BODYTYPE
TABLE FILE CAR
SUM RETAIL_COST AS 'RETAIL'
BY BODYTYPE
BY TOTAL COMPUTE COUNTRY /A10 = '';
BY TOTAL COMPUTE MODEL   /A24 = 'Total Bodytype';
BY TOTAL COMPUTE ROWID   /P3  = 300;
BY TOTAL COMPUTE ROWTYP  /P3  = 200;
ON TABLE HOLD AS BODYTOT
END
-RUN

-* MERGE FILES
TABLE FILE DETDATA
SUM RETAIL
BY BODYTYPE
BY ROWID    NOPRINT
BY COUNTRY
BY ROWTYP   NOPRINT
BY MODEL
MORE
FILE CNTRYTOT
MORE
FILE BODYTOT
END
-RUN

Hi MartinY, thanks for sharing this code. But I need something like SUBFOOT for an ACROSS field.

I need to reproduce this report:

LINK: http://imgur.com/G25o51q

Where I have ACROSS MONTH then I need to calculate TOTALs to % column.

Suggestions?


Thanks

Everything is possible. With my technique you build your data as you which then you display the way you want.

The ACROSS is just a way to display the data. The thing is to not process any TOTAL because you already have built it yourself.

SET ASNAMES=ON

-* HOLD DETAILED ROWS
TABLE FILE CAR
SUM RETAIL_COST AS 'RETAIL_CST'
    DEALER_COST AS 'DEALER_CST'
    COMPUTE PCT /P5.2C% = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100;
BY BODYTYPE
BY COUNTRY
BY MODEL
BY TOTAL COMPUTE ROWID   /P3 = 100;
BY TOTAL COMPUTE ROWTYP  /P3 = 100;
ON TABLE HOLD AS DETDATA
END
-RUN

-* HOLD SUBTOTAL BODYTYPE
TABLE FILE CAR
SUM RETAIL_COST AS 'RETAIL_CST'
    DEALER_COST AS 'DEALER_CST'
    COMPUTE PCT /P5.2C% = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100;
BY BODYTYPE
BY COUNTRY
BY TOTAL COMPUTE MODEL   /A24 = 'Total Bodytype';
BY TOTAL COMPUTE ROWID   /P3  = 100;
BY TOTAL COMPUTE ROWTYP  /P3  = 200;
ON TABLE HOLD AS BODYTOT
END
-RUN
-*
-* HOLD SUBTOTAL COUNTRY
TABLE FILE CAR
SUM RETAIL_COST AS 'RETAIL_CST'
    DEALER_COST AS 'DEALER_CST'
    COMPUTE PCT /P5.2C% = (RETAIL_COST - DEALER_COST) / DEALER_COST * 100;
BY TOTAL COMPUTE BODYTYPE/A12 = '';
BY COUNTRY
BY TOTAL COMPUTE MODEL   /A24 = 'Total Country';
BY TOTAL COMPUTE ROWID   /P3  = 300;
BY TOTAL COMPUTE ROWTYP  /P3  = 200;
ON TABLE HOLD AS CNTRYTOT
END
-RUN

-* MERGE FILES
TABLE FILE DETDATA
SUM RETAIL_CST
    DEALER_CST
    PCT
BY ROWID    NOPRINT
BY BODYTYPE
BY ROWTYP   NOPRINT
BY MODEL
ACROSS COUNTRY
MORE
FILE BODYTOT
MORE
FILE CNTRYTOT
END
-RUN

I am almost there MartinY.

I got the correct values but I couldn´t merge them.


Any suggestion?

 
-*************************
-* CALCULATING TOTAL LINE
-*************************
DEFINE FILE HTMP023
TOTAL_SUBSEG/A50 = 'TOTAL';
END

TABLE FILE  HTMP023
SUM VENTAS_SUBSEG_FORD AS 'X'  NOPRINT
VENTAS_SUBSEG NOPRINT
BY TOTAL_SUBSEG
SUM
DST.TOTAL_YEAR NOPRINT
TOTAL_LAST_YEAR NOPRINT
TOTAL_LAST_YEAR_FORD NOPRINT
TOTAL_LAST_YEAR_SUBSEG NOPRINT
TOTAL_LAST_YEAR_SUBSEG_FORD NOPRINT
DST.TOTAL_MONTH NOPRINT
VENTAS_SUBSEG  NOPRINT
VENTAS_SUBSEG_FORD
COMPUTE PCT/D8.1 = (VENTAS_SUBSEG_FORD/DST.TOTAL_MONTH)*100; 
BY TOTAL_SUBSEG
ACROSS SRSD83_PERIOD_R 
COMPUTE ACU/I11 = C1;
COMPUTE ACU_SUBSEG/I11 = C2; NOPRINT
COMPUTE PCT_ACU/D8.1 = (ACU/DST.TOTAL_YEAR)*100;
COMPUTE X/D8 = TOTAL_LAST_YEAR_SUBSEG_FORD; 
-*COMPUTE Y/D8 = TOTAL_LAST_YEAR; NOPRINT
COMPUTE Z/D8.1 = (TOTAL_LAST_YEAR_SUBSEG_FORD/TOTAL_LAST_YEAR)*100;  
IF SUBSEG EQ (HSUBSEG_FORD)
IF YEAR EQ '&YEAR'
ON TABLE HOLD AS HTOTALS
END

-* DETAILS
TABLE FILE HTMP023
SUM VENTAS_SUBSEG_FORD AS 'X' NOPRINT
VENTAS_SUBSEG NOPRINT
BY SUBSEG
SUM
DST.TOTAL_YEAR NOPRINT
TOTAL_LAST_YEAR NOPRINT
TOTAL_LAST_YEAR_FORD NOPRINT
TOTAL_LAST_YEAR_SUBSEG NOPRINT
TOTAL_LAST_YEAR_SUBSEG_FORD NOPRINT
DST.TOTAL_MONTH NOPRINT
VENTAS_SUBSEG  NOPRINT
VENTAS_SUBSEG_FORD
COMPUTE PCT/D8.1 = (VENTAS_SUBSEG_FORD/VENTAS_SUBSEG)*100; AS '%'
BY SUBSEG
ACROSS SRSD83_PERIOD_R 
COMPUTE ACU/I11 = C1;
COMPUTE ACU_SUBSEG/I11 = C2; NOPRINT
COMPUTE PCT_ACU/D8.1 = (ACU/ACU_SUBSEG)*100; AS '%'
COMPUTE X/D8 = TOTAL_LAST_YEAR_SUBSEG_FORD; AS '&LAST_YEAR ACU'
-*COMPUTE Y/D8 = TOTAL_LAST_YEAR_SUBSEG; NOPRINT
COMPUTE Z/D8.1 = (TOTAL_LAST_YEAR_SUBSEG_FORD/TOTAL_LAST_YEAR_SUBSEG)*100; AS '%'
IF SUBSEG EQ (HSUBSEG_FORD)
IF YEAR EQ '&YEAR'
END

 

HTOTALS has the correct values to my total line and the second report has the correct values. How do I merge them?

Hi Ricardo,

A bit of Dialog Manager to the rescue:

  
-* File ricardo03.fex
SET ASNAMES=ON
-*
DEFINE FILE CAR
BODYTYPE=IF MPG LT 10 THEN 'SEDAN' ELSE
         IF MPG LT 15 THEN 'HARDTOP' ELSE
		 IF MPG LT 20 THEN 'COUPE' ELSE 'ROADSTER';
END
-*
TABLE FILE CAR
SUM RCOST AS 'RCOST_BODY'
BY BODYTYPE
SUM  RCOST AS RCOST
BY BODYTYPE
BY COUNTRY
ON TABLE HOLD AS H1
END
-RUN
-*
TABLE FILE H1
BY COUNTRY
ON TABLE SAVE
END
-RUN
-SET &C=&LINES;
-RUN
TABLE FILE H1
SUM
RCOST
RCOST_BODY
COMPUTE PCT_TOT/D8.1 = (C1/C2)*100;
BY COUNTRY NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-SET &SUBTOT='"Totals';
TABLE FILE H1
SUM
RCOST
RCOST_BODY
COMPUTE PCT_TOT/D8.1 = (C1/C2)*100;
ACROSS COUNTRY
BY BODYTYPE
ON TABLE SUBFOOT
-REPEAT #TOTS FOR &I FROM 1 TO &C;
-READFILE HOLD 
-SET &RCOST=FPRINT(&RCOST,'D7','A9');
-SET &RCOST_BODY=FPRINT(&RCOST_BODY,'D7','A9');
-SET &PCT_TOT=FPRINT(&PCT_TOT,'D8.1','A9');
-SET &SUBTOT=&SUBTOT || ' <0 <0 ' | &RCOST | '<0 ' | &RCOST_BODY | '<0 ' | &PCT_TOT ;
-#TOTS
-SET &SUBTOT=&SUBTOT || '"';
&SUBTOT 
ON TABLE NOTOTAL
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,
$
TYPE=TABFOOTING, HEADALIGN=BODY, SIZE=10, JUSTIFY=RIGHT, $
TYPE=TABFOOTING, ITEM=1, size=10, JUSTIFY=LEFT, $

ENDSTYLE
END

This message has been edited. Last edited by: Danny-SRL,

Hi Ricardo,

1- Replace the ACROSS by a BY in both TABLE FILE : as far as I know (I've never tried it) you cannot merge files defined with ACROSS.

2- Remove the: AS '%' in the detailed file.
Both files (detail and totals) must have the exact same format and field's name to be merged.

3- Hold your detailed file...

4- Merge both files without the NOPRINT columns and apply your ACROSS. See my previous example.

Hi Daniel,

that loop to add values on subfoot using dm looks awesome but I couldn´t implement it on my code. Thanks anyway.

MartinY,

I followed your 4 steps then I am now able to have it working, but now I need to change few columns order.



Final code is:

TABLE FILE HDETAILS
SUM VENTAS_SUBSEG_FORD AS 'Vol'
PCT AS '%'
ACU NOPRINT
PCT_ACU NOPRINT
X NOPRINT
Z NOPRINT
BY SUBSEG AS ''
ACROSS SRSD83_PERIOD_R AS ''
COMPUTE A/D8=ACU; AS '&YEAR Acu'
COMPUTE B/D8.1 = PCT_ACU; AS '&YEAR %
COMPUTE C/D8 = X; AS '&LAST_YEAR Acu'
COMPUTE D/D8.1 = Z; AS '&LAST_YEAR %'
MORE
FILE HTOTALS
END
  

I need to have it like:

TABLE FILE HDETAILS
ON TABLE HOLD AS H023REPORT
SUM VENTAS_SUBSEG_FORD
PCT
ACU NOPRINT
PCT_ACU NOPRINT
X NOPRINT
Z NOPRINT
&BY 
BY SRSD83_PERIOD_R 
COMPUTE A/D8=ACU; 
COMPUTE B/D8.1 = PCT_ACU; 
COMPUTE C/D8 = X;
COMPUTE D/D8.1 = Z; 
MORE
FILE HTOTALS 
END


TABLE FILE H023REPORT

SUM VENTAS_SUBSEG_FORD AS 'Vol'
PCT AS '%'
ACU NOPRINT
PCT_ACU NOPRINT
X NOPRINT
Z NOPRINT
&BY AS ''
ACROSS SRSD83_PERIOD_R AS ''
COMPUTE A/D8=ACU; AS '&YEAR Acu'
COMPUTE B/D8.1 = PCT_ACU; AS '&YEAR %'
COMPUTE C/D8 = X; AS '&LAST_YEAR Acu'
COMPUTE D/D8.1 = Z; AS '&LAST_YEAR %'
HEADING CENTER
"&TITLE"
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=C, SEQUENCE=2,$
TYPE=REPORT, COLUMN=D, SEQUENCE=3,$
END
-EXIT

END
  

This is working but it places Vol and % at wrong places. Any suggestion?

This message has been edited. Last edited by: Ricardo Augusto,

Hi Ricardo,

Things that I don't understand is why you are holding in H023REPORT when what you are doing with that file can be done at the previous step ?

Also why would you like to have something different when your "Final code" does it the way you want ? Especially when your "I need to have it like" doesn't seems to do nothing more.

When a COMPUTE is done, except if the BY fields change between steps you don't need to do twice the exact same thing : BY becomes a ACROSS which is the "same" thing except for the display option. It's your only difference (except for AS that have been added also for display purposes).

Difficult to figure what is your need. It's not possible to reproduce with your files: try to reproduce with sample CAR file (or other IBI sample files) or post image of what you have already (Final code) and what you need (I need to have it).

quote:

ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=C, SEQUENCE=2,$
TYPE=REPORT, COLUMN=D, SEQUENCE=3,$

I edited the post.
I was not able to run this and MORE together, that´s why I created a HOLD.