Finding which cells are selected in RadPivotGrid

[Grinden]

Hello,

I like the fact that you've implemented cell selection now, but I can't find any way of detecting which cells are selected, which seems fairly fundamental. Am I missing something?

Thanks,

Richard

[Polya]

Hi Richard,

Thank you for contacting us.

With our selection functionality for RadPivotGrid we've provided a CopySelection command that copies the current selection data in the clipboard: http://docs.telerik.com/devtools/wpf/controls/radpivotgrid/features/selection#selection-commands
You can access this data from the clipboard:

var selectionData = Clipboard.GetText(TextDataFormat.Text);

Hope this helps. If not I will kindly ask you to provide more information about your scenario and what you wish to achieve. As we're constantly trying to improve our control any feedback and user scenarios regarding our features is greatly appreciated.

Regards,
Polya
Telerik by Progress

[Grinden]

Hi Polya,

Thanks for getting back to me.The scenario I have is that I have a pivot grid which displays aggregated data from a database table via the QueryableDataProvider. The user can dynamically configure their view of this data in the pivot grid using the field chooser, and then select a cell (or cells) to drill down to see a limited subset of the actual records that generated that aggregated data in a data grid.

In this scenario, the copy/paste functionality is not much use. I was expecting to see a SelectedCells (or similar) property on the pivot grid, like you have on the RadGridView. This would give me a list of the selected cells, and a way to get the column and row to which they belong from those cells (hopefully an IGroup object so we can get the entire row/column hierarchy).

We can then use the selected row/column to build the query for the records to display in the datagrid.

I see that your Roadmap says "Pivot Grid Drill Down Support" for R1 2017. Will this do what I need?

Thanks,

Richard

[Polya]

Hello Richard,

Thank you for providing this feedback. It is greatly appreciated.

This functionality is not in the planned design for the drill-down data feature but we will take it into consideration for future improvement of the control.

Regards,
Polya
Telerik by Progress

[Grinden]

Hi Polya,

I've implemented some code to determine the row/columns of the selected cells. Obviously using reflection to do this is horrible and this is probably going to break if you change anything thing, but I don't think I had any other choice. Is there any better way to do this, and if not, any chance of a public interface to allow me to do this properly in a future version?

private void PivotGridControl_SelectionChanged(object sender, EventArgs e)
{
    var pivotGrid = sender as RadPivotGrid;
    var selectionOverlayProperty = typeof(RadPivotGrid).GetProperty("SelectionOverlay", BindingFlags.NonPublic | BindingFlags.Instance);

    var selectionProperty = typeof(SelectionOverlay).GetProperty("Selection", BindingFlags.NonPublic | BindingFlags.Instance);

    var selection = selectionProperty.GetValue(selectionOverlayProperty.GetValue(pivotGrid));
    var selectionType = selection.GetType();
    var getSelectedCells = selectionType.GetMethod("GetAllSelectedCells", BindingFlags.Public | BindingFlags.Instance);
    var getSelectedRows = selectionType.GetMethod("GetAllSelectedRows", BindingFlags.NonPublic | BindingFlags.Instance);
    var getSelectedColumns = selectionType.GetMethod("GetAllSelectedColumns", BindingFlags.NonPublic | BindingFlags.Instance);

    var selectedCells = getSelectedCells.Invoke(selection, null) as IEnumerable;
    var selectedRows = getSelectedRows.Invoke(selection, null) as IEnumerable<int>;
    var selectedColumns = getSelectedColumns.Invoke(selection, null) as IEnumerable<int>;

    ViewModel.ClearSelectedGroups();

    if (selectedCells != null)
    {
        var cellInfoType = selectedCells.GetType().GenericTypeArguments.First();
        var columnProperty = cellInfoType.GetProperty("X", BindingFlags.Public | BindingFlags.Instance);
        var rowProperty = cellInfoType.GetProperty("Y", BindingFlags.Public | BindingFlags.Instance);

        foreach (var cell in selectedCells)
        {
            var column = (int) columnProperty.GetValue(cell);
            var row = (int) rowProperty.GetValue(cell);
            var columnGroup = GetSelectedGroup(column, pivotGrid.IsColumnCollapsed, pivotGrid.ColumnGroups);
            var rowGroup = GetSelectedGroup(row, pivotGrid.IsRowCollapsed, pivotGrid.RowGroups);
            ViewModel.AddSelectedCell(new Coordinate(rowGroup, columnGroup));
        }
    }
    if (selectedRows != null)
    {

        foreach (var row in selectedRows)
        {
            var rowGroup = GetSelectedGroup(row, pivotGrid.IsRowCollapsed, pivotGrid.RowGroups);
            ViewModel.AddSelectedRow(rowGroup);
        }
    }

    if (selectedColumns != null)
    {

        foreach (var column in selectedColumns)
        {
            var columnGroup = GetSelectedGroup(column, pivotGrid.IsColumnCollapsed, pivotGrid.ColumnGroups);
            ViewModel.AddSelectedColumn(columnGroup);
        }
    }
}

private IGroup GetSelectedGroup(int pos, Func<IGroup, bool> groupCollapsedFunc, IEnumerable<IGroup> groups)
{
    int currentPos = 0;
    return GetSelectedGroup(pos, groupCollapsedFunc, groups, ref currentPos);
}


private IGroup GetSelectedGroup(int pos, Func<IGroup, bool> groupCollapsedFunc, IEnumerable<IGroup> groups, ref int currentPos)
{
    foreach (var group in groups)
    {
        if (group.HasGroups && !groupCollapsedFunc(group))
        {
            var result = GetSelectedGroup(pos, groupCollapsedFunc, group.Groups, ref currentPos);
            if (result != null)
            {
                return result;
            }
        }
        else
        {
            if (currentPos == pos)
            {
                return group;
            }
            currentPos++;
        }
    }
    return null;
}

[Polya]

Hi Richard,

Thank you for providing this code snippet.
It, however, will not always work because the X and Y properties provided by the PointInt do not always correspond to the index in the child collection of an IGroup. Depending on the Horizontal and Vertical layout and the count of row and column group descriptions the deviation might vary.

For example if we have two column descriptions, depending on the layout, the row index extracted from the selection will return as group information the row bellow it. You can test with our Pivot Selection QSF example - http://demos.telerik.com/wpf/. Selecting the "June" row bellow "Copy Holder" and the debugging the provided implementation we see that the group information extracted is for the "July" row instead.

Thank you again for providing feedback regarding the extension of our selection api. We will consider providing an api for extracting the groups participating in selection.

Regards,
Polya
Telerik by Progress

[Grinden]

Hi Polya,

Thanks very much for the feedback. You are entirely correct; the code above doesn't work with anything other than tabular mode for rows and columns. I've tweaked it slightly (see below) and now it seems to work for both. I downloaded your demo source code and added it to both your Pivot Selection and Layout demos and had a play with it. I couldn't get it to report the wrong row/column any more.

 

private void PivotGridControl_SelectionChanged(object sender, EventArgs e)
{
    var pivotGrid = sender as RadPivotGrid;
    if (pivotGrid == null)
    {
        return;
    }
    var selectionOverlayProperty = typeof(RadPivotGrid).GetProperty("SelectionOverlay", BindingFlags.NonPublic | BindingFlags.Instance);

    var selectionProperty = typeof(SelectionOverlay).GetProperty("Selection", BindingFlags.NonPublic | BindingFlags.Instance);

    var selection = selectionProperty.GetValue(selectionOverlayProperty.GetValue(pivotGrid, null), null);
    var selectionType = selection.GetType();
    var getSelectedCells = selectionType.GetMethod("GetAllSelectedCells", BindingFlags.Public | BindingFlags.Instance);
    var getSelectedRows = selectionType.GetMethod("GetAllSelectedRows", BindingFlags.NonPublic | BindingFlags.Instance);
    var getSelectedColumns = selectionType.GetMethod("GetAllSelectedColumns", BindingFlags.NonPublic | BindingFlags.Instance);

    var selectedCells = getSelectedCells.Invoke(selection, null) as IEnumerable;
    var selectedRows = getSelectedRows.Invoke(selection, null) as IEnumerable<int>;
    var selectedColumns = getSelectedColumns.Invoke(selection, null) as IEnumerable<int>;

    //ViewModel.ClearSelectedGroups();

    if (selectedCells != null)
    {
        var cellInfoType = selectedCells.GetType().GetGenericArguments().First();
        var columnProperty = cellInfoType.GetProperty("X", BindingFlags.Public | BindingFlags.Instance);
        var rowProperty = cellInfoType.GetProperty("Y", BindingFlags.Public | BindingFlags.Instance);

        foreach (var cell in selectedCells)
        {
            var column = (int)columnProperty.GetValue(cell, null);
            var row = (int)rowProperty.GetValue(cell, null);
            var columnGroup = GetSelectedGroup(column, pivotGrid.IsColumnCollapsed, pivotGrid.ColumnGroups, pivotGrid.HorizontalLayout == PivotLayoutType.Tabular);
            var rowGroup = GetSelectedGroup(row, pivotGrid.IsRowCollapsed, pivotGrid.RowGroups, pivotGrid.VerticalLayout == PivotLayoutType.Tabular);
            //ViewModel.AddSelectedCell(new Coordinate(rowGroup, columnGroup));
            MessageBox.Show($"Column: {columnGroup}, Row: {rowGroup}");
        }
    }
    if (selectedRows != null)
    {

        foreach (var row in selectedRows)
        {
            var rowGroup = GetSelectedGroup(row, pivotGrid.IsRowCollapsed, pivotGrid.RowGroups, pivotGrid.VerticalLayout == PivotLayoutType.Tabular);
            //ViewModel.AddSelectedRow(rowGroup);
            MessageBox.Show($"Row: {rowGroup}");
        }
    }

    if (selectedColumns != null)
    {

        foreach (var column in selectedColumns)
        {
            var columnGroup = GetSelectedGroup(column, pivotGrid.IsColumnCollapsed, pivotGrid.ColumnGroups, pivotGrid.HorizontalLayout == PivotLayoutType.Tabular);
            //ViewModel.AddSelectedColumn(columnGroup);
            MessageBox.Show($"Column: {columnGroup}");
        }
    }
}

private IGroup GetSelectedGroup(int pos, Func<IGroup, bool> groupCollapsedFunc, IEnumerable<IGroup> groups, bool tabularMode)
{
    int currentPos = 0;
    return GetSelectedGroup(pos, groupCollapsedFunc, groups, tabularMode, ref currentPos);
}


private IGroup GetSelectedGroup(int pos, Func<IGroup, bool> groupCollapsedFunc, IEnumerable<IGroup> groups, bool tabularMode, ref int currentPos)
{
    foreach (var group in groups)
    {
        if (group.HasGroups && !groupCollapsedFunc(group))
        {
            if (!tabularMode)
            {
                if (currentPos == pos)
                {
                    return group;
                }
                currentPos++;
            }
            var result = GetSelectedGroup(pos, groupCollapsedFunc, group.Groups, tabularMode, ref currentPos);
            if (result != null)
            {
                return result;
            }
        }
        else
        {
            if (currentPos == pos)
            {
                return group;
            }
            currentPos++;
        }
    }
    return null;
}

 

Best regards,

Richard

[Polya]

Hello Richard,

With the provided modifications the code seems to work as intended.
Thank you again for providing this code snippet.

Regards,
Polya
Telerik by Progress

[Grinden]

Hi,

I found that the code I was using earlier wasn't very performant when a user selected a large area, so I've refactored it a bit to find the columns and rows in a single pass over the row/column structure, rather than once per selected cell. It works much faster now.

In case anyone is interested, here is the code:

private void PivotGridControl_SelectionChanged(object sender, EventArgs e)
{
    var pivotGrid = sender as RadPivotGrid;
    if (pivotGrid == null)
    {
        return;
    }
    var selectionOverlayProperty = typeof(RadPivotGrid).GetProperty("SelectionOverlay", BindingFlags.NonPublic | BindingFlags.Instance);

    var selectionProperty = typeof(SelectionOverlay).GetProperty("Selection", BindingFlags.NonPublic | BindingFlags.Instance);

    var selection = selectionProperty.GetValue(selectionOverlayProperty.GetValue(pivotGrid));
    var selectionType = selection.GetType();
    var getSelectedCells = selectionType.GetMethod("GetAllSelectedCells", BindingFlags.Public | BindingFlags.Instance);
    var getSelectedRows = selectionType.GetMethod("GetAllSelectedRows", BindingFlags.NonPublic | BindingFlags.Instance);
    var getSelectedColumns = selectionType.GetMethod("GetAllSelectedColumns", BindingFlags.NonPublic | BindingFlags.Instance);

    var selectedCells = getSelectedCells.Invoke(selection, null) as IEnumerable;
    var selectedRows = getSelectedRows.Invoke(selection, null) as IEnumerable<int>;
    var selectedColumns = getSelectedColumns.Invoke(selection, null) as IEnumerable<int>;

    ViewModel.ClearSelectedGroups();
    var selectedCellList = new List<SelectedCell>();

    if (selectedCells != null)
    {
        var cellInfoType = selectedCells.GetType().GenericTypeArguments.First();
        var columnProperty = cellInfoType.GetProperty("X", BindingFlags.Public | BindingFlags.Instance);
        var rowProperty = cellInfoType.GetProperty("Y", BindingFlags.Public | BindingFlags.Instance);

        var columnIndex = new Dictionary<int, List<SelectedCell>>();
        var rowIndex = new Dictionary<int, List<SelectedCell>>();

        foreach (var cell in selectedCells)
        {
            var column = (int)columnProperty.GetValue(cell);
            var row = (int)rowProperty.GetValue(cell);
            var selectedCell = new SelectedCell() { Column = column, Row = row };
            selectedCellList.Add(selectedCell);
            AddToIndex(columnIndex, column, selectedCell);
            AddToIndex(rowIndex, row, selectedCell);
        }
        PopulateSelectedGroups(columnIndex, SelectedCell.SetColumnGroup, pivotGrid.IsColumnCollapsed, pivotGrid.ColumnGroups, pivotGrid.HorizontalLayout == PivotLayoutType.Tabular);
        PopulateSelectedGroups(rowIndex, SelectedCell.SetRowGroup, pivotGrid.IsRowCollapsed, pivotGrid.RowGroups, pivotGrid.VerticalLayout == PivotLayoutType.Tabular);

    }
    if (selectedRows != null)
    {
        var rowIndex = new Dictionary<int, List<SelectedCell>>();
        foreach (var row in selectedRows)
        {
            var selectedCell = new SelectedCell() { Row = row };
            selectedCellList.Add(selectedCell);
            AddToIndex(rowIndex, row, selectedCell);
        }
        PopulateSelectedGroups(rowIndex, SelectedCell.SetRowGroup, pivotGrid.IsRowCollapsed, pivotGrid.RowGroups, pivotGrid.VerticalLayout == PivotLayoutType.Tabular);
    }

    if (selectedColumns != null)
    {
        var columnIndex = new Dictionary<int, List<SelectedCell>>();
        foreach (var column in selectedColumns)
        {
            var selectedCell = new SelectedCell() { Column = column };
            selectedCellList.Add(selectedCell);
            AddToIndex(columnIndex, column, selectedCell);
        }
        PopulateSelectedGroups(columnIndex, SelectedCell.SetColumnGroup, pivotGrid.IsColumnCollapsed, pivotGrid.ColumnGroups, pivotGrid.VerticalLayout == PivotLayoutType.Tabular);
    }
    ViewModel.AddSelectedCells(selectedCellList);
}

private static void AddToIndex(Dictionary<int, List<SelectedCell>> columnIndex, int column, SelectedCell selectedCell)
{
    if (columnIndex.ContainsKey(column))
    {
        columnIndex[column].Add(selectedCell);
    }
    else
    {
        columnIndex[column] = new List<SelectedCell>() { selectedCell };
    }
}


private void PopulateSelectedGroups(Dictionary<int, List<SelectedCell>> selectedGroupsIndex, Action<SelectedCell, IGroup> setGroupAction, Func<IGroup, bool> groupCollapsedFunc, IEnumerable<IGroup> groups, bool tabularMode)
{
    int currentPos = 0;
    PopulateSelectedGroups(selectedGroupsIndex, setGroupAction, groupCollapsedFunc, groups, tabularMode, ref currentPos);
}


private void PopulateSelectedGroups(Dictionary<int, List<SelectedCell>> selectedGroupsIndex, Action<SelectedCell, IGroup> setGroupAction, Func<IGroup, bool> groupCollapsedFunc, IEnumerable<IGroup> groups, bool tabularMode, ref int currentPos)
{
    foreach (var group in groups)
    {
        if (selectedGroupsIndex.Count == 0)
        {
            return;
        }
        if (group.HasGroups && !groupCollapsedFunc(group))
        {
            if (!tabularMode)
            {
                SetGroups(selectedGroupsIndex, setGroupAction, currentPos, group);
                currentPos++;
            }
            PopulateSelectedGroups(selectedGroupsIndex, setGroupAction, groupCollapsedFunc, group.Groups, tabularMode, ref currentPos);
        }
        else
        {
            SetGroups(selectedGroupsIndex, setGroupAction, currentPos, group);
            currentPos++;
        }
    }
}

private static void SetGroups(Dictionary<int, List<SelectedCell>> selectedGroupsIndex, Action<SelectedCell, IGroup> setGroupAction, int currentPos, IGroup group)
{
    if (selectedGroupsIndex.ContainsKey(currentPos))
    {
        foreach (var cell in selectedGroupsIndex[currentPos])
        {
            setGroupAction(cell, group);
        }
        selectedGroupsIndex.Remove(currentPos);
    }
}

public class SelectedCell
{
    public int Column { get; set; } = -1;
    public int Row { get; set; } = -1;
    public IGroup ColumnGroup { get; set; }
    public IGroup RowGroup { get; set; }

    public static void SetColumnGroup(SelectedCell cell, IGroup group)
    {
        cell.ColumnGroup = group;
    }
    public static void SetRowGroup(SelectedCell cell, IGroup group)
    {
        cell.RowGroup = group;
    }
}

[Grinden]

I should really test these things slightly better before I post them on here. There is a copy and paste error in the "if (selectedColumns != null)" block.

In the call to PopulateSelectedGroups the last parameter should check the horizontal layout, not the vertical.

if (selectedColumns != null)
{
    var columnIndex = new Dictionary<int, List<FactTableViewModel.SelectedCell>>();
    foreach (var column in selectedColumns)
    {
        var selectedCell = new FactTableViewModel.SelectedCell() { Column = column };
        selectedCellList.Add(selectedCell);
        AddToIndex(columnIndex, column, selectedCell);
    }
    PopulateSelectedGroups(columnIndex, FactTableViewModel.SelectedCell.SetColumnGroup, pivotGrid.IsColumnCollapsed, pivotGrid.ColumnGroups, pivotGrid.HorizontalLayout == PivotLayoutType.Tabular);
}