Focal Point
[SOLVED] Column Heading with Across

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

October 17, 2007, 10:44 AM
MadamZuZu
[SOLVED] Column Heading with Across
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
October 17, 2007, 10:47 AM
Francis Mariani
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
October 17, 2007, 10:49 AM
Francis Mariani
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
October 17, 2007, 10:51 AM
MadamZuZu
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
October 17, 2007, 10:08 PM
Tom Flynn
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
October 18, 2007, 01:34 AM
susannah
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
October 18, 2007, 02:56 AM
Tony A
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 
October 18, 2007, 10:31 AM
Kerry
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.
October 18, 2007, 10:47 AM
Tony A
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 
October 18, 2007, 10:56 AM
Francis Mariani
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
October 18, 2007, 12:46 PM
susannah
Eep? ok, Eep! sweet, Kerry.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
October 18, 2007, 12:47 PM
susannah
ah. elegant improvement. thanks T




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
October 18, 2007, 12:56 PM
Francis Mariani
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
October 18, 2007, 03:44 PM
Tony A
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 
October 18, 2007, 03:51 PM
Francis Mariani
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
October 19, 2007, 11:01 AM
Kerry
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.
October 24, 2007, 10:57 AM
vkrugman
thanks a lot
That's what I 'm looking for


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
February 25, 2009, 01:35 PM
Hua
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
February 25, 2009, 03:19 PM
RSquared
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
February 25, 2009, 03:40 PM
Hua
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
February 25, 2009, 04:52 PM
RSquared
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
February 25, 2009, 05:06 PM
j.gross
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)
February 25, 2009, 05:26 PM
Hua
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
February 26, 2009, 09:07 AM
linus
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
February 26, 2009, 09:48 AM
Hua
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
March 01, 2009, 02:34 PM
susannah
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
March 02, 2009, 09:27 AM
Hua
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
March 02, 2009, 10:13 AM
Francis Mariani
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
March 02, 2009, 11:28 AM
nubi
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
March 02, 2009, 03:10 PM
Hua
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