Focal Point
[SOLVED]Borders in Stylesheet for Excel output Report using Car File

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3197099336

October 08, 2013, 08:55 AM
Michele Brooks
[SOLVED]Borders in Stylesheet for Excel output Report using Car File
I am testing two borders that I created using the following code against the Car File. I am getting an extra bottom border at the end of the report. I need to know how to get rid of it. Thanks
  
BLANK/A1 WITH CAR  = ' ';
BLANK1/A1 = ' ';
BLANK2/A1 = ' ';
BLANK3/A1 = ' ';
FILLER/A1 = ' ';
END
TABLE FILE CAR
HEADING
"Testing Border in Subfoot  "
" "
" "
SUM RCOST DCOST
BY FILLER NOPRINT
BY BLANK  NOPRINT
BY BLANK1 NOPRINT
BY BLANK2 NOPRINT
BY BLANK3 NOPRINT
BY COUNTRY
ON BLANK3 SUBFOOT
""
""
"                             Summary Detail                                                    "
ON BLANK2 SUBFOOT
"                             Average Cost - Standard Invoice                                   "
"                             Invoice Count of Invoicess Meeting the Standard                   "
"                             % of the Invoices Meeting the Standard                            "
"                             Count of Invoices that are over the Standard Cost                 "
"                             % of Invoices that are over the total cost of the product         "
""
"                             Total Invoice Cost of all disability invoices                     "
"                             Count of Invoices that are not total disability invoices          "
"                             % of total disability invoices                                    "
"                             Count of disability that so not meet standards                    "
"                             % of partial disability invoices                                  "
""
"                             Average cost of all invoices including disability invoices        "
"                             Average count of all invoices                                     "
"                             % of average disability invoices                                  "
"                             Count invoices that are not disability                            "
"                             % of invoices that are not disability                             "
""
"                             Count of invoices that were not approved                          "
ON BLANK1 SUBFOOT
""
""
""
"                             Footnote "
ON BLANK SUBFOOT
""
"                             Calculation 1 "
"                             Calculation 2"
"                             Standard and non-standard invoices are separated according the the company`s standard policy procedure "
"                             Calculation 3"
"                             Calculation 4"
"                             Calculation 5"
"                             Calculation 6"
"                             Calculation 7"
""
ON FILLER SUBFOOT
""
ON TABLE SET PAGE-NUM OFF
-*ON TABLE SET EMPTYCELLS OFF
ON TABLE NOTOTAL
ON TABLE SET SHOWBLANKS ON
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
-*TYPE=GRANDTOTAL,STYLE=BOLD,BORDER-TOP=MEDIUM,BORDER-TOP-COLOR=RGB(31 46 110),$
-*TYPE=SUBTOTAL,BORDER-RIGHT=0.20,$
-*TYPE=SUBTOTAL,BORDER-LEFT=0.20,$


TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,$

-* Gets rid of the grid lines in the Subfoot
TYPE=SUBFOOT,BACKCOLOR=WHITE,COLSPAN=5,$

-* Top Border line after "Summary Detail"
TYPE=SUBFOOT,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='BLACK',
  COLSPAN=5,
$

-* Bottom Border line of the "Summary Detail" section
TYPE=SUBFOOT,
     BY=3,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='BLACK',
  COLSPAN=5,
$

-* Left Border
TYPE=SUBFOOT,
     BORDER-LEFT=MEDIUM,
     BORDER-LEFT-COLOR='BLACK',
$

-* Right Border
TYPE=SUBFOOT,
     BORDER-RIGHT=MEDIUM,
     BORDER-RIGHT-COLOR='BLACK',
$

-* Removes the right border from the blank lines that appear right before "Summary Detail"
TYPE=SUBFOOT,
     BY=5,
  BORDER-RIGHT=OFF,
$

-* Removes the right border from the blank lines that appear right after the "Footnote" section and extends the bottom border 5 columns
TYPE=SUBFOOT,
     BY=1,
  BORDER-RIGHT=OFF,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='BLACK',
  COLSPAN=5,
$

-* Removes the right border from the blank lines that appear right before "Footnote"
TYPE=SUBFOOT,
     BY=3,
  BORDER-RIGHT=OFF,
  BACKCOLOR=WHITE,
$

-* Makes "Footnote" BOLD
TYPE=SUBFOOT,
     BY=3,
     LINE=4,
     OBJECT=TEXT,
  STYLE=BOLD,
$

-* Makes "Summary Detail" BOLD
TYPE=SUBFOOT,
     BY=5,
     LINE=3,
     OBJECT=TEXT,
  STYLE=BOLD,
$
ENDSTYLE
END

This message has been edited. Last edited by: Michele Brooks,


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
October 08, 2013, 11:07 AM
susannah
Michelle,
your output is in excel, so i don't get any borders at all.
if you take excel off, you'll get borders.
if you remove the ON FILLER SUBFOOT bit, you won't get that last bordered box



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
October 08, 2013, 11:58 AM
Francis Mariani
The only way to debug this is to emphasize the different components - by assigning a different colour to each, and to enter different text. So, the following code generates the Excel spreadsheet as shown:

DEFINE FILE CAR
BLANK/A1 WITH CAR  = ' ';
BLANK1/A1 = ' ';
BLANK2/A1 = ' ';
BLANK3/A1 = ' ';
FILLER/A1 = ' ';
END
TABLE FILE CAR
HEADING
"Testing Border in Subfoot  "
" "
" "
SUM RCOST DCOST
BY FILLER NOPRINT
BY BLANK  NOPRINT
BY BLANK1 NOPRINT
BY BLANK2 NOPRINT
BY BLANK3 NOPRINT
BY COUNTRY
ON BLANK3 SUBFOOT
"111"
"111"
"                             Summary Detail                                                    "
ON BLANK2 SUBFOOT
"                             Average Cost - Standard Invoice                                   "
"                             Invoice Count of Invoicess Meeting the Standard                   "
"                             % of the Invoices Meeting the Standard                            "
"                             Count of Invoices that are over the Standard Cost                 "
"                             % of Invoices that are over the total cost of the product         "
""
"                             Total Invoice Cost of all disability invoices                     "
"                             Count of Invoices that are not total disability invoices          "
"                             % of total disability invoices                                    "
"                             Count of disability that so not meet standards                    "
"                             % of partial disability invoices                                  "
""
"                             Average cost of all invoices including disability invoices        "
"                             Average count of all invoices                                     "
"                             % of average disability invoices                                  "
"                             Count invoices that are not disability                            "
"                             % of invoices that are not disability                             "
""
"                             Count of invoices that were not approved                          "
ON BLANK1 SUBFOOT
"222"
"222"
"222"
"                             Footnote "
ON BLANK SUBFOOT
""
"                             Calculation 1 "
"                             Calculation 2"
"                             Standard and non-standard invoices are separated according the the company`s standard policy procedure "
"                             Calculation 3"
"                             Calculation 4"
"                             Calculation 5"
"                             Calculation 6"
"                             Calculation 7"
""
ON FILLER SUBFOOT
"333"
ON TABLE SET PAGE-NUM OFF
-*ON TABLE SET EMPTYCELLS OFF
ON TABLE NOTOTAL
ON TABLE SET SHOWBLANKS ON
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
-*TYPE=GRANDTOTAL,STYLE=BOLD,BORDER-TOP=MEDIUM,BORDER-TOP-COLOR=RGB(31 46 110),$
-*TYPE=SUBTOTAL,BORDER-RIGHT=0.20,$
-*TYPE=SUBTOTAL,BORDER-LEFT=0.20,$


TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,$

-* Gets rid of the grid lines in the Subfoot
TYPE=SUBFOOT,BACKCOLOR=WHITE,COLSPAN=5,$

-* Top Border line after "Summary Detail"
TYPE=SUBFOOT,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='BLACK',
  COLSPAN=5,
$

-* Bottom Border line of the "Summary Detail" section
TYPE=SUBFOOT,
     BY=3,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='RED',
  COLSPAN=5,
$

-* Left Border
TYPE=SUBFOOT,
     BORDER-LEFT=MEDIUM,
     BORDER-LEFT-COLOR='GREEN',
$

-* Right Border
TYPE=SUBFOOT,
     BORDER-RIGHT=MEDIUM,
     BORDER-RIGHT-COLOR='BLUE',
$

-* Removes the right border from the blank lines that appear right before "Summary Detail"
TYPE=SUBFOOT,
     BY=5,
  BORDER-RIGHT=OFF,
$

-* Removes the right border from the blank lines that appear right after the "Footnote" section and extends the bottom border 5 columns
TYPE=SUBFOOT,
     BY=1,
  BORDER-RIGHT=OFF,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='ORANGE',
  COLSPAN=5,
$

-* Removes the right border from the blank lines that appear right before "Footnote"
TYPE=SUBFOOT,
     BY=3,
  BORDER-RIGHT=OFF,
  BACKCOLOR=WHITE,
$

-* Makes "Footnote" BOLD
TYPE=SUBFOOT,
     BY=3,
     LINE=4,
     OBJECT=TEXT,
  STYLE=BOLD,
$

-* Makes "Summary Detail" BOLD
TYPE=SUBFOOT,
     BY=5,
     LINE=3,
     OBJECT=TEXT,
  STYLE=BOLD,
$
ENDSTYLE
END



It appears this code is causing the extra line because it's a second SUBFOOT: 
ON FILLER SUBFOOT
""
Perhaps you could eliminate this from your intricate code.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 08, 2013, 12:19 PM
Michele Brooks
Your suggestion eliminated the extra line, but now the bottom last border does not extend all of the way across. Here is the code that I ran.
  

SET NODATA = '';
SET ASNAMES = ON
-*SET SSDEBUG=256
SET PRINTPLUS=ON
DEFINE FILE CAR
BLANK/A1 WITH CAR  = ' ';
BLANK1/A1 = ' ';
BLANK2/A1 = ' ';
BLANK3/A1 = ' ';
FILLER/A1 = ' ';
END
TABLE FILE CAR
HEADING
"Testing Border in Subfoot  "
" "
" "
SUM RCOST DCOST
-*BY FILLER NOPRINT
BY BLANK  NOPRINT
BY BLANK1 NOPRINT
BY BLANK2 NOPRINT
BY BLANK3 NOPRINT
BY COUNTRY
ON BLANK3 SUBFOOT
""
""
"                             Summary Detail                                                    "
ON BLANK2 SUBFOOT
"                             Average Cost - Standard Invoice                                   "
"                             Invoice Count of Invoicess Meeting the Standard                   "
"                             % of the Invoices Meeting the Standard                            "
"                             Count of Invoices that are over the Standard Cost                 "
"                             % of Invoices that are over the total cost of the product         "
""
"                             Total Invoice Cost of all disability invoices                     "
"                             Count of Invoices that are not total disability invoices          "
"                             % of total disability invoices                                    "
"                             Count of disability that so not meet standards                    "
"                             % of partial disability invoices                                  "
""
"                             Average cost of all invoices including disability invoices        "
"                             Average count of all invoices                                     "
"                             % of average disability invoices                                  "
"                             Count invoices that are not disability                            "
"                             % of invoices that are not disability                             "
""
"                             Count of invoices that were not approved                          "
ON BLANK1 SUBFOOT
""
""
""
"                             Footnote "
ON BLANK SUBFOOT
""
"                             Calculation 1 "
"                             Calculation 2"
"                             Standard and non-standard invoices are separated according the the company`s standard policy procedure "
"                             Calculation 3"
"                             Calculation 4"
"                             Calculation 5"
"                             Calculation 6"
"                             Calculation 7"
""

-*ON FILLER SUBFOOT
-*""
ON TABLE SET PAGE-NUM OFF
-*ON TABLE SET EMPTYCELLS OFF
ON TABLE NOTOTAL
ON TABLE SET SHOWBLANKS ON
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
-*TYPE=GRANDTOTAL,STYLE=BOLD,BORDER-TOP=MEDIUM,BORDER-TOP-COLOR=RGB(31 46 110),$
-*TYPE=SUBTOTAL,BORDER-RIGHT=0.20,$
-*TYPE=SUBTOTAL,BORDER-LEFT=0.20,$


TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,$

-* Gets rid of the grid lines in the Subfoot
TYPE=SUBFOOT,BACKCOLOR=WHITE,COLSPAN=5,$

-* Top Border line after "Summary Detail"
TYPE=SUBFOOT,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='BLACK',
     COLSPAN=5,
$

-* Bottom Border line of the "Summary Detail" section
TYPE=SUBFOOT,
     BY=2,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='BLACK',
     COLSPAN=5,
$

-* Left Border
TYPE=SUBFOOT,
     BORDER-LEFT=MEDIUM,
     BORDER-LEFT-COLOR='BLACK',
$

-* Right Border
TYPE=SUBFOOT,
     BORDER-RIGHT=MEDIUM,
     BORDER-RIGHT-COLOR='BLACK',
$

-* Removes the right border from the blank lines that appear right before "Summary Detail"
TYPE=SUBFOOT,
     BY=4,
  BORDER-RIGHT=OFF,
$

-* Removes the right border from the blank lines that appear right after the "Footnote" section and extends the bottom border 5 columns
-*TYPE=SUBFOOT,
-*     BY=1,
-*  BORDER-RIGHT=OFF,
-*     BORDER-BOTTOM=MEDIUM,
-*     BORDER-BOTTOM-COLOR='BLACK',
-*     COLSPAN=5,
-*$

-* Removes the right border from the blank lines that appear right before "Footnote"
TYPE=SUBFOOT,
     BY=2,
  BORDER-RIGHT=OFF,
  BACKCOLOR=WHITE,
$

-* Makes "Footnote" BOLD
TYPE=SUBFOOT,
     BY=2,
     LINE=4,
     OBJECT=TEXT,
  STYLE=BOLD,
$

-* Makes "Summary Detail" BOLD
TYPE=SUBFOOT,
     BY=4,
     LINE=3,
     OBJECT=TEXT,
  STYLE=BOLD,
$
ENDSTYLE
END



WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
October 08, 2013, 12:40 PM
Francis Mariani
You don't have enough columns in the body of the report - COUNTRY, RCOST and DCOST are only three - you need five. Try this:

SUM
RCOST
DCOST
FILLER AS ''
FILLER AS ''


DEFINE FILE CAR
BLANK/A1 WITH CAR  = ' ';
BLANK1/A1 = ' ';
BLANK2/A1 = ' ';
BLANK3/A1 = ' ';
FILLER/A1 = ' ';
END
TABLE FILE CAR
HEADING
"Testing Border in Subfoot  "
" "
" "
SUM
RCOST
DCOST
FILLER AS ''
FILLER AS ''

BY FILLER NOPRINT
BY BLANK  NOPRINT
BY BLANK1 NOPRINT
BY BLANK2 NOPRINT
BY BLANK3 NOPRINT
BY COUNTRY
ON BLANK3 SUBFOOT
"111"
"111"
"                             Summary Detail                                                    "
ON BLANK2 SUBFOOT
"                             Average Cost - Standard Invoice                                   "
"                             Invoice Count of Invoicess Meeting the Standard                   "
"                             % of the Invoices Meeting the Standard                            "
"                             Count of Invoices that are over the Standard Cost                 "
"                             % of Invoices that are over the total cost of the product         "
""
"                             Total Invoice Cost of all disability invoices                     "
"                             Count of Invoices that are not total disability invoices          "
"                             % of total disability invoices                                    "
"                             Count of disability that so not meet standards                    "
"                             % of partial disability invoices                                  "
""
"                             Average cost of all invoices including disability invoices        "
"                             Average count of all invoices                                     "
"                             % of average disability invoices                                  "
"                             Count invoices that are not disability                            "
"                             % of invoices that are not disability                             "
""
"                             Count of invoices that were not approved                          "
ON BLANK1 SUBFOOT
"222"
"222"
"222"
"                             Footnote "
ON BLANK SUBFOOT
""
"                             Calculation 1 "
"                             Calculation 2"
"                             Standard and non-standard invoices are separated according the the company`s standard policy procedure "
"                             Calculation 3"
"                             Calculation 4"
"                             Calculation 5"
"                             Calculation 6"
"                             Calculation 7"
""
-*ON FILLER SUBFOOT
-*"333"
ON TABLE SET PAGE-NUM OFF
-*ON TABLE SET EMPTYCELLS OFF
ON TABLE NOTOTAL
ON TABLE SET SHOWBLANKS ON
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
-*TYPE=GRANDTOTAL,STYLE=BOLD,BORDER-TOP=MEDIUM,BORDER-TOP-COLOR=RGB(31 46 110),$
-*TYPE=SUBTOTAL,BORDER-RIGHT=0.20,$
-*TYPE=SUBTOTAL,BORDER-LEFT=0.20,$


TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,$

-* Gets rid of the grid lines in the Subfoot
TYPE=SUBFOOT,BACKCOLOR=WHITE,COLSPAN=5,$

-* Top Border line after "Summary Detail"
TYPE=SUBFOOT,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='BLACK',
  COLSPAN=5,
$

-* Bottom Border line of the "Summary Detail" section
TYPE=SUBFOOT,
     BY=3,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='RED',
  COLSPAN=5,
$

-* Left Border
TYPE=SUBFOOT,
     BORDER-LEFT=MEDIUM,
     BORDER-LEFT-COLOR='GREEN',
$

-* Right Border
TYPE=SUBFOOT,
     BORDER-RIGHT=MEDIUM,
     BORDER-RIGHT-COLOR='BLUE',
$

-* Removes the right border from the blank lines that appear right before "Summary Detail"
TYPE=SUBFOOT,
     BY=5,
  BORDER-RIGHT=OFF,
$

-* Removes the right border from the blank lines that appear right after the "Footnote" section and extends the bottom border 5 columns
TYPE=SUBFOOT,
     BY=1,
  BORDER-RIGHT=OFF,
     BORDER-BOTTOM=MEDIUM,
     BORDER-BOTTOM-COLOR='ORANGE',
  COLSPAN=5,
$

-* Removes the right border from the blank lines that appear right before "Footnote"
TYPE=SUBFOOT,
     BY=3,
  BORDER-RIGHT=OFF,
  BACKCOLOR=WHITE,
$

-* Makes "Footnote" BOLD
TYPE=SUBFOOT,
     BY=3,
     LINE=4,
     OBJECT=TEXT,
  STYLE=BOLD,
$

-* Makes "Summary Detail" BOLD
TYPE=SUBFOOT,
     BY=5,
     LINE=3,
     OBJECT=TEXT,
  STYLE=BOLD,
$
ENDSTYLE
END



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 08, 2013, 01:12 PM
Michele Brooks
It worked. You are the man. You are the best. Your suggestion worked. I need to open up another Post because I need the two bordered sections to start at around position 27, and not left justified. Thanks again.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
October 08, 2013, 02:47 PM
Francis Mariani
That might be hard to do! If you have a constant number of lines in the footing text, you might be better off using an Excel template.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server