GetCellsWithRowSuppression

Returns the data and statuses of cells, optionally excluding rows of cells that match specified criteria. You can exclude rows that contain no data, zero, and derived data, as well as rows consisting of invalid intersections. Data is returned in a Variant array of a Double subtype.

GetCellsWithRowSuppression takes arrays of dimension member IDs as arguments. These arrays must have a one-to-one correspondence to each other, as the corresponding array elements define the cells to be queried.

Tip:	For a similar function that provides formatting and scaling options, see GetTextCellsWithRowSuppression. This function returns data in a Variant array of a String subtype.

Syntax

<HsvData>.GetCellsWithRowSuppression varalScenario, varalYear, varalPeriod, varalView, varalEntity, varalParent, varalValue, varalAccount, varalICP, varalCustom1, varalCustom2, varalCustom3, varalCustom4, lSuppressOptions, lNumRows, pvaralNonSuppressedRowIndex, pvaradData, pvaralStatus

Argument	Description
varalScenario	Long array (ByVal). The member IDs of the cells' Scenario dimension members.
varalYear	Long array (ByVal). The member IDs of the cells' Year dimension members.
varalPeriod	Long array (ByVal). The member IDs of the cells' Period dimension members.
varalView	Long array (ByVal). The member IDs of the cells' View dimension members.
varalEntity	Long array (ByVal). The member IDs of the cells' Entity dimension members.
varalParent	Long array (ByVal). The member IDs of the parents of the varalEntity argument's entities.
varalValue	Long array (ByVal). The member IDs of the cells' Value dimension members.
varalAccount	Long array (ByVal). The member IDs of the cells' Account dimension members.
varalICP	Long array (ByVal). The member IDs of the cells' Intercompany Partner dimension members.
varalCustom1	Long array (ByVal). The member IDs of the cells' Custom 1 dimension members.
varalCustom2	Long array (ByVal). The member IDs of the cells' Custom 2 dimension members.
varalCustom3	Long array (ByVal). The member IDs of the cells' Custom 3 dimension members.
varalCustom4	Long array (ByVal). The member IDs of the cells' Custom 4 dimension members.
lSuppressOptions	Long (ByVal). The type of suppression to be applied. You can specify any combination of the following hexadecimal values: 0x00. No suppression; data is returned for all cells specified by the dimension member arguments. 0x01. Suppress rows in which all cells contain no data. 0x02. Suppress rows in which all cells contain derived data. 0x04. Suppress rows in which all cells contain a value of zero (0). 0x08. Suppress rows in which all cells consist of invalid intersections. For example, passing 0x01 suppresses rows consisting of cells that contain no data, while passing 0x05 suppresses rows consisting of cells that contain either zero or no data.
lNumRows	Long (ByVal). Specifies the number of rows that the point of view comprises. Caution! You must pass a valid number of rows; otherwise, an error will occur. For example, if you pass 3 and the member ID arrays cannot be resolved to three rows of data, an error occurs.
pvaralNonSuppressedRow Index	Variant array. Indicates whether a row is suppressed. Returns TRUE if the row is suppressed, FALSE otherwise. A row is suppressed only when all of the row’s cells meet the lSuppressOptions argument’s criteria. The array items are returned as a Boolean subtype.
pvaradData	Variant array. Returns data for the rows of cells that are not suppressed. Note: If only some of the cells in a row meet the lSuppressOptions argument’s suppression criteria, these cells will not be suppressed. Only rows in which all the cells meet the criteria are suppressed. You can correlate this data with the members passed in the member ID arguments by looping through the array returned in the pvarabRowIsSuppressed argument. The array items are returned as a Double subtype.
pvaralStatus	Variant array. Returns the statuses of the cells returned in the pvaradData argument. These two arrays have a one-to-one correspondence. For information on the values returned, see About Cell Statuses. The array items are returned as a Long subtype.

Example

The following subroutine gets data for all accounts that have valid intersections with a given set of dimension members. EnumAllMemberIDs gets the member IDs of all the Account dimension members. A count of the Account members is passed to the custom populateGlobalArrays procedure, which populates the arrays that identify the non-Account dimension members. The example loops through the array returned by GetCellsWithRowSuppression’s pvarabRowIsSuppressed argument. When an element is FALSE, GetLabel gets the corresponding account’s name, which is printed to Visual Basic’s Immediate window along with the data.

Private Sub getValidAcctRows(lScen As Long, lYear As Long, _ 
lPer As Long, lView As Long, lEnt As Long, lPar As Long, _ 
lVal As Long, lICP As Long, lCust1 As Long, lCust2 As Long, _ 
lCust3 As Long, lCust4 As Long)
Dim lNumRows As Long, vaRowRetd, vaData, vaStatus
Dim laAcct() As Long, sAcctName As String, lDataCount As Long
Dim cHsvTreeInfo As IHsvTreeInfo
Set cHsvTreeInfo = m_cHsvMetadata.Accounts
cHsvTreeInfo.EnumAllMemberIDs vaAcctIDs
lNumRows = UBound(vaAcctIDs)
ReDim laAcct(lNumRows)
For i = 0 To lNumRows
  laAcct(i) = vaAcctIDs(i)
Next i 
populateGlobalArrays lNumRows, lScen, lYear, lPer, lView, _ 
lEnt, lPar, lVal, lICP, lCust1, lCust2, lCust3, lCust4
m_cHsvData.GetCellsWithRowSuppression m_laScen, m_laYear, _ 
m_laPer, m_laView, m_laEnt, m_laPar, m_laVal, laAcct, _ 
m_laICP, m_laCust1, m_laCust2, m_laCust3, m_laCust4, &H8, _ 
lNumRows + 1, vaRowRetd, vaData, vaStatus
lDataCount = 0
For i = 0 To UBound(vaRowRetd)
  If vaRowRetd(i) = False Then
    cHsvTreeInfo.GetLabel laAcct(i), sAcctName
    Debug.Print sAcctName & ": " & vaData(lDataCount)
    lDataCount = lDataCount + 1
  End If
Next i
End Sub