Hello,

Here is my procedure

  
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.
Catherine

This message has been edited. Last edited by: Cati - France,

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.

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.

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.

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...

oh , you do? hmm.

just a bit

I find SET HOLDLIST has no effect on the interpretation of Cnn references appearing in COMPUTE.

Perhaps you were thinking of
SET CNOTATION=EXPLICIT

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.

Well, that's interesting! Thanks Jack.

ah
jack knows everything!!!

Yes, but can he give me a painless root canal (tomorrow at ten)?

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?

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

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.

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 ?

Thanks in advance.
Catherine

The result can be seen at : http://i27.tinypic.com/2qkpcvn.jpg

This message has been edited. Last edited by: Cati - France,

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?

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

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,

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

ON TABLE SET STYLE *
-*TYPE=HEADING,BORDER=MEDIUM,BORDER-STYLE=RIDGE,BORDER-COLOR=RGB(181 94 7),SIZE=12,BACKCOLOR=RGB(141 198 63),JUSTIFY=CENTER,$
TYPE=DATA,FONT='VERDANA',SIZE=9,BACKCOLOR=GRAY,$
TYPE=TITLE,STYLE=BOLD,BACKCOLOR=PURPLE,$
TYPE=REPORT, COLUMN=TWICE, STYLE=BOLD, $
TYPE=REPORT, COLUMN=THREE, STYLE=BOLD, $
TYPE=ACROSSTITLE,BACKCOLOR=BROWN,JUSTIFY=CENTER,STYLE=BOLD,$
TYPE=ACROSSVALUE,COLUMN=TWICE,BACKCOLOR=BLUE,$
TYPE=ACROSSVALUE,COLUMN=THREE,BACKCOLOR=YELLOW,$
TYPE=SUBTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=RED,$
TYPE=GRANDTOTAL, STYLE=BOLD+UNDERLINE, BACKCOLOR=SILVER, $
TYPE=ACROSSVALUE, STYLE=BOLD, BACKCOLOR=GREEN,$
ENDSTYLE
END

Catherine,

I think it's the border attributes - they probably don't work for Excel.

Try this instead:

TYPE=HEADING,SIZE=12,BACKCOLOR=RGB(141 198 63),JUSTIFY=CENTER,$

Merci,

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

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,

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.

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

TYPE=FOOTING,LINE=2,SIZE=6,BORDER-RIGHT=ON,
$ 

to no avail.

There is only one piece of documentation regarding SSDEBUG, a Tech Support "Technique":

How to get Excel borders with PCHOLD FORMAT EXL2K

Release: 7.6.7 is specified so I'm not sure it behaves any differently for v7.6.5