Focal Point
[SOLVED] Heading alignment issue with across field in excel format

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

May 14, 2020, 07:42 AM
Purushottam
[SOLVED] Heading alignment issue with across field in excel format
Hello Everyone,
I am getting issue while aligning the heading with the across field used in table file request.
Please refer to the below code created on car master file.

TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
BY CAR.COMP.CAR
BY CAR.BODY.BODYTYPE
ACROSS LOWEST CAR1.COMP.CAR
ON TABLE SUBHEAD
"Dev<+0>Reporting Languge<+0>&DATEtMDYY"
" <+0> <+0> "
"Dev<+0> <+0>&DATEtMDYY"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
TYPE=TABHEADING,
HEADALIGN=BODY,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=CENTER,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=RIGHT,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=2,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=3,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=2,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=LEFT,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=3,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 204 255),
COLSPAN=1,
JUSTIFY=RIGHT,
$
ENDSTYLE
END


The heading is not aligned fully with the report. It is getting squeezed to the left part of report.

Thanks in advance.

This message has been edited. Last edited by: FP Mod Chuck,


8207.06, Window 7
May 14, 2020, 11:47 AM
FP Mod Chuck
Hi Purushottam

Thanks for making your first post in Focal Point. I think you will find it helpful for getting answers to your development questions.

I think there is an issue with XLSX honoring the report heading alignment grid and that is why you are seeing it squeezed to the left. One thing I did different was when I first created the report I inserted the heading and the alignment grid and it allowed me to choose the number of lines and number of items per line vs when you add the heading and alignment grid after selecting columns for the report. When you do it that way the checkbox for aliginging to the data is pre-selected and can't be unchecked. When I did it my way I was able to have 6 itmes per line and space the heading components out. When I ran it with output format html it worked great but when I ran it with xlsx output it still squeezed it together. Below is my code but I think you should open a case with techsupport and hopefully they can help resolve this issue. Or maybe someone else on the forum has run across this and has an answer.


TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
ON TABLE SUBHEAD
" <+0>Dev<+0> <+0>Reporting Language<+0> <+0>&YYMD"
" <+0> <+0> <+0> <+0> <+0> "
" <+0>Dev<+0> <+0> <+0> <+0>&YYMD"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
WIDTH=.500,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=4,
WIDTH=2.000,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=5,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=1,
OBJECT=TEXT,
ITEM=6,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=2,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=3,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=4,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=5,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=2,
OBJECT=TEXT,
ITEM=6,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=2,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=3,
WIDTH=.500,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=4,
WIDTH=2.000,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=5,
WIDTH=1.000,
$
TYPE=TABHEADING,
LINE=3,
OBJECT=TEXT,
ITEM=6,
WIDTH=1.000,
$
ENDSTYLE
END



Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
May 14, 2020, 11:55 AM
MartinY
Which layout are you looking for the Heading ?
Something such as this ?


If it's the case, simply remove the COLSPAN lines but keep the HEADALIGN


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
May 14, 2020, 11:58 AM
Tom Flynn
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
ON TABLE SUBHEAD
"Dev<+0>Reporting Languge<+0>&DATEtMDYY"
" <+0> <+0> "
"Dev<+0> <+0>&DATEtMDYY"ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,
$
TYPE=TABHEADING, HEADALIGN=BODY, $
TYPE=TABHEADING, LINE=1, ITEM=1, COLSPAN=1,$
TYPE=TABHEADING, LINE=2, ITEM=2, COLSPAN=1,$
TYPE=TABHEADING, LINE=3, ITEM=3, COLSPAN=1,$
TYPE=TABHEADING, LINE=2, ITEM=1, COLSPAN=1,$
TYPE=TABHEADING, LINE=2, ITEM=2, COLSPAN=1,$
TYPE=TABHEADING, LINE=2, ITEM=3, COLSPAN=1,$
TYPE=TABHEADING, LINE=3, ITEM=1, COLSPAN=1,$
TYPE=TABHEADING, LINE=3, ITEM=2, COLSPAN=1,$
TYPE=TABHEADING, LINE=3, ITEM=3, COLSPAN=1,$
ENDSTYLE
END


-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
May 14, 2020, 12:10 PM
MartinY
@Tom

You've removed the ACROSS which is the pin point of Purushottam...


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
May 14, 2020, 12:34 PM
Tom Flynn
Used Chuck's code...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
May 14, 2020, 12:40 PM
Tom Flynn
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
ACROSS LOWEST CAR.COMP.CAR
ON TABLE SUBHEAD
"Dev<+0>Reporting Languge<+0>&DATEtMDYY"
" <+0> <+0> "
"Dev<+0> <+0>&DATEtMDYY"ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,
$
TYPE=TABHEADING, HEADALIGN=BODY, $
TYPE=TABHEADING, LINE=1, ITEM=1, COLSPAN=4, JUSTIFY=LEFT, $
TYPE=TABHEADING, LINE=1, ITEM=2, COLSPAN=4, JUSTIFY=CENTER, $
TYPE=TABHEADING, LINE=1, ITEM=3, COLSPAN=4, JUSTIFY=RIGHT, $
TYPE=TABHEADING, LINE=2, ITEM=1, COLSPAN=4, JUSTIFY=LEFT, $
TYPE=TABHEADING, LINE=2, ITEM=2, COLSPAN=4, JUSTIFY=CENTER, $
TYPE=TABHEADING, LINE=2, ITEM=3, COLSPAN=4, JUSTIFY=RIGHT, $
TYPE=TABHEADING, LINE=3, ITEM=1, COLSPAN=4, JUSTIFY=LEFT, $
TYPE=TABHEADING, LINE=3, ITEM=2, COLSPAN=4, JUSTIFY=CENTER, $
TYPE=TABHEADING, LINE=3, ITEM=3, COLSPAN=4, JUSTIFY=RIGHT, $
ENDSTYLE
END


-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
May 14, 2020, 01:50 PM
FP Mod Chuck
Ooooops sorry I missed the across when I recreated it...


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
May 14, 2020, 02:00 PM
Purushottam
TABLE FILE CAR1
SUM
CAR1.BODY.DEALER_COST
BY CAR1.COMP.CAR
BY CAR1.BODY.BODYTYPE
ACROSS LOWEST BODYTYPE
ON TABLE SUBHEAD
"Dev<+0>Reporting Languge<+0>&DATEtMDYY"
" <+0> <+0> "
"Dev<+0> <+0>&DATEtMDYY"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET XLSXPAGESETS ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
TYPE=TABHEADING, HEADALIGN=BODY, $
TYPE=TABHEADING, LINE=1, ITEM=1, COLSPAN=4, JUSTIFY=LEFT,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=1, ITEM=2, COLSPAN=4, JUSTIFY=CENTER,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=1, ITEM=3, COLSPAN=4, JUSTIFY=RIGHT,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=2, ITEM=1, COLSPAN=4, JUSTIFY=LEFT,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=2, ITEM=2, COLSPAN=4, JUSTIFY=CENTER,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=2, ITEM=3, COLSPAN=4, JUSTIFY=RIGHT,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=3, ITEM=1, COLSPAN=4, JUSTIFY=LEFT,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=3, ITEM=2, COLSPAN=4, JUSTIFY=CENTER,BACKCOLOR=RGB(0 204 255), $
TYPE=TABHEADING, LINE=3, ITEM=3, COLSPAN=4, JUSTIFY=RIGHT,BACKCOLOR=RGB(0 204 255), $
ENDSTYLE

END


Hi Tom,
I can not fix the heading span to the across field, as I changed the across field, the heading will go out of scope of report, please refer to above attached code, heading should dynamically align with the across field value. For instance, if number of column increases because of across field, heading should dynamically align with the report, same should happen in case when number of column decreases.


8207.06, Window 7
May 14, 2020, 02:07 PM
Purushottam
Hi MartinY,
I can not fix the heading with the across, it should dynamically align with header as across field changes, here in the case for across field BODYTYPE the header will go out of scope of report and for across field MODEL, header will not align with the report.


8207.06, Window 7
May 14, 2020, 02:12 PM
Purushottam
Hi Chuck,
This approach will work in case when I have prior information about across field length. The across field can have dynamic length like Car or BodyType or Model across field, heading should always be aligned with the report.
Thanks.


8207.06, Window 7
May 14, 2020, 02:47 PM
Tom Flynn
Acknowledging you are new, this would require a minimum of 3 steps - here are a possible 1st 2 steps. You'll need to work the rest...

TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
ACROSS LOWEST BODYTYPE
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS GET_COLUMNS FORMAT ALPHA
END
-RUN
CHECK FILE GET_COLUMNS HOLD
-RUN
TABLEF FILE HOLD
PRINT FIELDNAME/A50
ON TABLE HOLD AS GOT_COLUMNS FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-TYPE Number of Columns = &LINES
-EXIT


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
May 21, 2020, 01:36 PM
Purushottam
Issue is resolved by using dynamic colspan to align heading with across field. Wink



-SET &ECHO = ALL;
-DEFAULTH &MODEL = '';
TABLE FILE CAR1
SUM CNT.MODEL
ON TABLE SET ASNAME ON
ON TABLE HOLD AS TEST
END
-RUN
-READFILE TEST
-RUN
-TYPE &MODEL
-SET &CALSPANCNT1 = 1 ;
-TYPE &CALSPANCNT1

-SET &CALSPANCNT2 = &MODEL -1;
-TYPE &CALSPANCNT2

-SET &CALSPANCNT3 = 2 ;
-TYPE &CALSPANCNT3


TABLE FILE CAR1
SUM
DEALER_COST
BY COUNTRY
BY CAR
ACROSS LOWEST MODEL
HEADING
"Dev<+0>Reporting Languge<+0>&DATEtMDYY"
" <+0> <+0> "
"Dev<+0> <+0>&DATEtMDYY"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TITLETEXT='’Across Continent’',
$
TYPE=REPORT,
GRID=OFF,
FONT='IBIDEFAULT',
SIZE=9,
$
TYPE=TITLE,
COLOR='WHITE',
BACKCOLOR=RGB(102 102 102),
$
TYPE=HEADING,
SIZE=12,
BACKCOLOR=RGB(0 204 255),
STYLE=BOLD,
JUSTIFY=CENTER,
HEADALIGN=BODY,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
COLSPAN=&CALSPANCNT1.EVAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
COLSPAN=&CALSPANCNT2.EVAL,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
COLSPAN=&CALSPANCNT3.EVAL,
JUSTIFY=RIGHT,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
COLSPAN=&CALSPANCNT1.EVAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=2,
COLSPAN=&CALSPANCNT2.EVAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=3,
COLSPAN=&CALSPANCNT3.EVAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
COLSPAN=&CALSPANCNT1.EVAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=2,
COLSPAN=&CALSPANCNT2.EVAL,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=3,
COLSPAN=&CALSPANCNT3.EVAL,
JUSTIFY=RIGHT,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END


8207.06, Window 7
May 22, 2020, 08:19 AM
Berny
I was actually experiencing a similar situation. Thanks for posting your solution! I will definitely check out COLSPAN.

Webfocus 8202
Windows