Virtual Grid and Formulas

[Jens Lippmann]

We are using Essential Grid as a virtual grid because of the large number of rows we have to manage. Now we (well our users) want to use formulas, which opens a bunch of questions that I need to clarify:

1. Can I do that or do formulas require DataBinding?

2.Let''s say I have a sum for a column. How would the grid calculate that sum particularly from the cell that are currently not shown.

3. How do I notify the formula cell that data in the off have been changed?

4. How do you keep track of formula dependencies? Theoretically, the formula field could include a range that includes another formula field which depends on a range of different fields some which may or may not been shown...

This is all issues I could think of, but I''m definitely interested in any other traps that people could think of.

Thanks for your help,

Jens

[Administrator]

If you are not using the gridcontrol in virtual mode, then all the things you asked about are managed by GridControl through in FormulaCell CellType. You would make the summary cells be FormulaCell celltypes, and use a formula like =Sum(A1:A5000) (or whatever). Then the grid would track dependencies and automatically update the formula cells when any cell that the formula cell depends on is changed.

But with a virtual grid, there is some addition work that you need to do. Right now, you have a datastore that is used in QueryCellInfo to provide values to the grid, and is used in SaveCellInfo to store any changed values. To have formula cells, the additional work you need to do is to add another datastore that will hold GridFormulaTag objects, one for each cell that should be a FormulaCell. In QueryCellInfo, if e.RowIndex and e.ColIndex point to a formulacell, you get the stored GridFormulatag and set it to e.Style.FormulaTag. In SaveCellInfo, if e.RowIndex and e.ColIndex point to a formulacell, you would store e.Style.FromaulaTag into you datastore (to be used in QueryCellInfo when needed.)

Here is a link to a KB that expains how to do something similar so you can use FormulaCells in a GridDataBoundGrid (which is a type of virtual grid.)

[Jens Lippmann]

Thanks. Actually, the link to the kb article actually got chopped off in your post.

Our data store would handle additional content such as formulas the way you describe. (we have a self describing messages so it''s just a matter of adding another message type)

I didn''t understand from your reply how cell reference would get resolved by the formula cell:

Let''s say I want sum cell 1:1 (column:row) through 1:5000 in formula cell 1:5001.

1. Does the formula cell invokes QueryCellInfo for cell 1:1 through 1:5000 even though only let''s say 1:4990 to 1:5001 are shown?

2. Does the formula cell only get recalculated when QueryCellInfo hits cell 1:5001? But if we show cell 1:5001 and the data for cell 1:1 gets updated, the sum would be incorrect, or could I force a recalculation with RefreshRange(GridRangeInfo.Table()) (or RefreshRange(GridRangeInfo.Col(1))? (Probably would work because it''ll also call QueryCellInfo for cell 1:5001, if question 1 is to be answered with yes?

This issue is important because we cannot show the wrong data in a formula.

Thanks again for your help,

Jens

[Administrator]

Here is the link. (You can also search our KB for the word formula and bring it up.)
http://64.78.18.34/Support/article.aspx?id=10439

If you were using Excel, you would go to the cell C5001, and enter a formula like =Sum(C1:C5000), and that would be all you would have to do to see the sum of the first 5000 cells in column C.

Well, with the grid using FormulaCells, this is also all you have to do also.

When QueryCellInfo requests the style for C5001 (e.RowIndex==5001 and e.ColIndex==3), then you would need to set e.Style.Text="=Sum(C1:C500)" and also set e.Style.Formulatag any cached formulatag for this cell. That is all you have to do. The grid should know how to get the values its needs and compute the formula.

[Jens Lippmann]

Hm, I somehow discarded your search engine because I had trouble using it in the past. It doesn''t work very well with search terms that contain more than one word, but searching for "formula" would have worked perfectly - my bet.

Sorry for being pesky here but I don''t seem to get my point across. Let''s take a simple example:

We have a grid with a single column with 100 rows. Cell 1 through 99 contain the integers 1 to 99. Cell 100 contains the sum of cell 1 to 99 (the old Adam Riess story :) So we see on the screen cell 100 showing 4950 (if I do the math here correctly). The user doesn''t scroll or anything and he always sees 4950. Now cell 1 changes programmatically (without being shown at all) from 1 to 101. What do I need to do so cell 100, which still shows 4950, will now show 5050?

Thanks,

Jens

[Administrator]

If by progamatically changing the cell, you mean using code like

this.gridControl1[1,2].CellValue = 100;

to set this value that is not visible in cell B1, then you would have to do nothing else to see the new value in the visible cell at the bottom of the grid.

[Administrator]

>...then you would have to do nothing else...

Thanks, I always like such an answer (-:
Regards,

Jens

P.S. Great support!!!