Hello PPilev,

Using the latest 2011.2 controls.  Here is the method in the user control to convert the DataTable to an Ilist for the data selector and pivot grid:

public void SetFlatDatasource(DataTable dt)
{
    DynamicTypeBuilder typeBuilder = new DynamicTypeBuilder
    {
        DynamicAssemblyName = "MyAssembly",
        DynamicTypeName = "Pane"
    };

    IList properties = new List();

    foreach (DataColumn column in dt.Columns)
    {
        DynamicTypePropertyInfo propertyInfo = new DynamicTypePropertyInfo
        {
            PropertyName = column.ColumnName,
            PropertyType = column.DataType
        };
        properties.Add(propertyInfo);
    }

    Type dynamicType = typeBuilder.GenerateType(properties);
    Type listType = typeof(List<>);
    Type genericListType = listType.MakeGenericType(dynamicType);

    IList list = (IList)Activator.CreateInstance(genericListType);

    foreach (DataRow dataRow in dt.Rows)
    {
        object myDynamicInstance = Activator.CreateInstance(dynamicType);

        foreach (DataColumn column in dt.Columns)
        {
            PropertyInfo propertyVal = dynamicType.GetProperty(column.ColumnName);

            if (dataRow[column] != DBNull.Value)
            {
                propertyVal.SetValue(myDynamicInstance, dataRow[column], null);
            }
        }

        list.Add(myDynamicInstance);
    }

    FlatDataSource flatDataSource = new FlatDataSource()
    {
        ItemsSource = list,
        Cube = DataSourceBase.GenerateInitialCube("Pane"),
        // if you know the names of demensions you want to be in rows and columns you can define them here
        Columns = DataSourceBase.GenerateInitialItems("[Columns]"),
        Rows = DataSourceBase.GenerateInitialItems("[Row]"),
        Measures = DataSourceBase.GenerateInitialItems("Value")
    };

    dataSelector.DataSource = flatDataSource;
    pivotGrid.DataSource = flatDataSource;
}

When you say initialization, do you mean the xaml? If yes, how can we post xaml in this forum? I will say it does not contain anything special. AllowCompactLayout is set to True, Height and Width are set to Auto...there is really nothing noteworthy.

Simple question, should this control perform well with a row count of say 2,000 with 8 columns?

I replicated the memory issue by commenting the hierarchy code from the CreateExcelDataSource method above.  Turns out adding rows as collapsed heirarchies "All" and manually expanding them after they are loaded does not consume the memory when having no heirarchies...?

Thanks Plamen, I am scrambling to find examples that fit my environment.  All examples I see have pre-canned data model classes, something that is not real-world to us.  I need to see how I can setup hierarchies on my flatdatasource that is the product of a DataTable produced from a parameteratized SQL stored procedure call that is converted to a pivotgrid-friendly flatdatasource (the first method I posted here).  What is the best-practice for this?  How is it done?  Can you help? 

I cannot post data due to its confidential nature, but a datatype-only parallel would be something like Row groups: Chocolates > Brands > Brand Types > Product Names. Column groups: Distribution Regions > Distributors.  A measure might be "Pounds in stock".  There are no date \ time considerations.  The view is a single point in time; today.

Is it easy to quickly use this example to demonstrate how I can do this in the code-behind?  Do I need to define cube metadata or is the first method already doing that?

Again, it is my very first venture outside of Winforms and I feel like a total noob!  Thanks!

Hello Jaime,
Look at the attached sample and let me know of you have any further questions.

Plamen.

Hello Plamen,

Thank you very much for the code. Something else is still missing because I quickly ate up 1.5 GB of RAM and reached the System.OutOfMemory exception.  Also I do not have the IG silverlight controls so I cannot run your project, but I did my best to use your code in my project.  Here is what I have:

public partial class PivotGridHostControl : UserControl { private IEnumerable _flatData; public PivotGridHostControl() { InitializeComponent(); } /// /// Sets a flat data source to be used for a datasource of the Pivot Grid from a DataTable /// by dynamically building a pivotgrid-friendly list object. /// /// public void SetFlatDatasource(DataTable dt) { DynamicTypeBuilder typeBuilder = new DynamicTypeBuilder { DynamicAssemblyName = "MyAssembly", DynamicTypeName = "Consumer ABS" }; IList properties = new List(); foreach (DataColumn column in dt.Columns) { DynamicTypePropertyInfo propertyInfo = new DynamicTypePropertyInfo { PropertyName = column.ColumnName, PropertyType = column.DataType }; properties.Add(propertyInfo); } Type dynamicType = typeBuilder.GenerateType(properties); Type listType = typeof(List<>); Type genericListType = listType.MakeGenericType(dynamicType); IList list = (IList)Activator.CreateInstance(genericListType); foreach (DataRow dataRow in dt.Rows) { object myDynamicInstance = Activator.CreateInstance(dynamicType); foreach (DataColumn column in dt.Columns) { PropertyInfo propertyVal = dynamicType.GetProperty(column.ColumnName); if (dataRow[column] != DBNull.Value) { propertyVal.SetValue(myDynamicInstance, dataRow[column], null); } } list.Add(myDynamicInstance); } FlatDataSource flatDataSource = new FlatDataSource(); //FlatDataSource flatDataSource = new FlatDataSource() //{ // ItemsSource = list, // Cube = DataSourceBase.GenerateInitialCube("Consumer ABS"), // // if you know the names of demensions you want to be in rows and columns you can define them here // //Columns = DataSourceBase.GenerateInitialItems("[MgmtGrouping].[Portfolio]"), // //Rows = DataSourceBase.GenerateInitialItems("[CollateralType].[Issuer]"), // //Measures = DataSourceBase.GenerateInitialItems("TotalMvPrct") //}; this.SetHierarchies(flatDataSource); this.SetMetadata(flatDataSource); flatDataSource.ItemsSource = list; ////setup a hierarchy for the string datatype //HierarchyDescriptor stringDataDescriptor = // new HierarchyDescriptor // { // AppliesToPropertiesOfType = typeof(string) // }; //stringDataDescriptor.AddLevel(s => "All", "All Values"); //stringDataDescriptor.AddLevel(s => s, "Issuers"); ////stringDataDescriptor.AddLevel(s => "[Issue]","All Issues"); //flatDataSource.HierarchyDescriptors.Add(stringDataDescriptor); dataSelector.DataSource = flatDataSource; pivotGrid.DataSource = flatDataSource; } private void SetHierarchies(FlatDataSource flatDataSource) { HierarchyDescriptor ABS_Hierarchy = new HierarchyDescriptor(pd => pd.CollateralType) { HierarchyName = "Consumer ABS" }; ABS_Hierarchy.AddLevel("All Products", "All Products"); ABS_Hierarchy.AddLevel(pd => pd.Issuer, "Issuers"); ABS_Hierarchy.AddLevel(pd => pd.Issue, "Issues"); ABS_Hierarchy.AddLevel(pd => pd.Cusip, "Bonds"); flatDataSource.AddHierarchyDescriptor(ABS_Hierarchy); HierarchyDescriptor Account_Hierarchy = new HierarchyDescriptor(pd => pd.MgmtGrouping) { HierarchyName = "Management Groups" }; Account_Hierarchy.AddLevel("All Groups", "All Groups"); Account_Hierarchy.AddLevel(pd => pd.Portfolio, "Account"); //regionHierarchy.AddLevel(pd => pd.Distributor, "Distributors"); flatDataSource.AddHierarchyDescriptor(Account_Hierarchy); } private void SetMetadata(FlatDataSource flatDataSource) { CubeMetadata cubeMetadata = new CubeMetadata { DataTypeFullName = typeof(Data.ABS).FullName, DisplayName = "Consumer ABS" }; flatDataSource.CubesSettings.Add(cubeMetadata); DimensionMetadata costMetadata = new DimensionMetadata(); costMetadata.SourcePropertyName = "MV"; costMetadata.DimensionType = Infragistics.Olap.FlatData.DimensionType.Measure; cubeMetadata.DimensionSettings.Add(costMetadata); }

...and my first data class:

public class ABS { [DisplayAttribute(AutoGenerateField = false)] public string CollateralType { get; set; } [DisplayAttribute(AutoGenerateField = false)] public string Issuer { get; set; } [DisplayAttribute(AutoGenerateField = false)] public string Issue { get; set; } [DisplayAttribute(AutoGenerateField = false)] public string Cusip { get; set; } [DisplayAttribute(AutoGenerateField = false)] public string MgmtGrouping { get; set; } [DisplayAttribute(AutoGenerateField = false)] public string Portfolio { get; set; } [DisplayFormat(DataFormatString = "{0:N2}")] [DisplayAttribute(Name = "MV (Attribute)")] public double TotalMvPrct { get; set; } }

Two notes: 1.) You have

private IEnumerable _flatData;

but _flatData is never referenced. 2.) The commented block of code in the first method that "sets up a hierarchy for the string datatype", when uncommented allows me to drag in all of the rows and column without having a large memory hit. It works, but everything I add is collapsed with the text "All". I need everything to be expanded and not as "All".

I should try to think before posting.  :)  

All child nodes should be expanded.  What I would love is to not have totals for Category, Brand and Product where there is only one.  Please tell me this is possible?

Hello Plamen,

Your sample works great.  The only enhancement that would make it perfect would be to not expand child nodes if there is only 1.  This way you do not end up with a bunch of "total" rows for a single Category, Brand, and product.  Product should only be expanded if there is more than 1.  Same for Brand and Category.  Is this an easy tweak?

The good news is after expanding all nodes the memory only hit a maximum of 200,000 kb, so the issue is only with dragging dyamic rows into the grid with no pre-defined data class or heirarchies.

Thanks!

Disregard my question about the event method.  The answer was right in front of my face. Bad morning... Computer troubles delayed me for over two hours.  :)

Hi Plamen,

You are the best.  REALLY appreciate your support!  I was able to use your example to finally see my data pre-loaded in the pivot grid, however staying on topic with memory issues, I need to have all of these levels expanded by default.  Users will need to see this view by default else they need to click hundreds if not thousands of expanders...it is not going to happen. 

Ideally, what they want is the first row group level expanded by default, but the expansion of each of these to expand the all of the related remaining sub groups.  In your example this would mean having All Products expanded to see the names and totals of the Categories.  The expansion of one Category should auto-expand of of that Category's Brands and Products.

For Columns, all of the Regions and Distributors should be visible when loaded.

How did you generate this event method?  I copied the method into my code but it is never hit because I do not know how to wire it.  In winforms I am used to auto generating these events from each control's properties window.

private void AreaItems_CollectionChanging