The BOARD.READ function (previously BCUBE and BCUBE_K in the MS Office Add-in) retrieves data from a Board Data Model, Dataset, or Cube for a set of specified coordinates (named Entity members).
The MS Office Add-in functions, BCUBE and BCUBE_K, were equivalent to each other with one single difference: the Selection criteria as specified in the Board Platform was either ignored or respected.
The BOARD.READ function has a Boolean function to achieve the same difference in one function rather than two by adding the “Ignore sheet selection” parameter. The bulk refresh logic of BOARD.READ refreshes formulas in groups rather than individually, allowing a quicker execution.
Key benefits of BOARD.READ
Retrieves data from specific set of coordinates in a Dataset or Cube
Now uses one formula instead of two
Faster execution than before
Writing BOARD.READ syntax
The basic syntax of the BOARD.READ function is:
=BOARD.READ("Data_Model","Cube_Name","Entity1=member_code1,member_code2;Entity2=member_code30:member_code40","[Selection/ignore]") Mandatory syntax is “Data_Model” and “Cube_Name”.
"Data_Model", "Cube_Name", and “Entity” names are NOT case sensitive.Coordinates and ignore sheet selection switch are optional.
The full possible syntax for BOARD.READ is:
=BOARD.READ("Data_Model","Cube_Name;Rule=[Rule_name]","Entity1=member_code1,member_code2;Entity2=member_code30:member_code40","[Selection/ignore]","[Time_Function]") The additional parameters in the syntax are Rules and Time Functions.
Ignored or respect Selections
Using the BOARD.READ function, you can write a formula that defines whether sheet selections are ignored or respected with the “ Ignore sheet selection” parameter.
The difference is:
Ignore sheet selection=TRUE
When Selections are ignored, they are discarded and are NOT applied when the Excel BOARD.READ retrieves the data. This still allows users the freedom to specify the ‘filtering’ coordinates of the BOARD.READ formula to suit their current interests.
Ignore sheet selection=FALSE
When Selections are respected, the function ‘keeps’ the active Selection and retrieves data accordingly with that Selection still applied. This allows users to display data with another selection layer after respecting the sheet selection.
If the parameter is left blank, the default is “False” and respects sheet selections.
For both ignored and respected selections, security selections are always respected and applied before any other selection.
Coordinates are matched with either Entity member codes or description. Users don't need to specify the codes, but do need to specify descriptions to retrieve the data.
Duplicate member description known limitation
If multiple members have the same description, Excel applies only the first member that occurs and ignores the rest.Solution: Define the member by their code an not by description.
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, but the "&" character must be used to correctly to combine the different cells and obtain a parameter which is in line with the formula definitions.
If any errors occur retrieving the data, the error will be displayed in Excel using the standard error tooltip that can be found in any Excel formula.If a Cube has an unbalanced hierarchy, the default keeps the unbalance hierarchy rollup without adding the string DISABLEUNBALANCED=[false], but you can also use it to achieve the same outcome.
Member coordinates in the BOARD.READ formulas
The parameters 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"Use quotations to maintain all Entities, e.g.
"Entity1=member_code1;Entity5=member_code55"
The BOARD.SETSELECTION functions update the sheet selection criteria so they are recognized by BOARD.READ which retains and respects sheet Selections if parameter is set to “true”.
Optional strings and parameters
Additional Cube strings: Rules and disable Unbalance hierarchy
Cubes in BOARD.READ formulas can have extra parameters, applying a Rule or disabling the Unbalance hierarchy rollup.
To add a Rule and/or to disable the Unbalance hierarchy rollup, add the following syntax in the “ Cube_Name” section using “;” as separators:
“Cube_Name; RULE=[Rule_Name];DISABLEUNBALANCED=[true/false]"
The full syntax with Rule looks as follows:
=BOARD.READ("Data_Model","Cube_Name;RULE=[Rule_Name];DISABLEUNBALANCED=[true/false]","Entity1=member_code1,member_code2;Entity2=member_code30:member_code40","[Selection/ignore]")If no parameter is set, the default is “FALSE” and and keeps the Unbalance hierarchy rollup.
BOARD.READ Time Functions
The "Functions" parameter of BOARD.READ is used to calculate totals or retrieve data from other periods based on certain time variables, as described in full detail in the Time functions section of the Data Block settings. The "Functions" are optional parameters, separated from each other with semi-colons, that are used to evaluate the data queries.
Add the Time function to the end of the syntax:
=BOARD.READ("Data_Model","Cube_Name","Entity1=member_code1,member_code2;Entity2=member_code30:member_code40","[Selection/ignore]","[Time_Function]") The following available time functions are the same as in the Layout Editor:
"pp" = previous period
"py"= previous year
"ycv" = yearly cumulated value
"ymt" = yearly moving total
"yma" = yearly moving average
"lastvalue" = last value
"fy" = fiscal year
"fy" can only be used in combination with "ycv", separated by a semicolon: e.g. "ycv;fy".
"cycle" = N (the number of periods after which rolling cumulations are reset)
"cycle" can only be used to modify one of “ycv” - yearly cumulative value, “ymt” - yearly moving total, “yma” - yearly moving average, or “lastvalue”, separated by a semicolon and followed by a space and the cycle value: e.g. "ycv;cycle 3".
This number specifies how many of the dimensioned time periods constitutes a single "cycle" for the rolling period calculation.
For example, setting the cycle to 90 on a Cube dimensioned by Day will result in a rolling period of the most recent 90 days.
The relevant time dimensions are those specified in the Axes area of the Layout editor - the time dimension in the Cube structure does not impact the calculations.
If the cycle value = 0, then the calculations assume a cumulation period of 1 year.If the cycle value > 0, then the calculations are applied with the time dimension as defined in the Layout Axis.
"offset" = ±N (the number of earlier or later periods to offset based on the Time Entity for which the Cube is dimensioned)
"offset" can be used in combination with any other time function, separated by a semicolon from a following space and the mandatory offset value: e.g. “py;offset -1”
The time selection is shifted by the specified number of periods. The number of periods may be negative for earlier periods, or positive for later periods.
Multiple Time Functions can be combined with the “;” separator.
Examples
To better clarify, here are some examples of some complex formulas and their intended results. Note that a combination of security restrictions, selections or the non-existence of the requested data mean that the formulas might not return any data at all.
py;offset 2
Retrieve data values from 2 years ago for all periods.
pp;py;offset 2
Retrieve data values from 2 years ago corresponding to the period prior to the current one.
ycv;offset 2;cycle 2;fy
Retrieve yearly cumulated values from the financial year that was 2 years ago, resetting to zero every 2 years.
yma;py;offset 5; cycle 1
Retrieve yearly moving averages from the calendar year that was 5 years ago, resetting to zero every year.
ymt;cycle 3;py
Retrieve yearly moving totals from the current calendar, resetting to zero every 3 months.
BOARD.READ use cases
The BOARD.READ formula gives you control over how data is pulled into your sheet, especially when combined with selection parameters. Use the BOARD.READ formula to fetch data—either with or without sheet selections. The difference hangs on a key parameter: whether the formula should respect the current sheet selection, or should it always show the value of the exact coordinate specified, regardless of the sheet selected.
The video tutorial demonstrates the 3 different use cases, why the “ignore selection” parameter matters, and what changes when you adjust it.
All the formulas in this example call for values from Data Model “Demo_FR_SOP” and Cube “Customer Demand”.
The three BOARD.READ use cases are:
Calling data that ignores the sheet selection.
The video shows 3 examples of this:One with only the required parameters, Data Model and Cube, rendering the total value of that Cube without a sheet selection.
=BOARD.READ("Demo_FR_SOP", "Customer Demand",,TRUE)The next with the additional Coordinates parameter set to Year 2020, displaying the total value of that Cube for the year 2020 only without a sheet selection.
=BOARD.READ("Demo_FR_SOP", "Customer Demand","Year=2020",TRUE)The third with multiple Coordinates set to Year 2020 and Region North America, displaying the total value of that Cube for the year 2020 within the region of North America.
=BOARD.READ("Demo_FR_SOP", "Customer Demand","Year=2020;Region=NA",TRUE)
Calling data that respects the sheet selection.
The video shows a second set of 3 examples that respect the sheet selection. The first mimics the first formula in the aforementioned examples but with the ignore sheet selection parameter set to FALSE and therefore filtering the data of the formula according to the sheet selection which is Region Europe in the example.
=BOARD.READ("Demo_FR_SOP", "Customer Demand",,FALSE)Calling data with one or more coordinates that also respects the sheet selection. The data displayed is an intersection of the two Selections.
The next 2 examples respecting the sheet selection call for one or more coordinates:
One selecting data by the coordinate Year 2020 like the aforementioned example, but also the sheet selection which is Region Europe in the example.
=BOARD.READ("Demo_FR_SOP", "Customer Demand","Year=2020",FALSE)The last one with the coordinates Year 2020 and Region North America like the aforementioned example, but displays 0 because the coordinates fall outside of the sheet selection Region Europe.
=BOARD.READ("Demo_FR_SOP", "Customer Demand","Year=2020;Region=NA",FALSE)
If the coordinates of the formula are not coherent with the active selection (e.g. 2 conflicting filters applied to the same Entity), no data will be retrieved and the cell will display "0".