TABLE FILE SQLOUT
SUM
NB/D5 AS 'Nb'
MNT/D11.2S AS 'Montant'
COMPUTE MOYENNE/D6.2 = IF NB GE 1 THEN MNT / NB ELSE 0; AS 'Moyenne'
COMPUTE MNT12/D11.2 = IF LICPER EQ '1 - Mensuel' THEN MNT * 12
ELSE IF LICPER EQ '2 - Bimestriel' THEN MNT * 6
ELSE IF LICPER EQ '3 - Trimestriel' THEN MNT * 4
ELSE IF LICPER EQ '4 - Semestriel' THEN MNT * 2
ELSE MNT; NOPRINT
BY LIBACT NOPRINT
BY LOT AS 'Lot'
BY LIBLOT AS 'Libellé'
ACROSS LICPER AS 'PERIODICITE'
[COLOR:RED]COMPUTE MNTTOT/D11.2S = sum of mnt12 columns[/COLOR]
ON LIBACT RECOMPUTE AS '*TOTAL'
ON LIBACT SUBFOOT
" "
HEADING
"FMVE - STATISTIQUES RECRUTEMENT PA : <+0>&CAMPANT - édité le <+0>&DATEEDT"
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
$
After the compute, I want to totalize the column named MNT12. I don't know how many occurences I will have in the across.
How can I do that ? Thanks for help. CatherineThis message has been edited. Last edited by: Cati - France,
7.7.02 (Html, Excel, Ahtml, Pdf,Graph,.....) OS400 V5R3, V5R4 Windows 2000/2003/2007/2010
September 10, 2009, 01:30 PM
susannah
Cheat. (becoming my favorite word!) TABLE FILE CAR SUM SALES NOPRINT BY COUNTRY SUM SALES BY COUNTRY ACROSS CAR COMPUTE ENDTHING/I8=C1; END you calculate your complicated total calculation up front, before the ACROSS, and just NOPRINT it, then reference that column using column notation C1, in a COMPUTE. See if you can make that work for you.
This example makes use of DOUBLE VERBS, you can use as many nested verb sets as you want. just make sure each subsequent set of BY fields contains exactly the same BY fields as the one before it, and adds whatever new one you want. Multiple verbs rock.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
September 10, 2009, 02:00 PM
Francis Mariani
Susannah,
You beat me to it!
TABLE FILE GGSALES
SUM
COMPUTE MOYENNE/D12 = DOLLARS / UNITS; NOPRINT
BY CATEGORY
SUM
UNITS/D10
DOLLARS/D10
BUDUNITS/D10
BY CATEGORY
ACROSS ST
COMPUTE MOYENNE/D10 = C3; AS 'MOYENNE'
WHERE ST IN ('CA', 'CT', 'FL', 'GA', 'IL');
END
C3 is the third field in the request, the compute of MOYENNE.
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
September 10, 2009, 02:17 PM
susannah
and we both forgot to remind Cati that ON TABLE SET HOLDLIST PRINTONLY will help in using Column reference notation (eg C1)
In Francis' example, he uses C3 parce que the C1 and C2 positions are used up by the numerator and denominator of his COMPUTE. Using PRINTONLY, those 2 fields don't get counted.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
September 10, 2009, 02:33 PM
Francis Mariani
susannah,
I think SET HOLDLIST=PRINTONLY will only work once the TABLE request is output to the hold file, I think I still have to use C3 for the COMPUTE...
I think...
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
September 10, 2009, 02:51 PM
susannah
oh , you do? hmm.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
September 10, 2009, 02:51 PM
Francis Mariani
just a bit
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
September 10, 2009, 03:58 PM
j.gross
I find SET HOLDLIST has no effect on the interpretation of Cnn references appearing in COMPUTE.
Note: The 7.6.1 documentation cited states "[SET CNOTATION ...] is not supported in an ON TABLE phrase." In 7.6.9 there seems to be no such restriction.
- Jack Gross WF through 8.1.05
September 10, 2009, 04:10 PM
Francis Mariani
Well, that's interesting! Thanks Jack.
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
September 10, 2009, 04:11 PM
susannah
ah jack knows everything!!!
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
September 10, 2009, 04:27 PM
Francis Mariani
Yes, but can he give me a painless root canal (tomorrow at ten)?
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
September 11, 2009, 04:39 AM
GamP
But doesn't that depend on your notion / definition of the word 'painless'? (without pain or less pain).
How does Cati feel about the response you guys gave her?
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
September 11, 2009, 08:52 AM
Cati - France
Thanks a lot for your suggestions. Here is what I did to obtain the result I wanted :
DEFINE FILE SQLOUT
MNT12/D11.2 = IF LICPER EQ '1 - Mensuel' THEN MNT * 12
ELSE IF LICPER EQ '2 - Bimestriel' THEN MNT * 6
ELSE IF LICPER EQ '3 - Trimestriel' THEN MNT * 4
ELSE IF LICPER EQ '4 - Semestriel' THEN MNT * 2
ELSE MNT;
END
TABLE FILE SQLOUT
SUM
MNT12/D11.2S NOPRINT
NB/D7S AS 'Nb PA recrutés'
BY LIBACT NOPRINT
BY LOT AS 'Lot'
BY LIBLOT AS 'Libellé'
SUM
NB/D5S AS 'Nb'
MNT/D11.2S AS 'Montant'
COMPUTE MOYENNE/D6.2S = IF NB GE 1 THEN MNT / NB ELSE 0; AS 'Moyenne'
BY LIBACT NOPRINT
BY LOT AS 'Lot'
BY LIBLOT AS 'Libellé'
ACROSS LICPER AS 'PERIODICITE'
COMPUTE MNTTOT/D11.2S = C1; AS 'Collecte,sur 12 Mois'
COMPUTE MOY12/D7.2S = C1/C2; AS 'Moyenne,sur 12 mois'
ON LIBACT RECOMPUTE AS '*TOTAL'
ON LIBACT SUBFOOT
" "
HEADING
"FMVE - STATISTIQUES RECRUTEMENT PA : <+0>&CAMPANT - édité le <+0>&DATEEDT"
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
But now, I have problem to style the 2 columns I had after the across. what kind of columns are these columns ? (TYPE=ACROSSVALUE,COLUMN=MNTTOT, doesn't work) Thanks in advance for your next (very good) answers. Catherine
7.7.02 (Html, Excel, Ahtml, Pdf,Graph,.....) OS400 V5R3, V5R4 Windows 2000/2003/2007/2010
September 11, 2009, 09:12 AM
GamP
See this example:
TABLE FILE CAR
SUM SEATS
BY CAR
BY MODEL
ACROSS COUNTRY
COMPUTE TWICE/D12.2 = 2 * SEATS;
ON TABLE SET STYLE *
TYPE=DATA, FONT='VERDANA', SIZE=9, $
TYPE=DATA, COLUMN=TWICE, STYLE=BOLD, $
ENDSTYLE
END
So it is just enough to name the column by it's name.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
September 11, 2009, 11:17 AM
Cati - France
Thanks Gerard, It's work fine with TYPE=DATA for the detail lines. But would you have a solution for styling the title of these columns and the subtotal for these columns because the general style for type=SUBTOTAL is not applied to these columns in EXL2K output (it is applied in HTML format).
And an idea to center the title on the two lines of the across title ?
7.7.02 (Html, Excel, Ahtml, Pdf,Graph,.....) OS400 V5R3, V5R4 Windows 2000/2003/2007/2010
September 11, 2009, 03:14 PM
GamP
TABLE FILE CAR
SUM SEATS
BY CAR
BY MODEL
ACROSS COUNTRY
COMPUTE TWICE/D12.2 = 2 * SEATS;
ON TABLE SUBTOTAL
ON CAR SUBTOTAL
ON TABLE SET STYLE *
TYPE=DATA, FONT='VERDANA', SIZE=9, $
TYPE=DATA, COLUMN=TWICE, STYLE=BOLD, $
TYPE=REPORT, COLUMN=TWICE, STYLE=BOLD, $
TYPE=SUBTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=RED,$
TYPE=GRANDTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=SILVER, $
ENDSTYLE
END
Works for me, as far as the column title and the subtotals is concerned. Don't know exactly what you mean with ' the title on the two lines of the across title '?
Everything well with you? And Pierre?
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
September 12, 2009, 02:41 AM
Cati - France
I think I was not clear enough. Run this and you will see what I mean (As I said, no problem with HTML format, the problem comes with EXL2K format)
TABLE FILE CAR SUM SEATS BY CAR BY MODEL ACROSS COUNTRY COMPUTE TWICE/D12.2 = 2 * SEATS; AS 'Twice line1, line2' COMPUTE THREE/D12.2 = 3 * SEATS; AS 'Three line1, line2' ON TABLE SUBTOTAL ON CAR SUBTOTAL ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE * TYPE=DATA, FONT='VERDANA', SIZE=9, $ TYPE=DATA, COLUMN=TWICE, STYLE=BOLD, $ TYPE=REPORT, COLUMN=TWICE, STYLE=BOLD, $ TYPE=SUBTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=RED,$ TYPE=GRANDTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=SILVER, $ ENDSTYLE END
7.7.02 (Html, Excel, Ahtml, Pdf,Graph,.....) OS400 V5R3, V5R4 Windows 2000/2003/2007/2010
September 13, 2009, 12:08 AM
Francis Mariani
Does this help?
TABLE FILE CAR
SUM SEATS
BY CAR
BY MODEL
ACROSS COUNTRY
COMPUTE TWICE/D12.2 = 2 * SEATS; AS 'TWICE LINE1, LINE2'
COMPUTE THREE/D12.2 = 3 * SEATS; AS 'THREE LINE1, LINE2'
ON TABLE SUBTOTAL
ON CAR SUBTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=DATA, FONT='VERDANA', SIZE=9, $
TYPE=REPORT, COLUMN=TWICE, STYLE=BOLD, $
TYPE=REPORT, COLUMN=THREE, STYLE=BOLD, $
TYPE=SUBTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=RED,$
TYPE=GRANDTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=SILVER, $
TYPE=ACROSSVALUE, STYLE=BOLD, $
TYPE=TITLE, STYLE=BOLD, $
ENDSTYLE
END
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
September 14, 2009, 12:48 PM
Cati - France
Francis,
Could you try this code, first with the TYPE=HEADING line active and then without this line. When I run the code without the line, It's OK. When I run the code with the line, the style is not applied for the columns after the across. I'm in 7.6.7 release.
Any idea to by-pass this problem ? Thank you in advance for all your advices. Catherine
SET SSDEBUG=256 TABLE FILE CAR SUM SEATS BY CAR BY MODEL ACROSS COUNTRY COMPUTE TWICE/D12.2 = 2 * SEATS; AS 'TWICE LINE1, LINE2' COMPUTE THREE/D12.2 = 3 * SEATS; AS 'THREE LINE1, LINE2' HEADING "TEST" ""
ON TABLE SUBTOTAL ON CAR SUBTOTAL ON TABLE PCHOLD FORMAT EXL2K
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
September 14, 2009, 01:26 PM
Cati - France
Francis,
The borders in excel work if you add SET SSDEGUG=256 in the fex (I have lots of fex with this keyword and I have the borders in excel without using templates and macros). I think it's a bug, I have opened a case with IBI french support because I need to have the borders. To by pass the problem, for the moment, I will do a template with an excel macro.
Thanks for taking time to answer. (Merci beaucoup). Catherine
7.7.02 (Html, Excel, Ahtml, Pdf,Graph,.....) OS400 V5R3, V5R4 Windows 2000/2003/2007/2010
September 14, 2009, 01:45 PM
Francis Mariani
quote:
SET SSDEBUG=256
I did have that in the fex based on your code, but I've never seen it before.
Good luck with getting the bug fixed.
Ciao,
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
September 14, 2009, 02:10 PM
Francis Mariani
In v7.6.5, there's something wrong in even the simplest fex, when more than one HEADING line is coded:
SET SSDEBUG=256
TABLE FILE CAR
SUM SALES
BY COUNTRY
HEADING
"TEST LINE 1"
"TEST LINE 2"
ON TABLE SET STYLE *
TYPE=HEADING, BORDER=MEDIUM, BORDER-STYLE=RIDGE, BORDER-COLOR=BLUE, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END
Also, try BORDER=LIGHT to see different behaviour.
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
September 14, 2009, 02:32 PM
GinnyJakes
First of all, thank you Cati. Your suggestion helped me with a styling problem.
And, Francis, great minds think alike. I have been experiencing the same problem today with a 2 line footing after adding the Cati's set command. I even tried adding this