Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND] subtotal display problem using 'sequence'
Go
New
Search
Notify
Tools
Reply
  
[WORKAROUND] subtotal display problem using 'sequence'
 Login/Join
 
Silver Member
posted
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,


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks Frank,
I am looking into the earlier posts.
Will reply back if still get the problem.


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Reply With QuoteReport This Post
Silver Member
posted Hide Post
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??


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
I have found that using SEQUENCE messes up other styling - a bug with probably no workaround.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks very much Mickey and Francis for ur suggestions.
It seems a sin to use sequence for ordering the columns while subtotaling.


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
pink, it's an error of "ommisin" on IBI's part.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
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?


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
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


WebFOCUS 7.6.x
PMF 5.2.x
 
Posts: 58 | Location: Sydney, Australia | Registered: April 22, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
quote:
ON BODYTYPE SUBFOOT
"


Thanks Mika
Finally i got a workaround..


Thanks and Regards,

WF 7611
XFOCUS/FOCUS DB
Win XP
EXL2K/HTML
 
Posts: 37 | Location: India | Registered: September 25, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [WORKAROUND] subtotal display problem using 'sequence'

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.