Returns the data and statuses of cells, optionally applying scaling and formatting, and 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 String subtype.
GetTextCellsWithRowSuppression 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.
For a similar function that returns data as a Double subtype and does not provide formatting and scaling options, see GetCellsWithRowSuppression. This function returns data in a Variant array of a Double subtype. |
<HsvData>.GetTextCellsWithRowSuppression varalScenario, varalYear, varalPeriod, varalView, varalEntity, varalParent, varalValue, varalAccount, varalICP, varalCustom1, varalCustom2, varalCustom3, varalCustom4, sNumDecimals, sScale, lSuppressOptions, lNumRows, pvarabRowIsSuppressed, pvarabstrData, pvaralStatusLong array (ByVal). The member IDs of the cells' Scenario dimension members. | |
Long array (ByVal). The member IDs of the cells' Year dimension members. | |
Long array (ByVal). The member IDs of the cells' Period dimension members. | |
Long array (ByVal). The member IDs of the cells' View dimension members. | |
Long array (ByVal). The member IDs of the cells' Entity dimension members. | |
Long array (ByVal). The member IDs of the parents of the varalEntity argument's entities. | |
Long array (ByVal). The member IDs of the cells' Value dimension members. | |
Long array (ByVal). The member IDs of the cells' Account dimension members. | |
Long array (ByVal). The member IDs of the cells' Intercompany Partner dimension members. | |
Long array (ByVal). The member IDs of the cells' Custom 1 dimension members. | |
Long array (ByVal). The member IDs of the cells' Custom 2 dimension members. | |
Long array (ByVal). The member IDs of the cells' Custom 3 dimension members. | |
Long array (ByVal). The member IDs of the cells' Custom 4 dimension members. | |
Integer (ByVal). The number of decimal places that the data returned in the pvarabstrData argument will contain. To use the application’s default number of decimals, pass the HFMConstants type library constant DEFAULT_NUM_DECIMALS, described in Number Defaults Constants. | |
Integer (ByVal). Indicates the degree of scaling to apply to the data returned in the pvarabstrData argument. To leave the data unscaled, pass 0. To scale the data, each whole-number increment over 0 scales by ten. In other words, passing 1 scales by ten, passing 2 scales by a hundred, and so on. To apply the application’s default scaling, pass the HFMConstants type library constant DEFAULT_SCALE, described in Number Defaults Constants. | |
Long (ByVal). The type of suppression to be applied. You can specify any combination of the following hexadecimal values:
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. | |
Long (ByVal). Specifies the number of rows that the point of view comprises. | |
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. | |
Variant array. Returns data for the rows of cells that are not suppressed. 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. | |
Variant array. Returns the statuses of the cells returned in the pvarabstrData argument. These two arrays have a one-to-one correspondence. For information on the values returned, see About Cell Statuses. |
The following subroutine gets data for all accounts that have valid intersections with a given set of dimension members, and scales the data according to the scale factor passed in. 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 GetTextCellsWithRowSuppression’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 getValidAcctRowsScale(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, iScale As Integer) 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.GetTextCellsWithRowSuppression 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, _ DEFAULT_NUM_DECIMALS, iScale, &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