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
THis is my problem...STILL
The image below shows what I am getting, and not what I should get.
AddHandler ds.InitializeHierarchyDescriptor, AddressOf ds_InitializeHierarchyDescriptor
settings.Measures = "[Measures].[Gender]"
settings.Measures = "[Measures].[Equity]"
settings.Rows = "[Student].[SETA]"
settings.Rows = "[Student].[OFO_Code]"
settings.Columns = "[Student].[DateRegistered]"
settings.Columns = "[Student].[DateCompetent]"
Return ds
I am getting very frustrated with this control now...
This is not very user friendly. I actually regret starting to use it.
And the help is DBNull...!!
For some reason I can’t see the image that you have uploaded. Do you mind uploading it again? You can attach it by zipping it and when you click the Reply button, click option and then Add/Update.
From the code you have provided, it seems that you are still trying to use a string property as a Measure (in this case the Equity property):
and:
Public Property Equity As String
As I have explained in my previous post, the Pivot grid will use “numeric properties for Measures and any other properties for Columns and Rows”. So you can’t set the Measure to be the Equity field. You need to have numeric data types in order to have measures. Also if you want to have multiple fields as measures, rows or columns, you have to separate them with commas instead of assigning the property twice:
settings.Measures = "[Measures].[Gender], [Measures].[Equity]"
Do you still have the exception? Does it happen when you initialize the grid or when you try to do a specific action? In order to determine what exactly is causing the issue I will need a sample that reproduces it. It doesn’t need to use a data base, just data objects with which you reproduce it.
I am looking forward to your reply.
Hi, Yes I saw that the Measure's has to numbers, I changed my code.
This is what I have done so far
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.AggregateMeasure, AddressOf ds_AggregateMeasure
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]"
settings.Rows = "[Student].[DateCompetent]"
settings.Rows = "[Student].[DateRegistered]"
settings.Columns = "[Student].[OFO_Code]"
settings.Columns = "[Student].[SETA]"
settings.Columns = "[Student].[ProgramName]"
End Function
Private Function CreateIEnumerable(reader As SqlDataReader) As IEnumerable(Of Student)
Dim listOfStudent = reader.Cast(Of IDataRecord).
Select(Function(s) New Student(s.Item("SETA"), _
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), _
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"))))
Return listOfStudent
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?
Public Sub FillPivotGrid()
Dim query As String = " SELECT distinct top 1000 QUALIFICATION.[SETA] , " & _
QUALIFICATION.[OFO_Code] , " & _
"STUDENT.[Gender_Code] , " & _
"Student.[Equity_Code] , " & _
"QUALIFICATION.[DateRegisRes] , " & _
"QUALIFICATION.[DatecompRes] , " & _
"QUALIFICATION.[ProgramName] " & _
"QUALIFICATION.[ID] = STUDENT.[ID] ) " & _
"GROUP by QUALIFICATION.[SETA] , " & _
"QUALIFICATION.[OFO_Code] , " & _
"QUALIFICATION.[ProgramName]"
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
Ok, this is my last attempt , then I am going to chuck this control!!
Here is my code so far...
Imports System.Data
Imports System.IO
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports Infragistics.Olap.FlatData
Public Class PivotTableExample
Dim counter As Integer = 0
Public cmd As SqlCommand
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
Dim query As String = " SELECT distinct QUALIFICATION.[SETA] , " & _
"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] , " & _
Dim parameters = New CubeGenerationParameters()
Private Sub ds_AggregateMeasure(sender As Object, e As AggregateMeasureEventArgs)
If e.MeasureDescriptor.Name = "Male" 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.Male
count += 1.0F
Next
e.Value = total / count
e.Handled = True
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
Private Sub ds_InitializeMeasureDescriptor(sender As Object, e As InitializeMeasureDescriptorEventArgs)
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]"
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 "SETA"
level.MemberProvider = Function(item) "All SETA"
Case "OFO_CODE"
level.MemberProvider = Function(item) "All OFO CODE"
Exit Select
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"
End Select
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()
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"), _
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
End Class
Class Student
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
Get
Return m_DateCompetent
End Get
Set(value As Date)
m_DateCompetent = value
End Set
End Property
<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
Set(value As String)
m_ProgramName = value
<System.ComponentModel.Category("Equity Coloured")>
Public Property Coloured As Integer
Return m_Coloured
Set(value As Integer)
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
Thank you for the reply.
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("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)
"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.[DateCompRes] , " & _
"WHERE QUALIFICATION.[DateRegisRes] " & _
"BETWEEN @StartDate and @EndDate " & _
"QUALIFICATION.[ProgramName] , " & _
"QUALIFICATION.[National_id]"
Image