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