Hi All,
I am new to webfocus and using this forum first time.
I am getting a problem in display of subtotal, when i am using 'sequence' command
for ordering of my columns, the subtotal is displayed in two broken lines. what should i do to avoid this.
My code looks like this-



-SET &REPORT_ID ='OTM-RTS-O-001';
-SET &DBSERVER='OTMTTGT1R4';
-SET &RPTFMT='EXL2K';

-SET EMPTYREPORT=ON
SET SQLENGINE=SQLORA
SQL SET USER SERVER
SQL SQLORA SET SERVER &DBSERVER
SQL SQLORA
SELECT * FROM BOOK;
TABLE FILE SQLOUT
PRINT
*

ON TABLE HOLD AS DATESSSS
END


TABLE FILE DATESSSS
PRINT
BOOK_I AS 'BOOK ID'
SRVC_TYPE_C AS 'SERVICE TYPE'
BOOK_STAT_C AS 'BOOK STATUS'
BY TRAN_I AS 'TRANSACTION ID' SUBTOTAL BOOK_I


ON TABLE SUBHEAD
"REPORT"
"Report Name :Book Details"
"Report ID : GTM-LPR-O-006"
-*"Run Date :"
""

ON TABLE SET PAGE-NUM OFF
ON TABLE SET EMPTYREPORT ANSI
ON TABLE SET LINES 40
ON TABLE SET BYDISPLAY OFF
ON TABLE PCHOLD FORMAT &RPTFMT

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=OFF,
ORIENTATION=PORTRAIT,
BORDER=LIGHT,

$

TYPE=REPORT,
GRID=ON,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
STYLE=NORMAL,
TITLETEXT=SFF-TESTING REPORT,
BORDER=LIGHT,
$

TYPE=DATA,
COLUMN=BOOK_STAT_C,
SIZE=10,
STYLE=BOLD,
BACKCOLOR='YELLOW',
WHEN=BOOK_STAT_C EQ 'BLNCD',
JUSTIFY=LEFT,

$

TYPE=DATA,
COLUMN=BOOK_STAT_C,
SIZE=10,
STYLE=BOLD,
BACKCOLOR='AQUA',
WHEN=BOOK_STAT_C EQ 'APRVD',
JUSTIFY=LEFT,

$

TYPE=DATA,
COLUMN=BOOK_STAT_C,
SIZE=10,
STYLE=BOLD,
BACKCOLOR='RED',
WHEN=BOOK_STAT_C EQ 'CANLD',
JUSTIFY=LEFT,

$

TYPE=DATA,
COLUMN=BOOK_STAT_C,
SIZE=10,
STYLE=BOLD,
BACKCOLOR='GREY',
WHEN=BOOK_STAT_C EQ 'ASGND',
JUSTIFY=LEFT,

$

TYPE=DATA,
COLUMN=BOOK_STAT_C,
SIZE=10,
STYLE=BOLD,
BACKCOLOR='BLUE',
WHEN=BOOK_STAT_C EQ 'CNFRM',
JUSTIFY=LEFT,

$

TYPE=DATA,
COLUMN=BOOK_STAT_C,
SIZE=10,
STYLE=BOLD,
BACKCOLOR='PINK',
WHEN=BOOK_STAT_C EQ 'RSRVD',
JUSTIFY=LEFT,

$

TYPE=DATA,
COLUMN=BOOK_STAT_C,
SIZE=10,
STYLE=BOLD,
BACKCOLOR='GREEN',
WHEN=BOOK_STAT_C EQ 'INCMP',
JUSTIFY=LEFT,

$

TYPE=TITLE,
STYLE=BOLD,
GRID=OFF,
JUSTIFY=LEFT,
BORDER=LIGHT,
$

TYPE=TABHEADING,
SIZE=10,
JUSTIFY=CENTER,
BORDER=ON,
$
TYPE=TABFOOTING,
SIZE=8,
STYLE=BOLD,
COLOR='PINK',
JUSTIFY=LEFT,
$

TYPE=HEADING,BORDER=OFF,
$
TYPE=FOOTING,BORDER=OFF,
$


TYPE=TABHEADING,
JUSTIFY=CENTER,
$
TYPE=TABHEADING, LINE=1,
STYLE=BOLD,
SIZE=12,
JUSTIFY=CENTER,
$
TYPE=SUBHEAD,
SIZE=11,
STYLE=BOLD,
COLOR='YELLOW',
JUSTIFY=CENTER,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
$


TYPE=REPORT, COLUMN=BOOK_I, SEQUENCE=2,
$

TYPE=REPORT, COLUMN=TRAN_I, SEQUENCE=1,
$

TYPE=REPORT, COLUMN=BOOK_STAT_C, SEQUENCE=4,
$

TYPE=REPORT, COLUMN=SRVC_TYPE_C, SEQUENCE=3,
$

ENDSTYLE
END
-EXIT


Any help appreciated.
Thanks in advance.

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

Hi Pink

Welcome to the focalpoint website and welcome to the Webfocus world.

Your problem is an old one and as you might understand there are some workarounds to this problem.

But first I would suggest you some starter tips.

1) Try to simplify the problem for others by building a similar report on one of the databases that comes with webfocus like CAR. If you do so we all can execute the fex on our own systems and see if we get the same result.

2) If you post examples please put them between the coding tags to make it better readable

 [code]

-SET &REPORT_ID ='OTM-RTS-O-001';
-SET &DBSERVER='OTMTTGT1R4';
-SET &RPTFMT='EXL2K';
*.....

[/code])

3) Before asking please use the SEARCH button to see if your problem has been solved already.

If you search for "subtotal two lines" you will find several similar questions and answers.

Start by reading this issue how to avoid two lines

Thanks Frank,
I am looking into the earlier posts.
Will reply back if still get the problem.

Hi Frank,
I have gone thru some of the earlier posts regarding subtotal in two lines, but i think my problem is a bit different.
I am getting that two line subtotal only if i am using 'sequence' command for reordering the display of my columns, else if i am not using 'sequence' command, then am not getting that two line display, its working fine then.

acc. to car file my code will look like this:-

TABLE FILE CAR
PRINT
RETAIL_COST AS 'R COST'
DEALER_COST AS 'D COST'
SALES AS 'SALES'
BY BODYTYPE AS 'BODYTYPE' SUBTOTAL RETAIL_COST AS ''

ON TABLE SUBHEAD
"REPORT"
"Report Name :CAR Details"
"Report ID : OTM-LPR-O-006"
""

ON TABLE SET PAGE-NUM OFF
ON TABLE SET EMPTYREPORT ANSI
ON TABLE SET LINES 40
ON TABLE SET BYDISPLAY OFF
ON TABLE PCHOLD FORMAT 'HTML'

ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
  ORIENTATION=PORTRAIT,
  BORDER=LIGHT,
SQUEEZE=OFF,
$

TYPE=REPORT,
     GRID=ON,
     FONT='ARIAL',
     SIZE=10,
     COLOR='BLACK',
     STYLE=NORMAL,
  TITLETEXT=CAR-TESTING REPORT,
     BORDER=LIGHT,
$

TYPE=TITLE,
     STYLE=BOLD,
  GRID=ON,
     JUSTIFY=LEFT,
  BORDER=LIGHT,
 $

TYPE=TABHEADING,
     SIZE=10,
     JUSTIFY=CENTER,
  BORDER=ON,
$
TYPE=TABFOOTING,
     SIZE=8,
     STYLE=BOLD,
     JUSTIFY=LEFT,
$

TYPE=HEADING,BORDER=OFF,
$
TYPE=FOOTING,BORDER=OFF,
$


TYPE=TABHEADING,
     JUSTIFY=CENTER,
$
TYPE=TABHEADING, LINE=1,
     STYLE=BOLD,
  SIZE=12,
     JUSTIFY=CENTER,
$
TYPE=SUBHEAD,
     SIZE=11,
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
  STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     STYLE=BOLD,
$


TYPE=REPORT, COLUMN=RETAIL_COST, SEQUENCE=2,
$

TYPE=REPORT, COLUMN=BODYTYPE, SEQUENCE=1,
$

TYPE=REPORT, COLUMN=SALES, SEQUENCE=4,
$

TYPE=REPORT, COLUMN=DEALER_COST, SEQUENCE=3,
$

ENDSTYLE
END
-EXIT

Only sequencing of the columns is creating problem.
What should i do??

Pink,

General practice is to order your BY fields and your PRINT fields in the actual statements in the order in which you would like them to appear.

I would suggest the following:

1) Report this issue to Information Builders so they can address it.

2) Change your request to implement the suggestion above. Code your report so that the fields appear in the BYs/PRINT statements in the order you want them to appear on the report.

I have found that using SEQUENCE messes up other styling - a bug with probably no workaround.

Thanks very much Mickey and Francis for ur suggestions.
It seems a sin to use sequence for ordering the columns while subtotaling.

pink, it's an error of "ommisin" on IBI's part.

Hi Francis/Mickey,

Mickey, u suggested me to put the columns in the PRINT statement in the order i want them to appear in the report.

But i want my sort(BY) field to appear at the third position as by default it appears as first field in the report. I tried this:-

TABLE FILE CAR
PRINT
RETAIL_COST AS 'RETAIL COST'
DEALER_COST AS 'DEALER COST'
BODYTYPE AS 'BODYTYPE'
SALES AS 'SALES'
BY BODYTYPE NOPRINT
ON BODYTYPE SUBTOTAL RETAIL_COST AS ''

here i want BODYTYPE to appear as third column.
But here also i am getting subtotal in two lines, i checked in earlier posts also but didn't get the workaround for this(i don't want to sort by any other field so as to make the total length of visible sort fields greater than or equal to subtotal field value).

Is there any way to achieve this?

Hi Pink,

You can use SUBFOOT to show e.g. subtotal values. You can also add other fields and free text in SUBFOOT.

Regards,
Mika

 
TABLE FILE CAR
PRINT RETAIL_COST AS 'RETAIL COST'
      DEALER_COST AS 'DEALER COST'
      BODYTYPE AS 'BODYTYPE'
      SALES AS 'SALES'
BY BODYTYPE NOPRINT
-* ON BODYTYPE SUBTOTAL RETAIL_COST AS ''
ON BODYTYPE SUBFOOT
"<ST.RETAIL_COST"
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY, STYLE=BOLD, JUSTIFY=RIGHT, $
ENDSTYLE
END

quote:

ON BODYTYPE SUBFOOT
"

Thanks Mika
Finally i got a workaround..