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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Q: Coding a Report
 Login/Join
 
Tomsweb
Master
posted
Some days you just have a mental block and cannot 'Past the wall'... I have having a difficult
time coding a table request and I wonder if anyone has a suggestion on how to solve this problem.

TABLE FILE FEES1
HEADING CENTER
"SANTA CLARITA MORENO BUSINESS"
"PCO " "
SUM YTD/C
BY BPCO NOPRINT PAGE-BREAK
BY LOB NOPRINT
BY YEAR NOPRINT
BY DESC_SORT NOPRINT
BY DESCRIPTION AS 'FEE'
BY BLANK AS ' '
ON BPCO SUMMARIZE
FOOTING BOTTOM
"MIS REPORT: &FOCFOCEXEC <35 REPORT RUN DATE: &DATEMDYY"
"DISTRIBUTION: T FITZSIMMONS"
END
-RUN

This gives me a report like:

PCO 63 Breakdown for 2004 Page 1

FEE YTD
Installments 140,265.00
Policy Fee .00
Reinstatment .00
Filings .00
Endorsements .00
Certificates .00
Late .00
NSF .00
Rewrites .00
State Fraud .00

*TOTAL BPCO 63 140,265.00

PCO 68 Breakdown for 2004 Page 2

FEE YTD
Installments 597,665.00
Policy Fee 482,594.80
Reinstatment 34,510.00
Cancellation .00
Filings 48,435.00
Endorsements 55,225.00
Certificates .00
Late 194,039.00
NSF 1,223.00
Rewrites .00
State Fraud 24,326.00

*TOTAL BPCO 68 1,438.020.00


In the second report, I want a summation of all PCOs in that year so, I coded:

TABLE FILE FEES1
HEADING CENTER

"ALL PCO(S) FOR CALENDAR YEAR: &YR"
" "
SUM YTD/C COLUMN-TOTAL
ACROSS BPCO AS ''
BY BPCO AS 'PCO'
END
-RUN

This gives me a report like:

All PCO(s) for 2004

PCO YTD
63 140,265.00
67 2,100.00
68 1,438,020.40
69 133,083.80

TOTAL 1,713,469.20

Unfortunately, I am trying to get beyond this point with a report to list sum of all installments, policy fees, etc.
for the given PCO in a calendar (ie. 2004).

I am trying to get a report like this:

All PCO(s) for 2004

PCO YTD
Installments 738,070.00 (sum of pco 63+67+68+69)
67 ... ""
68 ... ""
69 ... ""

TOTAL Installments... ""

Does anyone have any suggestions?

Thanks very much!

Idea


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
GCohen
Platinum Member
posted Hide Post
Generally when you want both a summation and a detail listing on one report it is easier to use mulitple sentences... eg.
Sum AMOUNT
by Year
SUM AMOUNT
by year by detail


Release 7.6.9
Windows
HTML
 
Posts: 226 | Registered: June 08, 2003Report This Post
Tony A
Expert
posted Hide Post
.... although I don't think that will quite give you what you want in this instance, as it appears that you want a subtotal as the first row of data and not after each break on which the subtotal is performed?

You could use FML but you would have to make it flexible by saving out the sort fields and then using them in the FOR statement. This could get a bit messy.

Another method is to double pass the data and append into a temporary file -
FILEDEF TEMPFILE DISK TEMPFILE.FTM (APPEND
-RUN

TABLE FILE GGSALES
SUM DOLLARS
    COMPUTE STATE/A60 = 'Subtotal of Dollars for '|REGION|' Region';
    COMPUTE SOURCE/A1 = '1';
 BY REGION
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS TEMPFILE FORMAT ALPHA
END
TABLE FILE GGSALES
SUM DOLLARS
    COMPUTE STATE/A60 = ST;
    COMPUTE SOURCE/A1 = '2';
 BY REGION
 BY ST NOPRINT
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS TEMPFILE FORMAT ALPHA
END
-RUN

TABLE FILE TEMPFILE
SUM DOLLARS
 BY REGION NOPRINT
 BY SOURCE NOPRINT
 BY STATE
END

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
susannah
Expert
posted Hide Post
then there's always the simple approach
TABLE FILE CAR
HEADING
" WHOLE SHEBANG SUM SALES
BY COUNTRY
ON TABLE SUMMARIZE
END



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
jgelona
Master
posted Hide Post
"as it appears that you want a subtotal as the first row of data and not after each break on which the subtotal is performed?"

I have done this a lot in the past and I find that using MCGYVER works rather well.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders