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] Column Heading with Across
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Column Heading with Across
 Login/Join
 
Gold member
posted
TABLE FILE CAR
SUM
CNT.SEATS
BY COUNTRY
ACROSS MODEL
ON TABLE SET STYLE *
TYPE=REPORT,  SQUEEZE=ON, GRID=ON, WRAP=ON,  FONT='ARIAL', SIZE=9, STYLE=BOLD, ORIENTATION=LANDSCAPE, JUSTIFY=CENTER, $
ENDSTYLE
END


what can i do to put the "Country" title either on the same line as "Model" title, or model values?

basically, i want to get rid of the blank line that the title "Country" creates.

Thanks!

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


~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/Self-Serve Apps Only (No App Server)
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode
 
Posts: 74 | Location: Gaithersburg, MD | Registered: August 08, 2006Report This Post
Expert
posted Hide Post
There isn't any way to do that by sticking to normal WebFOCUS code. You could create a HEADING line that contained the column titles but, because you have an ACROSS, you would have to determine the ACROSS values to stick into this heading line, before running the actual report.


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, 2005Report This Post
Expert
posted Hide Post
If it's obvious what the contents of the BY column is (in your example it's obvious that the values are countries) I would simply do
BY column-name AS ''
to eliminate the column title.


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, 2005Report This Post
Gold member
posted Hide Post
the BY column is not obvious, and i won't know the exact number of across values either, becuase it changes daily.

thanks anyway. i'll have to think of something else.


~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/Self-Serve Apps Only (No App Server)
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode
 
Posts: 74 | Location: Gaithersburg, MD | Registered: August 08, 2006Report This Post
Expert
posted Hide Post
MadamZuZu,

Unfortunately, we have to do this all-too-often. IBI is too busy w/IBM, so, we have to figure out ways to accomplish our tasks.

I do this quite often, and, you can modify as you see fit.

Prerequisite: Need to know your data and what is/are the across column(s), lengths, etc.:

-SET &ECHO=ALL;
-**********************************************
-* Dump the ACROSS values *
-**********************************************
TABLE FILE CAR
BY MODEL
ON TABLE SAVE AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &X_CNT = 1;
-********************************************************************
-* Set the main BY column as the 1st SUBHEAD *
-********************************************************************
-SET &SUBH_TITLE='Country';
-********************************************************************
-* Loop to get the ACROSS column headings *
-********************************************************************
-LOOP_BEG
-READ HOLD1 &MODELS.A24.
-TYPE &MODELS
-IF &IORETURN NE 0 THEN GOTO ALL_DONE;
-SET &X_CNT = &X_CNT + 1;
-SET &SUBH_TITLE= &SUBH_TITLE || '<+0>'|| '&MODELS.EVAL';
-GOTO LOOP_BEG
-*****************************
-ALL_DONE
-*****************************
-TYPE &SUBH_TITLE
SET NODATA = ' '
DEFINE FILE CAR
DUMMY/A1 = ' ';
END
TABLE FILE CAR
SUM
SEATS AS ''
BY DUMMY NOPRINT
BY COUNTRY AS ''
ACROSS MODEL NOPRINT
ON DUMMY SUBHEAD
"&SUBH_TITLE"
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
SQUEEZE=ON,
GRID=ON, WRAP=ON,
FONT='ARIAL',
SIZE=9,
STYLE=BOLD,
JUSTIFY=CENTER,
$
-********************************************************************
-* Align the headings
-********************************************************************
TYPE=SUBHEAD,
BACKCOLOR=RGB(176 196 222),
HEADALIGN=BODY,
$
-********************************************************************
-* Dynamically create the SUBHEAD w/POSITION
-********************************************************************
-SET &Y_CNT=1;
-REPEAT SUBH_LOOP WHILE &Y_CNT LE &X_CNT.EVAL;
TYPE=SUBHEAD, LINE=1, OBJECT=TEXT, ITEM=&Y_CNT.EVAL, POSITION=P&Y_CNT.EVAL , $
-SET &Y_CNT = &Y_CNT+1;
-SUBH_LOOP
ENDSTYLE
END
-RUN


-********************************************************************
OR, to do it Dynamically:
-********************************************************************

-DEFAULTS &FLD_NAME = 'MODEL'
CHECK FILE CAR HOLD
-RUN
TABLE FILE HOLD
PRINT ACTUAL
WHERE FIELDNAME EQ '&FLD_NAME.EVAL';
ON TABLE SAVE AS FLD_FMT
END
-RUN
-READ FLD_FMT &FMT.A8.
-SET &FMT = EDIT(&FMT,'$999');
-********************************************************************
-* Create the SUBHEAD values *
-********************************************************************
TABLE FILE CAR
BY &FLD_NAME
ON TABLE SAVE AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &X_CNT=1;
-********************************************************************
-* Set the main BY column as the 1st SUBHEAD *
-********************************************************************
-SET &SUBH_TITLE='Country';
-READ HOLD1 &COL_NAME.A&FMT.EVAL.
-TYPE &COL_NAME
-IF &IORETURN NE 0 THEN GOTO ALL_DONE;
-SET &X_CNT = &X_CNT + 1;
-DIDIT
-SET &SUBH_TITLE= &SUBH_TITLE || '<+0>'|| '&COL_NAME.EVAL';
-GOTO LOOPIT
-*****************************
-ALL_DONE
-*****************************

then do the rest from above after the ALL_DONE label....



Good Luck...

Edit: Try doing this in the GUI - NOT!

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Expert
posted Hide Post
Zu, that famous un-addressable little area...
If your output is PDF , which i'm guessing from all the stuff in your style line, i can't help you.
but if its HTML, i've got a neat trick.
you're going to lay a table right on TOP of the report...works like a charm.
TABLE FILE CAR
..BY COUNTRY AS '' ACROSS MODEL
ON TABLE HOLD AS MYTAB FORMAT HTMTABLE
END
-RUN
-HTMLFORM BEGIN
<HTML>
<BODY topmargin="0" leftmargin="0"> 
<table border="0" cellpadding="0" cellspacing="0"   height:100;
style="border-collapse: collapse;
 font-family:MS Sans Serif; font-size:8.5pt;background-color:RGB(221 220 215);
color:RGB(0 10 20); position:absolute;left:  3; top: 51; z-index: 0 ;   "> 
<tr><td style="width:80;" > &|nbsp; country 
<tr><td style="width:80; height:70;">  &|nbsp;
</table>
!IBI.FIL.MYTAB ;
</BODY>
</HTML>

-HTMLFORM END

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




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
Susannah,

Good use of overlaid HTML, you could reduce your code by ditching the table and use a span instead -
TABLE FILE CAR
SUM RCOST DCOST
BY COUNTRY AS ''
ACROSS MODEL
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE HOLD AS MYTAB FORMAT HTMTABLE
END
-RUN
-HTMLFORM BEGIN
<html>
<body topmargin="0" leftmargin="0"> 
<span style="font-family:MS Sans Serif; font-size:10pt;background-color:RGB(221 220 215);
      color:RGB(0 0 0);position:absolute;left:3;top:5;z-index:0;width:80;height:50;text-align:center;">
COUNTRY
</span>
!IBI.FIL.MYTAB ;
</body>
</html>
-HTMLFORM END

Not as felxible as Tom's suggestion above but then easier for simple HTML output and newbies to get their head around. Although you have to tailor the positioning of the span for each report, depending upon whether you have page numbers, ACROSS column_name AS '' etc. but this is easily done.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Hi MadamZuZu and all,

Can you please try the following code:

SET ASNAMES=ON
TABLE FILE CAR
SUM
CNT.SEATS  
BY COUNTRY
ACROSS MODEL
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
END
TABLE FILE ACRSHOLD
PRINT *
END


Hope this helps. Wink

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Expert
posted Hide Post
Kerry,

Take a gold star!!!

T

This message has been edited. Last edited by: Tony A,



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Eep! That's great Kerry! So simple Smiler


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, 2005Report This Post
Expert
posted Hide Post
Eep? ok, Eep! sweet, Kerry.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
ah. elegant improvement. thanks T




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
A borrowed avatar, T?


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, 2005Report This Post
Expert
posted Hide Post
Yes, DHagan's avatar just seemd to fit the bill, so I borrowed it Smiler

So, has everyone got a decent signature now then? Wink

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
They're looking better and better Big Grin


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, 2005Report This Post
Expert
posted Hide Post
Hi folks,

Thanks so much for the nice compliment. Big Grin Your input is great as well. Keep the good idea/suggestion come please!!

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Gold member
posted Hide Post
thanks a lot
That's what I 'm looking for


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Guru
posted Hide Post
Hi there,

I tried to apply Kerry's technique in my procedure but having trouble getting the desired heading.
I have two across fields: period sequence & period ID, I am sorting across by the sequence but want to show the period ID only.
SET ASNAMES=ON
TABLE FILE CAR
SUM
DCOST AS ''
BY SEATS
ACROSS COUNTRY NOPRINT
ACROSS CAR
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
END
TABLE FILE ACRSHOLD
PRINT *
END
  


As in the above example, I like to see the CAR in the heading, not COUNTRY. Any advices?

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
quote:
SET ASNAMES=ON
TABLE FILE CAR
SUM
DCOST AS ''
BY SEATS
ACROSS COUNTRY NOPRINT
ACROSS CAR
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
END
TABLE FILE ACRSHOLD
PRINT *
END

Hua

Just turn in around

SET ASNAMES=ON
TABLE FILE CAR
SUM
DCOST AS ''
BY SEATS
ACROSS CAR
ACROSS COUNTRY

ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
END
TABLE FILE ACRSHOLD
PRINT *
END


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Guru
posted Hide Post
RSquared,

My real application is based on these across values:
COUNTRY 000001 000002 000003 000004 00005 ....
CAR OPS09 OCT08 NOV08 DEC08 JAN09 ....

can not turn them around.

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Guru
posted Hide Post
Hua,

Can an entry for 'Car' be used with more than 1 'Country' ? If not, then it doesn't make any difference which sequence you are sorting in. Please give some more information on what you are trying to do.

Thanks
Bob


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
Create a variable combining data from the two sort-across variables, and sort ACROSS that one variable instead. That compound value (within some length limit) becomes the fieldname in the HOLD file.

To control the order of the columns -- Either fashion the compound values so they naturally sort the columns the way you want, or else use COLUMNS to force the desired order. -- There are several earlier threads that show how to generate the
COLUMNS 'value' AND 'value' ...
phrase dynamically by means of dialog manager (using TABLE and -REPEAT and -READ and -SET)
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Guru
posted Hide Post
Hi Bob,

Thanks for the reply.

I want to show a YTD report that has upto 12 months for any given time period.
The two across-fields are really the same G/L period, one in numeric sequence and the other is the identifier.

Jack, you just gave me the right direction. I saw the COLUMNS 'value' AND 'value' .. but they were harded 'value's, mine is upto 12 across columns and like to have the 2nd field as the column headings. Let me try it in the dialog manager...

Thanks.

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Platinum Member
posted Hide Post
I use DM to build my column headings for rolling months, actually our reports are based on two week increments, but same difference. I then place my column headings in my heading logic. If you need an example let me know and I will email it to you.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Report This Post
Guru
posted Hide Post
Thanks linus.

Since you also sugguest DM is the way to go, I'll give it a try first and will ask for your help if needed. Thanks for the offer.


Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
ON TABLE SET HOLDLIST PRINTONLY

SET ASNAMES=ON
TABLE FILE CAR
SUM
DCOST AS ''
BY SEATS
ACROSS COUNTRY NOPRINT
ACROSS CAR
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
TABLE FILE ACRSHOLD
PRINT *
END

.. the printonly will allow your fields to be ordered by country, but will not save the country field, or any field that is NOPRINTed..
so your output in this case will have columns for CAR data, but not for COUNTRY data.

Is that what you wanted?
say, Winnipeg, this is a really really old thread... perhaps your post would have been better in a new on??




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Guru
posted Hide Post
Yes Susannah. This is exactly what I wanted! Thank you so much.

I know this is an old thread, but it looks like I not off the topic, and there are a few threads directing us to this one. Your magic PRINTONLY ultimately solve this type of problems, people like me don't have to look elsewhere.

In Winnipeg, at this moment, I just want the winter ti be over. Any ideal?

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Expert
posted Hide Post
It's no better in Toronto - it's 10 in the morning and it's -13 and "feels like" -23.


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, 2005Report This Post
Guru
posted Hide Post
quote:
Edit: Try doing this in the GUI - NOT!


what a horrible thought!!

anyone serious about getting the most out of WebFOCUS will dump the GUI and get used to doing it in raw code, once you understand the syntax everything becomes a lot easier and more efficient... imho


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Guru
posted Hide Post
Sorry that I fooled everyone when I said Susannah solved my problem. The CAR example is perfectly logical and the result is desirable. But it didn't work for me in my fex. The culprit is first ACROSS field that is numeric. Why WF behave this way?

Note the following example, the SEATS is the first ACROSS I want to hide.
SET ASNAMES=ON
TABLE FILE CAR
SUM
DCOST AS ''
BY COUNTRY
ACROSS SEATS NOPRINT 
ACROSS CAR
ON TABLE HOLD AS ACRSHOLD FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
TABLE FILE ACRSHOLD
PRINT *
END


Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Column Heading with Across

Copyright © 1996-2020 Information Builders