I have a report where the subtotal needs to be a % change year over year. Below is a mock-up of what is being requested
2016081 2016082
LEADS CREATED 2015081 2015082
School ID P8W1 P8W2
30 CY 3 0
PY 5 1
∆% -40% -100%
CY=2016
PY=2015

I am using a SQL Query to pull the data. I can't see an easy way to do this using subtotals. I am wondering if the recap function would work.

Here is a sample of the data source
FYPW FY PW SchoolID SchoolName DistrictID DistrictName Channel Sub_Channel LeadCreated
2016084 2016 084 30 Colonie 8814 Albany CT School Entered Unknown 2
2015073 2015 073 30 Colonie 8814 Albany CT School Entered Unknown 2
2015074 2015 074 30 Colonie 8814 Albany CT School Entered Unknown 2
2015082 2015 082 30 Colonie 8814 Albany CT School Entered Unknown 1
2015072 2015 072 30 Colonie 8814 Albany CT School Entered Unknown 2


This is the result I am getting with my report created using App Studio 8.1.04.

081 082
SchoolID SchoolName FY
0030 Colonie 2016 3 .
2015 5 1
*TOTAL 0030 8 1
TOTAL 8 1

This message has been edited. Last edited by: <Emily McAllister>,

Hello,

Your requirements were a bit confusing and I'm not sure I have the gist, but what you could try is to leverage hold files getting your SUMS and then joining those sums into your final report doing your percentage COMPUTE. Create then a sub-foot and place that computed value there. I took your data example and imported into a temp table and added some data here to try and produce the output. I'm sure more experienced people on this forum may be able to come up with an easier/more elegant option, but this is what I come up with. Perhaps it will generate an idea for you or point you in the right direction.

Output looks like:


CODE:

-*Grab the newest year
TABLE FILE RPT_JCTEST
BY HIGHEST 1 RPT_JCTEST.RPT_JCTEST.FY AS 'MAX_YEAR'
ON TABLE HOLD AS MAXYEAR FORMAT FOCUS
END

-*Set the amper variables for yearly calculations down the road
-DEFAULTH &MAX_YEAR = 0
-RUN
-READFILE MAXYEAR
-TYPE MAX_YEAR IS &MAX_YEAR

-SET &PREV_YEAR = &MAX_YEAR - 1;

-*Sum the Leads per school for the max year
TABLE FILE RPT_JCTEST
SUM
     RPT_JCTEST.RPT_JCTEST.LEADCREATED AS 'Leads_Current_Year'
BY  LOWEST RPT_JCTEST.RPT_JCTEST.SCHOOLID
WHERE RPT_JCTEST.RPT_JCTEST.FY EQ &MAX_YEAR;
ON TABLE HOLD AS HOLDCURYEARSUM FORMAT FOCUS
END

-*Sum the Leads per school for the previous year
TABLE FILE RPT_JCTEST
SUM
     RPT_JCTEST.RPT_JCTEST.LEADCREATED AS 'Leads_Prev_Year'
BY  LOWEST RPT_JCTEST.RPT_JCTEST.SCHOOLID
WHERE RPT_JCTEST.RPT_JCTEST.FY EQ &PREV_YEAR;
ON TABLE HOLD AS HOLDPREVYEARSUM FORMAT FOCUS
END

-*Join the files together
JOIN
 FILE RPT_JCTEST AT RPT_JCTEST.RPT_JCTEST.SCHOOLID TO MULTIPLE
 FILE HOLDCURYEARSUM AT HOLDCURYEARSUM.SEG01.SCHOOLID TAG J0 AS J0
 WHERE RPT_JCTEST.RPT_JCTEST.SCHOOLID EQ J0.SEG01.SCHOOLID;
 END
JOIN
 FILE RPT_JCTEST AT RPT_JCTEST.RPT_JCTEST.SCHOOLID TO MULTIPLE
 FILE HOLDPREVYEARSUM AT HOLDPREVYEARSUM.SEG01.SCHOOLID TAG J1 AS J1
 WHERE RPT_JCTEST.RPT_JCTEST.SCHOOLID EQ J1.SEG01.SCHOOLID;
 END

-*Spit out the report with computed field calculating off hold file values
-*Computed field hidden from report and displayed instead on subfoot per school
TABLE FILE RPT_JCTEST
PRINT
     RPT_JCTEST.RPT_JCTEST.SCHOOLNAME
     RPT_JCTEST.RPT_JCTEST.FY
     RPT_JCTEST.RPT_JCTEST.LEADCREATED
	 COMPUTE computed_percent/D12.2 = J0.SEG01.LEADS_CURRENT_YEAR / J1.SEG01.LEADS_PREV_YEAR; NOPRINT
BY  LOWEST RPT_JCTEST.RPT_JCTEST.SCHOOLID

ON RPT_JCTEST.RPT_JCTEST.SCHOOLID SUBFOOT
" <+0> <+0>% of Year Change:<computed_percent"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
TYPE=SUBFOOT,
     BY=1,
     HEADALIGN=BODY,
$
ENDSTYLE
END