[Solved] Loop through RadPivotGrid's cells, including sub or grand total cells

[Asuman]

Hi,

I need to loop radpivotgrid's all cells and to get cell, row and column header values, including sub or grand total cells.

Thanks...

[Polya]

Hello Asuman,

In the current implementation of RadPivotGrid there is no out of the box way to extract the RadPivotGrid's DataProvider results.
Could you please give us more information on what you are trying to achieve, when do you need this information and also which DataProvider are you using?
We have a feature request for implementing a DrillDown functionality for RadPivotGrid with LocalDataSourceProvider. You can find it logged here: http://feedback.telerik.com/Project/143/Feedback/Details/113499-implement-drilldown-functionality-for-radpivotgrid-for-localdatasourceprovider

Regards,
Polya
Telerik

[Asuman]

Hi Polya,

I am using LocalDataSourceProvider to show data on RadPivotGrid. At some pages I want to show just a few cell's data as summary information stickers, not whole RadPivotGrid's cells. For this, I am saving cell's row and column sequence to DB to get data that the sequence couple corresponds. I don't want to save cell's value instead of sequence positions, because the real data may be changed in every query. My problem is that, how can I access a data value from sequence positions, without showing PivotGrid?

 

Best Regards...

[Polya]

Hi Asuman,

As there is no out of the box way to extract the groups information from the LocalDataSourceProvider results I can only suggest using either export to excel (and getting the results from the export) or using 
the PivotChartViewModel (and getting the results from its SeriesSource collection).

We have a great example demonstrating the first approach. You can find it here: http://demos.telerik.com/silverlight/#PivotGrid/Export

Regarding the second approach - you can create a PivotChartViewModel using your DataProvider in the LocalDataSourceProvider.StatusChanged event handler and get the results from the PivotChartViewModel.SeriesSource property:

private void LocalDataSourceProvider_StatusChanged(object sender, DataProviderStatusChangedEventArgs e)
{
    if (e.NewStatus == DataProviderStatus.Ready && e.OldStatus == DataProviderStatus.RetrievingData)
    {
        var provider = sender as LocalDataSourceProvider;
        PivotChartViewModel vm = new PivotChartViewModel() { DataProvider = provider };
        var results = vm.SeriesSource;
    }
}

Hope this helps.

Regards,
Polya
Telerik

[Asuman]

Hi polya,

Thanks for your patience end answers. I hope this will be last question of mine: At the code below is it possible to get property name and value of the fields bound to row and column groups (hierarchically) of the selected cell?

 

void radPivotRapor_MouseLeftButtonUp(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            var clickedElement = e.OriginalSource as FrameworkElement;
            if (clickedElement == null) return;

            var cellData = clickedElement.DataContext as Telerik.Windows.Controls.Pivot.CellData;
            if (cellData == null) return;

            var rowItem = cellData.RowItem as Telerik.Pivot.Core.IGroup;
            var columnItem = cellData.ColumnItem as Telerik.Pivot.Core.IGroup;
            var cellDataValue = cellData.Data;
        }

[Polya]

Hello Asuman,

To get the value of the cell we can use the Value property of the cellDataAggregate:

var cellDataValue = cellData.Data as CellAggregateValue;
var value = cellDataValue.Value;

To get all the hierarchy of groups for rows/columns we can, for example, create a method that loops all parents of the current cellData and flattens them into a list:

var rowItem = cellData.RowItem as IGroup;
var columnItem = cellData.ColumnItem as IGroup;

ExtractItemNames(rowItem, ref rowItems);
ExtractItemNames(columnItem, ref columnItems);

// .....................
private void ExtractItemNames(IGroup group, ref List<string> resultList)
{
    if (group == null)
    {
        return;
    }

    resultList.Add(group.Name.ToString());

    ExtractItemNames(group.Parent, ref resultList);
}

Regards,
Polya
Telerik

[Asuman]

Hi Polya,

Thank you for your answer. But I had meant PropertyGroupDescription. Can we access PropertyGroupDescription of rows and columns (hierarchically) via clicked cell?

[Polya]

Hi Asuman,

From the CellData there is no way to receive information about the DataProvider group descriptions because it represents the grouped, aggregated and filtered result of the DataProvider over the Source and not its structure.
However, you can extract the LocalDataSourceProvider from the RadPivotGrid, which is a parent of the clicked cell. Then you can, similarly to the ExtractIemNames method get all the descriptions for rows/columns:

private void radPivotGrid_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
{
    var clickedElement = e.OriginalSource as FrameworkElement;
    var dataProvider = FindParent<RadPivotGrid>(clickedElement).DataProvider as LocalDataSourceProvider;

    var cellData = clickedElement.DataContext as CellData;

    if (cellData != null)
    {
        var aggrValue = cellData.Data as CellAggregateValue;

        var rowItem = cellData.RowItem as IGroup;
        var columnItem = cellData.ColumnItem as IGroup;

        List<PropertyGroupDescriptionBase> rowDescriptions = new List<PropertyGroupDescriptionBase>();
        List<PropertyGroupDescriptionBase> columnDescriptions = new List<PropertyGroupDescriptionBase>();

        ExtractRowGroupDescriptions(dataProvider, rowItem, ref rowDescriptions);
        ExtractColumnGroupDescriptions(dataProvider, rowItem, ref columnDescriptions);

        // .....................
    }
    // .....................
}

public static T FindParent<T>(DependencyObject child) where T : DependencyObject
{
    DependencyObject parentObject = VisualTreeHelper.GetParent(child);

    if (parentObject == null) return null;

    T parent = parentObject as T;
    if (parent != null)
        return parent;
    else
        return FindParent<T>(parentObject);
}

private void ExtractColumnGroupDescriptions(LocalDataSourceProvider dataProvider, IGroup group, ref List<PropertyGroupDescriptionBase> resultList)
{
    if (group == null)
    {
        return;
    }

    var currentLevelDescription = dataProvider.ColumnGroupDescriptions.ElementAtOrDefault(group.Level);

    resultList.Add(currentLevelDescription);

    ExtractColumnGroupDescriptions(dataProvider, group.Parent, ref resultList);
}

private void ExtractRowGroupDescriptions(LocalDataSourceProvider dataProvider, IGroup group, ref List<PropertyGroupDescriptionBase> resultList)
{
    if (group == null)
    {
        return;
    }

    var currentLevelDescription = dataProvider.RowGroupDescriptions.ElementAtOrDefault(group.Level);

    resultList.Add(currentLevelDescription);

    ExtractRowGroupDescriptions(dataProvider, group.Parent, ref resultList);
}

Hope this helps.

Regards,
Polya
Telerik