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 do set the data selector to the same flat data source:

dataSelector.DataSource = flatDataSource;

pivotGrid.DataSource = flatDataSource;

This is how I am able to manually drag the rows and columns I want.  After making your change it may have seemed a litle more responsive, but when I added the 4th row the memory jumped from 500M to 1.5G very quickly and I stopped debugging before the out of memory error.  This includes commenting the Rows, Columns and Measures init.

Is type of memory hit typical?  This is one of our smaller sets of data we want to use in the pivot grid and I need to know as soon as possible if this control shoud be able to handle it like I was lead to believe it would.  Thank you.

I put the same data into an Excel file and used it as the source.  The app tops out at 156,000k.  It performs great.  Besides working, one major difference is that as I add the each row they default to collapsed with the caption "All".  When I add columns from the flat source they appear in the pivot grid expanded, each after some time.  Not sure if this indicates there is something wrong with my implementation of the flat source or some bug with the conversion of the DataTable to a flat source (code posted above)? 

The Excel method:

 public void SetExcelDataSource(string fileName)
{
    Stream stream = null;
    ExcelSheetSelector esp = null;
    try
    {
        //open the sheet selector form to allow the user to select a sheet to use
        esp = new ExcelSheetSelector(fileName);
        esp.ShowDialog();
        //show an error message in case the selected sheet name is empty
        if (String.IsNullOrEmpty(esp.SelectedSheetName))
        {
            throw new Exception("Please select a worksheet in the selector window");
        }

        //open the excel file, construct a datasource for the sheet name selected by the user
        stream = File.Open(fileName, FileMode.Open);
        FlatDataSource dataSource = CreateExcelDataSource(stream, esp.SelectedSheetName);

        //set the datasource to the grid and the selector
        pivotGrid.DataSource = dataSource;
        dataSelector.DataSource = dataSource;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "PivotGrid Excel Sheet Selector", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
        esp.Close();
    }
    finally
    {
        if (stream != null)
            stream.Close();
    }
}

Forgot to include the other method.  Interesting point here is that the user control was borrowed from here and as you can see it is customized for the supplied Excel file. My data looks nothing like the sample file, yet this code is working great for me.

private FlatDataSource CreateExcelDataSource(Stream fileStream, string selectedSheetName)
{
    FlatDataSource flatDataSource = new FlatDataSource();
    //setup the connection to the excel file and worksheet to use
    ExcelDataConnectionSettings excelDataConnectionSettings =
            new ExcelDataConnectionSettings
            {
                FileStream = fileStream,
                GeneratedTypeName = "ExcelData",
                WorksheetName = selectedSheetName
            };

    string excelStingFormat = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)";
    string systemValueFormat = "{0:C3}";
    ExcelDataConnectionSettings.AddExcelCellFormatMapping(excelStingFormat, systemValueFormat);

    flatDataSource.Cube = DataSourceBase.GenerateInitialCube("ExcelData");

    //the following three lines select the row and column hierarchies and the measures to be used in the default view
    //these will appear as selected in the data selector
    flatDataSource.Rows = DataSourceBase.GenerateInitialItems("[Region]");
    flatDataSource.Columns = DataSourceBase.GenerateInitialItems("[Date]");
    flatDataSource.Measures = DataSourceBase.GenerateInitialItems("Units");

    //setup the names to appear in the data selector
    flatDataSource.ConnectionSettings = excelDataConnectionSettings;
    flatDataSource.CubesSettings.Add(
            new CubeMetadata
            {
                DataTypeFullName = "Excel Data",
                DisplayName = selectedSheetName + " Data"
            });

    //setup a hierarchy for the string datatype
    HierarchyDescriptor stringDataDescriptor =
            new HierarchyDescriptor
            {
                AppliesToPropertiesOfType = typeof(string)
            };
    stringDataDescriptor.AddLevel(s => "All", "All Values");
    stringDataDescriptor.AddLevel(s => s, "Members");

    //setup a hierarchy for the date time datatype
    HierarchyDescriptor dateTimeDataDescriptor =
            new HierarchyDescriptor
            {
                AppliesToPropertiesOfType = typeof(DateTime)
            };
    dateTimeDataDescriptor.AddLevel(date => "All Dates", "All Values");
    dateTimeDataDescriptor.AddLevel(date => date.Year, "Years");
    dateTimeDataDescriptor.AddLevel(date => date.Date, "Members");

    flatDataSource.HierarchyDescriptors.Add(stringDataDescriptor);
    flatDataSource.HierarchyDescriptors.Add(dateTimeDataDescriptor);

    return flatDataSource;
}

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...?

Hello Jaime,

Currently we don’t provide an option to separate the topics from a forum thread, still if you want you can post the question as a new topic and I will copy the answer that Mircho provided for you.

Hello Mircho,

Works exactly how I had hoped!  Many thanks.  Unfortunately I did a bad thing and have asked questions within this post not related to the main point of the post.  This is valuable lesson for other users that I fear will remain lost here. 

Is there a way to separate this question and your answer into another post for the benefit of the community?

Thanks again!

Jaime

Hi JaimeZ,

Members count shows you how many hierarchies you have in the row tuple. So this check, r.Tuple.Members.Count == 1, shows that there is only one hierarchy in the row. What i understood is that you want to hide all totals that are aggregating only one item. What i can offer is this one:

 foreach (PivotDataRow r in pivotGrid.DataRows)
            {
                foreach (var member in r.Tuple.Members)
                {
                    if (r.IsTotal & member.ChildCount == 1)
                    {
                        r.IsVisible = false;
                    }
                }
            }

Hope this will resolve the issue.

Regards,

Hello again,

I was not able to achieve the hiding of row totals only when the members being totalled are greater than a count of 1.  This would be a great feature for your pivot grid.  Having row totals only where there is more than one data element to total would mean more data to review on screen and less noise.  I tried the following code but it hides all row totals in the heirarchy being expanded, not just ones where there is only one member to total:

private void pivotGrid_LayoutLoaded(object sender, EventArgs e)
{
    foreach (PivotDataRow r in pivotGrid.DataRows)
    {
        if (r.IsTotal & r.Tuple.Members.Count == 1)
        {
            r.IsVisible = false;
        }
    }
}

It seems that Tuple.Members.Count is always one, which I assume is the root element of each hierarchy being expanded? I was hoping for a way to get a count of what is being totaled, which yet may be possible?

Hello Elena,

Plamen has answers a few secondary questions, so those are issues are solved, however the main topic of this post deals with running out of memory when dragging data items from the xamPivotDataSelector into pivot grid rows.  The data is from a DataTable that is converted into a FlatDataSource.  There is an open support case for this, so maybe it would be good to leave this post open until the issue is resolved via a service release or other solution?

Thank you for the thread link!