Unit Cwxtab

Designer: Craig Ward, 100554.2072@compuserve.com H Van Tasell, 76602.1123@compuserve.com Date: 9/5/96 Version: 1.32 Function: The TcwXTab component is a component, descended from TStringGrid, which allows cross-tabulations of a table's data to be built. XTab's: A crosstab is the representation of de-normalised table-data. Typically you'd have a summary by the unique values contained in a row field and a column field. In my component, you can have multiple results in a cell (a max of 3), each performing a discrete mathematical operation, and displaying this data in the format that you specify. Woopee! Calling: The method Execute will execute the crosstab. The method SaveXTab will save the crosstab as a Paradox table. Update: The following amendments have been made: [0] resolution for bug in reading negative currency values. [1] the OnDrawCell routine now ignores row\column headings since I felt it looked aesthetically unattractive to have text with hard-returns in the headings... [2] the crosstab can now have summary fields for both rows and columns (this is the AggRowCols property). Note that the summary cells used a maroon font, and in the case of using the SaveXTab procedure, they are not saved to the result table (since that would *seriously* violate normalisation rules). [3] the crosstab now has an additional property which allows the cells to resize to the maximum text displayed (this is the AutoResize property). In this case, the component will make row-heights big enough to fit the lines in a cell, whilst the column-widths will be made big enough to fit the maximum size of text in each respective column heading. [4] the crosstab now uses pointers to the custom types used in the PopXTab routine. [5] a new property, "EmptyCellChar", allows the developer to choose between blanks or zeros for empty cells. The main update was to implement summary rows and columns. To achieve this I had to *modularise* the code, so that the drawing to cells (and reading from) was done by discrete functions (which is what they should have been in the first place, but as I've said, this component is well and truly hacked). A problem that I had with implementing the auto-resize property was executing the routine at the correct time in the component's life. In particular, the grid is populated before it's drawn, so the auto-resize property (btw, that's implemented through the UpdateXTab procedure) had to come after the grid had finished drawing itself. I couldn't think of any other way around this then to create a bool, that's true on creation, and once the first DrawCell event has executed, is set to false (obviously, when the bool is true, call the UpdateXTab routine). Though this works okay, I find it somewhat brutish, and the flickering is a bit annoying...any ideas? Bugs: The following undocumented features are known: [1] Though the editor will allow you to select calculated fields, the crosstab will actually run into GPF if you try to run it where either the row or column fields are calculated. [2] The auto-resize function works on the text in column headings, and not on the text in cells. Therefore, a column could be resized so that the heading fits, but the value in the column's cells is not properly visible. Hackers: This component is severely hacked. It took me ages to develop, mainly due to the difficulty in getting the custom property editor to work (a job in itself). Therefore, the code is not quite optimal, and it certainly isn't going to win any prizes, but it might help in some way, least of all the fact that you now have a working Crosstab in your VCL. My main gripe would be the custom object that stores the XTab data (see the type declaration for TXObject). The fields should really have been stored in a custom record (ie: FieldName, MathOp, Format) as opposed to duplicating the object's fields - check out the custom type defined in the PopXTab procedure (that is what really should be stored in TXObject, though that in itself would probably require another custom property editor.....!). VCL: IMHO, this component is okay. I've seen better (one that springs to mind is the TCrosstab component by Kevin Liu - INTERNET:kliu@oodb.syscom.com.tw - which is particularly impressive in that you can set how many columns and rows to summarise by). However, I don't know of any crosstab components where they are free, and the source is available at no charge. Thanks: Thanks goto the following: [1] Dennis Passmore (71640.2464@compuserve.com) for help in drawing multi-line text in a cell [2] Greg Tresdell (74131.2175@compuserve.com) for help in writing custom property editors. Check out his "Gray Paper" for more information... [3] The following for help in getting text metrics: Pat Ritchey (700007.4660@compuserve.com) Julian Bucknall (72662.1324@compuserve.com) Harley L Pebley (103330.2334@compuserve.com) [4] Harry Van Tasell for taking time to add improvements to the component [5] Fox TV for "The X-Files"

Classes

TcwXTab - the declaration for the crosstab component
TXObject - custom type - this is the object that the property editor is editing

Functions

Register - the property editor dialog} {****VCL preferences************************************************************} {register

Types

parrCellData
parrFloat
parrSummaryField
TarrCellData
TarrFloat
TarrSummaryField
TCellData
TEmptyChar
TMathOp
TResultFormat
TSummaryField

Constants

iSizeOfArray

Variables

arrFields
iInc


Functions


procedure Register;

the property editor dialog} {****VCL preferences************************************************************} {register

Types


parrCellData = ^TarrCellData

parrFloat = ^TarrFloat

parrSummaryField = ^TarrSummaryField

TarrCellData = array[0..iSizeOfArray] of TCellData;
custom type - array of the previous custom type
TarrFloat = array[0..iSizeOfArray] of extended;
custom type - returns cell contents
TarrSummaryField = array[0..iSizeOfArray] of TSummaryField;
custom type - array of the previous custom type
TCellData = record
Result : extended;
Min : extended;
Max : extended;
Count : integer;
Avg : extended;
Sum : extended;
end;
custom type - record which stores data for the current cell
TEmptyChar = (ecBlank,ecZero);
custom type - empty character
TMathOp = (sum, avg, min, max, count);
custom type - possible math operations to be carried out
TResultFormat = (currency_format, integer_format, real_format);
custom type - possible display options for results
TSummaryField = record
FieldName : string;
MathOp : TMathOp;
Format : TResultFormat;
end;
custom type - record which stores the data in the TXObject

Constants

iSizeOfArray = 2

constants for array sizes

Variables

arrFields : parrSummaryField

variables

iInc : integer

stores details for summary fields