There are some situations where the developer would like to find out programmatically the row index of a set of data items that all relate to each other. For instance, let us assume we have the following cube view.

- 1995

     + Qtr 1

     + Qtr 2

     + Qtr 3

     + Qtr 4

- 1996

     + Qtr 1

     + Qtr 2

     + Qtr 3

     + Qtr 4

DynamiCube does not inherently support retrieving the RowIndex for the “Qtr 3” associated with 1996. However, we can achieve this with some custom code.

The way to do this would be to setup a GetRowIndex method. This method would take an array of DataItem names as its parameter. This will allow the method to account for a varying number of row fields and have a signature of

  
 ' Visual Basic
 
 Private Function GetRowIndex(itemNamesAs Variant) As Long
 ‘
 End Function 

To use the GetRowIndex method the developer would set up an array.

  
 ' Visual Basic
 
 Dim arDataItemList(1) As Variant
 
 arDataItemList (0) = "1996"
 arDataItemList (1) = "Qtr 3" 

Then, the developer would pass this array to our GetRowIndex method. To print the row index of our “1996”, “Qtr 3” array we would simply use a line of code like the following.

  
 ' Visual Basic
 Debug.Print GetRowIndex(arDataItemList) 

The implementation of the GetRowIndex method would essentially consist of looping through the rows in DynamiCube and using the RowHeading property to find each element of the array. Below is some sample code that you can use within your application to achieve this.

  
 ' Visual Basic
 Public Function GetRowIndex(ByVal itemNames As Variant) As Long
 
    Dim rowIndex As Long
    Dim depth As Long
    Dim arrayIndex As Long
    Dim startIndex As Long
    Dim endIndex As Long
 
    If Not IsArray(itemNames) Then Exit Function
 
    ' Initialize the variables to begin the recursive looping
    startIndex = 0
    endIndex = m_cube.RowCount - 1
 
    ' For every item in the array, find it
    For arrayIndex = LBound(itemNames) To UBound(itemNames)
 
       ' When the item is not found, return the "depth" (as a negative value)
       If Not IsItemFound(arrayIndex, itemNames(arrayIndex), startIndex, endIndex) Then
          GetRowIndex = (arrayIndex + 1) * -1
       Exit Function
       End If
 
    Next
 
    ' If all items match up, return the starting index (could be expanded)
    GetRowIndex = startIndex
 
 End Function
 
 Private Function IsItemFound(ByVal fieldPosition As Long, ByVal itemName As String, Optional ByRef startIndex As Long = -1, Optional ByRef endIndex As Long = -1) As Boolean
 
    Dim startRow As Long
    Dim endRow As Long
    Dim thisRow As Long
    Dim thisItemName As String
    Dim isFound As Boolean
 
    ' Move the start and end indexes to temp variables
    ' We use them as a starting point and we update them as we loop
    startRow = IIf(startIndex = -1, 0, startIndex)
    endRow = IIf(endIndex = -1, m_cube.RowCount - 1, endIndex)
 
    ' Initialize 'thisRow' so we can begin testing
    ' each item in the array, and also, indicate it isn't found yet
    thisRow = startRow
    isFound = False
 
    Do While thisRow <= endRow
 
       ' This logic helps porting code to a different language a lot easier
       If TypeName(m_cube.RowHeading(thisRow, fieldPosition + 1)) = "IDataItem" Then
          thisItemName = m_cube.RowHeading(thisRow, fieldPosition + 1).Name
       Else
          thisItemName = m_cube.RowHeading(thisRow, fieldPosition + 1)
       End If
 
       ' See if the item name matches the item passed in
       If thisItemName = itemName Then
          ' If it was not found yet, update the start
          ' position and set a flag
          If Not isFound Then
             startIndex = thisRow
             isFound = True
          End If
 
          ' We always update the 'endIndex' parameter as we loop
          endIndex = thisRow
 
       Else
 
          ' If the item has already been found, we
          ' exit the loop so that we don't keep updating
          ' the endIndex parameter; otherwise, it hasn't been found
          ' yet and we have to keep looking until we find it
          If isFound Then Exit Do
 
       End If
 
       ' update the row number
       thisRow = thisRow + 1
 
    Loop
 
    ' The start and end indexes (parameters to this method)
    ' were updated, now we simply return a true or false
    ' to inform the caller of this method's findings
    IsItemFound = isFound
 End Function 

You can find this code used in the attached sample. When running the sample, click the “Expand (Confections, Pavlova) row” button on the form. This will use the GetRowIndex method to determine the row index of the “Pavlova” data item within the “Confections” data item. Once we have the row index we expand the row.

Please feel free to use the GetRowIndex method sample code within your own application or as a building block.

If you have any questions on this topic or the sample provided please feel free to contact our support team at DynamiCube.Support@DataDynamics.com.