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

Hi Marius,

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).

I am looking forward to your reply.

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...!!

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.

Hi Marius,

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.

Private Function CreateIEnumerable(reader As SqlDataReader) As IEnumerable(Of Student)

Dim listOfStudent = reader.Cast(Of IDataRecord).

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"))))

Return listOfStudent

End Function

I also shortened my SQL part of my code.

Public Sub FillPivotGrid()

MsgBox(StartingDate & " " & EndingDate)

Dim Table As New DataTable

Dim query As String = " SELECT distinct QUALIFICATION.[SETA] , " & _

"QUALIFICATION.[OFO_Code] , " & _

"SUM(CASE " & _

"WHEN STUDENT.[Gender_Code] ='M' THEN 1 ELSE 0 END) as [MALE] " & _

", " & _

"SUM(CASE " & _

"WHEN STUDENT.[Gender_Code] ='F' THEN 1 ELSE 0 END) as [FEMALE] " & _

", " & _

"SUM(CASE " & _

"WHEN STUDENT.[Equity_Code] ='BA' THEN 1 ELSE 0 END) as [Black] " & _

", " & _

"SUM(CASE " & _

"WHEN STUDENT.[Equity_Code] ='BC' THEN 1 ELSE 0 END) as [Coloured] " & _

", " & _

"SUM(CASE " & _

"WHEN STUDENT.[Equity_Code] ='BI' THEN 1 ELSE 0 END) as [Indian] " & _

", " & _

"SUM(CASE " & _

"WHEN STUDENT.[Equity_Code] ='WH' THEN 1 ELSE 0 END) as [White] " & _

", " & _

"SUM(CASE " & _

"WHEN STUDENT.[Equity_Code] ='U' THEN 1 ELSE 0 END) as [Unknown] , " & _

"QUALIFICATION.[DateRegisRes] , " & _

"QUALIFICATION.[DateCompRes] , " & _

"QUALIFICATION.[ProgramName] " & _

"FROM (STUDENT JOIN QUALIFICATION ON " & _

"QUALIFICATION.[ID] = STUDENT.[ID] ) " & _

"WHERE QUALIFICATION.[DateRegisRes] " & _

"BETWEEN @StartDate and @EndDate " & _

"GROUP by QUALIFICATION.[SETA] , " & _

"QUALIFICATION.[OFO_Code] , " & _

"STUDENT.[Gender_Code] , " & _

"Student.[Equity_Code] , " & _

"QUALIFICATION.[DateRegisRes] , " & _

"QUALIFICATION.[DateCompRes] , " & _

"QUALIFICATION.[ProgramName] , " & _

"QUALIFICATION.[National_id]"

Using conn As New SqlConnection(constr)

Using cmd As New SqlCommand(query, conn)

cmd.Parameters.AddWithValue("@StartDate", StartingDate)

cmd.Parameters.AddWithValue("@EndDate", EndingDate)

cmd.CommandType = CommandType.Text

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

End Using

End Sub

Image

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

    End Sub

    Private Sub Month_Start_Click(sender As Object, e As EventArgs) Handles Month_Start.Click

        StartingDate = (Month_Start.CalendarInfo.ActiveDay.Date).ToShortDateString

    End Sub

    Public Sub FillPivotGrid()

        Dim Table As New DataTable

        Dim query As String = "  SELECT distinct   QUALIFICATION.[SETA]  ,                                          " & _

                                        "QUALIFICATION.[OFO_Code] ,                                                 " & _

                                        "SUM(CASE                                                                   " & _

                                        "WHEN STUDENT.[Gender_Code]  ='M' and                                       " & _

                                        "QUALIFICATION.[DateRegisRes]                                               " & _

                                        "BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [MALE]               " & _

                                        ",                                                                          " & _

                                        "SUM(CASE                                                                   " & _

                                        "WHEN STUDENT.[Gender_Code]  ='F'    and                                    " & _

                                        "QUALIFICATION.[DateRegisRes]                                               " & _

                                         "BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [FEMALE]            " & _

                                        ",                                                                          " & _

                                         "SUM(CASE                                                                  " & _

                                        "WHEN STUDENT.[Equity_Code]  ='BA'  and                                     " & _

                                        "QUALIFICATION.[DateRegisRes]                                               " & _

                                         "BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Black]             " & _

                                        ",                                                                          " & _

                                         "SUM(CASE                                                                  " & _

                                        "WHEN STUDENT.[Equity_Code]  ='BC'  and                                     " & _

                                        "QUALIFICATION.[DateRegisRes]                                               " & _

                                         "BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Coloured]          " & _

                                        "                                                                           " & _

                                        ",                                                                          " & _

                                        "SUM(CASE                                                                   " & _

                                        "WHEN STUDENT.[Equity_Code]  ='BI' and                                      " & _

                                        "QUALIFICATION.[DateRegisRes]                                               " & _

                                         "BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Indian]            " & _

                                        ",                                                                          " & _

                                        "SUM(CASE                                                                   " & _

                                        "WHEN STUDENT.[Equity_Code]  ='WH'    and                                   " & _

                                        "QUALIFICATION.[DateRegisRes]                                               " & _

                                         "BETWEEN @StartDate and @EndDate THEN 1 ELSE 0  END) as [White]            " & _

                                        ",                                                                          " & _

                                         "SUM(CASE                                                                  " & _

                                        "WHEN STUDENT.[Equity_Code]  ='U'  and                                      " & _

                                        "QUALIFICATION.[DateRegisRes]                                               " & _

                                        "BETWEEN @StartDate and @EndDate THEN 1 ELSE 0 END) as [Unknown]  ,          " & _

                                        "QUALIFICATION.[DateRegisRes]  ,           " & _

                                        "QUALIFICATION.[DatecompRes] ,             " & _

                                        "QUALIFICATION.[ProgramName]                " & _

                                        "FROM  (STUDENT  JOIN  QUALIFICATION   ON  " & _

                                        "QUALIFICATION.[ID] = STUDENT.[ID] )       " & _

                                        "GROUP by  QUALIFICATION.[SETA] ,           " & _

                                        "QUALIFICATION.[OFO_Code] ,                " & _

                                        "STUDENT.[Gender_Code] ,                   " & _

                                        "Student.[Equity_Code]  ,                  " & _

                                        "QUALIFICATION.[DateRegisRes]  ,           " & _

                                        "QUALIFICATION.[DatecompRes] ,              " & _

                                        "QUALIFICATION.[ProgramName]"

        Using conn As New SqlConnection(constr)

            Using cmd As New SqlCommand(query, conn)

                cmd.Parameters.AddWithValue("@StartDate", StartingDate)

                cmd.Parameters.AddWithValue("@EndDate", EndingDate)

                cmd.CommandType = CommandType.Text

                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

        End Using

    End Sub

    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

                End If

                count += 1.0F

            Next

            e.Value = total / count

            e.Handled = True

        End If

        If e.MeasureDescriptor.Name = "FeMale" 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.FeMale

                End If

                count += 1.0F

            Next

            e.Value = total / count

            e.Handled = True

        End If

        If e.MeasureDescriptor.Name = "Black" 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.Black

                End If

                count += 1.0F

            Next

            e.Value = total / count

            e.Handled = True

        End If

        If e.MeasureDescriptor.Name = "Indian" 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.Indian

                End If

                count += 1.0F

            Next

            e.Value = total / count

            e.Handled = True

        End If

        If e.MeasureDescriptor.Name = "White" 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.White

                End If

                count += 1.0F

            Next

            e.Value = total / count

            e.Handled = True

        End If

        If e.MeasureDescriptor.Name = "Coloured" 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.Coloured

                End If

                count += 1.0F

            Next

            e.Value = total / count

            e.Handled = True

        End If

        If e.MeasureDescriptor.Name = "Unknown" 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.Unknown

                End If

                count += 1.0F

            Next

            e.Value = total / count

            e.Handled = True

        End If

    End Sub

    Private Sub ds_InitializeMeasureDescriptor(sender As Object, e As InitializeMeasureDescriptorEventArgs)

        If (e.MeasureDescriptor.Name = "Male") Then

            e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum

        End If

        If (e.MeasureDescriptor.Name = "Female") Then

            e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum

        End If

        If (e.MeasureDescriptor.Name = "Black") Then

            e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum

        End If

        If (e.MeasureDescriptor.Name = "Indian") Then

            e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum

        End If

        If (e.MeasureDescriptor.Name = "Coloured") Then

            e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum

        End If

        If (e.MeasureDescriptor.Name = "White") Then

            e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum

        End If

        If (e.MeasureDescriptor.Name = "Unknown") Then

            e.MeasureDescriptor.Aggregation = MeasureAggregation.Sum

        End If

    End Sub

    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.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]"

        Return ds

    End Function

    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"

                    Exit Select

                Case "DateRegistered"

                    level.MemberProvider = Function(item) "All Registration Dates"

                    Exit Select

                Case "DateCompetent"

                    level.MemberProvider = Function(item) "All Completion Dates"

                    Exit Select

            End Select

        End If

    End Sub

    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click

        Me.Close()

    End Sub

    Private Sub UltraButton1_Click(sender As Object, e As EventArgs) Handles UltraButton1.Click

        FillPivotGrid()

    End Sub

    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")), "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")), "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

    End Function

    Private Sub btnLoadGrid_Click(sender As Object, e As EventArgs) Handles btnLoadGrid.Click

        Dim Table As New DataTable

        Dim query As String = "  SELECT  STUDENT.[id],                              " & _

                                        "QUALIFICATION.[SETA]  ,                    " & _

                                        "QUALIFICATION.[OFO_Code] ,                " & _

                                        "STUDENT.[Gender_Code] ,                   " & _

                                        "Student.[Equity_Code]  ,                  " & _

                                        "QUALIFICATION.[DateRegisRes]  ,           " & _

                                       " QUALIFICATION.[DatecompRes]               " & _

                                        "FROM  (STUDENT  JOIN  QUALIFICATION   ON  " & _

                                        "QUALIFICATION.[ID] = STUDENT.[ID] )       "

        Using conn As New SqlConnection(constr)

            Using cmd As New SqlCommand(query, conn)

                cmd.CommandType = CommandType.Text

                conn.Open()

                Dim reader = cmd.ExecuteReader()

                Table.Load(reader)

                UltraGrid1.DataSource = Table

            End Using

        End Using

    End Sub

    Private Sub PivotTableExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        StartingDate = (Now().AddDays(-180)).ToShortDateString

        EndingDate = Now.ToShortDateString

    End Sub

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

    End Sub

    <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

        Get

            Return m_DateRegistered

        End Get

        Set(value As Date)

            m_DateRegistered = value

        End Set

    End Property

    <System.ComponentModel.Category("Program Name")>

    Public Property ProgramName As String

        Get

            Return m_ProgramName

        End Get

        Set(value As String)

            m_ProgramName = value

        End Set

    End Property

    <System.ComponentModel.Category("Equity Coloured")>

    Public Property Coloured As Integer

        Get

            Return m_Coloured

        End Get

        Set(value As Integer)

            m_Coloured = value

        End Set

    End Property

    <System.ComponentModel.Category("Equity Black")>

    Public Property Black As Integer

        Get

            Return m_Black

        End Get

        Set(value As Integer)

            m_Black = value

        End Set

    End Property

    <System.ComponentModel.Category("Equity Indian")>

    Public Property Indian As Integer

        Get

            Return m_Indian

        End Get

        Set(value As Integer)

            m_Indian = value

        End Set

    End Property

    <System.ComponentModel.Category("Equity White")>

    Public Property White As Integer

        Get

            Return m_White

        End Get

        Set(value As Integer)

            m_White = value

        End Set

    End Property

    <System.ComponentModel.Category("Equity Unknown")>

    Public Property Unknown As Integer

        Get

            Return m_Unknown

        End Get

        Set(value As Integer)

            m_Unknown = value

        End Set

    End Property

    <System.ComponentModel.Category("SETA NAME")>

    Public Property SETA As String

        Get

            Return m_SETA

        End Get

        Set(value As String)

            m_SETA = value

        End Set

    End Property

    <System.ComponentModel.Category("OFO Code")>

    Public Property OFO_CODE As String

        Get

            Return m_OFO_Code

        End Get

        Set(value As String)

            m_OFO_Code = value

        End Set

    End Property

    <System.ComponentModel.Category("Male")>

    Public Property Male As Integer

        Get

            Return m_Male

        End Get

        Set(value As Integer)

            m_Male = value

        End Set

    End Property

    <System.ComponentModel.Category("FeMale")>

    Public Property FeMale As Integer

        Get

            Return m_FeMale

        End Get

        Set(value As Integer)

            m_FeMale = value

        End Set

    End Property

End Class

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

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.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]"

Return ds

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

 End Function

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 Table As New DataTable

 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] " & _

 "FROM (STUDENT JOIN QUALIFICATION ON " & _

 "QUALIFICATION.[ID] = STUDENT.[ID] ) " & _

 "GROUP by QUALIFICATION.[SETA] , " & _

 "QUALIFICATION.[OFO_Code] , " & _

 "STUDENT.[Gender_Code] , " & _

 "Student.[Equity_Code] , " & _

 "QUALIFICATION.[DateRegisRes] , " & _

 "QUALIFICATION.[DatecompRes] , " & _

 "QUALIFICATION.[ProgramName]"

  Using conn As New SqlConnection(constr)

 Using cmd As New SqlCommand(query, conn)

 cmd.CommandType = CommandType.Text

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

 End Using

 End Sub