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] COLUMN_TOTALS above the column instead of below

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] COLUMN_TOTALS above the column instead of below
 Login/Join
 
BGronli
Silver Member
posted
I have a requirement to produce a report in EXL07 which has the column totals above the column instead of below. Where I am having a problem is getting this column total to have text color red when the value is less than zero. In this code, everything works, the totals are placed above the appropriate column, I just haven't figured out how to get the text color when the value is less than zero. Thank you in advance for any and all assistance.
 
TABLE FILE BDGTOFCR           
PRINT APPR_GEN/P17.2BMC       
      APPR_ADDL/P17.2BMC      
      APPR_SUPP/P17.2BMC      
      APPR_TFR/P17.2BMC       
      AGCY_TFR/P17.2BMC       
      APPR_CNCL/P17.2BMC      
      APPR_TOTL/P17.2BMC      
      RSV/P17.2BMC            
      MND_RSV/P17.2BMC        
      CY_REL/P17.2BMC         
      REL/P17.2BMC            
      DISB_CY/P17.2BMC        
      UNEXP_REL_BAL/P17.2BMC  
      APPR_CF/P17.2BMC        
      REL_CF/P17.2BMC         
      DISB_CF/P17.2BMC        
      RVSN_OPR_FCO/P17.2BMC   
      RVSN_OPR_FCO/P17.2BMC                                       
      RVSN_CF/P17.2BMC                                            
  COMPUTE APPRGENTOTAL/D15.2BMC = TOT.APPR_GEN; NOPRINT           
  COMPUTE APPRADDLTOTAL/D15.2BMC = TOT.APPR_ADDL; NOPRINT         
  COMPUTE ADDRSUPPTOTAL/D15.2BMC = TOT.APPR_SUPP; NOPRINT         
  COMPUTE APPRTFRTOTAL/D15.2BMC = TOT.APPR_TFR; NOPRINT           
  COMPUTE AGCYTFRTOTAL/D15.2BMC = TOT.AGCY_TFR; NOPRINT           
  COMPUTE APPRCNCLTOTAL/D15.2BMC = TOT.APPR_CNCL; NOPRINT         
  COMPUTE APPRTOTAL/D15.2BMC = TOT.APPR_TOTL; NOPRINT             
  COMPUTE RSVTOTAL/D15.2BMC = TOT.RSV; NOPRINT                    
  COMPUTE MNDRSVTOTAL/D15.2BMC = TOT.MND_RSV; NOPRINT             
  COMPUTE CYRELTOTAL/D15.2MC = TOT.CY_REL; NOPRINT                
  COMPUTE RELTOTAL/D15.2BMC = TOT.REL; NOPRINT                    
  COMPUTE DISBCYTOTAL/D15.2BMC = TOT.DISB_CY; NOPRINT             
  COMPUTE UNEXPRELBALTOTAL/D15.2BMC = TOT.UNEXP_REL_BAL; NOPRINT  
  COMPUTE APPRCFTOTAL/D15.2BMC = TOT.APPR_CF; NOPRINT             
  COMPUTE RELCFTOTAL/D15.2BMC = TOT.REL_CF; NOPRINT               
  COMPUTE DISBCFTOTAL/D15.2BMC = TOT.DISB_CF; NOPRINT             
  COMPUTE RVSNOPRFCOTOTAL/D15.2BMC = TOT.RVSN_OPR_FCO; NOPRINT          
  COMPUTE RVSNCFTOTAL/D15.2BMC = TOT.RVSN_CF; NOPRINT                   
BY LOWEST FY_END                                                        
BY LOWEST L1_CD                                                         
BY LOWEST BE_CD                                                         
BY LOWEST BE_NM                                                         
BY LOWEST BE_SHRT_NM                                                    
BY LOWEST CAT                                                           
BY LOWEST CAT_NM                                                        
BY LOWEST CAT_SHRT_NM                                                   
BY LOWEST APPR_YR                                                       
BY LOWEST SF_FID3                                                       
BY LOWEST APPR_CAT_TYPE                                                 
BY LOWEST BDGT_LINE_NBR                                                 
HEADING                                                                 
" <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <+0> <0X            
 <APPRGENTOTAL<APPRADDLTOTAL<ADDRSUPPTOTAL<APPRTFRTOTAL<AGCYTFRTOTAL<APPRCNCLTOTAL<APPRTOTAL<RSVTOTAL<MNDRSVTOTAL<CYRELTOTAL<RELTOTAL<DISBCYTOTAL<UNEXPRELBALTOTAL<APPRCFTOTAL<RELCFTOTAL<DISBCFTOTAL<RVSNOPRFCOTOTAL<RVSNCFTOTAL"
"</1"                                                                 
ON TABLE PCHOLD FORMAT EXL07                                          
ON TABLE SET LINES 999999                                             
ON TABLE SET PAPER 999999                                             
ON TABLE SET PAGE NOPAGE                                              
ON TABLE SET BYDISPLAY ON                                             
ON TABLE SET STYLE *                                                  
-INCLUDE stdexcel.sty                                                 
TYPE=HEADING, HEADALIGN=BODY, $                                       
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=1, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=2, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=3, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=4, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=5, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=6, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=7, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=8, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=9, COLSPAN=1, JUSTIFY=RIGHT, $ 
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=10, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=11, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=12, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=13, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=14, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=15, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=16, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=17, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, OBJECT=TEXT, ITEM=18, COLSPAN=1, JUSTIFY=RIGHT, $
TYPE=DATA, COLOR=RED, COLUMN=APPR_GEN, WHEN=APPR_GEN LT 0, $           
TYPE=DATA, COLOR=RED, COLUMN=APPR_ADDL, WHEN=APPR_ADDL LT 0, $         
TYPE=DATA, COLOR=RED, COLUMN=APPR_SUPP, WHEN=APPR_SUPP LT 0, $         
TYPE=DATA, COLOR=RED, COLUMN=APPR_TFR, WHEN=APPR_TFR LT 0, $           
TYPE=DATA, COLOR=RED, COLUMN=AGCY_TFR, WHEN=AGCY_TFR LT 0, $           
TYPE=DATA, COLOR=RED, COLUMN=APPR_CNCL, WHEN=APPR_CNCL LT 0, $         
TYPE=DATA, COLOR=RED, COLUMN=APPR_TOTL, WHEN=APPR_TOTL LT 0, $         
TYPE=DATA, COLOR=RED, COLUMN=RSV, WHEN=RSV LT 0, $                     
TYPE=DATA, COLOR=RED, COLUMN=MND_RSV, WHEN=MND_RSV LT 0, $              
TYPE=DATA, COLOR=RED, COLUMN=CY_REL, WHEN=CY_REL LT 0, $                
TYPE=DATA, COLOR=RED, COLUMN=REL, WHEN=REL LT 0, $                      
TYPE=DATA, COLOR=RED, COLUMN=DISB_CY, WHEN=DISB_CY LT 0, $              
TYPE=DATA, COLOR=RED, COLUMN=UNEXP_REL_BAL, WHEN=UNEXP_REL_BAL LT 0, $  
TYPE=DATA, COLOR=RED, COLUMN=APPR_CF, WHEN=APPR_CF LT 0, $              
TYPE=DATA, COLOR=RED, COLUMN=REL_CF, WHEN=REL_CF LT 0, $                
TYPE=DATA, COLOR=RED, COLUMN=DISB_CF, WHEN=DISB_CF LT 0, $              
TYPE=DATA, COLOR=RED, COLUMN=RVSN_OPR_FCO, WHEN=RVSN_OPR_FCO LT 0, $    
TYPE=DATA, COLOR=RED, COLUMN=RVSN_CF, WHEN=RVSN_CF LT 0, $              
ENDSTYLE 
END      
-RUN

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


7.7, z/OS & Win7, excel & pdf
8.2 testing
 
Posts: 46 | Location: Tallahassee, FL ... was focused on, uh, what were we talking about? | Registered: February 03, 2012Report This Post
MartinY
Virtuoso
posted Hide Post
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
ON TABLE SUMMARIZE
ON TABLE SET SUBTOTALS ABOVE
-* Or
-*ON TABLE SET SUBTOTAL ABOVE
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
BGronli
Silver Member
posted Hide Post
Thanks Martin. I have tested out both and using SUMMARIZE is close, however, it puts the totals below the column totals, instead of above. Using 'ON TABLE SET SUBTOTAL ABOVE' did not produce a subtotal line at all.

Due to the requirements for this I need the totals to be three lines above the column titles and I need them to be red when the subtotal is less than zero.

The search continues.


7.7, z/OS & Win7, excel & pdf
8.2 testing
 
Posts: 46 | Location: Tallahassee, FL ... was focused on, uh, what were we talking about? | Registered: February 03, 2012Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Hi BGronli

Maybe think about using the alignment grid in the page heading and use
Hope this helps,


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
MartinY
Virtuoso
posted Hide Post
Here is the closest that I can give you.
You have must of the things that you need to play with. I put SUBHEAD and SUBFOOT but on or the other may be used. Both may not be needed. It only depend if you want a blank line between Grand-Total and first Sub-Total. SKIP-LINE can also be used.
since I don't have negative data, I put the condition as for GT 10000, you can change for LT 0.
Having the Sub-Total ABOVE the column's title, I don't think that is possible (in a simple way) since a Sub-Total is part of the data and the column's title displays ABOVE data.
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL

-*ON COUNTRY SKIP-LINE

ON COUNTRY SUBHEAD
""
ON COUNTRY SUBFOOT
""

ON COUNTRY SUBTOTAL AS 'Sub-Total : '
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SUBTOTALS ABOVE
ON TABLE COLUMN-TOTAL AS 'Grand-Total : '
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
     DEFMACRO=ST_NEG,
     MACTYPE=RULE,
     WHEN=RETAIL_COST GT 10000,
$
TYPE=SUBTOTAL,
     COLUMN=RETAIL_COST,
     COLOR=RED,
     MACRO=ST_NEG,
$
TYPE=SUBHEAD,
     BORDER=OFF,
$
TYPE=SUBFOOT,
     BORDER=OFF,
$
ENDSTYLE
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Hi BGronli,

Instead of using in your PRINT:
APPR_GEN/P17.2BMC
etc

use a DEFINE to change the format of you fields

DEFINE FILE BDGTOFCR
APPR_GEN/P17.2BMC=APPR_GEN;
...
END

and in the TABLE
PRINT APPR_GEN without a format

The reason for this is that when you change the format in the TABLE request, you are in fact creating a COMPUTE which means that for each field (e.g. APPR_GEN) you have 2 fields. I suppose that is why your style is not applied.
Worth a try...

Example with the CAR file:
  
-* File bgronli.fex
DEFINE FILE CAR
PRESALE/I6BMC=SALES - 10000;
END
TABLE FILE CAR
HEADING
" "
"Totals: <TOT.PRESALE"
" "
PRINT PRESALE
BY COUNTRY
ON TABLE SET STYLE *
TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=3, ITEM=1, COLSPAN=2, $
TYPE=DATA, COLUMN=PRESALE, COLOR=RED, WHEN=PRESALE LT 0,$
ENDSTYLE
END

This message has been edited. Last edited by: Danny-SRL,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
BGronli
Silver Member
posted Hide Post
Thank you Danny. While this does allow the formatting for the field to carry to the heading, it still does not change the COLOR of the field in the HEADING to red when the total is LT 0.


7.7, z/OS & Win7, excel & pdf
8.2 testing
 
Posts: 46 | Location: Tallahassee, FL ... was focused on, uh, what were we talking about? | Registered: February 03, 2012Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Sorry, I didn't see that you wanted the total in the HEADING to be colored.
  
-* File bgronli.fex
DEFINE FILE CAR
PRESALE/I6=SALES - 10000;
END
TABLE FILE CAR
HEADING
" "
"Totals: <TOT.PRESALE"
" "
SUM TOT.PRESALE NOPRINT
PRINT PRESALE
BY COUNTRY
ON TABLE SET STYLE *
TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=3, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=2, OBJECT=FIELD, ITEM=1, color=red, WHEN=TOT.PRESALE LT 30000, $
TYPE=DATA, COLUMN=PRESALE, COLOR=RED, WHEN=PRESALE LT 0,$
ENDSTYLE
END

... should do the trick


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
BGronli
Silver Member
posted Hide Post
That still did not do the trick, however, changes similar to what I have entered here did. I had to set-up the COMPUTE statement for the total and then use that in the TYPE=HEADING style. Thanks for the help and the responses.

DEFINE FILE CAR
PRESALE/I6=SALES - 10000;
END
TABLE FILE CAR
HEADING
" "
"Totals: <TOTPRESALE"
" "
PRINT PRESALE
COMPUTE TOTPRESALE/I6 = TOT.PRESALE; NOPRINT
BY COUNTRY
ON TABLE SET STYLE *
TYPE=HEADING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=HEADING, LINE=1, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=3, ITEM=1, COLSPAN=2, $
TYPE=HEADING, LINE=2, OBJECT=FIELD, ITEM=1, color=red, WHEN=TOTPRESALE LT 30000, $
TYPE=DATA, COLUMN=PRESALE, COLOR=RED, WHEN=PRESALE LT 0,$
ENDSTYLE
END


7.7, z/OS & Win7, excel & pdf
8.2 testing
 
Posts: 46 | Location: Tallahassee, FL ... was focused on, uh, what were we talking about? | Registered: February 03, 2012Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Interesting that my code did not work for you.
On my machine it does in 8.1 and 8.2.
If I may be so bold and ask did you run my code as-is?

This message has been edited. Last edited by: Danny-SRL,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 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] COLUMN_TOTALS above the column instead of below

Copyright © 1996-2020 Information Builders