Wiki Home

Cross Tab


Namespace: VB
I'm probably just being obtuse, but what is a Cross Tab?! I've never been able to figure out the difference between a Cross Tab and a simple relation...

Maybe I know: is a Cross Tab short for Cross Tabulation where you sum up all the occurrences of a list of values that occur in a separate list?

For example:

A Cross Tab would list the totals of the colors and sizes that occur in Table3. For example:
Color/SizeSmallMediumLarge
Red 5310
Green207
Blue 1779

Yes, that's a Cross Tab! There are at least 3 programs that are cross tabulation utilites for Foxpro / Visual FoxPro. Foxpro came with GenXTab.Prg. VFP comes with VFPXTab.Prg. Val Matison created Mat XTab and AlexanderGolovlev created Fast XTab. -- Mike Yearwood
I need to make a Paycheck Detail report. This is my first Cross Tab in a long time, so I figure I'll carefully document what is going on so that others can either correct me or learn.

Separate column for each cDeductionID, would like them in nSequence order (left to right). Not all checks have the same deduction.

data structure:
Person: kPerson_pk, cFirstName, cLastName
PayCheck: kPayCheck_pk, kPerson_fk, date, checkno
PayCheckDetail = kCheck_fk, kCheckDetailType_fk, yWages, yAmount
CheckDetailType: kCheckDetailType_pk. nSequence, cID.


Query:
SELECT Person.cPersonId, ;
		Person.cFirstName, ;
		Person.cLastName, ;
		Paycheck.dCheckDate, ;
		Paycheck.nCheckNo, ;
		CheckDetailType.cCheckDetailTypeID, ;
		Paycheckdetail.yWages, ;
		Paycheckdetail.yAmount;
	FROM dbo.PayCheckDetail ;
		JOIN dbo.PayCheck ;
			ON Paycheckdetail.kPayCheck_fk = Paycheck.kPayCheck_pk ;
		JOIN dbo.CheckDetailType ;
			ON Paycheckdetail.kCheckDetailType_fk = CheckDetailType.kCheckDetailType_pk ;
		JOIN dbo.Person  ;
			ON Paycheck.kPerson_fk = Person.kPerson_pk

It creates one result row for each detail record.

o_xtab1=NewObject("_xtab","f:\program files\microsoft [[Visual FoxPro]] 8\ffc\_utility.vcx")
o_xtab1.nrowfield = 5
o_xtab1.ncolfield = 6
o_xtab1.ndatafield = 8
o_xtab1.Runxtab()

Do (_genhtml) with Sys(2023)+"\xTab.html", "xTabQuery"
_ClipText = StrExtract( FileToStr(Sys(2023)+[\xTab.html]), [< body bgcolor="silver">], [] )
Ncheckno Cafuta Casdi Casui Cawh Medicare Net Socsec Uswh
1 0.0000 4.5500 0.0000 12.4200 6.6000 342.4700 28.2100 60.7500
2 0.0000 19.5000 0.0000 145.1800 28.2800 1161.3900 120.9000 474.7500
3 0.0000 2.0000 0.0000 2.1400 2.9000 158.0600 12.4000 22.5000

And now I am trying to figure out what to do next.

Here are my thoughts:

I need the first 5 fields from the query, not just the nCheckNo colum. nCheckNo still defines the group, or perhaps Paycheck.kPayCheck_pk would be better (never trust a Natural Key)

The 'generated' columns ( cafuta, casdi, etc) need to be sorted by CheckDetailType.nSequence

The frx should make column headers from CheckDetailType.cReportCaption

As long as the FRX is being used to display it, it can also be used to set up the column order, which seems like the right thing to do anyway.
Plan A: use VFP's .Prg, or one of the alternatives.
I have put this aside. I may come back to it if Plan B doesn't pan out.
Plan B: use VFE's cursor class, and generate a cross tab query.
* MODIFY CLASS v_PayrollDetailCursor of [libs\RPT_PAYROLLDETAIL.VCX] Method buildsql_pre
LPARAMETERS 	toSelectObject, toParameterValues

* Return the Cross Tab query

Local ;
	lcSQL, ;
	lcSQLAlias, ;
	loParameterValues, ;
	lnSQLResult, ;
	loSQLParser

Local ;
	lcRowKeyField, ;
	lcRowKeyValue, ;
	lcRowKeyExper, ;
	lcColumNameField, ;
	lcColumnDataField, ;
	lcFieldTableName, ;
	lcDataTableName, ;
	lcFieldOrder, ;
	lcRowFK

* Setup the parameters
lcLookupFK = "kCheckDetailType_fk"
lcLookupPK= "kCheckDetailType_pk"
lcLookupExpr = [''' + cast( ] + lcLookupPK + [ as char(36) ) + ''']
lcColumNameField = "cCheckDetailTypeID"
lcColumnDataField = "yAmount"
lcLookupTableName = "CheckDetailType"
lcDataTableName = "PayCheckDetail"
lcFieldOrder = "iSeq"

lcRowFK = "kPayCheck_fk"

* End of concrete parameters
********************************
* Begining of Abstract code

* Make a place to hang a list of new fields
AddProperty( This, "oXTabFields", Createobject("collection") )

lcPrimaryKeyField = CursorGetProp("KeyFieldList", .Alias)

With toSelectObject
	* Set things up for the xTabQuery Sub query
	.cTableList = .cTableList + ' Join ' + lcDataTableName ;
		+ " on " + lcPrimaryKeyField + " = " + lcRowFK

	lcWhere = Iif( Empty(.cWhere), "", "where " + .cWhere )

TEXT to lcSql noshow textmerge
SELECT
'sum( CASE when <> = <> then <> else 0 end )' as cColumnExp,
<> as cColumnName
FROM <>
where <> in (select <>.<> from <<.cTableList>> <> )
ORDER BY <>
ENDTEXT
* where <> in (select <> from <> <> )

	lcSQLAlias = "xTabQuery"
	loParameterValues = Createobject( "empty" )
	
	lnSQLResult = this.ExecuteSQL(lcSQL, lcSQLAlias, toParameterValues )

	If lnSQLResult > 0
		.cGroupBy = lcRowFK + ", " + .cfieldlist

		Select xTabQuery
		Scan
			lcColumnExp = Trim( cColumnExp )
			lcColumnName = "y" + Trim( cColumnName )
			.cfieldlist = .cfieldlist  + ", " +lcColumnExp + " as " + lcColumnName
			This.oXTabFields.Add( lcColumnName )
		Endscan

*		.cTableList = .cTableList + ' Join ' + lcDataTableName + " on " + lcPrimaryKeyField + " = " + lcRowFK
	Endif
Endwith

Return lcSQL

* MODIFY CLASS v_PayrollDetailCursor of [libs\RPT_PAYROLLDETAIL.VCX] Method loadcursor
LPARAMETERS 	tcAlias AS Character, 	tlZap AS Boolean, 	tlCleanBuffers AS Boolean

lcAlias = .alias

USE in (lcAlias)
USE dbf(tcAlias) Again In 0 Alias (lcAlias)
Select (lcAlias)

Return .t.

Woo! this is working pretty good. Here are the results from the above code:

The querys made in step 1:
SELECT 'sum( CASE when kCheckDetailType_fk = ''' + cast( kCheckDetailType_pk as char(36) ) + ''' then yAmount else 0 end ) as ' + rtrim(cCheckDetailTypeID) as cColumnName
FROM CheckDetailType
where kCheckDetailType_pk in (select kCheckDetailType_fk from PayCheckDetail )
ORDER BY iSeq


Which, when executed, results in:
cColumnName
sum( CASE when kCheckDetailType_fk = '2939397BE68C2743A9DCF4FD6D59E47C' then yAmount else 0 end ) as USWH
sum( CASE when kCheckDetailType_fk = '2DC4494B5A3356489FEE9439176F011A' then yAmount else 0 end ) as SocSec
sum( CASE when kCheckDetailType_fk = '2A0F4ED44FB4D749A9F52F5C531766CF' then yAmount else 0 end ) as Medicare
sum( CASE when kCheckDetailType_fk = '33D7EBED3C9A432F8F5A68E4629469A8' then yAmount else 0 end ) as CASUI
sum( CASE when kCheckDetailType_fk = '741D653DC58E4BA98F05D7A56ED7D4C2' then yAmount else 0 end ) as CAFUTA
sum( CASE when kCheckDetailType_fk = 'BD200A247E904BBFB46DF54C62F213D6' then yAmount else 0 end ) as CAWH
sum( CASE when kCheckDetailType_fk = 'AB0E93BEB1074DBEBDB426377113AFB4' then yAmount else 0 end ) as CASDI
sum( CASE when kCheckDetailType_fk = 'D1E7D5263E38B742A01CEAB14A5A54C5' then yAmount else 0 end ) as Net

Which is used to build the 2nd query:
select kPayCheck_fk,
sum( CASE when kCheckDetailType_fk = '2939397B-E68C-2743-A9DC-F4FD6D59E47C' then yAmount else 0 end ) as USWH,
sum( CASE when kCheckDetailType_fk = '2DC4494B-5A33-5648-9FEE-9439176F011A' then yAmount else 0 end ) as SocSec,
sum( CASE when kCheckDetailType_fk = '2A0F4ED4-4FB4-D749-A9F5-2F5C531766CF' then yAmount else 0 end ) as Medicare,
sum( CASE when kCheckDetailType_fk = '33D7EBED-3C9A-432F-8F5A-68E4629469A8' then yAmount else 0 end ) as CASUI,
sum( CASE when kCheckDetailType_fk = '741D653D-C58E-4BA9-8F05-D7A56ED7D4C2' then yAmount else 0 end ) as CAFUTA,
sum( CASE when kCheckDetailType_fk = 'BD200A24-7E90-4BBF-B46D-F54C62F213D6' then yAmount else 0 end ) as CAWH,
sum( CASE when kCheckDetailType_fk = 'AB0E93BE-B107-4DBE-BDB4-26377113AFB4' then yAmount else 0 end ) as CASDI,
sum( CASE when kCheckDetailType_fk = 'D1E7D526-3E38-B742-A01C-EAB14A5A54C5' then yAmount else 0 end ) as Net
 from PayCheckDetail
 group by kPayCheck_fk


Which marks the end of Step 1.

Step 2 is just executing that Query resulting in the cursor to be reported on:
Kpaycheck_fkUswhSocsecMedicareCasuiCafutaCawhCasdiNet
BE9CE77816A5406D8665468995AE655B 60.7500 28.2100 6.6000 0.0000 0.0000 12.4200 4.5500 342.4700
EB6A647E573B480A82CE542128A5AC30 474.7500 120.9000 28.2800 0.0000 0.0000 145.1800 19.5000 1161.3900
DCCFA6B6081640B095706486CBA91993 22.5000 12.4000 2.9000 0.0000 0.0000 2.1400 2.0000 158.0600

Hey, look at that: Same source data, same resulting cross tab. Good deal.

Now to figure out Rpt Engin.
Here is some 'sample code'
Create Database ct
Create Table Color ( ;
	kColor_pk int primary key, ;
	cID char(10), ;
	cDesc char(40) )
	
Create table Thing ( ;
	kThing_pk int primary key, ;
	kColor_fk int references Color, ;
	nAmt int )
	
Insert into Color VALUES ( 1, "R", "Red" )
Insert into Color VALUES ( 2, "B", "Blue" )
Insert into Color VALUES ( 3, "G", "Green" )
Insert into Color VALUES ( 4, "N", "Black" )
Insert into Color VALUES ( 5, "Y", "Yellow" )

Insert into Thing VALUES ( 1, 1, 3 )
Insert into Thing VALUES ( 2, 2, 3 )
Insert into Thing VALUES ( 3, 5, 3 )
Insert into Thing VALUES ( 4, 4, 3 )
Insert into Thing VALUES ( 5, 1, 3 )
Insert into Thing VALUES ( 6, 5, 3 )

Select Color.cID, Thing.nAmt ;
	from Thing join Color on kColor_pk = kColor_fk

Select [SUM(IIF(kColor_fk=] + Transform(kColor_pk) + [,nAmt,0))] as cColumnExp,;
	"Total" + Color.cID as cColumnName ;
	from Color

lcSql = "Select "
Scan
	lcColumnExp = Trim( cColumnExp )
	lcColumnName = Trim( cColumnName )
	lcSql = lcSql + lcColumnExp + " as " + lcColumnName + ","
EndScan

* Add a grand total field
lcSql = lcSql + [SUM(nAmt) as nLineTotal]

* Add the table the data is in
lcSql = lcSql + " from Thing"

? lcSql
&lcSql

Here is some code that might work on someone's data:
*Create Cursor foo ( cColumnExp char(10), cColumnName char(10) )
*Insert into foo values ("1", "ENE01" )
*Insert into foo values ("1", "FEB02" )
*Insert into foo values ("1", "MAR03" )

Select distinct "1" as cColumnExp,  cMonthy as cColumnName from estad

lcSql = ""
Scan
	lcColumnExp = Trim( cColumnExp )
	lcColumnName = Trim( cColumnName )
	lcSql = lcSql + Iif( Empty(lcSql), "Select ", lcSql + ", " ) + lcColumnExp + " as " + lcColumnName
Endscan

lcSql = lcSql + " from estad"
? lcSql
&lcSql

I found myself having to present to the users an /updateable/ calendar-like display of data from a normalized cursor where 1 day = 1 record. A question on Universal Thread indicated that a crosstab was the way to go. I looked into VFPXTAB; it was overkill in some areas, insufficient in others.

I selected into a dummy cursor the primary key, week, day of week, and data item. Some simple math told me the size of the array I needed to store the the primary key and data, which was filled based on week and day of week. I then appended the array into the cursor that was controlsource for the grid displaying the data. The gridtextbox.valid then updated the actual cursor.

Later, I more-or-less repeated the process to create a report cursor listing all those records on one line.
Chris Foster
http://www.devshed.com/c/a/MySQL/MySQL-wizardry/ A journey of Cross Tab discovery.
See Also: Fast XTab Mat XTab
Category Data Category Definitions
( Topic last updated: 2005.06.07 05:22:20 PM )