MatrixInputGrid - A pivot-edit grid

• Download source code - 98.8 KB

Introduction

This article is mostly a draft of a control which can handle specific database operation/presentation scenarios via a DataGridView. However, it works. I hope you find it useful when working with rather complex data. This is nothing but a DataGridView which can work in two general modes/ways:

1. as a simple pivot-table-grid, read-only mode;
2. as a matrix-grid, semi-editable mode.

A pivot table case was discussed a few times on CodeProject, as far as I remember. Pivot-table mode is just a by-product here. The aim of this article is to introduce a way to allow input of data when a DataGridView is showing cells in the pivot table order. I think the best way to explain what I mean is to visualize it:

Normally, the right side of the picture (showing the usual DataGridView) is not displayed to the end-user. What the end-user really deals with is the left side - the simple pivoted grid.

The idea is to pivot the DataGridView table by one column (PivotCol) while keeping the data still editable. This means to display data (DataCol values) in columns generated by PivotCol values, and be able to reflect changes made to data (DataCol values) back into the source.

MatrixInputGrid has four types of columns to be declared:

1. KeyMemberColumns (multi) - is a set of columns you want to become distinct; no matter how many rows with same KeyMemberColumns' values, give only one row in the Matrix; this is similar to the GROUP BY clause in SQL; the whole key is read-only;
2. PivotColumn (one) - is a column in which values will be used to generate PivotedColumns in the matrix-result;
3. DataColumn (one) - contains data which is populated into the matrix, and can accept changes in editable mode;
4. (Optional) AggregatedColumns (multi) - additional column set for displaying totals of some kind in read-only-pivot mode.

When in editable mode, the user can change the data values. The user can also press [Insert] to add a pivoted-column and then input data into it - new rows will be added into the source table. At this moment, there is no ability to add new keys into the matrix and reflect this to the source - but this seems possible.

Benefits

I can think of three general benefits of using this control:

1. decrease the number of rows displayed to the user,
2. assure data consistency needed in some scenarios,
3. clear view over a great amount of data.

The first one is quite common: the number of rows displayed is divided by two, three, or more, according to the scenario - by adding only a few columns. I found it very important, because I heard many complaints when users had to deal with a thousand or more rows at once. Mistakes happen, and some users find it difficult to find what they are looking for in the grid. Applying a filter seems not to be the right solution in some cases.

The second benefit I can think of is very specialized. By data consistency in this context, I mean to have exactly one DataCol value for:

- one key (Of KeyMember1, KeyMember2, KeyMember3, ...)

and

- one PivotCol value

This is a need, when one wants to display non-aggregated data (DataCol values). When this rule is broken, the control would have to show two (or more) different values in one cell. This is not only impossible in a standard DataGridView, but might also be unwanted in many database solutions. For further clarification, see the demo, where the [Sales planning example] can be compared to the [Data consistency error example]. I hope it is clear that one product cannot have two different prices for the same quantity range - unless some more dimensions get into play, or data values are aggregated some way like SUM, COUNT, MAX, etc.

The last but not least is, this grid can present/edit data in a compact way. This seems most special in 'DIST' modes (see: Usage Modes), where it can handle strings. It shows groups of data at a glance. It can sometimes give the ability to notice relationships between data, which would be difficult to see in a generic table format, and cannot be seen in a generic spreadsheet pivot table, since it cannot show straight distinct values but 'must' perform a function on them.

Background

MatrixInputGrid inherits from System.Windows.Forms.DataGridView. In design mode, it is just a standard DataGridView, which can be bound like generic.

During runtime, another DataGridView (unbound) is added to the ParentForm (called VisibleMatrix in the code), and drawn over the source DataGridView by overriding OnPaint and OnResize. The code takes care of - let's say - translation from one grid to another. I took some time to add comments explaining what the code does.

I tried to keep it as simple as I could, and this is the main reason I decided to do 'the two grids'. Although, I had to deal with some DataGridView specific issues to make it work, I agree the coding could use some (or more) improvements and further development. Feel free and welcome to do it! But please don't forget to share it on CodeProject, CodePlex, or anywhere.

Using the code

To bring this control into your project:

1. Pick the source project called InputMatrix from the demo solution
2. Attach it to your solution
3. Rebuild the whole solution - the MatrixInputGridX should appear in the Toolbox
4. Drag MatrixInputGridX onto your form

To start using this control:

1. Bind it to any datasource you want, like a generic DataGridView
2. Add columns, set their headers, etc.
3. Set the initial parameters in the form's Load event handler

The required initial parameters are (in the form's Load event handler):

• A form the control is on:

Me.MatrixInputGridX1.BaseForm = Me

• Determine the control behavior (enum):

Me.MatrixInputGridX1.UsageMode = 

• Declare each key column (string) like this:

Me.MatrixInputGridX1.PL_KeyColumns.Add("YourKeyColumnName1")

• The string name of a column meant to be the pivot:

Me.MatrixInputGridX1.PL_PivotColName = "YourColPivot"

• The string name of a column containing values:

Me.MatrixInputGridX1.PL_ValuesColName = "YourValCol"

There is also an optional parameter (in the fForm's Load event handler):

You can declare multiple aggregated columns, which are used in read-only (pivot) modes. These columns are intent to be totals of some kind. Now the only option is SUM, but you are free to implement more functions or can request me to do so. Declare it like this:

Me.MatrixInputGridX1.PL_AggColumns.Add("YourAggColName1", SUM)

Usage Modes

There are four runtime modes at the moment, which define how the control manages the DataCol values. These are:

1. PivotTableSUM - read-only pivot table mode; performs the SUM function over DataCol like a generic spreadsheet pivot (cross) table; data values are aggregated so no consistency error would appear; numeric data needed;
2. PivotTableCOUNT - like point 1., the COUNT of values matching Key and PivotCol values; any data type;
3. PivotTableDIST - read-only variant of matrix, where DataCol values are not aggregated; data consistency check is performed;
4. InputMatrixDIST - editable mode, the main mode of this control.

Remarks

1. Do not use Fill in the Form.Load event handler - use a button to load the data instead, because most initialization is done in BaseForm.Shown
2. Data commit is done as usual for a bound DataGridView
3. The control visualizes dirty cells (cells waiting for commit into the data source) - call PL_SetNotDirty (sub) after the data is committed
4. You can check the PL_HasDirtyRows property to indicate if there are cells waiting for save - e.g., in the FormClosing event handler to prevent accidental data loss
5. Do not use PL_AggColumns (optional) in editable mode (InputMatrixDIST mode)

Final notes

I started this control (I hope it's just a start) because I couldn't find any control to handle these specific data scenarios over the net. Thus, I believe this matrix control is exclusive in some way. Second, even more important thing, I took a chance to do a give back to the community. I learned a lot here at CodeProject over the last year, since I started my programming adventure.

Most recent ToDo off the top of my head:

1. Key adding support - press [Ins] on KeyMemberColumn and show a dialog letting to specify and add a whole new key to the table;
2. Show #ERR rows - press or double click on a cell showing #ERR (consistency error) and visualize rows that cause this error by showing a simple generic grid;
3. Show grouped columns, multi-pivot - e.g., planning prices for periods (like years), having all quantities grouped in a period (year) and repeated for each;
4. Show multiple rows in one cell mode - nest a new DataGridView in a VisibleMatrix's cell when inconsistency met, do not show #ERR in this mode.

If you like this control and/or have any comments or improvements on your mind, please do not hesitate to post. All are welcome.

This code is provided 'as is' and the author does not take any responsibility of any result made by using this code in any sort of way. If you use this control in your project, please preserve copyright by sending an e-mail to me (saying you are going to use my control).

Happy coding!

History

• 07 Nov. 2007 - Initial submission.

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)