How to use Underlying data feature of RadPivotControl?

[Deepak]

Hello,

I have created RadPivotControl dynamically and added it to a windows form. when i double click on data i cannot see the underlying data.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// Added by User
using Telerik.WinControls.UI;
using Telerik.Pivot.Core;
using System.Data;
using System.Windows.Forms;

namespace BusinessLogic
{
    public class RadControl
    {


        public RadPivotGrid fn_CreatePivotControl(DataTable inDt)
        {
            RadPivotGrid pivotGrid = new RadPivotGrid();
            pivotGrid.DataSource = inDt;


            pivotGrid.Name = "pivotFilteredData";
            pivotGrid.ThemeName = "EvalFormTheme";


            pivotGrid.ShowFilterArea = true;
            GroupNameComparer groupNameComparer = new GroupNameComparer();
            foreach (DataColumn col in inDt.Columns)
            {


                List<string> list = new List<string>();
                if (col.ColumnName.ToString().ToUpper() != "PV")
                {
                    list = inDt.AsEnumerable().Select(p => p.Field<String>(col.ColumnName.ToString())).Distinct().ToList();
                }

                string strColumnName = col.ColumnName;

                if (list.Count == 1)
                {
                    PropertyFilterDescription propertyFilterDescription1 = new PropertyFilterDescription();
                    propertyFilterDescription1.Condition = null;
                    propertyFilterDescription1.CustomName = null;
                    propertyFilterDescription1.PropertyName = strColumnName;
                    pivotGrid.FilterDescriptions.Add(propertyFilterDescription1);

                }
                else if (strColumnName.ToUpper() == "PV")
                {
                    PropertyAggregateDescription propertyAggregateDescription1 = new PropertyAggregateDescription();
                    SumAggregateFunction sumAggregateFunction1 = new SumAggregateFunction();
                    propertyAggregateDescription1.AggregateFunction = sumAggregateFunction1;
                    propertyAggregateDescription1.CustomName = null;
                    propertyAggregateDescription1.IgnoreNullValues = false;
                    propertyAggregateDescription1.PropertyName = strColumnName;
                    propertyAggregateDescription1.StringFormat = null;
                    propertyAggregateDescription1.StringFormatSelector = null;
                    propertyAggregateDescription1.TotalFormat = null;
                    pivotGrid.AggregateDescriptions.Add(propertyAggregateDescription1);

                }
                else if (strColumnName.ToUpper() == "PERIOD" || strColumnName.ToUpper() == "TIMESLICE")
                {
                    PropertyGroupDescription propertyGroupDescription = new PropertyGroupDescription();
                    propertyGroupDescription.CustomName = null;
                    // propertyGroupDescription.GroupComparer = groupNameComparer;
                    propertyGroupDescription.GroupFilter = null;
                    propertyGroupDescription.PropertyName = strColumnName;
                    propertyGroupDescription.ShowGroupsWithNoData = false;
                    propertyGroupDescription.SortOrder = Telerik.Pivot.Core.SortOrder.Ascending;
                    pivotGrid.ColumnGroupDescriptions.Add(propertyGroupDescription);
                }
                else if (strColumnName.ToUpper() == "SCENARIO")
                {
                    PropertyFilterDescription propertyFilterDescription1 = new PropertyFilterDescription();
                    propertyFilterDescription1.Condition = null;
                    propertyFilterDescription1.CustomName = null;
                    propertyFilterDescription1.PropertyName = strColumnName;
                    pivotGrid.FilterDescriptions.Add(propertyFilterDescription1);
                }
                else
                {
                    PropertyGroupDescription propertyGroupDescription = new PropertyGroupDescription();
                    propertyGroupDescription.CustomName = null;
                    propertyGroupDescription.GroupFilter = null;
                    propertyGroupDescription.PropertyName = strColumnName;
                    propertyGroupDescription.ShowGroupsWithNoData = false;
                    propertyGroupDescription.SortOrder = Telerik.Pivot.Core.SortOrder.Ascending;
                    pivotGrid.RowGroupDescriptions.Add(propertyGroupDescription);
                }
            }

            pivotGrid.ColumnGrandTotalsPosition = Telerik.WinControls.UI.TotalsPos.None;
            pivotGrid.ColumnsSubTotalsPosition = Telerik.WinControls.UI.TotalsPos.None;
            // row total
            pivotGrid.RowGrandTotalsPosition = Telerik.WinControls.UI.TotalsPos.None;
            pivotGrid.RowsSubTotalsPosition = Telerik.WinControls.UI.TotalsPos.None;


            foreach (PropertyGroupDescriptionBase groupDescription in pivotGrid.RowGroupDescriptions)
            {
                DistinctValuesProvider valuesProvider = ((IDistinctValuesDescription)groupDescription).GetDisctinctValuesProvider();
                List<object> distinctItems = new List<object>(valuesProvider.DisctinctValues);
            }

            pivotGrid.MouseDoubleClick += pivotGrid_MouseDoubleClick;
            return pivotGrid;
        }

        private void pivotGrid_MouseDoubleClick(object sender, System.Windows.Forms.MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                RadPivotGrid pivotGrid = (RadPivotGrid)sender;
                PivotCellElement cell = pivotGrid.ElementTree.GetElementAtPoint(e.Location) as PivotCellElement;
                if (cell != null)
                {
                    IGroup row = cell.Row.Group; IGroup column = cell.Column.Group;
                    LocalDataSourceProvider localProvider = pivotGrid.DataProvider as LocalDataSourceProvider;
                    localProvider.GetUnderlyingData(row, column);
                }
            }
        }
    }
}

[Hristo]

Hello Deepak,

Thank you for writing.

The GetUnderlyingData method provides information about the data source objects responsible for accumulating a particular value displayed in the pivot control. The results can be retrieved by handling the GetUnderlyingDataCompleted event. Please also pay attention that this event is raised from another thread and when you handle it in your form you need to invoke the controls you want to interact with: Drill Down.

If you want to display the underlying data in a grid view located on your form you can follow the example from our documentation. If you prefer the approach demonstrated in our demo application, showing the grid in a separate dialog, you can check the source control of the example. The examples are located in the installation folder of the suite, usually at C:\Program Files (x86)\Telerik\UI for WinForms R2 2017\Examples\QuickStart.

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo
Telerik by Progress

[Deepak]

Thanks Hristo.

I followed demo application approach but instead of RadGridView i want to use windows form GridView.

 

public void DisplayUnderlyingData(IEnumerable underlyingData)
{
       this.dataGridView1.DataSource = underlyingData;
       this.ShowDialog();
 }

[Hristo]

Hi Deepak,

Thank you for writing back.

Using the standard . grid should not be much different. The data source object is an IEnumarable instance so you should be able to bind the standard grid the same way. Please, note however that the sample is using summary rows which you might want to remove.

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo
Telerik by Progress

[Deepak]

Hristo,

I have already removed Summary rows and given IEnumerable data directly to DataGridView but cannot see data it in it.

Regards,

Deepak

 

[Hristo]

Hi Deepak,

Thank you for writing back.

The observed behavior appears to be a limitation of the standard . grid which fails to bind to a System.Collections.Generic.HashSet<T> which is the actual type of the retrieved result. A similar question has also been discussed here: https://stackoverflow.com/questions/32429085/how-to-use-hashset-as-datasource-in-datagridview-using-inotifypropertychanged.

In this case, I can suggest using RadGridView which you would be able to bind just as in the example project. If that is not suitable for your actual scenario you can bind the DataGridView this way:

private void provider_GetUnderlyingDataCompleted(object sender, Telerik.Pivot.Core.DrillDown.DrillDownCompletedEventArgs e)
{
    this.BeginInvoke((MethodInvoker)delegate
    {
        if (e.InnerExceptions.Count == 0)
        {
            HashSet<object> data = e.Result as HashSet<object>;
            this.dataGridView1.DataSource = data.ToList();
        }
        else if (e.InnerExceptions.Count == 1 && e.InnerExceptions[0].GetType() == typeof(InvalidOperationException))
        {
            RadMessageBox.ThemeName = this.ThemeName;
            RadMessageBox.Show("Please Refresh the Data Provider.");
        }
    });
}

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo
Telerik by Progress

[Deepak]

Hristo,

This didn't worked for me because data is in ItemArray. When i convert IEnumerable into data list, it converts collection of DataRows into Array.

 

 

Regards,

Deepak

 

 

[Deepak]

Hristo,

 

I am creating a table form IEnumerable collection and then adding data from collection. Below code working.

If this can be improved then please share it with me.

// Code

public void DisplayUnderlyingData(IEnumerable underlyingData, IWin32Window owner)
        {
            int counter = 0;
            DataTable dt = new DataTable(); // Create New table 

            foreach (DataRow row in underlyingData)
            {
                if (counter == 0)
                    dt = row.Table.Clone(); // Copy Table Structure

                // Add rows
                dt.Rows.Add(row.ItemArray.ToArray());
                counter++;
            }

            // Fill gridview data
            this.dataGridView1.DataSource = dt;

            this.ShowDialog(owner);
        }

[Hristo]

Hello Deepak,

Thank you for writing back.

The provided code snippet seems to be correct and you can go ahead with it. In case you keep experiencing issues you can send me your project so that I can test it locally.

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo
Progress Telerik

[Matthew]

I am having a similar problem and perhaps need help with implementing drill down functionality between a RadPivotGrid to a RadGridView, using a  DataSet binding.

I am drawing results from an SQL query into a DataSet and then using this as the DataSource for my PivotGrid.

In my GetUnderlyingDataCompleted Event I bind the RadGridView DataSource to the DrillDownCompletedEventArgs result.

But what I get is what is pictured in Deepak's error.png, that is rows of (RowError, RowState etc.)

How can I use the underlying automatically generated LocalDataSourceProvider to populate the GridView correctly?

 

 

[Hristo]

Hello Mathew,

Thank you for writing.

I have attached a project connecting to the Northwind data base. The approach used to data bind the control is similar to the one demonstrated here: http://docs.telerik.com/devtools/winforms/pivotgrid/populating-with-data/design-time-data-binding.

I have also attached a grid to the result of the returned underlying data. Can you please check how runs on your end. You can also check the solution suggested by Deepak. If you keep experiencing the issue please send us your project. For the purpose, you can use your ticket you have already opened.

I hope this helps. Should you have further questions please do not hesitate to write back.

Regards,
Hristo
Progress Telerik

[Matthew]

Thank you.

I was able to resolve the issue with the supplied source code and Deepak's suggestion. 

[Hristo]

Hi Mathew,

Thank you for the update.

I am glad that you have managed to resolve the issue. Please let me know if you need further assistance.

I hope this helps.

Regards,
Hristo
Progress Telerik