The new API would allow some of the default functions to be removed and custom aggregate functions to be added. At the moment the dialog can be modified like this: Public Class MyAggregateOptionsDialog Inherits AggregateOptionsDialog Private availableAggregateFunctions As IList(Of AggregateFunction) = New List(Of AggregateFunction)() From { AggregateFunctions.Sum, AggregateFunctions.Count, AggregateFunctions.Average, AggregateFunctions.Max, AggregateFunctions.Min, AggregateFunctions.Product, AggregateFunctions.StdDev, AggregateFunctions.StdDevP, AggregateFunctions.Var, AggregateFunctions.VarP, New SqrtSumAggregateFunction() } Public Overrides Sub LoadSettings(aggregateDescription As PropertyAggregateDescriptionBase) MyBase.LoadSettings(aggregateDescription) Dim listAggregateFunctions = DirectCast(Me.GetType().BaseType.GetField("listAggregateFunctions", BindingFlags.NonPublic Or BindingFlags.Instance).GetValue(Me), RadListControl) listAggregateFunctions.DataSource = availableAggregateFunctions End Sub End Class
How to reproduce: check the attached project and select an item from the drop-down list
The best solution would be to have cells in Excel with the correct data type and formatted values. This can be achieved with an additional property similar to RadGridView. At the moment data cells which are created from aggregate descriptions with an applied StringFormat property are exported as text and they do not persist the formatting which is not correct. A possible workaround is to set the ExportVisualSettings property of the PivotGridSpreadExport object to true so that the formatting event to fire. Then in the CellFormatting event one can set the FormatString this way: private void SpreadExport_CellFormatting(object sender, PivotGridSpreadExportCellFormattingEventArgs e) { if (e.Cell.Text.StartsWith("$")) { e.Cell.FormatString = "$ #.00"; } else if (e.Cell.Text.Contains("€")) { e.Cell.FormatString = "#.00 €"; } } PivotGridSpreadExportCellFormattingEventArgs, however, does not provide information about the actual aggregate of the data cell, so the applied number format on the aggregate cannot be obtained in the formatting event.
The issue may manifest if aggregate descriptions are added on the pivot`s column axis and if their member does not return data.
To reproduce:
- Set the ExportFlatData property to true
- Export the grid using PivotExportToExcelML
Workaround:
Use the new PivotGridSpreadExport
Check the attached screenshot:
To reproduce the problem, use the following setup for RadPivotGrid:
public RadForm1()
{
InitializeComponent();
DataTable table = new DataTable();
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Salary", typeof(int));
table.Columns.Add("Title", typeof(string));
table.Rows.Add("John",1200,"Accountant");
table.Rows.Add("Ana", 800, "Accountant");
table.Rows.Add("Sam", 2500, "Manager");
table.Rows.Add("Tom", 1300, "Manager");
this.radPivotGrid1.RowGroupDescriptions.Add(new PropertyGroupDescription() { PropertyName = "Title"});
this.radPivotGrid1.RowGroupDescriptions.Add(new PropertyGroupDescription() { PropertyName = "Name" });
this.radPivotGrid1.AggregateDescriptions.Add(new PropertyAggregateDescription() { PropertyName = "Salary", AggregateFunction = AggregateFunctions.Sum });
this.radPivotGrid1.DataSource = table;
}
Follow the steps:
1. Filter the child group ("Name") >> Salary>1000.
This is the result of the applied filter:
2. Filter the parent group ("Title") >> Salary>1500.
After applying the filter, the observed result is that the Accountant group is still displayed even though it should be filtered and hidden (like in Excel):
Observed result:
Expected result:
What I would like is to be able to export to a named sheet and keep the other sheets in file. For example:
"Sheet 1" (with new values), "Sheet2" (with old values)
Using your current naming convention, the Option might be named FileExportMode.CreateOrOverrideSheet
Argument exception when a specific layout is loaded: "An item with the same key has already been added."
Include a way for the developers to set the height of the header in code
If you have an object with string property and for all the available objects in the RadPivotgrid you have numeric values for this property with leading zero digits (e.g. "002"), it is exported in Excel as a numeric cell.
To reproduce: Add a RadPivotGrid and a few columns, select one column and show the PrintingDialog. Select "Print Selection Only", exception will occur. Workaround: Use the following print style - public class MyPrintStyle : PivotGridPrintStyle { private Func<Rectangle, SizeF> getScaleFactors; private Func<int, Rectangle, float, int> getStartColumn; private Func<int, Rectangle, float, int> getEndColumn; private Func<int, Rectangle, float, int> getStartRow; private Func<int, Rectangle, float, int> getEndRow; private List<AggregateDescriptionBase> aggregates; private List<int> columnWidths; private List<int> rowHeights; private Type baseType; private BindingFlags flags; public override void Initialize() { base.Initialize(); this.baseType = typeof(PivotGridPrintStyle); this.flags = BindingFlags.NonPublic | BindingFlags.Instance; this.getScaleFactors = this.GetFuncFromParent<Rectangle, SizeF>("GetScaleFactors"); this.getStartColumn = this.GetFuncFromParent<int, Rectangle, float, int>("GetStartColumn"); this.getEndColumn = this.GetFuncFromParent<int, Rectangle, float, int>("GetEndColumn"); this.getStartRow = this.GetFuncFromParent<int, Rectangle, float, int>("GetStartRow"); this.getEndRow = this.GetFuncFromParent<int, Rectangle, float, int>("GetEndRow"); this.aggregates = GetField<List<AggregateDescriptionBase>>("aggregates"); this.columnWidths = GetField<List<int>>("columnWidths"); this.rowHeights = GetField<List<int>>("rowHeights"); } private T GetField<T>(string name) { return (T)this.baseType.GetField(name, flags).GetValue(this); } private Func<T, TResult> GetFuncFromParent<T, TResult>(string method) { MethodInfo getScaleFactor = baseType.GetMethod(method, flags); var func = (Func<T, TResult>)Delegate.CreateDelegate(typeof(Func<T, TResult>), this, getScaleFactor); return func; } private Func<T1, T2, T3, TResult> GetFuncFromParent<T1, T2, T3, TResult>(string method) { MethodInfo getScaleFactor = baseType.GetMethod(method, flags); var func = (Func<T1, T2, T3, TResult>)Delegate.CreateDelegate(typeof(Func<T1, T2, T3, TResult>), this, getScaleFactor); return func; } public override void DrawPage(Rectangle drawArea, Graphics graphics, int pageNumber) { int x = drawArea.X; int y = drawArea.Y; SizeF scale = this.getScaleFactors(drawArea); int startColumn = this.getStartColumn(pageNumber, drawArea, scale.Width); int endColumn = this.getEndColumn(startColumn, drawArea, scale.Width); int startRow = this.getStartRow(pageNumber, drawArea, scale.Height); int endRow = this.getEndRow(startRow, drawArea, scale.Height); int aggregateIndex = 0; for (int i = startRow; i <= endRow; i++) { x = drawArea.X; int maxHeight = 0; for (int j = startColumn; j <= endColumn; j++) { if (j == 0 && i == 0 && aggregateIndex < this.aggregates.Count) { while (aggregateIndex < this.aggregates.Count) { if (j >= this.columnWidths.Count || i >= this.rowHeights.Count) { break; } Rectangle aggregateElementRect = new Rectangle(x, y, this.columnWidths[j], this.rowHeights[i]); RadPrintElement aggregatePrintElement = this.GetAggregateDescriptorCell(aggregateIndex++); aggregatePrintElement.ScaleTransform = scale; RectangleF aggregateTransformedRect = aggregatePrintElement.GetTransformedBounds(aggregateElementRect); this.PrintElement(aggregatePrintElement, drawArea, aggregateElementRect, graphics); x += (int)Math.Floor(aggregateTransformedRect.Width); maxHeight = Math.Max(maxHeight, (int)Math.Floor(aggregateTransformedRect.Height)); if (aggregateIndex < this.aggregates.Count) { j++; } } continue; } Rectangle elementRect = new Rectangle(x, y, this.columnWidths[j], this.rowHeights[i]); RadPrintElement printElement = this.GetPrintElementForCell(i, j); printElement.ScaleTransform = scale; RectangleF transformedRect = printElement.GetTransformedBounds(elementRect); this.PrintElement(printElement, drawArea, elementRect, graphics); x += (int)Math.Floor(transformedRect.Width); maxHeight = Math.Max(maxHeight, (int)Math.Floor(transformedRect.Height)); } y += maxHeight; } } }
To reproduce: Create a localization provider following the article - http://wwwsit.telerik.com/help/winforms/pivotgrid-localization-localization.html. When you start the app you will see that the PivotAggregateP0ofP1 is not used. Workaround: Use the formatting events to format the needed values - http://wwwsit.telerik.com/help/winforms/pivotgrid-formatting-appearance.html
To reproduce: private void Form1_Load(object sender, EventArgs e) { this.ordersTableAdapter.Fill(this.nwindDataSet.Orders); LocalDataSourceProvider dataProvider = new LocalDataSourceProvider(); dataProvider.Culture = new System.Globalization.CultureInfo("ru-RU"); dataProvider.ItemsSource = this.ordersBindingSource; dataProvider.BeginInit(); dataProvider.RowGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "OrderDate", Step = DateTimeStep.Year, GroupComparer = new GroupNameComparer() }); dataProvider.RowGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "OrderDate", Step = DateTimeStep.Quarter, GroupComparer = new GroupNameComparer() }); dataProvider.RowGroupDescriptions.Add(new DateTimeGroupDescription() { PropertyName = "OrderDate", Step = DateTimeStep.Month, GroupComparer = new GroupNameComparer() }); dataProvider.ColumnGroupDescriptions.Add(new PropertyGroupDescription() { PropertyName = "EmployeeID", GroupComparer = new GrandTotalComparer() }); dataProvider.EndInit(); dataProvider.BeginInit(); dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription() { PropertyName = "Freight", AggregateFunction = AggregateFunctions.Sum }); dataProvider.AggregateDescriptions.Add(new PropertyAggregateDescription() { PropertyName = "Freight", AggregateFunction = AggregateFunctions.Average }); dataProvider.EndInit(); this.radPivotGrid1.DataProvider = dataProvider; } Workaround: CultureInfo russianCultureInfo = new System.Globalization.CultureInfo("ru-RU"); List<string> monthNames = DateTimeFormatInfo.CurrentInfo.MonthNames.ToList(); public Form1() { InitializeComponent(); this.radPivotGrid1.GroupDescriptorElementCreating += radPivotGrid1_GroupDescriptorElementCreating; } private void radPivotGrid1_GroupDescriptorElementCreating(object sender, GroupDescriptorElementCreatingEventArgs e) { if (e.GroupDescriptorElement.Text == "Month") { e.GroupDescriptorElement.FilterPopup.PopupOpening -= FilterPopupPopupOpening; e.GroupDescriptorElement.FilterPopup.PopupOpening += FilterPopupPopupOpening; } } private void FilterPopupPopupOpening(object sender, CancelEventArgs args) { PivotGroupFilterPopup popup = sender as PivotGroupFilterPopup; if (popup != null) { popup.TreeViewMenuItem.TreeElement.TreeView.NodeFormatting -= TreeView_NodeFormatting; popup.TreeViewMenuItem.TreeElement.TreeView.NodeFormatting += TreeView_NodeFormatting; } } private void TreeView_NodeFormatting(object sender, TreeNodeFormattingEventArgs e) { DateTime date; int monthIndex = monthNames.IndexOf(e.Node.Text); if (monthIndex > -1) { e.Node.Text = russianCultureInfo.DateTimeFormat.MonthNames[monthIndex]; } }