How to sort the state rows within a region by the first occurrence of rainfall as opposed to by state name??
I'm writing in C# using VS2008 unpatched and Infragistics 9.1.20091.2056.
Let's pretend I'm using a WinGrid to display rainfall amounts for states within regions over a changing date range.
The regions - such as Western, Northwest, Eastern, Southeast, etc. - are the categories in the Group By. A hidden category column allows me to group by category while still displaying a "header" row for each region in the grid.
Sorting is turned off for all columns other than the hidden category column which means Category is the only sort button in the sorting band across the top, no additional columns can be dragged there and all additional sorting needs to be handled programmatically.
The states - such as California, Nevada, etc. - belong to a region and are categorized under their region based on the region they are attached to when the rows are added to the grid.
The columns - other than the column containing the region heading and state names - are dates and appear in the grid based on there having been rainfall during the specified date range. Sorting by the user is not allowed.
If a date column appears then it indicates that at least one state in one region has a rainfall value for that date.
My confusion:
How to sort the state names within a region by the first occurrence of rainfall as opposed to by state name??
For example, if California had rain on 10/20/2009 and Nevada had rain on 10/19/2009 then Nevada should appear before California in its region group. How to accomplish this?
BTW I've been reading the posts regarding sorting WinGrids closely and repeatedly but am still confused so please give it to me simply and with examples if possible. :-)
Thanks.
Allen
Okay, I was thinking that you would apply a comparer to the Region column and have the comparer also handle the sorting. But it looks like you took a different approach here and simply applied a SortComparer to the State column. That's fine... the grid will be grouped by region and sorting by state will ignore the actual name of the state and sort by rainfall, which is just what you wanted.
allenovereem said:Now if I can just figure out how I added the stickpins
Not sure I follow you there. You seem to already have pushpins showing up for the columns. Do you mean you want them on rows? I don't think the grid supports this for GroupByRows, only for normal data rows. But in order to do that, you have to set the FixedRowUIType, I think. And you also need to turn on the RowSelectors.
allenovereem said:ability to modify column order and disable that
This is done via the AllowColMoving property.
allenovereem said:AND if I can figure out how to sort the regions
If you want to sort the regions by some order other than alphabetical, then you could use a SortComparer on the Region column. That was my original thinking here. You could use a second comparer for this, but really if you use the comparer you have here on the Region column, I think it will work out better than on the State column.
Figured it out and verified that your solution does in fact work.
I had to add the state column to the sortable columns (and set groupby to false for it) and had to change to sort on that column instead of region in the InitializeLayout event like so:
this.ultraGrid1.DisplayLayout.Bands[0].SortedColumns.Add("State", false, false); UltraGridColumn col = ultraGrid1.DisplayLayout.Bands[0].Columns["State"]; col.SortComparer = new GridCellComparer();
I also had to modify the compare method I had pasted above to look like this:
public int Compare(object x, object y) { UltraGridCell xCell = (UltraGridCell)x; UltraGridCell yCell = (UltraGridCell)y; //compare the value of the group by column and return comparison of those if they differ if (xCell.Row.Cells[1].Value.ToString() != yCell.Row.Cells[1].Value.ToString()) { return xCell.Row.Cells[1].Value.ToString().CompareTo(yCell.Row.Cells[1].Value.ToString()); } //find the index of the first column that has a non-null value for x int indexX = 0; foreach (UltraGridCell cell in xCell.Row.Cells) { if (cell.Column.Index > 1) { if (!(cell.Value is DBNull || cell.Value == null)) { indexX = cell.Column.Index; break; } } } //find the index of the first column that has a non-null value for y int indexY = 0; foreach (UltraGridCell cell in yCell.Row.Cells) { if (cell.Column.Index > 1) { if (!(cell.Value is DBNull || cell.Value == null)) { indexY = cell.Column.Index; break; } } } //if the indexes are not same then return value of their comparison if (indexX != indexY) { return indexX.CompareTo(indexY); } else { //else return comparison of the indexes of the rows themselves return xCell.Row.Index.CompareTo(yCell.Row.Index); } }
...and the output which is obtained from data intentionally sorted in alphabetical order looks like this:
Now if I can just figure out how I added the stickpins and ability to modify column order and disable that AND if I can figure out how to sort the regions I'll be happy.
Thanks Mike!
I made the assumption I need to attach to the region column as a starting point so i added the following to the end of the InitializeLayout event of the grid:
UltraGridColumn regionCol = ultraGrid1.DisplayLayout.Bands[0].Columns["Region"];regionCol.SortComparer = new GridCellComparer();
I also added my compare class as below with the end result that every row has its own region.
Did I err when I connected the compare to the Region column or did I err in my implementation of IComparer?
I know it works to group the regions appropriately if I leave the region comparison in place and then return 0 in cases where the regions are not the same.
private class GridCellComparer : IComparer { public GridCellComparer() { } public int Compare(object x, object y) { UltraGridCell xCell = (UltraGridCell)x; UltraGridCell yCell = (UltraGridCell)y; if (xCell.Value.ToString() != yCell.Value.ToString()) return xCell.Value.ToString().CompareTo(yCell.Value.ToString()); int indexX = 0; foreach (UltraGridCell cell in xCell.Row.Cells) { if (cell.Column.Index > 0) { if (!(cell.Value is DBNull || cell.Value == null)) { indexX = cell.Column.Index; break; } } } int indexY = 0; foreach (UltraGridCell cell in yCell.Row.Cells) { if (cell.Column.Index > 0) { if (!(cell.Value is DBNull || cell.Value == null)) { indexY = cell.Column.Index; break; } } } if (indexX != indexY) return indexX.CompareTo(indexY); else return xCell.Row.Index.CompareTo(yCell.Row.Index); //MessageBox.Show("xCell is" + xCell.Value.ToString()); //MessageBox.Show("yCell is" + yCell.Value.ToString()); }
Hi Mike, I've finally gotten the chance and am spending some more time on this.
I've created a test rainfall application to experiment with this. I am unclear how / what to attach the compare routine to. Would seem I need to attach to the rows but not sure where / how to do that.
Haven't written the custom comparer logic yet either but I'll do that when I'm actually able to pass cell objects to it.
Hi Allen,
Oh, okay, that makes things even easier.
So your comparer would work like so:
First, you compare the regions. If the regions are different, then you are done and you can return a value.
If the regions are the same, you examine the cells in the first row and look for the first date field that has a value that is not DBNull.
Then you do the same thing in the second row - find the first populated rainfall cell that has an actual value.
Then you just compare the date you got from the first row to the date you got from the second row.
If they are different, you return. If not, you compare the Index of the rows and return that.