Hello,

I need some help to calculate a column-total using RECOMPUTE on a column populated from a DB_LOOKUP.

I reproduced it using CAR.
On my real report I need use RECOMPUTE on column-total, but without RECOMPUTE the db_lookup column is ok. Weird.

Any suggestions?

Thanks

-SET &ECHO=ALL;

-**********
-* SETs
-**********
SET CDN = ON
SET NODATA = 0
SET ASNAMES = ON
SET EMPTYCELLS = OFF
SET CENT-ZERO = ON

-*************************
-* VARIABLES MANIPULATIONS
-*************************




SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT PDF AS TEST_DB_LOOKUP
UNITS=PTS, $
SECTION=section1, LAYOUT=ON, METADATA='5^5^5^5^4', MERGE=OFF, ORIENTATION=LANDSCAPE, PAGESIZE=LETTER, SHOW_GLOBALFILTER=OFF, $


PAGELAYOUT=1, NAME='PAGE1', text='PAGE1', TOC-LEVEL=1, TOPMARGIN=1, ORIENTATION=LANDSCAPE, $
-*OBJECT=IMAGE, NAME='LOGO', IMAGE=ford_logo.jpg, ALT='', POSITION=(250 150), DIMENSION=(* *),  $
COMPONENT='HEADER', TEXT='HEADER', POSITION=(* *), DIMENSION=(* *),  $

COMPONENT='REPORT1', TEXT='REPORT1', POSITION=(+1 50), DIMENSION=(* *), RELATIVE-TO=HEADER, $


-RUN
-* END COMPOND REPORT


SET COMPONENT=HEADER
TABLE FILE CAR
HEADING
"TESTING DB_LOOKUP + RECOMPUTE COMPOUND PDF REPORT"
"TEST"
"CONFIDENTIAL"
BY COUNTRY NOPRINT
IF READLIMIT EQ 1
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, IMAGE=ford_logo.jpg, SIZE=(0.6 0.25), POSITION=(0.1 0.1),  $
TYPE=HEADING, GRID=OFF, FONT="FORD CE BLACK",  $
TYPE=HEADING, LINE=1, SIZE=7, JUSTIFY=CENTER, $
TYPE=HEADING, LINE=2, SIZE=5, JUSTIFY=CENTER, $
TYPE=HEADING, LINE=3, SIZE=4, JUSTIFY=RIGHT, STYLE='BOLD+UNDERLINE', $

TYPE=HEADING, LINE=4, object=box, name='line1', position = (1.0 0.4), dimension = (9.0 0.002), backcolor=BLACK, border-color=BLACK,$

END


TABLE FILE CAR
SUM SALES AS 'TOTAL_SALES'
BY COUNTRY
ON TABLE HOLD AS HTOTALS
END


SET COMPONENT=REPORT1


TABLE FILE CAR
SUM SALES NOPRINT
SUM
SALES NOPRINT
COMPUTE TST/I9C=DB_LOOKUP(HTOTALS, COUNTRY, COUNTRY, TOTAL_SALES);
BY COUNTRY
SUM SALES/I9C
BY COUNTRY
ACROSS MODEL
COMPUTE TOTALX/I9C=C2; AS 'TOTAL'
COMPUTE PCT/D12.1 = (TOTALX/C1)*100;
ON TABLE COLUMN-TOTAL RECOMPUTE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=3,
	 WRAP=0.2,
     COLOR='BLACK',
     STYLE=NORMAL,
	  LEFTMARGIN=0.00,
	 ORIENTATION=LANDSCAPE,
	 BORDER-BOTTOM=LIGHT,
	 BORDER-LEFT=LIGHT,
	 BORDER-RIGHT=LIGHT,
	 BORDER-TOP=LIGHT,
$

TYPE=TITLE,
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=COUNTRY,
WRAP=0.9,
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
BACKCOLOR='LIGHT GREY',
$

END

  

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

I suggest creating a HOLD file, then use MIN.TST:

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
-RUN

TABLE FILE CAR
SUM SALES AS 'TOTAL_SALES'
BY COUNTRY
ON TABLE HOLD AS HTOTALS
END
-RUN

TABLE FILE CAR
SUM
SALES
COMPUTE TST/I9C=DB_LOOKUP(HTOTALS, COUNTRY, COUNTRY, TOTAL_SALES);
BY COUNTRY
BY MODEL
ON TABLE HOLD AS HCAR1
END
-RUN

TABLE FILE HCAR1
SUM SALES NOPRINT
SUM
SALES NOPRINT
MIN.TST
BY COUNTRY
SUM SALES/I9C
BY COUNTRY
ACROSS MODEL
COMPUTE TOTALX/I9C=C2; AS 'TOTAL'
COMPUTE PCT/D12.1 = (TOTALX/C1)*100;
ON TABLE COLUMN-TOTAL RECOMPUTE
END

If you just need a column total, can you use a define?

SET ASNAMES = ON

TABLE FILE CAR
SUM SALES AS 'TOTAL_SALES'
BY COUNTRY
ON TABLE HOLD AS HTOTALS
END

DEFINE FILE CAR
TST/I9C = DB_LOOKUP(HTOTALS, COUNTRY, COUNTRY, TOTAL_SALES);
END

TABLE FILE CAR
SUM SALES NOPRINT
SUM
SALES NOPRINT
TST
BY COUNTRY
SUM SALES/I9C
BY COUNTRY
ACROSS MODEL
COMPUTE TOTALX/I9C=C2; AS 'TOTAL'
COMPUTE PCT/D12.1 = (TOTALX/C1)*100;
ON TABLE COLUMN-TOTAL RECOMPUTE
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=3,
	 WRAP=0.2,
     COLOR='BLACK',
     STYLE=NORMAL,
	  LEFTMARGIN=0.00,
	 ORIENTATION=LANDSCAPE,
	 BORDER-BOTTOM=LIGHT,
	 BORDER-LEFT=LIGHT,
	 BORDER-RIGHT=LIGHT,
	 BORDER-TOP=LIGHT,
$

TYPE=TITLE,
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=COUNTRY,
WRAP=0.9,
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
BACKCOLOR='LIGHT GREY',
$

END

  

Thanks Both but the MIN operator is `forcing` a wrong calculations due to my data granularity.

I will try to reproduce my actual scenario.

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