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,
I am new to C#. Have not implemented IComparer before but have read about it and it seems fairly straightforward to me.
I'm not sure I follow you nevertheless. You lose me when you say that I will comare the values of rainfall in the next columns and if they are different I return a value else I move on to the next date and so on.
The values in each cell of each row will always be different unless there happened to be the exact same amount of rainfall on more than one day.
For example in the table below I'm sorting first on Nevada because it has the first occurrence of rainfall within the date range and then New Mexico which had rainfall earlier in the period than did Arizona and so on. The values in the columns for a row are not significant in terms of sorting beyond the premise that whichever one has a date with a rainfall amount first should show up first within its respective region.
Likewise in my real world application, whichever drug regimen was administered first should show up first for that drug type group and so on based on the dates of administration of any regimen in that group.
I guess I will have to produce an example and then maybe you can insert your wisdom into my code where I start to go awry.
I will do that and post again when I have the chance unless you feel inclined to produce a C# coded example beforehand.
Thanks for your time and attention,
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.
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!
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.
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).