How to use Underlying data feature of RadPivotControl?

13 posts, 0 answers
  1. Deepak
    Deepak avatar
    20 posts
    Member since:
    May 2016

    Posted 23 May Link to this post

    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);
                    }
                }
            }
        }
    }

  2. Hristo
    Admin
    Hristo avatar
    1031 posts

    Posted 23 May Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Deepak
    Deepak avatar
    20 posts
    Member since:
    May 2016

    Posted 24 May in reply to Hristo Link to this post

    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();
     }

  4. Hristo
    Admin
    Hristo avatar
    1031 posts

    Posted 24 May Link to this post

    Hi Deepak,

    Thank you for writing back.

    Using the standard .NET 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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  5. Deepak
    Deepak avatar
    20 posts
    Member since:
    May 2016

    Posted 24 May in reply to Hristo Link to this post

    Hristo,

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

    Regards,

    Deepak

     

  6. Hristo
    Admin
    Hristo avatar
    1031 posts

    Posted 24 May Link to this post

    Hi Deepak,

    Thank you for writing back.

    The observed behavior appears to be a limitation of the standard .NET 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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  7. Deepak
    Deepak avatar
    20 posts
    Member since:
    May 2016

    Posted 24 May Link to this post

    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

     

     

  8. Deepak
    Deepak avatar
    20 posts
    Member since:
    May 2016

    Posted 24 May Link to this post

    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);
            }

  9. Hristo
    Admin
    Hristo avatar
    1031 posts

    Posted 25 May Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  10. Matthew
    Matthew avatar
    2 posts
    Member since:
    Sep 2017

    Posted 06 Sep Link to this post

    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?

     

     

  11. Hristo
    Admin
    Hristo avatar
    1031 posts

    Posted 07 Sep Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  12. Matthew
    Matthew avatar
    2 posts
    Member since:
    Sep 2017

    Posted 13 Sep Link to this post

    Thank you.

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

  13. Hristo
    Admin
    Hristo avatar
    1031 posts

    Posted 14 Sep Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top