Focal Point
[CLOSED] First Column wider than rest using SUM and OVER

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

October 15, 2010, 10:11 AM
Michelle Skaggs
[CLOSED] First Column wider than rest using SUM and OVER
First off I am new to WebFocus so hopefully this is an easy fix. I have just updated to Developer Studio 7701.
I have the following code:

JOIN
APLPROPOSAL.APLPROPOSAL.APLPROPOSALID IN aplproposal TO MULTIPLE
G108ASSUMPTIONS.G108ASSUMPTIONS.APLPROPOSALID IN g108assumptions AS J0
END
JOIN
G108ASSUMPTIONS.G108ASSUMPTIONS.G108ASSUMPTIONSID IN aplproposal TO MULTIPLE
G108OPTIONS.G108OPTIONS.G108ASSUMPTIONSID IN g108options AS J3
END
TABLE FILE APLPROPOSAL
SUM
'G108OPTIONS.G108OPTIONS.UNDER40RATE/F5.2M' AS '' OVER
'G108OPTIONS.G108OPTIONS.FORTIESRATE/F5.2M' AS ''
ACROSS 'G108OPTIONS.G108OPTIONS.G108OPTIONSID' NOPRINT
WHERE APLPROPOSAL.APLPROPOSAL.APLPROPOSALID EQ &APLPROPOSALID;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=0.652778,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
BORDER-TOP=OFF,
BORDER-BOTTOM=OFF,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
FONT='ARIAL',
SIZE=8,
RIGHTGAP=0.000000,
LEFTGAP=0.000000,
TOPGAP=0.000000,
BOTTOMGAP=0.000000,
JUSTIFY=CENTER,
LINES-PER-PAGE=20,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
BACKCOLOR=RGB(153 153 153),
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSVALUE,
ACROSS=1,
BORDER-TOP=LIGHT,
STYLE=NORMAL,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

If I remove the second field (FORTIESRATE) then all the columns are the same size. As soon as I add the second field then the first column is always wider than the rest. It has something to do with using OVER I believe. Any help would be appreciated.

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


DevStudio Version 7701
WebFocus 7.7 Windows Server 2003 PDF Format
October 17, 2010, 04:47 PM
Waz
Are you able to reproduce with the sample files ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

October 18, 2010, 12:08 PM
Michelle Skaggs
Thanks for your reply. I have just finished a test with the sample files in Developer Studio and it does the same thing.

Here is that code:
TABLE FILE PLANTS
SUM
'PLANTS.PLANTS.PLANTNAME' AS '' OVER
'PLANTS.PLANTS.PLANTCITY' AS '' OVER
'PLANTS.PLANTS.PLANTREGION' AS ''
ACROSS 'PLANTS.PLANTS.PLANTCODE' NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=1.000000,
$
TYPE=REPORT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
RIGHTGAP=0.000000,
LEFTGAP=0.000000,
JUSTIFY=CENTER,
$
ENDSTYLE
END

If you remove CITY and REGION and OVER then the columns are all the same size. If you put them back the first column is always larger. I can also make this happen by just having OVER after the first data item without a second data item listed. I hope that makes sense. Please let me know if you have any suggestions.

I have gotten around the problem by creating 4 separate reports that get called from my main document composer fex. It is not exactly how I wanted it to look though.


DevStudio Version 7701
WebFocus 7.7 Windows Server 2003 PDF Format
October 18, 2010, 01:19 PM
GinnyJakes
Just to clarify, are you using DS 77 with a 7.66 backend? That is not recommended. Or are you 77 all the way through. Your signature indicates only 7.66. Can you please clarify the situation? Thanks.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
October 18, 2010, 01:42 PM
Michelle Skaggs
Yes I am using DS 77 with a 7.66 backend. We have downloaded 77 for the server but do not have it installed yet. But, I was having this problem with DS 76, that is where it started. I only upgraded to see if it would fix some of the issues I was having with DS 76.


DevStudio Version 7701
WebFocus 7.7 Windows Server 2003 PDF Format
October 18, 2010, 02:17 PM
Prarie
Have you tried adjusting your field sizes in a define instead of within your request? I think it has something to do with the over and it's really using the original size of the field.
October 18, 2010, 05:17 PM
Waz
This is certainly weird. the first column seems to have spaces after the truncation (!).

I am glad you have a workaround, as this plainly doing the wrong thing.

The issue does seem to be related to the OVER.

Below is a reworked version of your report.

It uses the Macgyver technique.

The idea is to take the OVER out of the equation.

APP PATH IBIDEMO
 
EX -LINES 7 EDAPUT MASTER,fseq,CV,FILE
 FILE=FSEQ, SUFFIX=FIX
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$
 
FILEDEF FSEQ DISK fseq.mas
 
-RUN

TABLE FILE FSEQ
 SUM CONTROL       AS BLANK
 BY COUNTER
 WHERE COUNTER LE 3
 ON TABLE HOLD AS FOC_SEQ FORMAT FOCUS INDEX BLANK
END

-RUN

JOIN CLEAR *
JOIN BLANK WITH PLANTCODE IN PLANTS TO ALL BLANK IN FOC_SEQ AS JSEQ 

DEFINE FILE PLANTS
 BLANK/A1 WITH PLANTCODE = ' ' ;
 PLANT_DATA/A25 = IF COUNTER EQ 1 THEN PLANTNAME ELSE
                  IF COUNTER EQ 2 THEN PLANTCITY ELSE
                  IF COUNTER EQ 3 THEN PLANTREGION ELSE ' ' ;
END
 
TABLE FILE PLANTS
SUM PLANT_DATA AS ' '
ACROSS 'PLANTS.PLANTS.PLANTCODE' NOPRINT
BY COUNTER NOPRINT
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-*INCLUDE = endeflt,$
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=1.000000,
$
TYPE=REPORT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
RIGHTGAP=0.000000,
LEFTGAP=0.000000,
JUSTIFY=CENTER,
$
ENDSTYLE
END



Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

October 19, 2010, 09:01 AM
Michelle Skaggs
Thanks for the reworked version. I will attempt to implement my report like that and see what happens. I will let you know. Thanks for all the help and responses.


DevStudio Version 7701
WebFocus 7.7 Windows Server 2003 PDF Format
October 20, 2010, 08:31 AM
Danny-SRL
Waz,

As you know I am a great fan of McGuyver.
Just a short note: it is not necessary to create a FOCUS file. Just use the FSEQ and add a WHERE COUNTER in the TABLE FILE.

  
-* File WAZ2.fex
-SET &ECHO=ALL;

APP PATH IBIDEMO
 
EX -LINES 7 EDAPUT MASTER,fseq,CV,FILE
 FILE=FSEQ, SUFFIX=FIX
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$
-* 
FILEDEF FSEQ DISK fseq.mas
-*
JOIN CLEAR *
JOIN BLANK WITH PLANTCODE IN PLANTS TO BLANK IN FSEQ AS J_ 
-*
DEFINE FILE PLANTS
 BLANK/A1 WITH PLANTCODE = ' ' ;
 PLANT_DATA/A25 = IF COUNTER EQ 1 THEN PLANTNAME ELSE
                  IF COUNTER EQ 2 THEN PLANTCITY ELSE
                  IF COUNTER EQ 3 THEN PLANTREGION ELSE ' ' ;
END
-*
TABLE FILE PLANTS
WHERE COUNTER LE 3
SUM PLANT_DATA AS ' '
ACROSS 'PLANTS.PLANTS.PLANTCODE' NOPRINT
BY COUNTER NOPRINT
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-*INCLUDE = endeflt,$
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
JUSTIFY=CENTER,
SIZE=9,
$
ENDSTYLE
END



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

October 20, 2010, 05:04 PM
Waz
The FOCUS file was used only as a precaution, as joining to an indexed column is safer.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code