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,
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):
settings.Measures = "[Measures].[Equity]"
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.
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...!!
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.Rows = "[Student].[SETA]"
settings.Rows = "[Student].[OFO_Code]"
settings.Columns = "[Student].[DateRegistered]"
settings.Columns = "[Student].[DateCompetent]"
Return ds
I read through your notes and I understand the basic working of the PivotGrid.
Just some problems with my app. The Pivot table stops responding (with a big red cross). I will place the error mess at the bottom.
When I select some of the items for rows or columns, it seems that they do not contain any data.
The Measurables --in my app is not the same as in my code. It keep on giving me 'Equity' and 'OFO code' although in my code I selected two different items.
see below
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 Sub FillPivotGrid()
Dim query As String = " SELECT STUDENT.[id], " & _
"QUALIFICATION.[SETA] , " & _
"QUALIFICATION.[OFO_Code] , " & _
"STUDENT.[Gender_Code] , " & _
"Student.[Equity_Code] , " & _
"QUALIFICATION.[DateRegisRes] , " & _
" QUALIFICATION.[DatecompRes] " & _
"QUALIFICATION.[ID] = STUDENT.[ID] ) "
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 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)
End Function
Private Sub ds_InitializeMeasureDescriptor(sender As Object, e As InitializeMeasureDescriptorEventArgs)
If (e.MeasureDescriptor.Name = "Gender") Then
e.MeasureDescriptor.Aggregation = MeasureAggregation.Count
If (e.MeasureDescriptor.Name = "Equity") Then
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 "DateRegistered"
level.MemberProvider = Function(item) "All Registration Dates"
Exit Select
Case "DateCompetent"
level.MemberProvider = Function(item) "All Completion Dates"
Case "SETA"
level.MemberProvider = Function(item) "All SETA's"
Case "OFO_CODE"
level.MemberProvider = Function(item) "All OFO CODE"
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()
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("SETA")), "None", s.Item("SETA")), _
IIf(IsDBNull(s.Item("OFO_Code")), "0", s.Item("OFO_Code")), _
IIf(IsDBNull(s.Item("Gender_code")), "M", s.Item("Gender_code")), _
IIf(IsDBNull(EquityCode(s.Item("Equity_code"))), "U", EquityCode(s.Item("Equity_code"))), _
IIf(IsDBNull(s.Item("DateRegisRes")), "1900/01/01", s.Item("DateRegisRes")), _
IIf(IsDBNull(s.Item("DatecompRes")), "1900/01/01", s.Item("DatecompRes"))))
Return listOfStudent
End Class
Class Student
Private m_SETA As String
Private m_OFO_Code As String
Private m_Gender As String
Private m_Equity As String
Private m_DateRegistered As Date
Private m_DateCompetent As Date
Public Sub New(SETA As String, OFO_CODE As String, GENDER As String, EQUITY As String, DateRegistered As Date, DateCompetent As Date)
Me.m_SETA = SETA
Me.m_OFO_Code = OFO_CODE
Me.m_Gender = GENDER
Me.m_Equity = EQUITY
Me.m_DateRegistered = DateRegistered.ToShortDateString
Me.m_DateCompetent = DateCompetent.ToShortDateString
<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 String
Return m_DateRegistered
Set(value As String)
m_DateRegistered = value
<System.ComponentModel.Category("Equity code")>
Return m_Equity
m_Equity = 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 Double
Return m_OFO_Code
Set(value As Double)
m_OFO_Code = value
<System.ComponentModel.Category("Gender")>
Public Property Gender As Integer
Return m_Gender
Set(value As Integer)
m_Gender = value
See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text ************** System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index at System.ThrowHelper.ThrowArgumentOutOfRangeException() at System.Collections.Generic.List`1.get_Item(Int32 index) at Infragistics.Olap.Core.TableView.Initialize() at Infragistics.Olap.Core.TableView.get_ColumnHeaders() at Infragistics.Win.UltraWinPivotGrid.Data.OlapDataView.get_ColumnHeaderGrid() at Infragistics.Win.UltraWinPivotGrid.Data.OlapDataView.get_ColumnTupleIndices() at Infragistics.Win.UltraWinPivotGrid.Data.OlapDataView.SyncSortPositions(TableView tableView) at Infragistics.Win.UltraWinPivotGrid.Data.OlapDataView.get_TableView() at Infragistics.Win.UltraWinPivotGrid.Data.OlapDataView.VerifySort() at Infragistics.Win.UltraWinPivotGrid.UIElements.UltraPivotGridUIElement.PositionChildElements() at Infragistics.Win.UIElement.VerifyChildElements(ControlUIElementBase controlElement, Boolean recursive) at Infragistics.Win.UIElement.DrawHelper(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Boolean clipText, Boolean forceDrawAsFocused, Boolean preventAlphaBlendGraphics) at Infragistics.Win.UIElement.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Boolean forceDrawAsFocused, Boolean preventAlphaBlendGraphics) at Infragistics.Win.ControlUIElementBase.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Size elementSize, Boolean preventAlphaBlendGraphics) at Infragistics.Win.ControlUIElementBase.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode, Size elementSize) at Infragistics.Win.ControlUIElementBase.Draw(Graphics graphics, Rectangle invalidRectangle, Boolean doubleBuffer, AlphaBlendMode alphaBlendMode) at Infragistics.Win.UltraControlBase.OnPaint(PaintEventArgs pe) at Infragistics.Win.UltraWinPivotGrid.UltraPivotGrid.OnPaint(PaintEventArgs pe) at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer) at System.Windows.Forms.Control.WmPaint(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
************** Loaded Assemblies ************** mscorlib Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.18449 built by: FX451RTMGDR CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll ---------------------------------------- StudentPlacement Assembly Version: 1.0.5422.39166 Win32 Version: 1.0.* CodeBase: file:///C:/College%20Placement%20Program/StudentPlacement/StudentPlacement/bin/Debug/StudentPlacement.exe ---------------------------------------- Microsoft.VisualBasic Assembly Version: 10.0.0.0 Win32 Version: 11.0.50709.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll ---------------------------------------- System Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.34239 built by: FX452RTMGDR CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll ---------------------------------------- System.Core Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll ---------------------------------------- System.Windows.Forms Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.18046 built by: FX45RTMGDR CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll ---------------------------------------- System.Drawing Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.18022 built by: FX45RTMGDR CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll ---------------------------------------- System.Runtime.Remoting Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.34107 built by: FX45W81RTMGDR CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Remoting/v4.0_4.0.0.0__b77a5c561934e089/System.Runtime.Remoting.dll ---------------------------------------- Infragistics4.Documents.Reports.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Documents.Reports.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Documents.Reports.v14.2.dll ---------------------------------------- Infragistics4.Win.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.v14.2.dll ---------------------------------------- Infragistics4.Shared.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Shared.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Shared.v14.2.dll ---------------------------------------- System.Data Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.18046 built by: FX45RTMGDR CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll ---------------------------------------- System.Xml Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.34230 built by: FX452RTMGDR CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll ---------------------------------------- System.Configuration Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.18058 built by: FX45RTMGDR CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll ---------------------------------------- Infragistics4.Win.UltraWinDataSource.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinDataSource.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinDataSource.v14.2.dll ---------------------------------------- Infragistics4.Win.Misc.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.Misc.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.Misc.v14.2.dll ---------------------------------------- UIAutomationProvider Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/UIAutomationProvider/v4.0_4.0.0.0__31bf3856ad364e35/UIAutomationProvider.dll ---------------------------------------- Infragistics4.Win.UltraWinGrid.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinGrid.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinGrid.v14.2.dll ---------------------------------------- Infragistics4.Win.SupportDialogs.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.SupportDialogs.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.SupportDialogs.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinGrid.ExcelExport.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinGrid.ExcelExport.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinGrid.ExcelExport.v14.2.dll ---------------------------------------- Infragistics4.Documents.Excel.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Documents.Excel.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Documents.Excel.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinPrintPreviewDialog.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinPrintPreviewDialog.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinPrintPreviewDialog.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinGrid.DocumentExport.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinGrid.DocumentExport.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinGrid.DocumentExport.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinToolbars.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinToolbars.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinToolbars.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinStatusBar.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinStatusBar.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinStatusBar.v14.2.dll ---------------------------------------- Accessibility Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Accessibility/v4.0_4.0.0.0__b03f5f7f11d50a3a/Accessibility.dll ---------------------------------------- System.Transactions Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll ---------------------------------------- System.EnterpriseServices Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll ---------------------------------------- System.Numerics Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll ---------------------------------------- UIAutomationTypes Assembly Version: 4.0.0.0 Win32 Version: 4.0.30319.17929 built by: FX45RTMREL CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/UIAutomationTypes/v4.0_4.0.0.0__31bf3856ad364e35/UIAutomationTypes.dll ---------------------------------------- Infragistics4.Win.UltraWinTabControl.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinTabControl.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinTabControl.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinEditors.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinEditors.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinEditors.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinSchedule.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinSchedule.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinSchedule.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinPivotGrid.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinPivotGrid.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinPivotGrid.v14.2.dll ---------------------------------------- Infragistics4.Olap.DataSource.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Olap.DataSource.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Olap.DataSource.v14.2.dll ---------------------------------------- Infragistics4.Win.UltraWinTree.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Win.UltraWinTree.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Win.UltraWinTree.v14.2.dll ---------------------------------------- Infragistics4.Olap.Core.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Olap.Core.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Olap.Core.v14.2.dll ---------------------------------------- Infragistics4.Olap.DataSource.Flat.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Olap.DataSource.Flat.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Olap.DataSource.Flat.v14.2.dll ---------------------------------------- Infragistics4.Olap.DataProvider.Flat.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Olap.DataProvider.Flat.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Olap.DataProvider.Flat.v14.2.dll ---------------------------------------- Infragistics4.Olap.DataSource.Mdx.v14.2 Assembly Version: 14.2.20142.2010 Win32 Version: 14.2.20142.2010 CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Infragistics4.Olap.DataSource.Mdx.v14.2/v4.0_14.2.20142.2010__7dd5c3163f2cd0cb/Infragistics4.Olap.DataSource.Mdx.v14.2.dll ----------------------------------------
************** JIT Debugging ************** To enable just-in-time (JIT) debugging, the .config file for this application or computer (machine.config) must have the jitDebugging value set in the system.windows.forms section. The application must also be compiled with debugging enabled.
For example:
<configuration> <system.windows.forms jitDebugging="true" /> </configuration>
When JIT debugging is enabled, any unhandled exception will be sent to the JIT debugger registered on the computer rather than be handled by this dialog box.
Thank you for the provided code.
I will try elaborate on how the code works and this will hopefully make everything clear and will allow us to find what is going wrong in your sample.
First off after the CreateIEnumerable method the sample no longer works with fields from the data base. The FlatDataSource works with IEnumerable collections, which means that the objects must be loaded in memory. The point of the CreateIEnumerable is to convert the SqlDataReader records to an IEnumerable collection which can be used by the FlatDataSource. After that all the names you see are properties and not field names. The FlatDataSource has no way to interpret data from your database. That is why additional class (in your case Student) must be defined – so you can map the records from the database to in-memory objects, which are used by the FlatDataSource. The properties of this class will be shown in the PivotGrid.
The events InitializeHierarchyDescriptor, InitializeMeasureDescriptor and AggregateMeasure are entirely for customization of the grid. I have added them so you can see how you can make custom aggregations, show the values with custom format and display custom strings for the rows and columns headers. You don’t need them for simply displaying the default pivot grid. The FlatDataSourceInitialSettings object determines the initial measures, rows and columns. This is also optional - you can later set them through the UI, by using the OlapDataSelector.
As for your Student class, it has only string properties. The pivot grid use will numeric properties for Measures and any other properties for Columns and Rows (in my sample Players and Budget are measures, while Sport, City and Nationality - rows and columns). This means that in your case, the grid will have columns and rows, but no measures (the actual data in the cells). You need numeric types for the PivotGrid to work. If in your case the grid is displaying only rows and column headers and no cells, then this is definitely the issue.
I hope this makes everything about how the sample works clear. Let me know if you still don’t understand anything. Also please answer the questions I have asked in my previous post (so we can narrow when the issue is happening).