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,
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
-HTMLFORM ENDThis 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.
Cheers,
Kerry
Kerry Zhan Focal Point Moderator Information Builders, Inc.
October 18, 2007, 10:47 AM
Tony A
Kerry,
Take a gold star!!!
TThis 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
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
So, has everyone got a decent signature now then?
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
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. 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