Requirement I have is to show variance percentages after counts. Not side by side. See code below and image. I am trying to achieve this with a basic table request. I tried to do a multi verb across but its not working. Maybe we can't do a multi-verb across? Looks like I am going to have to pull variance columns seperately hold them and then MORE the data using dummy regions to achieve this. Another issue is that the regions are coming from user input. They can pick from 1 to 5 regions. So the report output is dependent on what the user selects. This report is being developed in 7.7.03 and HTML,PDF,EXL2K output. Any thoughts?

Report output

  
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
SET CENT-ZERO = ON
SET ACROSSLINE = OFF

DEFINE FILE GGSALES
REGION_ORDER/I11 = DECODE REGION('Midwest' 1 'Northeast' 2 'Southeast' 3 'West' 4 ELSE 99);
END

TABLE FILE GGSALES
SUM
    UNITS

BY PRODUCT
BY REGION_ORDER
BY REGION
ON TABLE HOLD AS HDATA
END
-RUN

TABLE FILE HDATA
SUM
   UNITS AS 'PRIMARY'

BY PRODUCT
WHERE REGION EQ 'Midwest'
ON TABLE HOLD AS HPRIMARY
END
-RUN

JOIN PRODUCT IN HDATA TO
     PRODUCT IN HPRIMARY AS J0
END
-RUN

TABLE FILE HDATA
SUM
    UNITS
	COMPUTE VARIANCE/D12.2 = IF UNITS EQ 0 THEN 0 ELSE ((PRIMARY - UNITS)/UNITS) * 100;

BY REGION_ORDER
BY REGION
BY PRODUCT
ON TABLE HOLD AS HRPTDATA
END
-RUN

TABLE FILE HRPTDATA
SUM
    UNITS AS 'Units'
-*ACROSS REGION_ORDER NOPRINT
-*ACROSS REGION
-*BY PRODUCT
-*SUM
    VARIANCE AS '% Variance vs Primary'
ACROSS REGION_ORDER NOPRINT
ACROSS REGION AS ''
BY PRODUCT AS 'Product'
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
-RUN

This message has been edited. Last edited by: Mighty Max,

Its not a single table request, but Macgyver may be the way to go.

Waz is right. This does not seem feasible with a single table request but playing around with HOLD files and a bit of dynamic formatting this may work:

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY
SET CENT-ZERO = ON
SET ACROSSLINE = OFF
SET LINES = 999999

DEFINE FILE GGSALES
REGION_ORDER/I11 = DECODE REGION('Midwest' 1 'Northeast' 2 'Southeast' 3 'West' 4 ELSE 99);
END

TABLE FILE GGSALES
SUM
    UNITS
BY PRODUCT
BY REGION_ORDER
BY REGION
ON TABLE HOLD AS HDATA
END
-RUN

TABLE FILE HDATA
SUM
   UNITS AS 'PRIMARY'

BY PRODUCT
WHERE REGION EQ 'Midwest'
ON TABLE HOLD AS HPRIMARY
END
-RUN

FILEDEF HRESULTS DISK hresults.ftm (APPEND
-RUN

-*** Capture UNITS ***
DEFINE FILE HDATA
DATA_GRP/A10 = 'UNITS';
DATA_LBL/A30 = 'Units';
END

TABLE FILE HDATA
PRINT
    UNITS/D12.2 AS 'RESULT'
BY DATA_GRP
BY DATA_LBL
BY PRODUCT
BY REGION_ORDER
BY REGION
ON TABLE HOLD AS HRESULTS
END
-RUN

-*** Capture VARIANCE ***
JOIN PRODUCT IN HDATA TO
     PRODUCT IN HPRIMARY AS J0
END
-RUN

DEFINE FILE HDATA
DATA_GRP/A10 = 'VARIANCE';
DATA_LBL/A30 = '% Variance vs Primary';
END

TABLE FILE HDATA
SUM
	COMPUTE VARIANCE/D12.2 = IF UNITS EQ 0 THEN 0 ELSE ((PRIMARY - UNITS)/UNITS) * 100; AS 'RESULT'

BY DATA_GRP
BY DATA_LBL
BY PRODUCT
BY REGION_ORDER
BY REGION
ON TABLE HOLD AS HRESULTS
END
-RUN

-*** Produce report ***
DEFINE FILE HRESULTS
RSLT_FMT/A8 = IF DATA_GRP EQ 'UNITS' THEN 'D12' ELSE 'D12.2';
END

TABLE FILE HRESULTS
SUM
    RESULT/RSLT_FMT AS ''
ACROSS DATA_GRP NOPRINT
ACROSS REGION_ORDER NOPRINT
ACROSS REGION AS ''
ACROSS DATA_LBL AS ''
BY PRODUCT AS 'Product'
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
-RUN

Titles don't show exactly as your sample output but hopefully they are close enough. The ACROSS statements could be changed to something like the following for a slightly different effect:

ACROSS DATA_GRP NOPRINT
ACROSS DATA_LBL AS ''
ACROSS REGION_ORDER NOPRINT
ACROSS REGION AS ''

Thanks! I believe the issue is resolved.