Hi,<br>I read Fernand's question aout Dynamic columns and decided it was time to write, once again, to the list about the power of coupling Excel Pivot tables to PH Quiz.<br>Let Quiz churn out a CSV-style list of the data, then FTP the CSV file over to a PC with Excel and let Excel take care of getting the data into a table.
<br><br>In French, "Pivot Tables" are called "Dynamic CrossTables".<br>The "pivot" notion refers to the fact that you can rearrange the data display easily using the mouse but doesn't get across the "dynamic" idea, which is a pity.
<br>Pivot tables are "dynamic" in three senses : <br>1. They can re-read, or "update themselves" from a remote data source a regular intervals<br>2. They will add (or subtract) rows and columns according to the data they find in the source file.
<br>3. They do all sorts of calculations on the data found in the file.<br><br>Cognos had its own product that did this. I'm sorry I can't remember the name of it, but we had Excel bundled on our PCs and it handles about 99% of our needs.
<br><br>Here is an example of how to get Quiz to prepre the data.<br>There are howtos fot Pivot Tables out there. <br><br>!JOB LOFTY,MGR.ACCOUNT etc<br><br>!QUIZ<br><br>acc *lloft2 link viaindex Clientnum to Client<br><br>
def oper char *2 = Clientori[1:2]<br><br>def fich char *2 = Clientori[3:2]<br><br>def mess char *2 = Clientori[5:2]<br><br> <br><br> <br><br>def header char * 100 = &,<br><br>"ClientID;DateCreation;Origin;Mailing;Payments;Amounts;Oper;Fich;Mess;X"
<br><br> <br><br>:purge lloft3<br><br>set rep dev disc name lloft3<br><br>def packet char * 100 = &,<br><br>pack ( asc(ClientID) + ";" + &,<br><br> asc(Clientdate,8)[7:2] + "/" + &,
<br><br> asc(Clientdate,8)[5:2] + "/" + &,<br><br> asc(Clientdate,8)[1:4] + ";" + &,<br><br> "'" + Clientori + ";" + &,
<br><br> asc(mailctr) + ";" + &,<br><br> asc(venctr) + ";" + &,<br><br> asc(Amount2,13)[ 1:11] + "." + &,<br><br>
asc(Amount2,13)[12:2 ] + ";" + &,<br><br> oper + ";" + &,<br><br> fich + ";" + &,<br><br> mess + ";" + &,
<br><br> "X" )<br><br> <br><br>set nohead<br><br>set rep spa 0<br><br>set page len 0 wid 200<br><br>init head header<br><br>rep packet<br><br>go<br><br> <br><br>:ftp<br><br>open <a href="http://192.168.0.33">
192.168.0.33</a><br><br>user username<br><br>pass password<br><br>put lloft3 lloft3.csv<br><br>quit<br><br>e<br><br>!EOJ<br><br> <br><br>:print lloft3<br><br>ClientID;DateCreation;Origin;Mailing;Payments;Amounts;Oper;Fich;Mess;X
<br><br>1;08/11/1989;'AKAXAI;1;0;00000000000.00;AK;AX;AI;X<br><br>45;03/09/1984;'000100;1;0;00000000000.00;00;01;00;X<br><br>47;31/08/1984;'000100;1;0;00000000000.00;00;01;00;X<br><br>58;31/08/1984;'000100;1;0;00000000000.00;00;01;00;X
<br><br>73;10/01/1986;'000000;1;1;00000000100.00;00;00;00;X<br><br>76;03/09/1984;'ATCDEK;1;1;00000000100.00;AT;CD;EK;X<br><br>82;19/08/1984;'000100;1;0;00000000000.00;00;01;00;X<br><br>86;03/09/1984;'000100;1;0;00000000000.00;00;01;00;X
<br><br> <br><br>The basic idea is to get the CSV data into an Excel Pivot Table.<br><br>Pivot Tables can handle up to 256 columns. Dynamically.<br><br>The table will adapt the number of columns according to the data in the CSV file.
<br><br>If the number of columns is greater than 256 you can group the columns on criteria.<br><br>For example, Date columns can be grouped by Year, Semester, Quarter, Month, Week etc.<br><br> <br><br>(And the new version (Office
v.12, still in beta) can handle an unlimited number of columns!)<br><br> <br><br>Notes:<br><br>--------------<br><br>My example shows <br><br>a number – Client ID<br><br>a Date - DateCreation<br><br>a field where the data may be alpha or numerical so that's why the apostrophe is added on to the left of the data. – Origin,
<br><br>because Excel will trim off any leading zeroes in display. The Apostrophe keeps them in, not very elegant, but it works.<br><br>A money field with two figures after the decimal point. – Payments.<br><br>And the Origin field split up into its three components. Oper, Fich, Mess.
<br><br>The X field is just a way of getting rid of any trailing spaces.<br><br> <br><br>And if the Pivot table seems a tricky weird way of doing things, well it is ! <br>I agree up to a point. If you have never tried it.
<br><br>Once you have the hang of it, it is mind-blowingly powerful. Well worth the effort.<br>If you have mastered QUIZ the you can master Pivot Tables.<br><br> <br><br>The nec plus ultra is to open the CSV file as External data and to save your Excel file as an Empty Template. "Empty" meaning without the data.
<br><br>File, Save As, Template, Empty? Reply "Yes" and Excel will reload the data when you reopen the template. So you get an auto update every tim the file is opened.<br>(Couple this to the fact that you can get the CSV file generated in a JOB that runs itself at regular intevals... so that the data file is regenerated every so often and the JOB can handle the FTP transfer too. )
<br><br>If the data has changed in the meantime, Excel will automatically recalculate summaries, averages etc. and add in or take out columns and lines depending on the new data. <br><br>Another thing is that the tables can be printed only when needed. Authorized users can access the Excel files across the network.
<br><br> <br><br>HTH<br><br> <br><br>Wayne Dunlop<br><br> <br><br>