Hello,

I am using the pivot grid control would like to know if it is possible to add a start and end to the grouping?

My pivot grid looks like this: I would like the user to be able to specify a start and end for the grouping.

Excel has this option:

How can I replicate this functionality?

As a user I want to hide the Grand Totals for specific groups:

The group description now has AutoShowSubTotals property determining whether subtotals will be generated for the description:

this.radPivotGrid1.RowGroupDescriptions.Add(new DateTimeGroupDescription
{
    PropertyName = "OrderDate",
    Step = DateTimeStep.Quarter,
    GroupComparer = new GroupNameComparer(),
    AutoShowSubTotals = false
});

Check the attached screenshot:

Hour, minute, second, if possible also week

Workaround:

Use the new PivotGridSpreadExport

To reproduce: 

- Set the ExportFlatData property to true

- Export the grid using PivotExportToExcelML

How to reproduce: see the attached video

Workaround:
public partial class Form2 : Form
{
    public Form2()
    {
        InitializeComponent();

        this.radPivotFieldList1.DragDropService = new CustomPivotFieldListDragDropService(this.radPivotFieldList1);
    }

    private void Form2_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'nwindDataSet.Orders' table. You can move, or remove it, as needed.
        this.ordersTableAdapter.Fill(this.nwindDataSet.Orders);

    }
}

public class CustomPivotFieldListDragDropService : PivotFieldListDragDropService
{
    private RadPivotFieldList fieldList;
    private Telerik.WinControls.UI.PivotFieldList.FieldPayload payload;

    public CustomPivotFieldListDragDropService(RadPivotFieldList fieldList)
        : base(fieldList)
    {
        this.fieldList = fieldList;
    }

    protected override void PerformStart()
    {
        base.PerformStart();

        Telerik.WinControls.UI.PivotFieldList.IField draggedField = this.GetField(this.Context);

        if (draggedField == null)
        {
            this.Stop(false);
            return;
        }

        this.payload = new Telerik.WinControls.UI.PivotFieldList.FieldPayload(draggedField);
    }

    protected override void OnPreviewDragOver(RadDragOverEventArgs e)
    {
        RadElement targetElement = this.DropTarget as RadElement;
        Telerik.WinControls.UI.PivotFieldList.IField targetField = this.GetField(targetElement);

        if (targetElement != null && targetElement.ElementTree.Control == this.fieldList.ReportFiltersControl && this.payload != null)
        {
            foreach (Telerik.WinControls.UI.PivotFieldList.IField field in this.fieldList.ViewModel.Filters)
            {
                if (field.FieldInfo == payload.DraggedField.FieldInfo &&  this.Context is TreeNodeElement ))
                {
                    e.CanDrop = false;
                    this.payload.SetDestination(null);
                    this.payload.RemoveFromSource = false;

                    return;
                }
            }
        }

        base.OnPreviewDragOver(e);
    }

    private Telerik.WinControls.UI.PivotFieldList.IField GetField(object context)
    {
        RadListVisualItem listItem = context as RadListVisualItem;
        TreeNodeElement treeItem = context as TreeNodeElement;
        PivotFieldListItemButton button = context as PivotFieldListItemButton;

        if (button != null)
        {
            listItem = button.Owner;
        }

        Telerik.WinControls.UI.PivotFieldList.IField field1 = null, field2 = null;
        if (listItem != null && listItem.Data != null)
        {
            field1 = listItem.Data.DataBoundItem as Telerik.WinControls.UI.PivotFieldList.IField;
        }

        if (treeItem != null && treeItem.Data != null)
        {
            field2 = treeItem.Data.DataBoundItem as Telerik.WinControls.UI.PivotFieldList.IField;
        }

        return field1 ?? field2;
    }
}

Workaround: If possible add the label filters with code
PropertyGroupDescription dateGroupDescription = new PropertyGroupDescription();
dateGroupDescription.PropertyName = "Date";

LabelGroupFilter filter = new LabelGroupFilter();
ComparisonCondition condition = new ComparisonCondition
{
    Condition = Telerik.Pivot.Core.Filtering.Comparison.IsGreaterThan,
    Than = new DateTime(2010, 6, 6)
};
filter.Condition = condition;
dateGroupDescription.GroupFilter = filter;

this.provider.RowGroupDescriptions.Add(dateGroupDescription);

The issue may manifest if aggregate descriptions are added on the pivot`s column axis and if their member does not return data.

I came across an exception when trying to use the Report Filter area with data that contains blank (DBNull) values.  It looks like the control tries to build a distinct list of values to show in the filter's drop-down, but throws an exception if one of the values is blank.  I attached a ZIP file with a Visual Studio project that demonstrates the issue, and included a Word file to describe how to reproduce the error.  

Any assistance is greatly appreciated.  Thank you.


Drew White
Developer
CRMCulture, LLC

Email:         dwhite@CRMCulture.com
Web:          http://www.crmculture.com

How to reproduce: 
Me.RadPivotGrid1.DataSource = Nothing

Workaround: 
DirectCast(Me.RadPivotGrid1.DataProvider, LocalDataSourceProvider).ItemsSource = Nothing
Dim viewModel = Me.RadPivotGrid1.PivotGridElement.GetType().GetField("viewModel", BindingFlags.Instance Or BindingFlags.NonPublic).GetValue(Me.RadPivotGrid1.PivotGridElement)
viewModel.GetType().GetProperty("DataProvider").SetValue(viewModel, Nothing)

How to reproduce: check the attached project and screenshots, the correct items are shown in screenshot correct-items-calculated-field.jpg

Workaround: 
public partial class Form1 : Form
{
    private LocalDataSourceProvider provider;

    public Form1()
    {
        InitializeComponent();

        //Setup pivot and add calculated field

        //Workaround
        this.radPivotFieldList1.ValuesControl.CreatingVisualListItem += ValuesControl_CreatingVisualListItem;
    }

    private void ValuesControl_CreatingVisualListItem(object sender, CreatingVisualListItemEventArgs args)
    {
        args.VisualItem = new MyPivotFieldListVisualItem(this.radPivotFieldList1.ViewModel);
    }
}

public class MyPivotFieldListVisualItem : PivotFieldListVisualItem
{
    FieldListViewModel viewModel;

    public MyPivotFieldListVisualItem(FieldListViewModel viewModel)
        : base(viewModel)
    {
        this.viewModel = viewModel;
    }

    protected override void UpdateContextMenu()
    {
        base.UpdateContextMenu();

        PivotFieldListItemButton button = (PivotFieldListItemButton)typeof(PivotFieldListVisualItem).GetField("button", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).GetValue(this);
        string providerName = this.viewModel.DataProvider != null ? this.viewModel.DataProvider.GetType().FullName : String.Empty;
        if (this.Data == null)
        {
            return;
        }

        if (this.Data.DataBoundItem is Value && !(providerName.Contains("Xmla") || providerName.Contains("Adomd")))
        {
            for (int i = 0; i < 5; i++)
            {
                button.Items.RemoveAt(i);
            }
        }
    }
}

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.

How to reproduce: check the attached project and select an item from the drop-down list

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