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 Marius,
Thank you for contacting Infragistics Developer Support.
You can use our new FlatDataSource that was added in 14.2 in order to do that. You will need to create an IEnumerable collection out of the DataReader and provide that to the FlatDataSource. After that you can see how to use the FlatDataSource with UltraPivotGrid from this thread:
http://help.infragistics.com/Doc/WinForms/2014.2/CLR4.0/?page=WinPivotGrid_Using_FlatDataSource.html
I have attached a sample using local database in order to demonstrate this suggestion.
Please let me know if you have any additional questions.
HI Dimitar
Ok, I had a look at the example and I understand the basic working of it. I am just not sure how to create an IEnumerale from my Table..
Will I create a DataTable like in my code and then create the IEnum ?
Can you give me a simple example of that , please
Thank you
I have tried the following code... still does not load the pivot... gives an error
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)), 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))))
Return listOfSETA_Qualifications
End Function
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?
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)), _
IIf(IsDBNull(s.Item(5)), "", s.Item(5)), _
IIf(IsDBNull(s.Item(6)), "", s.Item(6))))
Anybody that can help me? I need to populate the PivotGrid... but my code above does not do tht
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.
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)), _
Return listOfStudent
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]
I am really glad that you have found the issue. Let me know if you have any additional questions on this matter.
Thank you for using Infragistics Components.
Found the error. Some of my fields in my database had "" and some DBNULL in and the conversion to string did not complete.
This is what I did.
Select(Function(s) New Student(IIf(s.Item("SETA") = "", "None", s.Item("SETA")), _
IIf(IsDBNull(s.Item("OFO_Code")), "0", s.Item("OFO_Code")), _
s.Item("Male"), _
s.Item("Female"), _
s.Item("Black"), _
s.Item("Coloured"), _
s.Item("Indian"), _
s.Item("White"), _
s.Item("Unknown"), _
IIf(IsDBNull(s.Item("DateRegisRes")), Nothing, s.Item("DateRegisRes")), _
IIf(IsDBNull(s.Item("DateCompRes")), Nothing, s.Item("DateCompRes")), _
IIf(Trim(s.Item("ProgramName")) = "", "None", s.Item("ProgramName"))))
I also shortened my SQL part of my code.
MsgBox(StartingDate & " " & EndingDate)
Dim query As String = " SELECT distinct QUALIFICATION.[SETA] , " & _
"QUALIFICATION.[OFO_Code] , " & _
"WHEN STUDENT.[Gender_Code] ='M' THEN 1 ELSE 0 END) as [MALE] " & _
"WHEN STUDENT.[Gender_Code] ='F' THEN 1 ELSE 0 END) as [FEMALE] " & _
"WHEN STUDENT.[Equity_Code] ='BA' THEN 1 ELSE 0 END) as [Black] " & _
"WHEN STUDENT.[Equity_Code] ='BC' THEN 1 ELSE 0 END) as [Coloured] " & _
"WHEN STUDENT.[Equity_Code] ='BI' THEN 1 ELSE 0 END) as [Indian] " & _
"WHEN STUDENT.[Equity_Code] ='WH' THEN 1 ELSE 0 END) as [White] " & _
"WHEN STUDENT.[Equity_Code] ='U' THEN 1 ELSE 0 END) as [Unknown] , " & _
"QUALIFICATION.[DateRegisRes] , " & _
"QUALIFICATION.[DateCompRes] , " & _
"QUALIFICATION.[ProgramName] " & _
"QUALIFICATION.[ID] = STUDENT.[ID] ) " & _
"WHERE QUALIFICATION.[DateRegisRes] " & _
"BETWEEN @StartDate and @EndDate " & _
"GROUP by QUALIFICATION.[SETA] , " & _
"STUDENT.[Gender_Code] , " & _
"Student.[Equity_Code] , " & _
"QUALIFICATION.[ProgramName] , " & _
"QUALIFICATION.[National_id]"
Image
Ok, this is my last attempt , then I am going to chuck this control!!
Here is my code so far...
Dim counter As Integer = 0
Public StartingDate, EndingDate As Date
Private Sub Month_End_Click(sender As Object, e As EventArgs) Handles Month_End.Click
'' User selected Period End date
EndingDate = (Month_End.CalendarInfo.ActiveDay.Date).ToShortDateString
Private Sub Month_Start_Click(sender As Object, e As EventArgs) Handles Month_Start.Click
StartingDate = (Month_Start.CalendarInfo.ActiveDay.Date).ToShortDateString
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Black] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Coloured] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Indian] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [White] " & _
"BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Unknown] , " & _
"QUALIFICATION.[DatecompRes] , " & _
"QUALIFICATION.[ProgramName]"
If e.MeasureDescriptor.Name = "Male" Then
total += item.Male
If e.MeasureDescriptor.Name = "FeMale" Then
total += item.FeMale
If e.MeasureDescriptor.Name = "Black" Then
total += item.Black
If e.MeasureDescriptor.Name = "Indian" Then
total += item.Indian
If e.MeasureDescriptor.Name = "White" Then
total += item.White
If e.MeasureDescriptor.Name = "Coloured" Then
total += item.Coloured
If e.MeasureDescriptor.Name = "Unknown" Then
total += item.Unknown
If (e.MeasureDescriptor.Name = "Male") Then
e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum
If (e.MeasureDescriptor.Name = "Female") Then
If (e.MeasureDescriptor.Name = "Black") Then
If (e.MeasureDescriptor.Name = "Indian") Then
If (e.MeasureDescriptor.Name = "Coloured") Then
If (e.MeasureDescriptor.Name = "White") Then
If (e.MeasureDescriptor.Name = "Unknown") Then
'' AddHandler ds.InitializeHierarchyDescriptor, AddressOf ds_InitializeHierarchyDescriptor
'' AddHandler ds.AggregateMeasure, AddressOf ds_AggregateMeasure
settings.Measures = "[Measures].[Male],[Measures].[Female] ,[Measures].[Black], [Measures].[White],[Measures].[Indian], [Measures].[Unknown], [Measures].[Coloured]"
settings.Rows = "[Student].[DateCompetent]"
settings.Rows = "[Student].[DateRegistered]"
settings.Columns = "[Student].[OFO_Code]"
settings.Columns = "[Student].[SETA]"
settings.Columns = "[Student].[ProgramName]"
Case "SETA"
level.MemberProvider = Function(item) "All SETA"
Case "OFO_CODE"
level.MemberProvider = Function(item) "All OFO CODE"
Case "ProgramName"
level.MemberProvider = Function(item) "All Program Names"
Case "DateRegistered"
level.MemberProvider = Function(item) "All Registration Dates"
Case "DateCompetent"
level.MemberProvider = Function(item) "All Completion Dates"
Select(Function(s) New Student(s.Item("SETA"), _
IIf(IsDBNull(s.Item("DateRegisRes")), "1900/01/01", s.Item("DateRegisRes")), _
IIf(IsDBNull(s.Item("DatecompRes")), "1900/01/01", s.Item("DatecompRes")), _
IIf(IsDBNull(s.Item("ProgramName")), "", s.Item("ProgramName"))))
Private Sub btnLoadGrid_Click(sender As Object, e As EventArgs) Handles btnLoadGrid.Click
Dim query As String = " SELECT STUDENT.[id], " & _
"QUALIFICATION.[SETA] , " & _
" QUALIFICATION.[DatecompRes] " & _
"QUALIFICATION.[ID] = STUDENT.[ID] ) "
Table.Load(reader)
Private Sub PivotTableExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
StartingDate = (Now().AddDays(-180)).ToShortDateString
EndingDate = Now.ToShortDateString
Private m_SETA As String
Private m_OFO_Code As String
Private m_ProgramName As String
Private m_Male As Integer
Private m_FeMale As Integer
Private m_Black As Integer
Private m_Coloured As Integer
Private m_White As Integer
Private m_Indian As Integer
Private m_Unknown As Integer
Private m_DateRegistered As Date
Private m_DateCompetent As Date
Public Sub New(SETA As String, _
OFO_CODE As String, _
Male As Integer, _
Female As Integer, _
Black As Integer, _
Coloured As Integer, _
Indian As Integer, _
White As Integer, _
Unknown As Integer, _
DateRegistered As Date, _
DateCompetent As Date, _
Programname As String)
Me.m_SETA = SETA
Me.m_OFO_Code = OFO_CODE
Me.m_Male = Male
Me.m_FeMale = FeMale
Me.m_Black = Black
Me.m_Coloured = Coloured
Me.m_White = White
Me.m_Indian = Indian
Me.m_Unknown = Unknown
Me.m_DateRegistered = DateRegistered.ToShortDateString
Me.m_DateCompetent = DateCompetent.ToShortDateString
Me.m_ProgramName = ProgramName
<System.ComponentModel.Category("Date Competent")>
Public Property DateCompetent As Date
Return m_DateCompetent
Set(value As Date)
m_DateCompetent = value
<System.ComponentModel.Category("Date Registered")>
Public Property DateRegistered As Date
Return m_DateRegistered
m_DateRegistered = value
<System.ComponentModel.Category("Program Name")>
Public Property ProgramName As String
Return m_ProgramName
m_ProgramName = value
<System.ComponentModel.Category("Equity Coloured")>
Public Property Coloured As Integer
Return m_Coloured
m_Coloured = value
<System.ComponentModel.Category("Equity Black")>
Public Property Black As Integer
Return m_Black
m_Black = value
<System.ComponentModel.Category("Equity Indian")>
Public Property Indian As Integer
Return m_Indian
m_Indian = value
<System.ComponentModel.Category("Equity White")>
Public Property White As Integer
Return m_White
m_White = value
<System.ComponentModel.Category("Equity Unknown")>
Public Property Unknown As Integer
Return m_Unknown
m_Unknown = value
<System.ComponentModel.Category("SETA NAME")>
Public Property SETA As String
Return m_SETA
m_SETA = value
<System.ComponentModel.Category("OFO Code")>
Public Property OFO_CODE As String
Return m_OFO_Code
m_OFO_Code = value
<System.ComponentModel.Category("Male")>
Public Property Male As Integer
Return m_Male
m_Male = value
<System.ComponentModel.Category("FeMale")>
Public Property FeMale As Integer
Return m_FeMale
m_FeMale = value
The Image below shows that the selections on the righthand side is not enabled. The moment I choose from the rows or columns, the list loses its focus. It is not enabled.
The (SETA, OFO Code, ProgramName) Expand also does not expand the lists
Hi, Yes I saw that the Measure's has to numbers, I changed my code.
This is what I have done so far
settings.Measures = "[Measures].[Male]"
settings.Measures = "[Measures].[Female]"
settings.Measures = "[Measures].[Black]"
settings.Measures = "[Measures].[White]"
settings.Measures = "[Measures].[Indian]"
settings.Measures = "[Measures].[Unknown]"
settings.Measures = "[Measures].[Coloured]"
IIf(IsDBNull(s.Item("OFO_Code")), "", s.Item("OFO_Code")), _
IIf(s.Item("Gender_code") = "M", 1, 0), _
IIf(s.Item("Gender_code") = "F", 1, 0), _
IIf(s.Item("Equity_Code") = "BA", 1, 0), _
IIf(s.Item("Equity_Code") = "BC", 1, 0), _
IIf(s.Item("Equity_Code") = "BI", 1, 0), _
IIf(s.Item("Equity_Code") = "WH", 1, 0), _
IIf(s.Item("Equity_Code") = "U", 1, 0), _
My Problem I am having now is that when I select a Column or Row (SETA or OFO Code) category the Items can not expand. The selections list of the righthand size also becomes grey (nothing is enabled)
What am I missing here?
Is my SQL statement a problem?
Dim query As String = " SELECT distinct top 1000 QUALIFICATION.[SETA] , " & _
QUALIFICATION.[OFO_Code] , " & _
Dim parameters = New CubeGenerationParameters