Hello,
I am trying to use a simple DataTable or an XML file (generated by another application) as a FlatDataSource for the xamPivotGrid. This should be easy I assume but I do not manage to have it work.
Using the following code doesn not make it (dt is my DataTable)
FlatDataSource flatDataSource = new FlatDataSource()
{
ItemsSource = dt.AsEnumerable().ToList()
Measures = XmlaDataSource.GenerateInitialItems("AmountOfSale")*/
};
I want something very simple to plug any Table to the xamPivotGrid. I cannot define a priori a class to serialize the dataTable (or XML file) so it is not so obivious to create the IEnumerable. Thanks for your help.
To bind a table as a data cource for pivot grid you should do a little extra work. With the help of our little framework for dynamic built of type you can easily to convert your table or xml into class with appropriated properties which can be read from pivotGrid.
The below is a sample how to do that.
this.table - is the instance of your data table
using Infragistics.Olap;
using Infragistics.Olap.FlatData;
private void BuildDataSource()
DynamicTypeBuilder typeBuilder = new DynamicTypeBuilder
DynamicAssemblyName = "MyAssembly",
DynamicTypeName = "Pane"
IList<DynamicTypePropertyInfo> properties = new List<DynamicTypePropertyInfo>();
foreach (DataColumn column in this.table.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 this.table.Rows)
object myDynamicInstance = Activator.CreateInstance(dynamicType);
PropertyInfo propertyVal = dynamicType.GetProperty(column.ColumnName);
if (dataRow[column] != DBNull.Value)
propertyVal.SetValue(myDynamicInstance, dataRow[column], null);
list.Add(myDynamicInstance);
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")
pivotDataSelector.DataSource = flatDataSource;
pivotGrid.DataSource = flatDataSource;
You can read more about dinamic type creation here.
I translated the C# Source to VB .Net.
But i still have a problem. When i Assign the FlatDataSource to a XamPivotGridSelector the List of Measures is always empty.
Is there something wrong with my code ?
Thanks
Christian Gill
---------
Private Sub BuildDataSourceFromDataTable(ByVal InputTable As System.Data.DataTable, ByVal TabellenName As String, ByVal MeasureList As List(Of String), ByRef OutputFlatDataSource As FlatData.FlatDataSource) Dim Column As System.Data.DataColumn Dim PropertyInfo As DynamicTypePropertyInfo Dim Row As System.Data.DataRow Dim MeasureString As String Dim typeBuilder As New DynamicTypeBuilder With typeBuilder .DynamicAssemblyName = "MyAssembly" .DynamicTypeName = TabellenName End With Dim Properties As IList(Of DynamicTypePropertyInfo) = New List(Of DynamicTypePropertyInfo) For Each Column In InputTable.Columns PropertyInfo = New DynamicTypePropertyInfo With PropertyInfo .PropertyName = Column.ColumnName .PropertyType = Column.DataType End With Properties.Add(PropertyInfo) Next Dim DynamicType As System.Type = typeBuilder.GenerateType(Properties) Dim ListType = GetType(List(Of )) Dim GenericListType = ListType.MakeGenericType(DynamicType) Dim List As IList = Activator.CreateInstance(GenericListType) Dim MeasureStringList As String = "" For Each Row In InputTable.Rows Dim MyDynamicInstance As Object = Activator.CreateInstance(DynamicType) For Each Column In InputTable.Columns Dim PropertyVal As System.Reflection.PropertyInfo = DynamicType.GetProperty(Column.ColumnName) If IsDBNull(Row(Column)) = False Then PropertyVal.SetValue(MyDynamicInstance, Row(Column), Nothing) End If Next List.Add(MyDynamicInstance) Next OutputFlatDataSource = New FlatData.FlatDataSource With OutputFlatDataSource .ItemsSource = List .Cube = DataSourceBase.GenerateInitialCube(TabellenName) .Columns = DataSourceBase.GenerateInitialItems("[Columns]") .Rows = DataSourceBase.GenerateInitialItems("[Row]") For Each MeasureString In MeasureList If MeasureStringList.Length > 0 Then MeasureStringList = MeasureStringList + "," End If MeasureStringList = MeasureStringList + MeasureString Next .Measures = DataSourceBase.GenerateInitialItems(MeasureStringList) End With For Each Column In InputTable.Columns Dim Hier As New FlatData.HierarchyDescriptor Hier.SourcePropertyName = Column.ColumnName Dim AllLevel As New FlatData.HierarchyLevelDescriptor AllLevel.LevelName = "Total " + Column.ColumnName Dim EntriesLevel As New FlatData.HierarchyLevelDescriptor EntriesLevel.LevelName = Column.ColumnName EntriesLevel.LevelExpressionPath = "" Hier.LevelDescriptors.Add(AllLevel) Hier.LevelDescriptors.Add(EntriesLevel) OutputFlatDataSource.HierarchyDescriptors.Add(Hier) Next End Sub
Hi
Could you check your MeasureStringList value. I suppose that it ends with ',' and that is the reason measures to be empty.
RegardsTodor
No the List is o.k. Have a short look at the source i posted.
I build the string from a stringlist.
The string is empty at the beginning.
Then I iterate all items in my stringlist.
if the string contains allready at least on item a "," is added.
and after that the actual measurement item is added ...
At the end of the loop i get a string with all measurements seperated by , but there is no , at the end of the string. (and none at the beginning because at this time the stringlength is 0)
Any other hints ?
I tested your code and it works. It seems that the name of measures are different and that is the reason to have emprty measures.
I have attached a sample project, which works.
Hi Philip,
Thanks a ton. It got worked after doing the modification you suggested.
Regards,
Narasimhan.
Hello, Narasimhan!
In the code behind (MainWindow.xaml.vb) you need to change the
EntriesLevel.LevelExpressionPath = ""
with
EntriesLevel.LevelExpressionPath = Column.ColumnName
Let me know if this fixes your issue.
Philip
Hi Todor,
First of all sorry for replying the older thread. I have gone through the sample project attached by you.
I have upgraded that project for 13.1 version. My problem here is after i drag drop a field from Data selector to Pivot Grid (either in Column or Row) and when i expand the field in pivot grid, it show the Cube name instead of its detail values.
Kindly help me.
Thanks in advance,