wpf-401123-controls-and-libraries-pivot-grid-examples-menu-how-to-add-menu-item-to-display-top-5.md
This example demonstrates how to handle the PivotGridControl.PopupMenuShowing event to create a context menu item with a command that displays the Top N Values for the selected row or column.
To determine whether the context menu appears for the Field Value, cast the PopupMenuShowingEventArgs.TargetElement to the FieldValueElement type. If the cast is successful, the resulting object’s DataAreaElement.ElementData property allows you to access the FieldValueElementData object that contains information on the target field.
Create a new bar item of the BarCheckItem type and store the FieldValueElementData object in the Tag property. Assign a custom handler to the BarCheckItem.CheckedChanged event and add the bar item to the PopupMenuShowingEventArgs.Customizations collection.
The menu item’s BarCheckItem.CheckedChanged event handler retrieves the saved FieldValueElementData object from the bar item’s Tag property. Subsequently, the event handler uses the FieldValueElementData.PivotGrid property to get access to the PivotGridControl and call the PivotGridControl.GetFieldsByArea method to get all fields by area. The target field and its characteristics are obtained from the FieldValueElementData.Field, FieldValueElementData.DataField, and FieldValueElementData.MaxIndex properties. The PivotGridField.TopValueCount property value specifies the number of values to display.
View Example: How to Implement a Context Menu Action to Display Top 5 Values in a Column or Row
using DevExpress.DataAccess.Excel;
using DevExpress.Xpf.Bars;
using DevExpress.Xpf.PivotGrid;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows;
namespace ContextMenuToShowTopN_Example
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : DevExpress.Xpf.Core.ThemedWindow
{
ExcelDataSource xlDataSource = new ExcelDataSource();
public MainWindow()
{
InitializeComponent();
InitializeExcelDataSource();
InitializePivot();
}
private void InitializeExcelDataSource()
{
xlDataSource.FileName = "SalesPerson.xlsx";
ExcelWorksheetSettings worksheetSettings = new ExcelWorksheetSettings("Data");
xlDataSource.SourceOptions = new ExcelSourceOptions(worksheetSettings);
xlDataSource.SourceOptions.SkipEmptyRows = false;
xlDataSource.SourceOptions.UseFirstRowAsHeader = true;
xlDataSource.Fill();
}
private void InitializePivot()
{
pivotGridControl1.DataSource = xlDataSource;
pivotGridControl1.RetrieveFields();
pivotGridControl1.Fields["Sales Person"].Area = FieldArea.RowArea;
pivotGridControl1.Fields["CategoryName"].Area = FieldArea.RowArea;
pivotGridControl1.Fields["Quantity"].Area = FieldArea.DataArea;
pivotGridControl1.Fields["OrderDate"].Area = FieldArea.ColumnArea;
pivotGridControl1.Fields["OrderDate"].GroupInterval = FieldGroupInterval.DateYear;
}
private void pivotGridControl1_PopupMenuShowing(object sender, PopupMenuShowingEventArgs e)
{
FieldValueElement fvElement = e.TargetElement as FieldValueElement;
if (fvElement == null) return;
FieldValueElementData valueItem = fvElement.ElementData as FieldValueElementData;
if (valueItem.IsLastLevelItem)
{
string itemCaption = string.Format("Top 5 Values in this {0}", valueItem.IsColumn ? "Column" : "Row");
BarCheckItem item = new BarCheckItem { Content = itemCaption };
if (IsTopFiveValuesApplied(valueItem))
item.IsChecked = true;
item.CheckedChanged += Item_CheckedChanged;
item.Tag = valueItem;
e.Customizations.Add(new AddBarItemAction { Item = item });
}
}
private void Item_CheckedChanged(object sender, ItemClickEventArgs e)
{
BarCheckItem item = sender as BarCheckItem;
FieldValueElementData elementData = e.Item.Tag as FieldValueElementData;
if ((bool)item.IsChecked)
SetTopFiveValues(elementData);
else
ResetTopFiveValues(elementData.PivotGrid);
}
private static void SetTopFiveValues(FieldValueElementData valueItem)
{
var sortConditions = GetConditions(valueItem);
valueItem.PivotGrid.BeginUpdate();
ResetTopFiveValues(valueItem.PivotGrid);
valueItem.PivotGrid.GetFieldsByArea(valueItem.IsColumn ? FieldArea.RowArea : FieldArea.ColumnArea)
.ForEach(f => {
f.SortOrder = FieldSortOrder.Descending;
f.SortByField = valueItem.DataField;
f.SortByConditions.Clear();
f.SortByConditions.AddRange(sortConditions.Select(c => new SortByCondition(c.Key, c.Value)));
f.TopValueCount = 5;
f.TopValueShowOthers = true;
});
valueItem.PivotGrid.EndUpdate();
}
private static bool IsTopFiveValuesApplied(FieldValueElementData valueItem)
{
var fields = valueItem.PivotGrid.GetFieldsByArea(valueItem.IsColumn ? FieldArea.RowArea : FieldArea.ColumnArea);
if (fields.Count == 0)
return false;
var conditions = GetConditions(valueItem);
foreach (PivotGridField f in fields)
{
if (f.TopValueCount != 5)
return false;
if (conditions.Count != f.SortByConditions.Count)
return false;
for (int i = 0; i < conditions.Count; i++)
{
if (f.SortByConditions[i].Field != conditions[i].Key ||
f.SortByConditions[i].Value != conditions[i].Value)
return false;
}
}
return true;
}
private static void ResetTopFiveValues(PivotGridControl pivotGrid)
{
pivotGrid.BeginUpdate();
var fields = pivotGrid.GetFieldsByArea(FieldArea.ColumnArea).Union(pivotGrid.GetFieldsByArea(FieldArea.RowArea));
foreach (var f in fields)
{
f.SortByField = null;
f.SortByConditions.Clear();
f.TopValueCount = 0;
f.TopValueShowOthers = false;
}
pivotGrid.EndUpdate();
}
private static List<KeyValuePair<PivotGridField, object>> GetConditions(FieldValueElementData valueItem)
{
var fields = valueItem.PivotGrid.GetFieldsByArea(valueItem.IsColumn ? FieldArea.ColumnArea : FieldArea.RowArea)
.Where(f => f.AreaIndex <= valueItem.Field.AreaIndex);
return fields.
Select(f => new KeyValuePair<PivotGridField, object>(f,
valueItem.PivotGrid.GetFieldValue(f, valueItem.MinIndex)
)).ToList();
}
private void PivotGridControl1_Loaded(object sender, RoutedEventArgs e)
{
pivotGridControl1.BestFit(FieldArea.ColumnArea);
}
}
}
Imports DevExpress.DataAccess.Excel
Imports DevExpress.Xpf.Bars
Imports DevExpress.Xpf.PivotGrid
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.Windows
Namespace ContextMenuToShowTopN_Example
''' <summary>
''' Interaction logic for MainWindow.xaml
''' </summary>
Partial Public Class MainWindow
Inherits DevExpress.Xpf.Core.ThemedWindow
Private xlDataSource As New ExcelDataSource()
Public Sub New()
InitializeComponent()
InitializeExcelDataSource()
InitializePivot()
End Sub
Private Sub InitializeExcelDataSource()
xlDataSource.FileName = "SalesPerson.xlsx"
Dim worksheetSettings As New ExcelWorksheetSettings("Data")
xlDataSource.SourceOptions = New ExcelSourceOptions(worksheetSettings)
xlDataSource.SourceOptions.SkipEmptyRows = False
xlDataSource.SourceOptions.UseFirstRowAsHeader = True
xlDataSource.Fill()
End Sub
Private Sub InitializePivot()
pivotGridControl1.DataSource = xlDataSource
pivotGridControl1.RetrieveFields()
pivotGridControl1.Fields("Sales Person").Area = FieldArea.RowArea
pivotGridControl1.Fields("CategoryName").Area = FieldArea.RowArea
pivotGridControl1.Fields("Quantity").Area = FieldArea.DataArea
pivotGridControl1.Fields("OrderDate").Area = FieldArea.ColumnArea
pivotGridControl1.Fields("OrderDate").GroupInterval = FieldGroupInterval.DateYear
End Sub
Private Sub pivotGridControl1_PopupMenuShowing(ByVal sender As Object, ByVal e As PopupMenuShowingEventArgs)
Dim fvElement As FieldValueElement = TryCast(e.TargetElement, FieldValueElement)
If fvElement Is Nothing Then
Return
End If
Dim valueItem As FieldValueElementData = TryCast(fvElement.ElementData, FieldValueElementData)
If valueItem.IsLastLevelItem Then
Dim itemCaption As String = String.Format("Top 5 Values in this {0}",If(valueItem.IsColumn, "Column", "Row"))
Dim item As BarCheckItem = New BarCheckItem With {.Content = itemCaption}
If IsTopFiveValuesApplied(valueItem) Then
item.IsChecked = True
End If
AddHandler item.CheckedChanged, AddressOf Item_CheckedChanged
item.Tag = valueItem
e.Customizations.Add(New AddBarItemAction With {.Item = item})
End If
End Sub
Private Sub Item_CheckedChanged(ByVal sender As Object, ByVal e As ItemClickEventArgs)
Dim item As BarCheckItem = TryCast(sender, BarCheckItem)
Dim elementData As FieldValueElementData = TryCast(e.Item.Tag, FieldValueElementData)
If CBool(item.IsChecked) Then
SetTopFiveValues(elementData)
Else
ResetTopFiveValues(elementData.PivotGrid)
End If
End Sub
Private Shared Sub SetTopFiveValues(ByVal valueItem As FieldValueElementData)
Dim sortConditions = GetConditions(valueItem)
valueItem.PivotGrid.BeginUpdate()
ResetTopFiveValues(valueItem.PivotGrid)
valueItem.PivotGrid.GetFieldsByArea(If(valueItem.IsColumn, FieldArea.RowArea, FieldArea.ColumnArea)).ForEach(Sub(f)
f.SortOrder = FieldSortOrder.Descending
f.SortByField = valueItem.DataField
f.SortByConditions.Clear()
f.SortByConditions.AddRange(sortConditions.Select(Function(c) New SortByCondition(c.Key, c.Value)))
f.TopValueCount = 5
f.TopValueShowOthers = True
End Sub)
valueItem.PivotGrid.EndUpdate()
End Sub
Private Shared Function IsTopFiveValuesApplied(ByVal valueItem As FieldValueElementData) As Boolean
Dim fields = valueItem.PivotGrid.GetFieldsByArea(If(valueItem.IsColumn, FieldArea.RowArea, FieldArea.ColumnArea))
If fields.Count = 0 Then
Return False
End If
Dim conditions = GetConditions(valueItem)
For Each f As PivotGridField In fields
If f.TopValueCount <> 5 Then
Return False
End If
If conditions.Count <> f.SortByConditions.Count Then
Return False
End If
For i As Integer = 0 To conditions.Count - 1
If f.SortByConditions(i).Field IsNot conditions(i).Key OrElse f.SortByConditions(i).Value IsNot conditions(i).Value Then
Return False
End If
Next i
Next f
Return True
End Function
Private Shared Sub ResetTopFiveValues(ByVal pivotGrid As PivotGridControl)
pivotGrid.BeginUpdate()
Dim fields = pivotGrid.GetFieldsByArea(FieldArea.ColumnArea).Union(pivotGrid.GetFieldsByArea(FieldArea.RowArea))
For Each f In fields
f.SortByField = Nothing
f.SortByConditions.Clear()
f.TopValueCount = 0
f.TopValueShowOthers = False
Next f
pivotGrid.EndUpdate()
End Sub
Private Shared Function GetConditions(ByVal valueItem As FieldValueElementData) As List(Of KeyValuePair(Of PivotGridField, Object))
Dim fields = valueItem.PivotGrid.GetFieldsByArea(If(valueItem.IsColumn, FieldArea.ColumnArea, FieldArea.RowArea)).Where(Function(f) f.AreaIndex <= valueItem.Field.AreaIndex)
Return fields.Select(Function(f) New KeyValuePair(Of PivotGridField, Object)(f, valueItem.PivotGrid.GetFieldValue(f, valueItem.MinIndex))).ToList()
End Function
Private Sub PivotGridControl1_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
pivotGridControl1.BestFit(FieldArea.ColumnArea)
End Sub
End Class
End Namespace