Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Repeating Columns Headers in Excel and Subtotal Issue

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Repeating Columns Headers in Excel and Subtotal Issue
 Login/Join
 
Gold member
posted
I am having difficulty envisioning how to accomplish this.


I can create a hold file with data like this:

GROUP LINENO IN OUT
GROUP1 01 100 50
GROUP1 02 200 100
GROUP1 03 300 150
GROUP2 01 100 50
GROUP2 02 200 100
GROUP2 03 300 150
GROUP3 01 100 50
GROUP3 02 200 100
GROUP3 03 300 150


I need to create an excel spreadsheet as shown below.
GROUP1 LINENO IN OUT
DATA 01 100 50
DATA 02 200 100
DATA 03 300 150
DATA TOTAL 600 300
GROUP2 LINENO IN OUT
DATA 01 100 50
DATA 02 200 100
DATA 03 300 150
DATA TOTAL 600 300
GROUP3 LINENO IN OUT
DATA 01 100 50
DATA 02 200 100
DATA 03 300 150
DATA TOTAL 600 300

I have 2 questions

How do I get the column headers to REPEAT as shown and with the GROUP Name change?

Another difficulty is the row with the TOTAL (Which is the Sub-Total of LINENO’S 01,02 & 03) – How can I get the word DATA in the first column and word TOTAL in the second column?

Any suggestions would be appreciated - Thanks

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


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Report This Post
Expert
posted Hide Post
I am working on a solution for you. Give me a few more minutes.


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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
Here is my solution. You might have to adjust the numbers of the phony sort groups and the trick is to make all of the formats the same. This technique is useful in all kinds of situations.
APP FI GRPMAS DISK grplines.mas
-RUN
-WRITE GRPMAS FILENAME=GRPLINES,SUFFIX=FIX
-WRITE GRPMAS SEGNAME=GRPLINES,SEGTYPE=S0
-WRITE GRPMAS FIELDNAME=GROUPNAME,ALIAS=GRPNM,FORMAT=A6,ACTUAL=A6,$
-WRITE GRPMAS FIELDNAME=,ALIAS=,FORMAT=A1,ACTUAL=A1,$
-WRITE GRPMAS FIELDNAME=LINENO,ALIAS=LNNO,FORMAT=A2,ACTUAL=A2,$
-WRITE GRPMAS FIELDNAME=INAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
-WRITE GRPMAS FIELDNAME=OUTAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
APP FI GRPLINES DISK grplines.ftm
-RUN
-WRITE GRPLINES GROUP1 01 100 50
-WRITE GRPLINES GROUP1 02 200 100
-WRITE GRPLINES GROUP1 03 300 150
-WRITE GRPLINES GROUP2 01 100 50
-WRITE GRPLINES GROUP2 02 200 100
-WRITE GRPLINES GROUP2 03 300 150
-WRITE GRPLINES GROUP3 01 100 50
-WRITE GRPLINES GROUP3 02 200 100
-WRITE GRPLINES GROUP3 03 300 150
DEFINE FILE GRPLINES
GRPCOL/A6=GROUPNAME;
LNNOCOL/A6='LINENO';
INCOL/A5='IN';
OUTCOL/A5='OUT';
SORT2/I1=1;
END
TABLE FILE GRPLINES
SUM GRPCOL LNNOCOL INCOL OUTCOL
COMPUTE SORT1/I1=IF GROUPNAME NE LAST GROUPNAME THEN SORT1+1 ELSE SORT1; NOPRINT
BY TOTAL SORT1
BY SORT2
BY GROUPNAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COLHEAD FORMAT ALPHA
END
DEFINE FILE GRPLINES
GRPCOL/A6='DATA';
LNNOCOL/A6='TOTAL';
SORT2/I1=9;
END
TABLE FILE GRPLINES
SUM GRPCOL LNNOCOL 
INAMT NOPRINT OUTAMT NOPRINT
COMPUTE INCOL/A5=EDIT(INAMT);
        OUTCOL/A5=EDIT(OUTAMT);
COMPUTE SORT1/I1=IF GROUPNAME NE LAST GROUPNAME THEN SORT1+1 ELSE SORT1; NOPRINT
BY TOTAL SORT1
BY SORT2
BY GROUPNAME NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COLFOOT FORMAT ALPHA
END
DEFINE FILE GRPLINES
GRPCOL/A6='DATA';
LNNOCOL/A6=LINENO;
SORT1/I1=IF GROUPNAME NE LAST GROUPNAME THEN SORT1+1 ELSE SORT1;
SORT2/I1=2;
END
TABLE FILE GRPLINES
PRINT 
INAMT NOPRINT OUTAMT NOPRINT
COMPUTE INCOL/A5=EDIT(INAMT);
        OUTCOL/A5=EDIT(OUTAMT);
BY SORT1
BY SORT2
BY GROUPNAME NOPRINT
BY GRPCOL
BY LNNOCOL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS COLDTL FORMAT ALPHA
END
TABLE FILE COLHEAD
PRINT GRPCOL AS '' LNNOCOL AS '' INCOL AS '' OUTCOL AS ''
BY SORT1 NOPRINT
BY SORT2 NOPRINT
MORE
FILE COLDTL
MORE 
FILE COLFOOT
END


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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Expert
posted Hide Post
To produce the output in Excel, add this line just before the first MORE in the last TABLE request:
ON TABLE PCHOLD FORMAT EXL2K


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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Gold member
posted Hide Post
Ginny,

Thank You - this is just what I needed.

I only have to change the amounts so they are numeric - which I can do by changing the FMT on INAMT/OUTAMT. If it is the column header then 'A20' else 'D20'.


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Report This Post
Virtuoso
posted Hide Post
Oops - I replaced my earlier code using a multi-verb request because it is not necessary...

Here is another approach, using SUBHEADs, SUBFOOTs, and STYLESHEET. I borrowed Ginny's code for creating the test data and master.

APP FI GRPMAS DISK grplines.mas
-RUN
-WRITE GRPMAS FILENAME=GRPLINES,SUFFIX=FIX
-WRITE GRPMAS SEGNAME=GRPLINES,SEGTYPE=S0
-WRITE GRPMAS FIELDNAME=GROUPNAME,ALIAS=GRPNM,FORMAT=A6,ACTUAL=A6,$
-WRITE GRPMAS FIELDNAME=LINENO,ALIAS=LNNO,FORMAT=A2,ACTUAL=A2,$
-WRITE GRPMAS FIELDNAME=INAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
-WRITE GRPMAS FIELDNAME=OUTAMT,ALIAS=,FORMAT=I4,ACTUAL=A4,$
APP FI GRPLINES DISK grplines.ftm
-RUN
-WRITE GRPLINES GROUP101 100 50
-WRITE GRPLINES GROUP102 200 100
-WRITE GRPLINES GROUP103 300 150
-WRITE GRPLINES GROUP201 100 50
-WRITE GRPLINES GROUP202 200 100
-WRITE GRPLINES GROUP203 300 150
-WRITE GRPLINES GROUP301 100 50
-WRITE GRPLINES GROUP302 200 100
-WRITE GRPLINES GROUP303 300 150
-RUN
-*
DEFINE FILE GRPLINES
 GROUPHEAD/A4 = 'DATA';
END
-*
TABLE FILE GRPLINES
  SUM GROUPHEAD AS ''
      LINENO    AS ''
      INAMT     AS ''
      OUTAMT    AS ''
   BY GROUPNAME NOPRINT
   BY LINENO    NOPRINT
   ON GROUPNAME SUBHEAD
      "<GROUPNAME<+0>LINENO<+0>IN<+0>OUT"
   ON GROUPNAME SUBFOOT
      "DATA<+0>TOTAL<ST.INAMT<ST.OUTAMT"
   ON TABLE NOTOTAL
   ON TABLE SET HTMLCSS ON
   ON TABLE PCHOLD FORMAT EXL2K
   ON TABLE SET STYLESHEET *
      TYPE=SUBHEAD, HEADALIGN=BODY, $
      TYPE=SUBFOOT, HEADALIGN=BODY, $
      TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT, $
   ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
I believe this will work too - I will choose the winner!

Thanks


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Repeating Columns Headers in Excel and Subtotal Issue

Copyright © 1996-2020 Information Builders