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
Hi Allen,
In order to group the row, the grid must first sort the rows by region. This is neccessary and you can't change that, because the grid needs to sort the rows by region in order to loop through and group rows with the same values.
But rows that have the same region are placed in no particular order or in the original order in which they appeared in the grid.
One easy way to handle this would be to enable sorting on the rainfail column and sort it. But, you obviously don't want to do that.
So what you need to do is set it up so that the Region columns sorts by both the region (first) and the rainfall (second). As long as the Region sorting takes precedence, this won't interfere with the grouping. So all you have to do is use a SortComparer on the Region column that sorts based on two columns instead of one.
I don't know as we are on the same page Mike.
Yes it would be nice to sort the regions too by geographical area instead of by name but I'm assuming I can't do that in a group by situation.
There are a variable number of rows depending on which states had rainfall in the variable date range. There will be a row for any state that has a date in the range being selected for. Likewise the columns will change based on how many dates have states with rainfall within the range being selected for.
There is not one column that will have the rainfall. For example, if one state has rain on 1/1 and one state has rain on 1/2 and one state has rain on 1/3 then there will be three columns (one for each date) and three rows (one for each state) and I want to sort the rows on 1/1 followed by 1/2 followed by 1/3 regardless of state name (but within region of course).
Are we on the same page? How am I going to do that with one SortComparer sorting based on Region and only one date field?
I do realize I can sort before populating the grid. I'm interested in knowing how to manipulate the grid in code as if I were moving the rows around by hand.
Perhaps it is not possible in code? Perhaps I need to create a sample program to better describe my issue? In real life I'm working with patient data, not rainfall amounts.
Thank you,
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.
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()); }
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!
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.
Hi Mike,
I meant pushpins like you said and I was wondering how I'd added them simply so I could remove them. Not finding any AllowColMoving property on an extensive search thru the properties of a column. What is that a property of or is it perhaps some other name?
Perhaps you could mark up my comparer method a bit to indicate how I would sort by region without losing control of the state sorting by first rainfall and still gain control of the sorting of the region column (which is also the groupby column).
Oops, sorry about that, I mixed up the name. Glad you found it. :)
I think I got rid of the pushpins cleanly by adding this:
e.Layout.Override.FixedHeaderIndicator = FixedHeaderIndicator.None;
I found the AllowColMoving property where you indicated...I added the following to the ultraGrid1_InitializeLayout event:
e.Layout.Override.AllowColMoving = AllowColMoving.NotAllowed;
I am having some trouble finding FixedHeaderUIType on the Override of the band or the layout. How do I assign this so that the pushpins are gone?
Thanks,
allenovereem said:I meant pushpins like you said and I was wondering how I'd added them simply so I could remove them.
Ah, okay, that's the FixedHeaderUIType property on the Override.
allenovereem said:Not finding any AllowColMoving property on an extensive search thru the properties of a column. What is that a property of or is it perhaps some other name?
It's on the Override. So there's an Override on the band and also on the layout.