Class Range Function (FSN.Range.Classifications)
Class Range Function (FSN.Range.Classifications)
The FSN.Range.Classifications function returns a dynamic array that can be used to quickly build out worksheets that segment data based upon the classifications. The syntax for the function is:
| Argument Name | Input Information | Required | Default Value |
|---|---|---|---|
| Include Filter | Specifies the classes to include. Multiple values can be specified by another dynamic array (e.g., using a named range, specifying a range of cells like E1:E10, etc) or concatenated as a string using the ^ character. Additionally, if the values are numeric wildcards and range values can be used such as 100-200, 10*, or 1*-20?0. | No | Include all classifications |
| Exclude Filter | Specifies the classes to exclude. Values can be specified as they are in the Include Filter | No | Exclude none |
| Subsidiary | Specifies the subsidiary to use to determine the classifications to return. Only classifications that are valid for the subsidiary are returned. | No | Includes all subsidiaries |
| Include Blank | Determines whether or not to include the <No Classifications> explicit blank value in the list | No | TRUE |
| Level | Specifies the maximum level of the hierarchy to return. A value of 2 will return all levels <= 2 | No | Includes all levels |
| Include Inactive | Specifies whether or not to return classifications marked as IsInactive=T in NetSuite | No | FALSE (Excludes inactive) |
| Include Rollup | Include rollup value (Hierarchy) for values with children | No | TRUE (parent rollup Hierarchy values are returned) |
| Rollup Only | If TRUE, then only top-level rollup values are returned | No | FALSE |
| Horizontal | Controls whether the returned dynamic array spills vertically or horizontally. This values is useful when creating reports based on classification, where values are specified in a single row across the sheet. | No | FALSE (returns a vertical list) |
| Rollup First | When TRUE, returns the rollup (Hierarchy) values before the children. When false, the rollup value is returned after the children | No | TRUE (Rollup value is returned first) |
Remarks
- Review the Function filter syntax for selecting ranges as needed.
- #SPILL! may appear in the array function cell when the resulting array determined by your parameters will not fit in the provided Excel area.
Example
Suppose that a report is needed which shows balances based upon classification. Start with a standard Balance Sheet template, then select the Classification cell D11. From the Finsyte ribbon, select the Clear option submenu in the Productivity section and select Clear All. This will clear the cell's formatting and data validation rules for the dropdown. In the same cell, enter the formula =FSN.Range.Classifications(,,D$5,,,,,,TRUE) to generate the list of classifications horizontally. From there, copy column D over to column E. This will generate a #SPILL! error, since the formula was copied over to column E. Simply delete the value in cell E11 to remove the #SPILL! error. Finally, copy column E across the worksheet in each column where a value appears for the classification.