Genxtab.prg is a utility program which you can find in your Foxpro directory. Genxtab will generate a cross-tabulation from a table containing at least three fields corresponding to the following data :
the row field : row identifiers the column field : column headings the cell field : cell value corresponding to the row/column intersection
In our example, a first approach would be using employee id as row identifier, and time as column heading. If you look at the final display of the timetable, you will notice that more information has been included, as Name, First Name and initials to identify employees, that additional lines have been included to identify the different dates in the timetable, and that column headings have been simplified to indicate the hours only.
Additionally, some time slots may exist in the data file, without any employee allocated. In that case, we want to replace the employee name with a series of question marks.
A last thing to notice is that Genxtab will only work with numeric cell values and caracter type row identifiers and column headings.
Preparing the data for Genxtab
Two groups of data will be needed, the first one corresponding to the ‘employee’ lines, the second to the ‘day of the week’ lines. These two groups will be built using SELECT SQL and UNION :
before, we have to determine the starting date of the timetable :
CALCULATE MIN(ha_date) TO ld_start
SELECT DISTINCT empl_id, ; PROPER(ALLTRIM(nom))+' '+PROPER(ALLTRIM(prenom))+' ['+init+']' AS empl ; FROM employee INTO ARRAY e_id ; ORDER BY nom ASCENDING
IF _tally = 0 DIMENSION e_id(1,2) e_id(1,1) = SPACE(6) e_id(1,2) = SPACE(10) ENDIF
Now we can proceed with the main SELECT-SQL :
SELECT STR(ha_date-ld_start+1,1)+IIF(EMPTY(h_start+m_start),' ',; IIF(EMPTY(hempl_id), ; '000000' + STR(ha_seq,3),hempl_id+' '))+SPACE(55) AS ha_d, ; PADL(ALLTRIM(STR(h_start*100+m_start,4)),4,'0') AS hm, ; 01 AS npers, ha_date FROM horattr HAVING &lc_filter ; UNION ; SELECT STR(ha_date-ld_start+1,1)+SPACE(64) AS ha_d, '9999' AS hm, ; 01 AS npers, ha_date FROM horattr ; GROUP BY ha_d ORDER BY ha_d ; INTO TABLE Temp1
You can see now that Genxtab can be applied to this table with Row identifiers in Ha_d, column headers in Hm and cell values in Npers. Further processing will be needed to get row and column headings meaningful to the user.We will also need the date of each of the days of the timetable, so let’s save these into an array for later use
SELECT Temp1 SELECT LEFT(ha_d,1) AS hdow, ha_date FROM dm011200 ; GROUP BY hdow ORDER BY hdow INTO ARRAY la_ddate
DO genxtab2 WITH 'Temp2',.f.,.f.,.t.,1,2,3,.F.
1. Output table name 2. Cursor only output 3. Close input table 4. Show thermometer bar 5. Field position for crosstab rows 6. Field position for crosstab columns 7. Field position for crosstab cells 8. Create a total field
Here is what we find after applying Genxtab to Temp1:
You can see our timetable taking shape now, but with meaningless column headers and row descriptions. Notice also the column headers generated by Genxtab from the contents of the column headers field, hm.
Crosstab data manipulation
The data in the first column has to be transformed in meaningful line descriptions corresponding to one day of the timetable or to one employee.
The day line description includes the day of the week and the date and corresponds to ha_d containing a single digit in the first position followed by six blanks. The function FDOW() returns a string like "Wednesday 29.11.95" and takes the date as parameter.
The employee line description contains Name, First Name and Initials, which we extract from the e_id arrray we prepared earlier. Rows where no employee has been assigned (employee ID = ‘000000’) have a series of question marks "??????" as line description.
Here is the transformation of the first column :
REPLACE ALL ha_d WITH ; IIF(SUBSTR(ha_d,2,6)==SPACE(6), ; fdow(ld_start+VAL(ha_d)-1), ; IIF( SUBSTR(ha_d,2,6)='000000','??????', ; e_id(CEILING(ASCAN(e_id,SUBSTR(ha_d,2,6))/2),2) ) )
At the same time, we will also change field names from C_0830 to something more meaningful to the user. Be careful, though. Modifying the structure of a table containing data may rresult in the loss of your data. FoxPro creates a new table and appends data from the old table to the new table. Fields with a new name have no equivalent in the old table and will remain empty after the modification.
Our first step will then consist in saving our data from Temp2 table to an array :.
COPY TO ARRAY la_brw
FOR ln_loop = 1 TO ALEN(la_brw) IF TYPE('la_brw(ln_loop)')='N' DO CASE CASE la_brw(ln_loop) = 0 la_brw(ln_loop) = ' ' CASE la_brw(ln_loop) > 0 la_brw(ln_loop) = 'XX' ENDCASE ENDIF ENDFOR
COPY STRUCTURE EXTENDED TO Temp3 USE Temp3 in 0 SELECT Temp3
Here we have a direct access to the structure of our data table and we can modify field types, lengths, or names programmatically.
REPLACE ALL field_type WITH 'C' FOR field_name='C_' REPLACE ALL field_name WITH 'C' + RIGHT(STR(100+RECNO()-1,3),2) ; FOR field_name = 'C_'
While we have access to the table structure, we can prepare column headers for our BROWSE command.
Columns corresponding to the hours have a two-digit "hours" description while the remaining columns, which correspond to the half hours, have a blank description.
COPY TO ARRAY la_colname FIELDS field_name *-- number of data columns ln_brcol = ALEN(la_colname) - 2 DIMENSION la_header(ln_brcol), la_colvar(ln_brcol) *-- generate headers FOR ln_loop = 1 TO ln_brcol lc_min = SUBSTR(la_colname(ln_loop+1),5,2) lc_hr = SUBSTR(la_colname(ln_loop+1),3,2) la_header(ln_loop) = IIF(lc_min='00',lc_hr,' ') ENDFOR
CREATE Temp4 FROM Temp3 SELECT Temp4 APPEND FROM ARRAY la_brw
Almost there now, except for column headers.
Preparing the BROWSE command
Our BROWSE window contains one fixed column displaying line descriptions and a variable number of data columns with variable headers as the timetable can cover any time range within the 24 hours of a day.
We are going to prepare the lc_brfld string, the variable part of the FIELDS clause of the BROWSE command. The FIELD clause for a C_0900 column would look like this :
C_0900 :H="09",
lc_brfld = "" FOR ln_loop = 1 TO ln_brcol lc_colhead = 'C' + RIGHT(STR(100+ln_loop,3),2) la_colvar(ln_loop) = lc_colhead lc_brfld = lc_brfld + lc_colhead + " :H=la_header(" + ; STR(ln_loop,2) + ")," ENDFOR
lc_brfld = ALLTRIM(lc_brfld) lc_brfld = LEFT(lc_brfld, LEN(lc_brfld) - 1 )
lc_lastcol = 'C' + RIGHT(STR(100+ln_brcol+1,3),2) && last column SCAN FOR &lc_lastcol = 'XX' FOR ln_loop = 0 TO ln_brcol lc_thiscol = 'C' + RIGHT(STR(100+ln_loop+1,3),2) REPLACE &lc_thiscol WITH '--' ENDFOR ENDSCAN
DEFINE WINDOW lw_br from 1,0 TO 24,79 ; TITLE "HORAIRE (ATTRIBUTIONS)" SYSTEM CLOSE FLOAT COLOR SCHEME 10 BROWSE FIELDS ha_d :7 :H=' ', &lc_brfld ; NOMODIFY NOAPPEND NODELETE NOMENU WINDOW lw_br