The BOARD.SETSELECTION function (previously SetSelection & SetSelectionwithRefresh) specifies the Selections on your Excel Sheet, by specifying the Data Model and Entity Member rather than using a whole Sheet Selection. Selections defined at the Dataset and Layout level are always respected and take precedence over the function.
BOARD.SETSELECTION() with the optional parameter "Refresh=True" has replaced the MS Office Add-in SetSelection() and SetSelectionWithRefresh() formulas.
The MS Office GetSelection() is deprecated.
As with all of filters, the formula selection is applied as a further restriction to the current Selections at other levels of the Four Security Layers model, such as with the Security and Role profiles. This function does not permit a user to breach the security restrictions inherited through the other layers of the security model.
BOARD.SETSELECTION syntax
The BOARD.SETSELECTION formula is functionally equivalent to the MS Office "SetSelection" formula. It applies a Selection filter on the Excel Sheet, but does not refresh the data retrieved from the Board Platform by default. To refresh the data when the formula applies the selection, set the "Refresh" parameter to "TRUE".
An additional parameter, "Do Refresh sheet", is available and defaults to "FALSE"; when set to "TRUE", the function also triggers a data refresh. BOARD.SETSELECTION recalculates upon refresh and also when it is part of another Excel formula.
Excel formulas that do not contain a Board formula are recalculated according to Excel settings and logic. The Board refresh does not affect these formulas. Read more about Board Refresh in the M365 Excel Add-in.
The BOARD.SETSELECTION syntax is as follows:
=BOARD.SETSELECTION("Data_Model","Entity=member_code",TRUE/FALSE)When setting a selection on a sheet, use one of the following metods:
Use a BOARD.SETSELECTION formula only. The formula applies its defined selection and, if present, clears any selection made in the Sheet Selection panel when it runs.
Use the Sheet Selection panel only. Select members in the panel to filter the sheet. See panel below.
Only one BOARD.SETSELECTION function applies to a sheet. If you create multiple formulas, the last one evaluated applies. Instead of having multiple BOARD.SETSELECTION formulas, configure all Selections you want to apply in one BOARD.SETSELECTION formula. Every time the formula runs, the sheet refreshes.
Formula requirements and limitations
Data Model and Entity names are not case-sensitive.
Coordinates are matched with either Entity member codes or description. Users don't need to specify the codes to retrieve the data.
Entity names, Data Model names, and Entity elements that contain the following reserved characters ("=", ",", ":", ";") are not supported in Board Excel formulas.
Security restrictions always apply.
Cell references can make formulas dynamic. Use Excel functions such as TEXTJOIN or "&" to build parameters that follow Board formula syntax. The "&" character must be used correctly to combine the different cells and obtain a parameter which is in-line with Board formula definitions.
If an error occurs, Excel displays its standard formula error indicator and tooltip. See the example below:
.png)
Coordinates
BOARD.SETSELECTION uses the standard coordinate syntax and validation rules shared across Board Excel functions.
The specified coordinates define the Entity members to apply to the current Sheet Selection. If a coordinate contains an invalid or inaccessible member, the selection is not applied and Excel displays an error indicator.
For information about specifying coordinates, validation rules, supported syntax, and examples, see Coordinate syntax and validation.
To add multiple or more restrictive Selection coordinates to BOARD.SETSELECTION, specify each Entity and its selected member codes within the same quotation marks and separated by semicolons as shown in this example:
"Entity1=member_code1,member_code2;Entity2=member_code3;Entity3=member_code4,member_code5,member_code6"The example below sets the Selection to filter the months of March and April in 2010 and to filter Customers with the code C02312 or C04916 and then refreshes.
=BOARD.SETSELECTION("Echo","Month=201003,201004;Customer=C02312,C04916",TRUE)The last parameter must be "TRUE" or "FALSE".
Using BOARD.SELECTION with BOARD.READ
BOARD.SETSELECTION sets the Sheet Selection for the worksheet. BOARD.READ can then use that Selection as one layer of filtering and its own coordinates as another.
By default, BOARD.READ respects the Sheet Selection.
Set "Ignore sheet selection" to "TRUE" to exclude that layer and ignore the active Sheet Selection.
Set "Ignore sheet selection" to "FALSE" (or leave it blank) to apply the active Sheet Selection together with the coordinates specified in the BOARD.READ formula. The Sheet Selection limits the data scope, and the formula coordinates can narrow it further.
In this example, BOARD.SETSELECTION selects "Europe" for the Region Entity:
=BOARD.SETSELECTION("Echo","Region=Europe",TRUE)The following BOARD.READ formula adds a "Year=2020" coordinate. Because "Ignore sheet selection" is "FALSE", the Sheet Selection restricts the data scope, and the coordinate further filters that data. The result is data for Europe in 2020:
=BOARD.READ("Echo","Customer Demand", "Year=2020",FALSE)If the BOARD.READ coordinates conflict with the Sheet Selection, for example, if the formula specifies "Region=North America" while the Sheet Selection specifies "Region=Europe", the filters have no intersection and the cell returns "0".
To have BOARD.READ ignore the Sheet Selection, set "Ignore sheet selection" to "TRUE". In this example, the formula returns data for North America in 2020:
=BOARD.READ("Echo","Customer Demand","Year=2020;Region=North America",TRUE)