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.
Hello, I have the Infragistics 2014.2 version and I am trying to use this framework to bind a XML file to the flatdatasource.
I referenced all Infragistics dll, but my solution cannot find a reference to DynamicTypeBuilder. Again, I am using Winforms and the 2014.2 version.
Thanks for your help.
Hello, could someone respond to me..?
I really need to use the PivotGrid with an XML file, and I cannot find how to reference the DynamicTypeBuilder using v2014.2 and Winforms .NET 4.0.
This is becoming to be urgent.
Have you tried loading your XML data into a datatable and then using the flatdatasource for binding the PivotGrid.
I’m using the flatdatasoure from SQL tables and works quite well.
Cheers,
Fabian
Thanks for your reply.
Would you have a sample of code showing how to turn this DataTable into a flatdatasource?
The code below is in vb.net and has many things that probably you don’t need, but hopefully will give you an idea.
I think there should be more examples in the Infragistics help / samples.
Good luck.
Public Sub BuildDataSourceFromDataTable2(ByVal InputTable As System.Data.DataTable, ByVal oCubeName As String) Dim typeBuilder As New DynamicTypeBuilder With typeBuilder .DynamicAssemblyName = "MyAssembly_2" .DynamicTypeName = oCubeName End With Dim properties As IList(Of DynamicTypePropertyInfo) = New List(Of DynamicTypePropertyInfo)() Dim oCOL As System.Data.DataColumn Dim PropertyInfo As DynamicTypePropertyInfo Dim oROW As System.Data.DataRow For Each oCOL In InputTable.Columns PropertyInfo = New DynamicTypePropertyInfo PropertyInfo.PropertyName = FIELD_NAMES_MAKER(oCOL.ColumnName) PropertyInfo.PropertyType = oCOL.DataType 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 = DirectCast(Activator.CreateInstance(GenericListType), IList) For Each oROW In InputTable.Rows Dim MyDynamicInstance As Object = Activator.CreateInstance(DynamicType) For Each oCOL In InputTable.Columns Dim propertyVal As Reflection.PropertyInfo = DynamicType.GetProperty(FIELD_NAMES_MAKER(oCOL.ColumnName)) If IsDBNull(oROW(oCOL)) = False Then propertyVal.SetValue(MyDynamicInstance, oROW(oCOL), Nothing) End If Next List.Add(MyDynamicInstance) Next '************************************************** 'Improving the captions and formatting Dim cubeMetadata As New CubeMetadata() cubeMetadata.DisplayName = "Budgets and Actuals" cubeMetadata.DataTypeFullName = "Budgets" Dim OutputFlatDataSource = New FlatDataSource For Each oCOL In InputTable.Columns Dim oDIM_METADATA As New DimensionMetadata() oDIM_METADATA.SourcePropertyName = FIELD_NAMES_MAKER(oCOL.ColumnName) oDIM_METADATA.DisplayName = FIELD_NAMES_MAKER(oCOL.ColumnName) 'This is only to make the names more user friendly. Select Case True Case oCOL.ColumnName.Contains("Sales") oDIM_METADATA.DisplayFormat = "{0:C0}" oDIM_METADATA.DimensionType = DimensionType.Measure Case oCOL.ColumnName.Contains("Budget") oDIM_METADATA.DisplayFormat = "{0:C0}" oDIM_METADATA.DimensionType = DimensionType.Measure Case oCOL.ColumnName.Contains("Tot") oDIM_METADATA.DisplayFormat = "{0:C0}" oDIM_METADATA.DimensionType = DimensionType.Measure End Select cubeMetadata.DimensionSettings.Add(oDIM_METADATA) Next 'ADDING HIERARCHIES TO EACH DIMENSION **************** For Each oDIM In cubeMetadata.DimensionSettings If oDIM.DimensionType = 3 Then '(3 means Dimensions, not Measures) Dim oHIERARCHY As New HierarchyDescriptor oHIERARCHY.SourcePropertyName = oDIM.SourcePropertyName Dim oLEVEL_DESCRIPTOR As New HierarchyLevelDescriptor oLEVEL_DESCRIPTOR.LevelName = "All " & oDIM.SourcePropertyName oHIERARCHY.LevelDescriptors.Add(oLEVEL_DESCRIPTOR) oLEVEL_DESCRIPTOR = New HierarchyLevelDescriptor() oLEVEL_DESCRIPTOR.LevelName = oDIM.SourcePropertyName & "s" oLEVEL_DESCRIPTOR.LevelExpressionPath = oDIM.SourcePropertyName oHIERARCHY.LevelDescriptors.Add(oLEVEL_DESCRIPTOR) OutputFlatDataSource.HierarchyDescriptors.Add(oHIERARCHY) End If Next OutputFlatDataSource.CubesSettings.Add(cubeMetadata) OutputFlatDataSource.DimensionsGenerationMode = DimensionsGenerationMode.Mixed 'this is very important OutputFlatDataSource.ItemsSource = List OutputFlatDataSource.Rows = DataSourceBase.GenerateInitialItems("[Customer Id].[Customer Id]") Me.XamPivotGrid1.DataSource = OutputFlatDataSource Me.XamPivotDataSelector1.DataSource = OutputFlatDataSource End Sub