I am using SQL server with my application. I need to create Pivot tables in various formats. I am not sure if a binding between a SQL database and the Pivot control is possible, but if it is can you please help me with sample code to achieve something like I did with the code below
Public Sub FIllGrid()
Dim cmd As New SqlClient.SqlCommand
cmd.Connection = con
Dim Table As New DataTable
If con.State = ConnectionState.Closed Then
con.Open()
End If
cmd.CommandText = " SELECT distinct QUALIFICATION.[SETA] as [SETA] " & _
", " & _
"SUM(CASE " & _
"WHEN STUDENT.[Gender_Code] ='M' and " & _
"QUALIFICATION.[DateRegisRes] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [MALE] " & _
"WHEN STUDENT.[Gender_Code] ='F' and " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [FEMALE] " & _
"WHEN STUDENT.[Equity_Code] ='BA' and " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [BA] " & _
"WHEN STUDENT.[Equity_Code] ='BC' and " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [BC] " & _
" " & _
"WHEN STUDENT.[Equity_Code] ='BI' and " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [BI] " & _
"WHEN STUDENT.[Equity_Code] ='WH' and " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [WH] " & _
"WHEN STUDENT.[Equity_Code] ='U' and " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [U] " & _
"WHEN Student.[Gender_Code] ='M' and " & _
"QUALIFICATION.[DatecompRes] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [MALE 2] " & _
" , " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [FEMALE 2] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [BA 2] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [BC 2] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [BI 2] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [WH 2] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [U 2] " & _
"FROM (STUDENT JOIN QUALIFICATION ON " & _
"QUALIFICATION.[ID] = STUDENT.[ID] ) where QUALIFICATION.[SETA] <> '' " & _
" and QUALIFICATION.[SETA] <> 'STUDENT REGISTRATIONS' " & _
"GROUP by QUALIFICATION.[SETA] order by QUALIFICATION.[SETA] "
cmd.Parameters.AddWithValue("@StartDate", StartingDate)
cmd.Parameters.AddWithValue("@EndDate", EndingDate)
cmd.CommandType = CommandType.Text
Table.Load(cmd.ExecuteReader())
UltraGrid1.DataSource = Table
If Table.Rows.Count > 0 Then
btnExcelExport.Enabled = True
If con.State = ConnectionState.Open Then
con.Close()
End Sub
Hi , I am still not getting this to work. When I look at your example , I have a hard time to determine from your code when you are referring to a field name in your table and when you are referring to a property of the Sports-Class. Is it possible that you can look at my code and see if I am missing something. I will also include my table structure.
Imports System.Data
Imports System.IO
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports Infragistics.Olap.FlatData
Public Class PivotTableExample
Public cmd As SqlCommand
Public Sub FillPivotGrid()
Dim query As String = " SELECT * FROM STUDENT "
Using conn As New SqlConnection(constr)
Using cmd As New SqlCommand(query, conn)
conn.Open()
Dim reader = cmd.ExecuteReader()
Dim data As IEnumerable(Of Student) = CreateIEnumerable(reader)
Dim ds As FlatDataSource = InitializePivotData(data)
Dim parameters = New CubeGenerationParameters()
ds.GenerateCube(parameters)
ds.InitializeAsync(UltraPivotGrid1)
UltraPivotGrid1.DataSource = ds
OlapDataSelector1.DataSource = ds
End Using
Private Function CreateIEnumerable(reader As SqlDataReader) As IEnumerable(Of Student)
Dim listOfStudent = reader.Cast(Of IDataRecord).
Select(Function(s) New Student(IIf(IsDBNull(s.Item(1)), "", s.Item(1)), _
IIf(IsDBNull(s.Item(2)), "", s.Item(2)), _
IIf(IsDBNull(s.Item(3)), "", s.Item(3)), _
IIf(IsDBNull(s.Item(4)), "", s.Item(4)), _
IIf(IsDBNull(s.Item(5)), "", s.Item(5)), _
IIf(IsDBNull(s.Item(6)), "", s.Item(6))))
Return listOfStudent
End Function
Private Function InitializePivotData(data As IEnumerable(Of Student)) As FlatDataSource
Dim itemSource As IEnumerable(Of Student) = data
Dim settings = New FlatDataSourceInitialSettings()
Dim ds = New FlatDataSource(itemSource.ToList(), GetType(Student), settings)
AddHandler ds.InitializeHierarchyDescriptor, AddressOf ds_InitializeHierarchyDescriptor
AddHandler ds.InitializeMeasureDescriptor, AddressOf ds_InitializeMeasureDescriptor
AddHandler ds.AggregateMeasure, AddressOf ds_AggregateMeasure
settings.Rows = "[Student].[Person_Title]" ---> FIELDNAME or PROPERTY ???
settings.Columns = "[Student].[Equity_code]"
settings.Measures = "[Measures].[Gender_code]"
Return ds
Private Sub ds_InitializeHierarchyDescriptor(sender As Object, e As InitializeHierarchyDescriptorEventArgs)
Dim level As LevelDescriptor = e.HierarchyDescriptor.LevelDescriptors.ToList().FirstOrDefault(Function(lvl) lvl.Name = "all")
If level IsNot Nothing Then
Select Case e.HierarchyDescriptor.PropertyDescriptor.Name
Case "Person_Title" -------> FIELDNAME or PROPERTY ????
level.MemberProvider = Function(item) "All Persons"
Case "Equity_code"
level.MemberProvider = Function(item) "All Equity"
Exit Select
Case "National_Id"
level.MemberProvider = Function(item) "All ID"
End Select
Private Sub ds_InitializeMeasureDescriptor(sender As Object, e As InitializeMeasureDescriptorEventArgs)
If (e.MeasureDescriptor.Name = "Gender_code") Then ------> FIELD NAME or PROPERTY or VARIABLE ???
e.MeasureDescriptor.DisplayFormat = "$#"
e.MeasureDescriptor.Aggregation = MeasureAggregation.Count
Private Sub ds_AggregateMeasure(sender As Object, e As AggregateMeasureEventArgs)
If e.MeasureDescriptor.Name = "Gender_code" Then
Dim total As Double = 0.0F
Dim count As Double = 0.0F
For Each o As Object In e.Items
Dim item = TryCast(o, Student)
If item IsNot Nothing Then
total += item.Gender_code
count += 1.0F
Next
e.Value = total / count
e.Handled = True
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
Me.Close()
Private Sub UltraButton1_Click(sender As Object, e As EventArgs) Handles UltraButton1.Click
FillPivotGrid()
End Class
Class Student
Private m_PersonTitle As String
Private m_Equitycode As String
Private m_NationalId As String
Private m_Gender As String
Private m_DisabilityStatus As String
'' WHAT AM I PASSING HERE?? FIELDNAMES ??????
Public Sub New(Equity_code As String, Person_Title As String, National_Id As String, Gender_Code As String, Disability_Status_Code As String, SETA As String)
Me.m_Equitycode = Equity_code
Me.m_PersonTitle = Person_Title
Me.m_NationalId = National_Id
Me.m_Gender = Gender_Code
Me.m_DisabilityStatus = Disability_Status_Code
<System.ComponentModel.Category("Person_Title")>
Public Property Person_Title As String
Get
Return m_PersonTitle
End Get
Set(value As String)
m_PersonTitle = value
End Set
End Property
<System.ComponentModel.Category("Equity_code")>
Public Property Equity_code As String
Return m_Equitycode
m_Equitycode = value
<System.ComponentModel.Category("Identification")>
Public Property National_Id As String
Return m_NationalId
m_NationalId = value
<System.ComponentModel.Category("Metrics")>
Public Property Gender_code As Double
Return m_Gender
Set(value As Double)
m_Gender = value
Public Property Disability_Status_Code As Integer
Return m_DisabilityStatus
Set(value As Integer)
m_DisabilityStatus = value
Here are some of my fields: (Copied from SQL server)
[ID]
,[National_Id]
,[Person_First_Name]
,[Person_Middle_Name]
,[Person_Last_Name]
,[Equity_Code]
,[Person_Birth_Date]
,[Person_Title]
,[Gender_Code]
,[Citizen_Resident_Status_Code]
,[Disability_Status_Code]
Hi Marius,
Thank you for the reply.
Your logic for checking the DBNulls seems correct to me. Is the FlatDataSource populated with items after you the InitializePivotData method? You can check that when you are debugging, by seeing if the ItemSource property has the items that you expect to be pulled out of the data base. Is the AggregateMeasure event fired? Have you checked for any exceptions in the Output?
I am looking forward to your reply.
Anybody that can help me? I need to populate the PivotGrid... but my code above does not do tht
I am still struggling with this. THe Error is now fixed, I removed the .Value.
The Grid is NOT populated with any items. Am I checking the Items correctly? How else can I check if the item contains a DBNULL valiue?
Private Function CreateIEnumerable(reader As SqlDataReader) As IEnumerable(Of SETA_Qualifications)
Dim listOfSETA_Qualifications = reader.Cast(Of IDataRecord).
Select(Function(s) New SETA_Qualifications(IIf(IsDBNull(s.Item(1)), "", s.Item(1)), _
Return listOfSETA_Qualifications
I have tried the following code... still does not load the pivot... gives an error
Select(Function(s) New SETA_Qualifications(IIf(IsDBNull(s.Item(1)), "", s.Item(1)), IIf(IsDBNull(s.Item(2)), "", s.Item(2)), IIf(IsDBNull(s.Item(3)), "", s.Item(3)), IIf(IsDBNull(s.Item(4)), "", s.Item(4).Value), IIf(IsDBNull(s.Item(5)), "", s.Item(5)), IIf(IsDBNull(s.Item(6)), "", s.Item(6))))