Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Comparison Subtotal

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Comparison Subtotal
 Login/Join
 
Member
posted
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>,


WebFOCUS 8.1.3
Windows
 
Posts: 4 | Registered: November 17, 2015Report This Post
Platinum Member
posted Hide Post
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



JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 146 | Registered: November 09, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Comparison Subtotal

Copyright © 1996-2020 Information Builders