Infralution Support Forum Index Infralution Support
Support groups for Infralution products
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Updateable, Multicolumn Pivot Control (Windows Forms)

 
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Tree Feature Requests
View previous topic :: View next topic  
Author Message
jaherbert



Joined: 27 Jan 2008
Posts: 43

PostPosted: Sat Mar 08, 2008 8:58 pm    Post subject: Updateable, Multicolumn Pivot Control (Windows Forms) Reply with quote

I'm looking for a control that will take a DataTable, pivot the row data on one or more columns into a grid, allow data entry in the grid, and save the data back to the underlying DataTable. My initial research hasn't found one. I realize you don't offer such a control (unless you're working on one Smile), but I thought I'd ask for advice on two counts:

1. If you know of such a control in the market, or have a recommendation for vendors I could check with, I'd appreciate it.
2. If not, I suspect I'll have to roll my own, and I'm wondering whether using VirtualTree would be a reasonable place to start. In this scenario, I'll pivot and unpivot the data myself, so the control need only worry about updating the (possibly dynamically-generated) pivoted table.

NB: while it may be nice to use VTs hierarchical support, it's not required, and I may even desire to "turn it off" and treat it as a regular grid. Why am I considering VT then? It has to do with RowWidgets, dynamic generation, and virtual columns, but I'm getting ahead of myself.

With that in mind, let me state some requirements, and then pose some questions. For simplicity, I'll use a concrete example. The application is task scheduling. Here's the table:

task_id, employee_id, date, startTime, numHours
t1,e1,d1,7:00,3
t1,e2,d1,9:00,4
t2,e3,d1,8:00,2
t3,e2,d2,8:00,1

Where e1,e2,e3 or employees
d1,d2 are consecutive dates
t1,t2,t3 are tasks
and t1.# refers to # of hours worked on t1 (on that day by that emp).

We pivot on date and employee columns. The pivoted table structure would be:
startTime, d1.e1.task_id, d1.e1.hours, d1.e2.task_id, d1.e2.hours, d1.e3.task_id, d1.e3.hours, d2.e1.task_id, d2.e1.hours, d2.e2.task_id, d2.e2.hours, d2.e3.task_id, d2.e3.hours

Let's look at a weekly or monthly calendar view with startTime as a row header and dates/employees (combined) as columns and each cell contains both task_id and numHours. Something like this:

Code:
         d1.e1    d1.e2    d1.e3    d2.e1    d2.e2    d2.e3
7:00      t1.3   
8:00                        t2.2              t3.1
9:00               t1.4

Imagine we drag/drop tasks onto the grid (update the appropriate two columns in 1 row of the pivoted table) with the # of hours being set by the remaining time on the task (editable later). Now an appropriately sized rectangle covering the time slots appears on the grid labeled with the task name and colored by a priority (attached to task_id).

Q1: Can a RowWidget be constructed to accomplish this mapping/display of cell data?

Now to the the static/dynamic part:

One possibility is to statically generate the pivot/unpivot with a fixed set of columns and some mapping of actual employees and dates to e#'s adn d#'s. With a modest # of employees (say 10) and a 10 week (or longer) scheduling horizon. We have 10 emps * 10 weeks * 7 days/week * 2 cols (task/hours) = 1400 columns in data set and half (700) shown through grid. Even though many times we'll only look at 3 employees and 2 weeks of data (~80/40 columns);

Q2: Do you know if there's a practical limit to the # of columns in a dataTable? How about VirtualTree? Since most will be hidden, does that lower the overhead in any significant way? That is, are VT columns in any way like the rows? Also is it convenient to programmatically walk through to hide/unhide columns based on a (mapped) index?

So, if fixing the number of column becomes too burdensome, we switch to a dynamic method. Creating the dataTable based on a dynamically genned statement shouldn't be a problem.

Q3: Can we AutoGenerate the associated VT bindings at RunTime? What would performance be like? If this is practical, does this dynamic method mess up your answer to #1 about RowWidgets? What about other functionality (e.g., Drag & drop)? Can all of this be neatly wrapped in a derived VT or will the dynamic generation complicate that?

I'm sure there's plenty more, but this seems like a good start. Thanks for reading this far and considering.

Jeff
Back to top
View user's profile Send private message
Infralution



Joined: 28 Feb 2005
Posts: 5027

PostPosted: Sun Mar 09, 2008 4:18 am    Post subject: Reply with quote

Quote:
1. If you know of such a control in the market, or have a recommendation for vendors I could check with, I'd appreciate it.


I don't know of such a control - but there may be one out there.

Quote:
2. If not, I suspect I'll have to roll my own, and I'm wondering whether using VirtualTree would be a reasonable place to start. In this scenario, I'll pivot and unpivot the data myself, so the control need only worry about updating the (possibly dynamically-generated) pivoted table.


There are two ways you could handle this with Virtual Tree:

1. Pivot the data yourself into a new Data Table and then just get Virtual Tree to display the pivoted data table using the normal DataSet binding mechanism.

2. Use programmatic binding (or write your own RowBindings class) that pulls the VirtualTree row data from the Data Table columns and create the columns dynamically.

Option 1 will probably be easier to write (at least for display) - but won't be as efficient or flexible and may be harder to handle update.

Quote:
Q1: Can a RowWidget be constructed to accomplish this mapping/display of cell data?


Using programmatic binding you can display just about anything.

Quote:
Q2: Do you know if there's a practical limit to the # of columns in a dataTable? How about VirtualTree?


Since DataSets are complete memory based, memory is the limiting factor - this applies as much to rows as to columns. Virtual Tree only stores data for the columns/rows that are current visible - so the memory overhead is much less. For each not-visible column there is just the minimal information (such as width, caption etc) that is stored.

Quote:
Since most will be hidden, does that lower the overhead in any significant way? That is, are VT columns in any way like the rows?


Columns are not quite the same as rows. Non-visible columns are not drawn nor is the CellData for non-visible rows stored (or retreived from the data source until required). So scrolling performance is not affected by the number of columns. Unlike rows you have to create the column upfront. So whereas the Virtual Tree memory foot print for a datasource with one million rows would be the same as for one with 1000 rows the same would not be true for columns. Each column added will require a small amount of additional memory to represent it upfront. The bottom line is that 1000's of columns will probably perform OK but millions may not.

Quote:
Also is it convenient to programmatically walk through to hide/unhide columns based on a (mapped) index?


You may want to look at ContextSensitive columns to handle this. You can handle the GetColumnInContext event (or override the corresponding method) to determine with a column should be displayed in the current application state.

Quote:
Q3: Can we AutoGenerate the associated VT bindings at RunTime? What would performance be like? If this is practical, does this dynamic method mess up your answer to #1 about RowWidgets? What about other functionality (e.g., Drag & drop)? Can all of this be neatly wrapped in a derived VT or will the dynamic generation complicate that?


I wouldn't use VirtualTree AutoGenerate bindings methods to generate bindings at runtime. The bindings these methods generate are generally used just as a starting point and generally need tweeking to get the user interface appearance you require. There is nothing to stop you creating bindings on the fly yourself however.
_________________
Infralution Support
Back to top
View user's profile Send private message Visit poster's website
jaherbert



Joined: 27 Jan 2008
Posts: 43

PostPosted: Sun Mar 09, 2008 4:52 pm    Post subject: Reply with quote

Thanks for the info. If I go the VT route, I'm suspect I'll be asking for some help Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Infralution Support Forum Index -> Virtual Tree Feature Requests All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group