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     [SOLVED] Assistance needed with subtotals and grandtotals

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Assistance needed with subtotals and grandtotals
 Login/Join
 
Platinum Member
posted
Hello all I have a report that should look as follows:
(dash's are just spaces they are not to appear in the rpt)n
bank---merchant----gross sales---ach net amount
1111---12345678-----1234567.98---987653.98
1111---12345678-----9877634.98---767273.00
subtotal:-----------11112202.96---1754926.98
1111---12345677-----11111111.98---1111111.98
1111---12345677-----11111111.00---1111111.00
subtotal:-----------22222222.98---2222222.98

Total:-------------33334425.94----3977149.98

In my code (which is included) those totals are to only appear when the user has selected from the launsh page...that thoese fields be displayed.

all the fields in the -set commands are fields that the user chooses to appear on the report. I have looked at the advanced help and can not find anything simular to what I'm doing.
Could someone offer assistance please:
 
-* File rf_daily_ach_activity_rpt.fex
-*******************************************************************************
-* Date: 01/29/2009
-* Program:  rf_daily_ach_activity_rpt
-* Purpose:  Creates the RF Daily ACH Activity Report
-* Other  Pgm :
-*
-*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-* Change/Update/Add
-* Date        By                   Description
-* ----------  -------------------  -------------------------------------------
-* 01/29/2009  Timothy Brydie       Initial Version
-******************************************************************************
-*-DEFAULT &RECS = ' '
-*  echo debugging
-INCLUDE STDECHO
-SET &ECHO='ALL';
-*  set DM variables
-INCLUDE rf_parms&NTID.EVAL
-*  set eCon client CSS
SET CSSURL = &CLIENTSTYLE.EVAL
-*  set work dir
APP HOLD emr_work
-*  HOLD File from the SQL Pull
FILEDEF ACHRFD001 DISK emr_work/achrfd001&NTID...ftm

-*  HTM Filedefs for file created in this program
FILEDEF RFDAILY  DISK emr_work/RFDAILY&NTID...HTM
-RUN
-*
-INCLUDE rf_daily_ach_sql
-SET &RECS = IF &REX GE 1000 THEN 1000 ELSE &RECS;
SET PAGE  = NOLEAD
-*SET LINES = 995
-RUN
-*  Strip out whatever characters from date and replace with slashes... just in case!
-*
-*-SET &REPDATE = EDIT(&RPTDATE,'99$/99$/9999');
-* This statement will determine which section of the program gets excuted
-IF &OUTF EQ ' ' GOTO ISHTML;
-*
-INCLUDE rf_daily_ach_activity_exlpdf.fex
-*
-ISHTML
-*

-*---------------------------
-* HTML Output from Extract
-*---------------------------
-* Default Counter - This counter will determine the value for "ColumnSpan"
-SET &CC = 0;
-SET &CRESRELAMT = 'ach_net_deposit_amt - ach_final_net_deposit_amt';
-SET &CALCAMTB   = 'ach_net_deposit_amt - ach_final_net_deposit_amt + carryover_amt';
-*
-*
-* ********************************************************************************
-* This section will Dynamically create the data for Columns that are selected to *
-* appear on the report                                                           *
-* ********************************************************************************
-*
-SET &PACTTYPE     = IF &ACTTYPEFL    EQ 'Y' THEN 'COMPUTE PACTTYPE/A100      = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:50px">'' |action_type| ''</SPAN>'';' ELSE ' ';
-SET &PACHRUNDTM   = IF &ACHRUNDTMFL  EQ 'Y' THEN 'COMPUTE PACHRUNDTM/A100    = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:115px">'' |SOURCEDTM| ''</SPAN>'';' ELSE ' ';
-SET &PRESDDA_NUM  = IF &DDACCTFL     EQ 'Y' THEN 'COMPUTE PRESDDA_NUM/A100   = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:90px">'' |reserve_dda_num| ''</SPAN>'';' ELSE ' ';
-SET &PRESTROUT_NUM= IF &TRANSRTINGFL EQ 'Y' THEN 'COMPUTE PRESTROUT_NUM/A100 = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:80px">'' |reserve_tr_num| ''</SPAN>'';' ELSE ' ';
-SET &PRESRELID_NUM= IF &RESRELIDFL   EQ 'Y' THEN 'COMPUTE PRESRELID_NUM/A100 = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:80px">'' |A_RELRES_ID| ''</SPAN>'';' ELSE ' ';
-SET &PRFPERCENT   = IF &RFPERCENTFL  EQ 'Y' THEN 'COMPUTE PRFPERCENT/A100    = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:80px">'' |RRDPCT| ''</SPAN>'';'  ELSE ' ';
-SET &PRFAMT       = IF &RFAMTFL      EQ 'Y' THEN 'COMPUTE PPRFAMT/A100       = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RRDAMT| ''</SPAN>'';' ELSE ' ';
-SET &PCEILAMT     = IF &RESCEILAMTFL EQ 'Y' THEN 'COMPUTE PCEILAMT/A100      = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RMAXDAMT| ''</SPAN>'';' ELSE ' ';
-SET &PFLORAMT     = IF &RESFLORAMTFL EQ 'Y' THEN 'COMPUTE PFLORAMT/A100      = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RMINDAMT| ''</SPAN>'';' ELSE ' ';
-SET &PACHIND      = IF &ACHENTINDFL  EQ 'Y' THEN 'COMPUTE PACHIND/A100       = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:60px">'' |ach_ind| ''</SPAN>'';' ELSE ' ';
-SET &PCARYOVIND   = IF &CARYOVRINDFL EQ 'Y' THEN 'COMPUTE PCARYOVIND/A100    = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:70px">'' |carry_over_ind| ''</SPAN>'';' ELSE ' ';
-SET &PGROSALEAMT  = IF &GROSSALESFL  EQ 'Y' THEN 'COMPUTE PGROSALEAMT/A100   = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RGROSALEAMT| ''</SPAN>'';' ELSE ' ';
-SET &PCALCAMT     = IF &CALCAMTFL    EQ 'Y' THEN 'COMPUTE PCALCAMT/A100      = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RDCALCAMT| ''</SPAN>'';' ELSE ' ';
-SET &PACHNETAMT   = IF &ACHNETDPITFL EQ 'Y' THEN 'COMPUTE PACHNETAMT/A100    = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RDACHDEPAMT| ''</SPAN>'';' ELSE ' ';
-SET &PACHFINAMT   = IF &ACHFINETAMTFL EQ 'Y' THEN 'COMPUTE PACHFINAMT/A100   = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RDACHFINAMT| ''</SPAN>'';' ELSE ' ';
-SET &PRESRELAMT   = IF &RRAMTWHLDFL  EQ 'Y' THEN 'COMPUTE PRESRELAMT/A100    = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RDCALCAMT| ''</SPAN>'';' ELSE ' ';
-SET &PCOAMT       = IF &CARYOVRAMTFL EQ 'Y' THEN 'COMPUTE PCOAMT/A100        = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RDCOAMT| ''</SPAN>'';' ELSE ' ';
-SET &PCOBAL       = IF &CARYOVRBALFL EQ 'Y' THEN 'COMPUTE PCOBAL/A100        = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RDCOBAL| ''</SPAN>'';' ELSE ' ';
-SET &PRESBAL      = IF &RESERVEBALFL EQ 'Y' THEN 'COMPUTE PRESBAL/A100       = ''<SPAN STYLE="text-ALIGN:CENTER;WIDTH:100px">'' |RDRESBAL| ''</SPAN>'';' ELSE ' ';
-*
-* ************************************************************************************
-* This section will Dynamically create Subtotals for the appropriate columns selected*
-* ************************************************************************************
-*
-SET &STGROSALEAMT  = IF &GROSSALESFL   EQ 'Y' THEN '<ST.RGROSALEAMT' ELSE ' ';
-SET &STCALCAMT     = IF &CALCAMTFL     EQ 'Y' THEN '<ST.RDCALCAMT'   ELSE ' ';
-SET &STACHNETAMT   = IF &ACHNETDPITFL  EQ 'Y' THEN '<ST.RDACHDEPAMT' ELSE ' ';
-SET &STACHFINAMT   = IF &ACHFINETAMTFL EQ 'Y' THEN '<ST.RDACHFINAMT' ELSE ' ';
-SET &STRESRELAMT   = IF &RRAMTWHLDFL   EQ 'Y' THEN '<ST.RDCALCAMT'   ELSE ' ';
-SET &STCOAMT       = IF &CARYOVRAMTFL  EQ 'Y' THEN '<ST.RDCOAMT'     ELSE ' ';
-SET &STCOBAL       = IF &CARYOVRBALFL  EQ 'Y' THEN '<ST.RDCOBAL'     ELSE ' ';
-SET &STRESBAL      = IF &RESERVEBALFL  EQ 'Y' THEN '<ST.RDRESBAL'    ELSE ' ';
-*
-* *********************************************************************************
-* This section will Dynamically create Totals for the appropriate columns selected*
-* *********************************************************************************
-*
-SET &TOTGROSALEAMT  = IF &GROSSALESFL   EQ 'Y' THEN '<TOT.RGROSALEAMT'  ELSE ' ';
-SET &TOTCALCAMT     = IF &CALCAMTFL     EQ 'Y' THEN '<TOT.RDCALCAMT'    ELSE ' ';
-SET &TOTACHNETAMT   = IF &ACHNETDPITFL  EQ 'Y' THEN '<TOT.RDACHDEPAMT'  ELSE ' ';
-SET &TOTACHFINAMT   = IF &ACHFINETAMTFL EQ 'Y' THEN '<TOT.RDACHFINAMT'  ELSE ' ';
-SET &TOTRESRELAMT   = IF &RRAMTWHLDFL   EQ 'Y' THEN '<TOT.RDCALCAMT'    ELSE ' ';
-SET &TOTCOAMT       = IF &CARYOVRAMTFL  EQ 'Y' THEN '<TOT.RDCOAMT'      ELSE ' ';
-SET &TOTCOBAL       = IF &CARYOVRBALFL  EQ 'Y' THEN '<TOT.RDCOBAL'      ELSE ' ';
-SET &TOTRESBAL      = IF &RESERVEBALFL  EQ 'Y' THEN '<TOT.RDRESBAL'     ELSE ' ';
-*
DEFINE FILE ACHRFD001
-* bank
PBANK_NUM     /A100   = '<SPAN STYLE="text-ALIGN:CENTER;WIDTH:50px">' |bank_num|| '</SPAN>';
-* Merchant Num
PMERCHANT_NUM /A100   = '<SPAN STYLE="text-ALIGN:CENTER;WIDTH:120px">' |be_merch_num|'</SPAN>';
-* DBA Name
PDBANAME      /A100   = '<SPAN STYLE="text-ALIGN:CENTER;WIDTH:126px">'|dba_nam| '</SPAN>';
-* Association ID
PASSOCID      /A100   = '<SPAN STYLE="text-ALIGN:CENTER;WIDTH:90px">' |assoc_num|| '</SPAN>';
-*
-* RF Percent
RRPCT        /D10.2  =  reserve_release_pct;
RRDPCT       /A30    =  FTOA(RRPCT,'(D10.2)',RRDPCT);
-*
-* RF AMOUNT
RRAMT           /D20.2  =  reserve_release_amt;
RRDAMT          /A30    =  FTOA(RRAMT,'(D20.2)',RRDAMT);
-* Reserve Ceiling Amount
RMAXAMT         /D20.2  =  reserve_max_amt;
RMAXDAMT        /A30    =  FTOA(RMAXAMT,'(D20.2)',RMAXDAMT);
-* Reserve Floor Amount
RMINAMT         /D20.2  =  reserve_min_amt;
RMINDAMT        /A30    =  FTOA(RMINAMT,'(D20.2)',RMINDAMT);
-* Gross Sales
GROSALEAMT      /D20.2  =  gross_sales_amt;
RGROSALEAMT     /A30    =  FTOA(GROSALEAMT,'(D20.2)',RGROSALEAMT);
-* Calculated Amt
CALCAMT         /D20.2 = IF (action_type) EQ '3' THEN 0.00
             ELSE IF (action_type) EQ '4' THEN 0.00
             ELSE IF (action_type) EQ '7' THEN 0.00
             ELSE IF (action_type) EQ 'C' THEN 0.00
             ELSE IF (action_type) EQ 'B' THEN (&CALCAMTB);
-*RCALCAMT       /D20.2  =  &CALCAMTB;
RCALCAMT       /D20.2  =  CALCAMT;
RDCALCAMT      /A30    =  FTOA(RCALCAMT,'(D20.2)',RDCALCAMT);
-* ACH NET DEPOSIT AMOUNT AMT
RACHDEPAMT     /D20.2  =  ach_net_deposit_amt;
RDACHDEPAMT    /A30    =  FTOA(RACHDEPAMT,'(D20.2)',RDACHDEPAMT);
-* ACH Final NET DEPOSIT AMOUNT AMT
RACHFINAMT     /D20.2  =  ach_final_net_deposit_amt;
RDACHFINAMT    /A30    =  FTOA(RACHFINAMT,'(D20.2)',RDACHFINAMT);
-* Reserve/Release Amt withheld

RESRELAMT       /D20.2 = IF (action_type) EQ '3' THEN ach_final_net_deposit_amt
                         ELSE IF (action_type) EQ '4' THEN ach_final_net_deposit_amt
             ELSE IF (action_type) EQ '7' THEN ach_final_net_deposit_amt
             ELSE IF (action_type) EQ 'C' THEN ach_final_net_deposit_amt
             ELSE IF (action_type) EQ 'B' THEN (&CRESRELAMT);
RDCALCAMT      /A30    =  FTOA(RESRELAMT,'(D20.2)',RDCALCAMT);
-*Carry Over Amt
RCOAMT       /D20.2  =  carryover_amt;
RDCOAMT      /A30    =  FTOA(RCOAMT,'(D20.2)',RDCOAMT);
-*Carry Over Balance
RCOBAL       /D20.2  =  carryover_balance_amt;
RDCOBAL      /A30    =  FTOA(RCOBAL,'(D20.2)',RDCOBAL);
-*Reserve Balance
RESBAL       /D20.2  =  reserve_balance_amt;
RDRESBAL      /A30    =  FTOA(RESBAL,'(D20.2)',RDRESBAL);

END
-*
TABLE FILE ACHRFD001
PRINT
activity_dtm NOPRINT
COMPUTE SOURCEDTM/A20 = HCNVRT(activity_dtm,'(HMDYYS)',20,'A20'); NOPRINT
PBANK_NUM      AS ' '
PMERCHANT_NUM  AS ' '
PDBANAME       AS ' '
PASSOCID       AS ' '
&PACTTYPE      AS ' '
&PACHRUNDTM    AS ' '
&PRESDDA_NUM   AS ' '
&PRESTROUT_NUM AS ' '
&PRESRELID_NUM AS ' '
&PRFPERCENT    AS ' '
&PRFAMT        AS ' '
&PCEILAMT      AS ' '
&PFLORAMT      AS ' '
&PACHIND       AS ' '
&PCARYOVIND    AS ' '
&PGROSALEAMT   AS ' '
&PCALCAMT      AS ' '
&PACHNETAMT    AS ' '
&PACHFINAMT    AS ' '
&PRESRELAMT    AS ' '
&PCOAMT        AS ' '
&PCOBAL        AS ' '
&PRESBAL       AS ' '
COMPUTE LN/I01= IF LN EQ 1 THEN 2 ELSE 1; NOPRINT
BY bank_num NOPRINT
BY be_merch_num  NOPRINT
BY activity_dtm  NOPRINT
-*---------------------
-*SUBTOTAL
-*---------------------
-*IF RECORDLIMIT IS 1000
-*ON CATEGORY SUBTOTAL AS '*TOTAL'
ON be_merch_num  SUBTOTAL 
'Subtotal &STGROSALEAMT&STCALCAMT&STACHNETAMT&STACHFINAMT&STRESRELAMT&STCOAMT&STCOBAL&STRESBAL'
-*ON be_merch_num  SUBTOTAL 
-*'Subtotal &STGROSALEAMT&STCALCAMT&STACHNETAMT&STACHFINAMT&STRESRELAMT&STCOAMT&STCOBAL&STRESBAL'
-*---------------------
-* SUBFOOT
-*---------------------
ON bank_num SUBHEAD
 "Total &TOTGROSALEAMT&TOTCALCAMT&TOTACHNETAMT&TOTACHFINAMT&TOTRESRELAMT&TOTCOAMT&TOTCOBAL&TOTRESBAL"
-*
  ON TABLE SET PAGE-NUM NOLEAD
  ON TABLE NOTOTAL
  ON TABLE SET HTMLCSS OFF
  ON TABLE HOLD AS RFDAILY FORMAT HTML
-*
ON TABLE SET STYLE *
     UNITS=IN,
$
TYPE=REPORT,GRID=OFF,$
TYPE=DATA,WHEN=LN EQ 1,CLASS=whiterow,$
TYPE=DATA,WHEN=LN EQ 2,CLASS=greyrow,$
-*TYPE=SUBHEAD,CLASS=GREYROWBOLD,JUSTIFY=LEFT,$
-*TYPE=SUBFOOT,HEADALIGN=BODY,$
-*TYPE=SUBFOOT,BY=DUM,COLSPAN=&CC.EVAL,CLASS=GREYROWBOLD,$
-*TYPE = SUBFOOT,BY=bank_num ,JUSTIFY=LEFT,CLASS=whiterowbold,$
TYPE = SUBFOOT,JUSTIFY=LEFT,CLASS=whiterowbold,$
ENDSTYLE
END
-RUN 

This message has been edited. Last edited by: Kerry,




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Report This Post
Platinum Member
posted Hide Post
This has been solved- My solution to this is the following:

ON bank_num SUBFOOT
"Total &TOTGROSALEAMT&TOTCALCAMT&TOTACHNETAMT&TOTACHFINAMT&TOTRESRELAMT&TOTCOAMT&TOTCOBAL&TOTRESBAL"
ON be_merch_num SUBFOOT
"Subtotal &STGROSALEAMT&STCALCAMT&STACHNETAMT&STACHFINAMT&STRESRELAMT&STCOAMT&STCOBAL&STRESBAL"


This method was needed due to the fact that I obtained my totals/subtotals by computing them. For that reason I could not use the "on field SUBTOTAL" command


Thanks




Prod: WebFOCUS 7.6.10 MRE
Oracle/Sybase
Test: DevStudio 7.6.6
WF Server 7.6.6
Report Caster 7.6.6
Web Server - Tomcat
MS Windows XP SP2
Output: HTML, Excel 2000 , PDF, CSV, DOC

 
Posts: 133 | Registered: December 29, 2006Report 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     [SOLVED] Assistance needed with subtotals and grandtotals

Copyright © 1996-2020 Information Builders