Skip to Content
author's profile photo
Former Member

DI UI : fill matrix with a query from user table data

hello,

I create a user table.

I want to fill a matrix on a form with some aggregated data of this user table.

I do :

Public Sub SetMatrixRep()

Dim oRecordSet As SAPbobsCOM.Recordset

Dim oCentre As SAPbouiCOM.EditText

Dim oCompte As SAPbouiCOM.EditText

Dim oBudget As SAPbouiCOM.EditText

Dim oDepense As SAPbouiCOM.EditText

Dim oSolde As SAPbouiCOM.EditText

Dim i As Long

i = 0

Set oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

oRecordSet.DoQuery ("select * from [@CAP_OPRC_BUDGET]")

While oRecordSet.EOF = False

' filling the matrix

Set oCentre = oMatrixRep.Columns("clCentre").Cells.Item(i).Specific

oCentre.String = oRecordSet.Fields.Item(0).Value

'oMatrixRep.Columns("clCentre").Cells(1).Specific.String = oRecordSet.Fields.Item(0).Value

'SBO_Application.MessageBox (oRecordSet.Fields.Item(0).Value)

oMatrixRep.AddRow

oRecordSet.MoveNext

i = i + 1

Wend

End Sub

I've got an error : Row - Index invalid.

Could you help me please ?

Thanks.

Romeo.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • author's profile photo
    Former Member
    Posted on Jun 24, 2004 at 04:20 PM

    Hi

    This is a routine that I call from my Item Event handler

    Hope it can help you

    Public Sub PopolaMatrice(oApplicazione As SAPbouiCOM.Application, pVal As SAPbouiCOM.IItemEvent)

    Dim oForm As SAPbouiCOM.Form

    Dim oMatrix As SAPbouiCOM.Matrix

    Dim oDBDataSource As SAPbouiCOM.DBDataSource

    Dim oConditions As New SAPbouiCOM.Conditions

    Dim oCondition As SAPbouiCOM.Condition

    Dim oDocNum As SAPbouiCOM.EditText

    Dim lIndice As Long

    Set oForm = oApplicazione.Forms.GetFormByTypeAndCount(pVal.FormType, pVal.FormTypeCount)

    Set oDBDataSource = oForm.DataSources.DBDataSources.Item("MyUserTableName")

    Set oMatrix = oForm.Items("MyMatrixName").Specific

    oMatrix.Clear

    'setting the condition object

    'this is equal to the following SQL statement

    'WHERE UserFieldName1 = "MyCriteria1" and UserFieldName2 = "MyCriteria2"

    Set oCondition = oConditions.Add()

    oCondition.BracketOpenNum = 2

    oCondition.Alias = "UserFieldName1"

    oCondition.Operation = co_EQUAL

    oCondition.CondVal = "MyCriteria1"

    oCondition.BracketCloseNum = 1

    oCondition.Relationship = cr_AND

    Set oCondition = oConditions.Add()

    oCondition.BracketOpenNum = 1

    oCondition.Alias = "UserFieldName2"

    oCondition.Operation = co_EQUAL

    oCondition.CondVal ="MyCriteria2"

    oCondition.BracketCloseNum = 2

    oDBDataSource.Query oConditions

    'filling the matrix

    For lIndice = 0 To oDBDataSource.Size - 1

    oDBDataSource.Offset = lIndice

    oMatrix.AddRow

    Next lIndice

    End Sub

    Add comment
    10|10000 characters needed characters exceeded

    • Hi again (still waiting for any answer to my previous post),

      I have had same problem with the Query object. It seems that is not possible to use order or group functionallity with it. Does anybody have another solution for this?

      Regards,

      Ibai Peñ

  • author's profile photo
    Former Member
    Posted on Jun 24, 2004 at 06:34 PM

    Hi,

    if you want to fill a matrix using an edittext (or other control) property, you need to add a row first. Then you can access the cells of this row. This does make sense, too. You cannot write in a row which does not exist yet. This is why you get the exception.

    If you use the userdatasource.value property (as recommended by SAP), you first fill the value properties and then add a row as one would expect.

    HTH Lutz Morrien

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Ibai Peña

      Ibai,

      SAP has published a pdf file in SAP Portal. It is called something like "Performance issues when filling a matrix". Take a look at it.

      I'll see if i can get a hold of the real title.

      I tried both variants (filling by control and filling by userdatasource). I could not see a lot of difference between the two performancewise. (For some reason, they both are really slow)

      However, filling by userdatasource seems to make more sense to me. You fill the datasource and then add the row.

      HTH Lutz Morrien

  • author's profile photo
    Former Member
    Posted on Jun 25, 2004 at 07:30 AM

    hello,

    thanks for answer.

    But Giovani how do you proceed if your select query is like that :

    select count(*), field1 from table1 group by field1

    Is there a solution with Condition Objects ?

    regards,

    Romeo.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Jun 25, 2004 at 07:43 AM

    Hi !

    I'va tried to do this ( Thanks Lutz in fact I had to create a row before ! )

    Dim oRecordSet As SAPbobsCOM.Recordset

    Dim oCentre As SAPbouiCOM.EditText

    Dim oCompte As SAPbouiCOM.EditText

    Dim oBudget As SAPbouiCOM.EditText

    Dim oDepense As SAPbouiCOM.EditText

    Dim oSolde As SAPbouiCOM.EditText

    Dim i As Long

    i = 1

    Set oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

    oRecordSet.DoQuery ("select * from [@CAP_OPRC_BUDGET]")

    While oRecordSet.EOF = False

    ' HERE ADD A ROW TO THE MATRIX

    ' Addrow (number of the row to be added, number next row)

    oMatrixRep.AddRow i, i + 1

    ' filling the matrix

    Set oCentre = oMatrixRep.Columns("clCentre").Cells.Item(i).Specific

    oCentre.String = oRecordSet.Fields.Item(0).Value

    ' SBO_Application.MessageBox (oRecordSet.Fields.Item(0).Value)

    oRecordSet.MoveNext

    i = i + 1

    Wend

    Thanks,

    Romeo.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Jun 28, 2004 at 07:56 AM

    Hi Lutz,

    can u please tell me in which part if the market Place is post the PDF, I can find it.

    Thank's

    Add comment
    10|10000 characters needed characters exceeded