Assistance with Connecting Wisej.NET KendoUI PivotGrid to a DataTable in Visual Basic : reza kaboli

Dear Wisej Support Team,

I am working on a Visual Basic .NET application and trying to connect a Wisej.NET KendoUI PivotGrid to a DataTable. My requirement is very simple: I want all columns of my DataTable to appear in the filter section of the PivotGrid, without any additional features like calculations, rows, or columns in other sections. Despite my efforts, including converting the sample code from the following link and testing it with manual data, I have not succeeded.

Link to Sample Code: https://github.com/iceteagroup/wisej-demobrowser/blob/main/Wisej.DemoBrowser.Premium/Wisej.Web.Ext.Kendo.Demo/Component/kendoPivotGrid.cs

I would greatly appreciate it if you could provide a simple sample program or guide that demonstrates how to achieve this. The solution should be straightforward, using Visual Basic .NET, and allow me to quickly understand and implement it.

Thank you for your assistance!

Best regards,
reza kaboli

Simple Sample Program in Visual Basic .NET

Below is a basic example to connect a Wisej.NET KendoUI PivotGrid to a DataTable with all columns in the filter section. This code assumes you have Wisej.NET and KendoUI extensions installed in your project.

Imports Wisej.Web
Imports Wisej.Web.Ext.Kendo
Imports System.Data

Public Class Form1
Inherits Form

Private WithEvents kendoPivotGrid1 As KendoPivotGrid

Public Sub New()
InitializeComponent()
End Sub

Private Sub InitializeComponent()
‘ Create PivotGrid
kendoPivotGrid1 = New KendoPivotGrid()
kendoPivotGrid1.Location = New Point(10, 10)
kendoPivotGrid1.Size = New Size(800, 600)
Me.Controls.Add(kendoPivotGrid1)

‘ Create sample DataTable
Dim dt As New DataTable(“SampleData”)
dt.Columns.Add(“ID”, GetType(Integer))
dt.Columns.Add(“Name”, GetType(String))
dt.Columns.Add(“Price”, GetType(Double))
dt.Rows.Add(1, “Product A”, 10.5)
dt.Rows.Add(2, “Product B”, 20.0)
dt.Rows.Add(3, “Product C”, 15.75)

‘ Configure PivotGrid DataSource
Dim dataSourceOptions As Object = New With {
.type = “local”,
.data = ConvertDataTableToDynamicArray(dt),
.schema = New With {
.model = New With {
.fields = CreateFields(dt)
}
},
.filters = CreateFilters(dt) ‘ All columns in filters
}

Dim options As Object = New With {
.dataSource = dataSourceOptions,
.autoBind = True,
.height = 600,
.showFieldList = True
}

kendoPivotGrid1.Options = options
kendoPivotGrid1.Update()
End Sub

Private Function ConvertDataTableToDynamicArray(dt As DataTable) As Object()
Dim dataArray As New List(Of Object)
For Each row As DataRow In dt.Rows
Dim expando As New System.Dynamic.ExpandoObject()
Dim expandoDict As IDictionary(Of String, Object) = expando
For Each col As DataColumn In dt.Columns
Dim value As Object = If(IsDBNull(row(col)), Nothing, row(col))
If col.DataType Is GetType(Integer) OrElse col.DataType Is GetType(Double) Then
expandoDict(col.ColumnName) = Convert.ToDouble(value)
Else
expandoDict(col.ColumnName) = If(value IsNot Nothing, value.ToString(), “”)
End If
Next
dataArray.Add(expando)
Next
Return dataArray.ToArray()
End Function

Private Function CreateFields(dt As DataTable) As Object
Dim fields As New Object()
fields = New With {
.ID = New With {.type = “number”},
.Name = New With {.type = “string”},
.Price = New With {.type = “number”}
}
Return fields
End Function

Private Function CreateFilters(dt As DataTable) As Object()
Dim filtersList As New List(Of Object)
For Each col As DataColumn In dt.Columns
filtersList.Add(New With {.name = col.ColumnName})
Next
Return filtersList.ToArray()
End Function

Public Shared Sub Main()
Application.Run(New Form1())
End Sub
End Class

Explanation

1. Form Setup: A simple form with a KendoPivotGrid is created.
2. DataTable: A sample DataTable with columns “ID”, “Name”, and “Price” is created manually.
3. Data Conversion: The ConvertDataTableToDynamicArray function converts the DataTable to an array of dynamic objects, preserving data types (numbers without quotes, strings with quotes).
4. PivotGrid Configuration:
   • dataSource.data is set to the converted array.
   • filters includes all columns from the DataTable.
   • No rows, columns, or measures are set, keeping it simple with only filters.
5. Run: The application runs and displays the PivotGrid with filter options for all columns.

Notes

• Ensure you have the Wisej.NET KendoUI extension installed via NuGet.
• Replace the sample DataTable with your actual DataTable (SData from your SelectX function).
• Test and check if the filter section shows all columns. If not, share the error or output for further debugging.