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
The BOARD.SETSELECTION formula is functionally equivalent to the MS Office "SetSelection". It applies a Selection filter on the Excel Sheet, but does not refresh the data retrieved from the Board Platform by default. To add a refresh to this formula, you can 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.
The BOARD.SETSELECTION syntax is as follows:
=BOARD.SETSELECTION("Data_Model","Entity=member_code",TRUE/FALSE)The BOARD.SETSELECTION function overwrites any existing active Sheet Selections. That way, the function clears all current Selections on the Sheet and applies only the Selection defined in the formula.
Only one BOARD.SETSELECTION function applies. If you create multiple ones, the last created will apply. To have multiple set Selections, you must add all Selections to one formula. Every time the formula runs, the sheet refreshes.
"Data_Model", "Cube_Name", "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, Cube names, Data Model names, and Entity elements containing the following reserved characters ("=", ",", ":", ";") are not supported with these Excel formulas.
Security is always applied on the formulas, similarly to any other in-Platform Board Object.
Cell references can be used to make formulas dynamic using Excel functions like TEXTJOIN or combining text and numbers with the “&”, but the "&" character must be used correctly to combine the different cells and obtain a parameter which is in-line with Board formula definitions.
Errors: if there is any error in retrieving the data, the error will be displayed in Excel using the standard error tooltip that can be found in any Excel formula. See the example below:
.png)
Member coordinates in the BOARD.SETSELECTION formulas
The coordinates of the functions can be identified either as a sequence of member values, or as a range of member values (but not both).
Use commas to separate a sequence of members of the same Entity, e.g.
"Entity1=member_code1,member_code2,member_code8,member_code9"Use a colon to define a range, e.g.
"Entity2=member_code30:member_code40"Use semi-colons to separate multiple entities from each other, e.g.
"Entity1=member_code1;Entity5=member_code55"
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".