I was wondering if we have the ability to set the aggregate column when have a grouping. When i put in a summary row it sets the aggregate the appropriate column, however I'd like to put it in the group header. The main reason behind this to show the user the sum without having to expand the row to see the summary row.
Attached is a screenshot of an example grid of what I'd like the data to show. You'll see each row has the group, then the aggregates get summed up to that level.
Does this make sense? Is it possible?
Thanks
-Matt
31 Answers, 1 is accepted
Please take a look at the following example:
using
System;
using
System.ComponentModel;
using
System.Linq;
using
System.Windows.Forms;
using
Telerik.WinControls.UI;
public
partial
class
Form1 : Form
{
BindingList<Test> list =
new
BindingList<Test>();
RadGridView radGridView1 =
new
RadGridView();
public
Form1()
{
InitializeComponent();
this
.Controls.Add(radGridView1);
radGridView1.Dock = DockStyle.Fill;
radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
radGridView1.GroupSummaryEvaluate +=
new
GroupSummaryEvaluateEventHandler(radGridView1_GroupSummaryEvaluate);
for
(
int
i = 0; i < 100; i++)
{
list.Add(
new
Test { Id = list.Count, Name =
"Item "
+ list.Count, Value = list.Count % 5 });
}
radGridView1.DataSource = list;
radGridView1.MasterTemplate.SelectLastAddedRow =
false
;
var button =
new
RadButton();
button.Text =
"Add"
;
button.Click +=
new
EventHandler(button_Click);
button.Dock = DockStyle.Bottom;
this
.Controls.Add(button);
radGridView1.GroupDescriptors.Add(
"Value"
, ListSortDirection.Ascending);
}
void
radGridView1_GroupSummaryEvaluate(
object
sender, GroupSummaryEvaluationEventArgs e)
{
var name = e.Value.ToString();
e.Value = name +
", total value: "
+ e.Group.Sum(r => ((Test)r.DataBoundItem).Value);
}
void
button_Click(
object
sender, EventArgs e)
{
list.Add(
new
Test { Id = list.Count, Name =
"Item "
+ list.Count });
}
}
public
class
Test
{
public
int
Id {
get
;
set
; }
public
string
Name {
get
;
set
; }
public
int
Value {
get
;
set
; }
}
You can always handle the GroupSummaryEvaluate event and format the data displayed in the group row according your requirements.
Hope this helps, if you have any other questions or comments, please let me know,
Best Regards,
Emanuel Varga
Telerik WinForms MVP
Thanks for the response but it was not quite what I was looking for. In your example, the Totals are all left aligned in the group header. I'm looking to align them up to each column they are summing up. If I add in a summary row, this will align them up properly, however I'm looking to duplicate the results from the screenhots in the first post.
Any help would be appreciated.
Thanks
-Matt
You can achieve your requirement by using a custom content cell for the group rows. You can hide the text of the cell, add and arrange separate elements for each column summary. Below is an example implementation of this custom cell:
public
class
CustomGroupContentCellElement : GridGroupContentCellElement
{
private
LightVisualElement elementHeader;
private
LightVisualElement elementSummary1;
private
LightVisualElement elementSummary2;
public
CustomGroupContentCellElement(GridViewColumn column, GridRowElement row)
:
base
(column, row)
{
}
public
override
object
Value
{
get
{
return
string
.Empty; }
set
{
base
.Value = value; }
}
public
override
void
Initialize(GridViewColumn column, GridRowElement row)
{
base
.Initialize(column, row);
GridViewGroupRowInfo rowInfo = (GridViewGroupRowInfo)
base
.RowInfo;
string
headerText = rowInfo.HeaderText;
string
summary = rowInfo.GetSummary();
string
[] summaries = summary.Split(
new
char
[] {
','
});
this
.elementHeader.Text = headerText;
this
.elementSummary1.Text = summaries[0];
this
.elementSummary2.Text = summaries[1];
}
protected
override
void
CreateChildElements()
{
base
.CreateChildElements();
this
.elementHeader =
new
LightVisualElement();
this
.elementSummary1 =
new
LightVisualElement();
this
.elementSummary2 =
new
LightVisualElement();
this
.Children.Add(
this
.elementHeader);
this
.Children.Add(
this
.elementSummary1);
this
.Children.Add(
this
.elementSummary2);
}
protected
override
SizeF MeasureOverride(SizeF availableSize)
{
base
.MeasureOverride(availableSize);
this
.elementHeader.Measure(availableSize);
this
.elementSummary1.Measure(availableSize);
this
.elementSummary2.Measure(availableSize);
return
availableSize;
}
protected
override
SizeF ArrangeOverride(SizeF finalSize)
{
IList<GridViewColumn> renderColumns =
base
.TableElement.ViewElement.RowLayout.RenderColumns;
int
x1 = renderColumns[0].Width + renderColumns[1].Width;
int
x2 = x1 + renderColumns[2].Width;
int
x3 = x2 + renderColumns[3].Width;
this
.Children[0].Arrange(
new
RectangleF(0, 0, 0, 0));
this
.Children[1].Arrange(
new
RectangleF(x2, 0, 0, 0));
this
.Children[2].Arrange(
new
RectangleF(x3, 0, 0, 0));
return
finalSize;
}
}
You can use the CreateCell event of RadGridView to replace the GridGroupContentCellElement with the custom cell:
private
void
radGridView1_CreateCell(
object
sender, GridViewCreateCellEventArgs e)
{
if
(e.CellType ==
typeof
(GridGroupContentCellElement))
{
e.CellType =
typeof
(CustomGroupContentCellElement);
}
}
I have used the following sample data to test this approach:
this
.radGridView1.Columns.Add(
new
GridViewDecimalColumn(
"Column1"
));
this
.radGridView1.Columns.Add(
new
GridViewDecimalColumn(
"Column2"
));
this
.radGridView1.Columns.Add(
new
GridViewDecimalColumn(
"Column3"
));
this
.radGridView1.Columns.Add(
new
GridViewDecimalColumn(
"Column4"
));
for
(
int
i = 1; i < 11; i++)
{
this
.radGridView1.Rows.Add(i, i * 2, i * 3, i * 4);
}
this
.radGridView1.MasterTemplate.SummaryRowGroupHeaders.Add(
new
GridViewSummaryRowItem(
new
GridViewSummaryItem[] {
new
GridViewSummaryItem(
"Column3"
,
"{0}"
, GridAggregateFunction.Sum) }));
this
.radGridView1.MasterTemplate.SummaryRowGroupHeaders.Add(
new
GridViewSummaryRowItem(
new
GridViewSummaryItem[] {
new
GridViewSummaryItem(
"Column4"
,
"{0}"
, GridAggregateFunction.Sum) }));
this
.radGridView1.GroupDescriptors.Add(
new
GroupDescriptor(
"Column1"
));
I hope it helps.
Best regards,
Alexander
the Telerik team
If so, please share.
TIA.
-Megan
I have attached a sample project which demonstrates the approach described in my previous post . The only difference in the source code I have made is in the ArrangeOverride method of the custom cell in order to make it appropriate for more scenarios.
Please let us know if it helps you to accomplish your case.
Best regards,
Alexander
the Telerik team
Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get it now >>
Thank you for your reply and work. Unfortunately, I am unable to run your project.
Fortunately, though, I already have something working using this example:
http://www.telerik.com/community/forums/silverlight/gridview/aggregate-result-display.aspx
I apologize for not responding to this thread sooner. I was so excited that I forgot I had requested this project.
Thanks again for your efforts.
-Megan
I am glad to hear you have found how to implement your requirements. The project I have attached uses the Telerik WinForms controls as this thread is in the WinForms forums.
Feel free to contact our teams in the future if you have questions.
Best regards,
Alexander
the Telerik team
Explore the entire Telerik portfolio by downloading the Ultimate Collection trial package. Get it now >>
1. I'm pinning some of my columns (Location, Project and Task). I'm wondering if the manually added elements for each column summary in the CustomGroupContentCellElement will scroll and stay alligned with the columns they're aggregating.
2. I'm generating the number of column (Days) dynamically based on hte number of days the user selects. Will the CustomGroupContentCellElement be able to work with an unknown number of aggregates?
3. What if the user resizes a particular column? Will the aggregates get out of alignment?
Thanks for any help / suggestion on how to approach this.
I ended up using a different solution because I was using Silverlight/web, not win forms, but I can say that, in my situation:
1. freezing columns was not an issue, the columns still stayed aligned, however; i needed to set EnableColumnVirtualization to false
2. i'm not positive, but i think this will still work, i have applied filters, and the grouped aggregates still work
3. resizing works!
Good luck.
-Megan
Thank you for your question. In general, you should be able to accomplish your scenario using the discussed in this thread approach.
As the number of aggregates is unknown in your case, you should create a different number of summary elements in the custom cell (for example, creating a list of LightVisualElement). For this task you will need the RenderColumns collection (the one used in the ArrangeOverride method of the custom cell) and create the summary elements according to the columns it contains. You should move the code for creating summary elements from the CreateChildElements method to the constructor of the custom cell because in the CreateChildElements method the TableElement of the cell is not still assigned and you will not be able to access the RenderColumns collection.
The pinned and varying number of columns could complicate the ArrangeOverride method of the cell which needs to arrange the summary elements according to the columns layout. You should update its implementation.
The ArrangeOverride method of the cell should rearrange the summary elements when the user resizes a particular column.
As you see, using this approach in your scenario is quite complicated. I would suggest you to use summary rows in your groups which align automatically with the columns they summarize. For your scenario, you could create one summary row with summary items for the "Reg", "OT" and "T" columns.
Best regards,
Alexander
the Telerik team
Q2’11 SP1 of RadControls for WinForms is available for download (see what's new); also available is the Q3'11 Roadmap for Telerik Windows Forms controls.
-Jason
I have a question, in ArrangeOverride, I want to Arrange the summaries from the right, but I can't get the width of the gridview.
I can get the MyBase.TableElement.ViewElement.RowLayout.DesiredSize.Width, but it is bigger than the width of the gridview.
Would you help me to get the width of the gridview?
Thanks.
Ada
Thank you for writing.
The ArrangeOverride method has an argument called finalSize of type SizeF. This argument gives you the space in which you should arrange the child elements. Since you are in the group content cell element you get the width of the content cell. This is practically the width of the grid minus the group expander and the row header cell (if visible). Having that size you should have no problem to align the summary cells on the right side. Additionally you can have a look at the following Knowledge base article which demonstrates how you can have summary cell values in collapsed groups - Summaries in groups.
I hope this will be useful. Should you have further questions, I would be glad to help.
Regards,
Ivan Petrov
Telerik
Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
Sign up for Free application insights >>
The grid example is exactly what I want.
I tried to comment on that example, but I don't have the right. so I posed here and hope it works too.
Thanks.
Ada
Ivan, thanks for your help, The grid example is exactly what I want.
Would you please help me with the bug I found.
Thanks.
Ada
Thank you for writing.
I tried to reproduce the issue you have described but I was unable to do so. I would kindly ask you to open a support thread and send us a sample project where you reproduce the issue so I can investigate the issue there and give you an adequate response. Please, mention this thread if you open a ticket so we can update it with any information that might be useful to other users.
Looking forward to your reply.
Regards,
Ivan Petrov
Telerik
Learn what features your users use (or don't use) in your application. Know your audience. Target it better. Develop wisely.
Sign up for Free application insights >>
I tried your solution, it works.
But if some columns are hidden, I don't want to show the summaries either, and if columns' order are changed, I want to change the summaries order as well, how should I do it?
Thanks.
Ada
Thank you for posting.
I have modified the project from the Knowledge Base article to track the columns visibility and update the group summaries accordingly. The example already trackс the position of columns and their order. The new code that I have added is enclosed in commends with the format //NEW. Basically I have subscribed to each column's RadPropertyChanged event and if this property is the IsVisible I update the grid hence the group rows.
I hope this helps. Feel free to write back with further questions.
Regards,
Ivan Petrov
Telerik
Hello,
Is this still the best way to do this? I need to display summed values on the Group Header row. One for each column. The value needs to align with the column(s) that are being summed.
But, the number of columns (that need to be summed) is unknown until run time. In the example it looks like a static number of columns are added (2) to the group header row. The number of columns can change as the application is run.
Thanks,
Mike Baxa
Thank you for writing.
The recommended approach for displaying summary items in the group cells aligned with the respective columns is to create a custom GridGroupContentCellElement that is demonstrated in the GridView with visible summary cells in a collapsed group KB article. Have you looked at the modified sample project provided by Ivan in the post from 21-Jul-2014 9:54:32 AM. When the columns are hidden or reordered at run time the corresponding summary elements are hidden/reordered accordingly. Is your case different? I would kindly ask you to open a support thread and send us a sample project where you reproduce the issue so I can investigate the issue there and give you an adequate response. Thank you in advance.
Should you have further questions, I would be glad to help.
Dess
Telerik
Hello Dess,
Thank you for writing back.
I did download the example that was already in this thread and with some minor changes it works exactly as we need it to.
Thank you for your help.
Mike Baxa
Hi Dess,
I have implemented the functionality based on the recommended KB article referenced above (RadGridView with visible summary cells in a collapsed group). I have a number of questions based the results I'm seeing. FYI - the grid I'm testing has 2000 rows populated via a datatable.
1. If I have a grouping that results in around 60 +- generated group results then scrolling the results is extremely slow (mouse wheel, page, or using the scroll bar). It's actually to slow to make live for our user base. Is there any modifications that can speed this up?
2. I had Ivan's changes in place that would work with hidden fields or reordering but I took it out. It would take a lot longer to load the grid and you could just see it slowly paint the grid. Is there any other fast methods you have since this was first recommended?
3. I utilize the save an load feature of the grid to XML. If I save the grid with a grouping in place. Then the next time I load the grid I receive a null error. It only happens with a group summary that has an expression (Image attached). Is this a known issue and is there a fix?
4. I know this KB article results in having two lines for each group header. Is there an adjustment or other method that can keep this to one line per group?
5. If I have alignment logic and font formats in place for the top summary will the group summaries use the same logic already in place?
6. Is this KB article still the only or best recommended way?
You feedback will be vary much appreciated!
Thanks
Thank you for writing.
Straight to your questions:
- I tested the solution as it is in the KB article with groups of about 60 and it performed OK on my end. However, I am not sure if your issues are coming when you are having 60 groups at one level or the count is 60 with the inner levels as well.
- In order to reach the result Ivan`s solution proposes, you would need to refresh the MasterTemplate of your grid so that the changes are reflected and become visible. This operation will result in the refreshing the whole layout and depending on you setup is more or less time consuming.
- I tried to reproduce the exception but without success.
- The approach suggested in the KB article creates a custom GridGroupContentCellElement having SummaryCellElements which display the summary data. The second line which you mention is actually the border of this newly created cell. If you like you could hide the bottom border and this way you will have only one line. Please see my snippet below, showing how the CreateStackElement method in the MyGridGroupContentCellElement needs to be changed:
private
void
CreateStackElement(GridRowElement row)
{
this
.stack =
new
StackLayoutElement();
this
.stack.AutoSizeMode = Telerik.WinControls.RadAutoSizeMode.FitToAvailableSize;
this
.stack.AutoSize =
true
;
this
.stack.StretchHorizontally =
true
;
this
.stack.Alignment = ContentAlignment.BottomCenter;
this
.stack.DrawFill =
true
;
this
.stack.BackColor = Color.White;
for
(
int
i = 0; i < row.RowInfo.Cells.Count; i++)
{
SummaryCellElement element =
new
SummaryCellElement();
element.ColumnName = row.RowInfo.Cells[i].ColumnInfo.Name;
element.StretchHorizontally =
false
;
element.StretchVertically =
true
;
element.DrawBorder =
true
;
element.BorderBoxStyle = Telerik.WinControls.BorderBoxStyle.FourBorders;
element.BorderGradientStyle = Telerik.WinControls.GradientStyles.Solid;
element.BorderTopColor = Color.LightBlue;
element.BorderLeftColor = Color.LightBlue;
element.BorderRightWidth = 0;
element.BorderBottomWidth = 0;
this
.stack.Children.Add(element);
}
this
.Children.Add(
this
.stack);
}
- The group summaries inherit the GridGroupContentCellElement class and they would not get created until they are needed. So you need to explicitly specify the type of formatting and alignment.
- Currently, this is the recommended approach for providing this type of functionality.
Considering questions 1, 2 & 3, in order to perform tests according to your scenario I am going to need a sample project correctly depicting your local set up. Since project attachments are not allowed in forum posts, please open up a support ticket. Please mention in the ticket any additional information you might consider useful, and also specify what version of the suite you are using.
I hope this information was useful. Should you have further questions, please do not hesitate to write back.
Hristo Merdjanov
Telerik
Hi Hristo,
Thanks for the information!! I'm going over these in further detail.
I do have 2 new issues and I was able to reproduce these using the KB article project.
Changes to project:
1. Commented out this line: this.radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
(we have a large variance in column width in all or grids)
2. Change 5 to a 12 to generate more columns for the grid:
#region Populate with data
for (int i = 0; i < 12; i++)
New Issues:
1. When you have a grid's columns exceed the view-able window the group header row summary columns move independently of the column headings. We have grids that have about 30+ columns so this is noticeable and becomes very hard to read based on alignment issue. See attached: groupheader_scroll_issue1.png
2. If there are header summary columns generated but are not currently visible in the window then if a grouping occurs the header summary columns not visible do not display if you scroll to the right.
Attachment: grid_scroll_2_A.png --> Screen resized to have fields not visible
grid_scroll_2_B.png --> Scroll to the right Col2 moves (out of alignment) but other columns don't appear
Thanks for your help!
Thank you for writing.
GridGroupContentCellElement which our custom implementation inherits is statically positioned and it does not update its location if the scroll bar changes its value. This is the reason for the issues you observe on your end.
You can try the following solution, subscribe your GridRowElement.GridControl.TableElement.HScrollBar to the Scroll event. This can be done in the constructor of the GridGroupContentCellElement. Then in the handler you would need to set an offset of the StackLayoutElement containing your summary items. The new offset position can be calculated according to the old and new values of the scroll bar. Please see below my modifications in the MyGridGroupContentCellElement class:
public
MyGridGroupContentCellElement(GridViewColumn column, GridRowElement row)
:
base
(column, row)
{
//creating the elements here in order to have a valid instance of a row
if
(
this
.stack ==
null
)
{
this
.CreateStackElement(row);
}
row.GridControl.TableElement.HScrollBar.Scroll +=
new
ScrollEventHandler(HScrollBar_Scroll);
row.GridControl.ColumnWidthChanged +=
new
ColumnWidthChangedEventHandler(GridControl_ColumnWidthChanged);
row.GridControl.GroupDescriptors.CollectionChanged +=
new
NotifyCollectionChangedEventHandler(GroupDescriptors_CollectionChanged);
}
private
void
HScrollBar_Scroll(
object
sender, ScrollEventArgs e)
{
if
(e.NewValue != e.OldValue)
{
this
.stack.PositionOffset =
new
SizeF(0 - e.NewValue, 0);
}
}
I am also sending you a gif file showing the result on my end.
I hope this helps. Should you have further questions please do not hesitate to write back.
Regards,
Hristo Merdjanov
Telerik
In order to improve the visual appearance of the custom GridGroupContentCellElement when the grid is being scrolled, you can set its ClipDrawing property to true:
public
MyGridGroupContentCellElement(GridViewColumn column, GridRowElement row)
:
base
(column, row)
{
//creating the elements here in order to have a valid insance of a row
if
(
this
.stack ==
null
)
{
this
.CreateStackElement(row);
}
this
.ClipDrawing =
true
;
row.GridControl.TableElement.HScrollBar.Scroll +=
new
ScrollEventHandler(HScrollBar_Scroll);
row.GridControl.ColumnWidthChanged +=
new
ColumnWidthChangedEventHandler(GridControl_ColumnWidthChanged);
row.GridControl.GroupDescriptors.CollectionChanged +=
new
NotifyCollectionChangedEventHandler(GroupDescriptors_CollectionChanged);
}
The attached gif file shows how the cells look now. I have also the KB article to include this solution.
Should you have further questions please do not hesitate to write back.
Regards,
Hristo Merdjanov
Telerik
Thanks Hristo,
This scrolls vertically much smoother now.
Hi,
I have implemented CustomGroupContentCellElement as described and it works perfect. Thanks for that :)
Now i want to extend this functionality with possibility of selecting aggregate cells in view of copying and/or calculating selected values.
I found a property "Selectable", but it is read-only.
Please help me with selection or another way to copy values of aggregated cells.
Regards,
Simeon
Thank you for writing.
Please note that only data rows can be selected, the group row can be current only. In addition, when a single row is clicked the other selection is cleared. However, I can suggest you to handle the MouseDown event and customize the SummaryCellElement in order to simulate selection. Then, you can add a copy menu item and copy the selected value. Here is a sample code snippet which result is illustrated in the attached gif file:
private
void
radGridView1_MouseDown(
object
sender, MouseEventArgs e)
{
if
(e.Button == System.Windows.Forms.MouseButtons.Left)
{
SummaryCellElement summaryCell =
this
.radGridView1.ElementTree.GetElementAtPoint(e.Location)
as
SummaryCellElement;
if
(summaryCell !=
null
)
{
summaryCell.DrawFill =
true
;
summaryCell.GradientStyle = GradientStyles.Solid;
if
(selectedSummaryCells.Contains(summaryCell))
{
selectedSummaryCells.Remove(summaryCell);
summaryCell.BackColor = Color.Transparent;
}
else
{
selectedSummaryCells.Add(summaryCell);
summaryCell.BackColor = Color.Orange;
}
}
}
}
private
void
radGridView1_ContextMenuOpening(
object
sender, ContextMenuOpeningEventArgs e)
{
if
(e.ContextMenuProvider
is
MyGridGroupContentCellElement && selectedSummaryCells.Count > 0)
{
RadMenuItem myCopyItem =
new
RadMenuItem();
myCopyItem.Text =
"Copy summary items"
;
e.ContextMenu.Items.Add(myCopyItem);
myCopyItem.Click += myCopyItem_Click;
}
}
private
void
myCopyItem_Click(
object
sender, EventArgs e)
{
if
(selectedSummaryCells.Count > 0)
{
StringBuilder sb =
new
StringBuilder();
foreach
(SummaryCellElement cell
in
selectedSummaryCells)
{
sb.AppendLine(cell.Text);
}
DataObject dataObject =
new
DataObject();
dataObject.SetData(DataFormats.Text,
false
, sb.ToString());
Clipboard.SetDataObject(dataObject);
RadMessageBox.Show(sb.ToString());
}
}
Note that this is just a sample approach and it may not cover all possible cases. Feel free to modify it in a way which suits your requirement best.
I hope this information helps. Should you have further questions I would be glad to help.
Regards,
Dess
Telerik
Hi Dess,
Thank you for your answer!
Firstly i would like to point on your incredible response time and your thorough and comprehensive writing as well. I really appreciate it :)
I'm slightly dissapointed of missing possibility to selecting and copiing summary rows/cells out of the box , but i am also very enthusiastic to do this on my own and i need of little bit more help on this place.
When i examine the grid groups i came accross out of range exception on:
myRadGrid.Groups[i].GroupRow.TopSummaryRows[n]
It seems that the TopSummaryRows are available only for rows which are displayed on screen.
I can scroll down and up to select all rows, but is it a way to fill this summaryrows on all groups without scrolling?
Thank you in advance.
Best regards,
Simeon
Thank you for writing back.
In this custom implementation summary rows are not used at all. That is why the TopSummaryRows collection doesn't contain any records and you may obtain an exception. In the CreateCell event, we create a custom group cell element which hosts custom SummaryCellElement (a simple derivative of LightVisualElement) to display the summary data. No summary items are actually added.
When you group by a certain column the custom summary values for the respective column are always visible even while scrolling. However, the selection may be messed up because RadGridView uses virtualization and cell elements are being reused. That is why I can suggest you to store the group row info (instead of the SummaryCellElement) by accessing the ((MyGridGroupContentCellElement)summaryCell.Parent.Parent).RowInfo in the MouseDown event. Alternatively, you can use the Tag property of the row info in order to indicate whether the summary cells will be highlighted or not. Then, in the custom GridGroupContentCellElement, you can override the SetContentCore method, extract the Tag property of the data row and apply the color to the inner summary elements according to the selected value.
Note that this is quite a custom scenario and it may require some time for achieving the complete requirement. However, if you have any additional questions, please let me know.
Regards,
Dess
Progress Telerik