Hi guys, I am wondering if it would be possible to create a single DataTable and then use some sort of "grouping" functionality to create a hierarchy suitable for the grid. Basically right now I create multiple stored procedures that populate data tables, and then I create a relationship between the tables and bind them to the grid. I am wondering if I could just write one stored procedure with a larger join based select statement that will give me a flat representation of the hierarchy, then somehow define the grouping and bind it to the gird. I believe this is how MS Reporting Services works, where I can define the "grouping" columns from within a single source, in essence producing a hierarchy. I'm using 7.2 controls if that matters. What I am trying to accomplish is having less stored procedures, especially specialized procedures that will likely never be used outside of my screen. If all fails I think I can just go the multiple select statements in one procedure route as well. All and any ideas are welcome!
Thanks,
litvak said: But you should be able to tweak your column.AllowGroupBy and column.IsGroupByColumn properties to get what you want.
But you should be able to tweak your column.AllowGroupBy and column.IsGroupByColumn properties to get what you want.
I am not sure I understand what this would accomplish, are there any examples you can point me to?
[/quote]
If you create your universal flat table, and bind it to a non-hierarchical grid, and then manually drag a column header into the GroupBy box, you end up with a grid that looks and feels like a hierarchical grid.
Again, I'm not recommending this; I think your initial approach is probably your best bet. But if you want, you can use those properties to do the equivalent of dragging a column header into the GroupBy box.
What follows is a sub I use to do a one-column GroupBy. It contains a lot of code you don't need.
Dim lReader As SqlDataReader Dim lCol As UltraGridColumn
lReader = EP_CDBG_Agencies.GetAgenciesAndAgencyTypesActiveOnly() Me.RecipientAgencyUltraWebGrid.DataSource = lReader Me.RecipientAgencyUltraWebGrid.DataBind() lReader.Close()
lCol = Me.RecipientAgencyUltraWebGrid.Columns(RECIPIENT_GRID_COLUMN_NUMBER_ID) lCol.Hidden = True lCol = Me.RecipientAgencyUltraWebGrid.Columns(RECIPIENT_GRID_COLUMN_NUMBER_NAME) lCol.Header.Caption = "Agency Name" lCol.Key = "AgencyName" lCol.AllowGroupBy = AllowGroupBy.No 'turn off GroupBy
lCol = Me.RecipientAgencyUltraWebGrid.Columns(RECIPIENT_GRID_COLUMN_NUMBER_TYPE) lCol.IsGroupByColumn = True 'set as default GroupeBy column lCol.Key = "TypeName" lCol.Header.Caption = "Type" lCol.Header.Title = "Type: to remove grouping, drag this title out of the header box"
lCol = Me.RecipientAgencyUltraWebGrid.Columns(RECIPIENT_GRID_COLUMN_NUMBER_ATC) lCol.Key = "ATC" lCol.Hidden = True lCol = Me.RecipientAgencyUltraWebGrid.Columns(RECIPIENT_GRID_COLUMN_NUMBER_ISACTIVE) lCol.Header.Caption = "Active" lCol.Key = "IsActive" lCol.CellStyle.HorizontalAlign = HorizontalAlign.Center
Me.RecipientAgencyUltraWebGrid.Bands(0).SortedColumns.Add(RecipientAgencyUltraWebGrid.Columns.FromKey("AgencyName"), True)
End Sub
Let us know how you fare. ;-)
gernblandston said: From a maintenance perspective, I'd opt for multiple, simpler procs than trying to put everything in one basket.
From a maintenance perspective, I'd opt for multiple, simpler procs than trying to put everything in one basket.
The way you're doing it now is the way the grid is designed to work. It would take a really compelling reason to get me to try and buck that system.
No, I was thinking more along the lines of:
>>I could just write one stored procedure with a larger join based select statement that will give me a flat representation of the hierarchy <<
Which would return a single datareader/datatable, then you would loop through it and build the multiple tables from that manually.
This would accomplish your goal of generalizing your proc and still allow you to present hierarchically.
gernblandston said:Not really what you're asking for, but... You could pull back a big, honkin' datareader from a single proc, then loop through and build datatables from it, then add them to a dataset and slap in a relation and bind it. I'm not sure it's worth the effort to do this though.
Not really what you're asking for, but... You could pull back a big, honkin' datareader from a single proc, then loop through and build datatables from it, then add them to a dataset and slap in a relation and bind it.
I'm not sure it's worth the effort to do this though.
Yes that's the way I would do it right now, I would have one stored procedure with multiple select statements for example. I just thought perhaps there is a feature such as what I described in my original post. As far as I know reporting services does it, as well as crystal reports.
WombatEd said:But you should be able to tweak your column.AllowGroupBy and column.IsGroupByColumn properties to get what you want.