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

It seems that the error happens because some of your columns allow null values and those can’t be converted to String. So what you could do in order to fix this is to add logic in the CreateIEnumerable method, which checks if Item(1), Item(2), etc. is equal to DBNull and if it is to use the default value for the value type instead. Note that those checks has to be implemented for columns of your database, where null values are allowed.

As for the queries, I have used SELECT * for simplicity, as an example. If you are joining two tables you should use the name of the fields that you are requesting.

Please let me know if you have any additional questions.

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?

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

IIf(IsDBNull(s.Item(5)), "", s.Item(5)), _

IIf(IsDBNull(s.Item(6)), "", s.Item(6))))

Return listOfSETA_Qualifications

End Function

Anybody that can help me?  I need to populate the PivotGrid... but my code above does not do tht

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