Board Excel Add in functions

Board has integrated some of its own functions (in addition to the many Excel functions) that work with the Board Excel Add-in and assist users to leverage data from Board more easily within Excel.

In order to use these functions, you must have the appropriate license and permission and have a successful connection to Board. To find out more about the prerequisites, please read Getting started with Office Add-ins and Board Office Add-ins installation.

The current Board Excel Add-in functions are the following:

BCUBE & BCUBE_K

The BCUBE function retrieves data from a Board Data model or Cube of specified coordinates. Selections made from the Sheet or Workbook Selection windows or Pagers are not considered or kept.

The BCUBE syntax is as follows:

=BCUBE("Data_model","Cube_Name","Entity1=member_code;Entity2=member_code","Functions") 

The BCUBE_K function also retrieves data from a Board Data model or Cube of specified coordinates and has the same syntax as BCUBE. However, the significance of the K represents "keeps selection". The difference is that BCUBE_K maintains the active selection and retrieves data accordingly with the Select applied. This allows users to display data without having to necessarily specify each and every coordinate, but only the ones not already defined in the selection.
 

The BCUBE_K syntax is as follows:

=BCUBE_K("Data_model","Cube_Name","Entity1=member_code1,member_code2;Entity2=member_code","Functions")

An example syntax is the following:
=BCUBE_K("Echo", "Sales Packs","Quarter=201203,201204;Customer=C02134,C04345","pp")

This syntax will render the value of Data model "Echo" from the "Sales Packs" Cube with the coordinates of the 3rd and 4th quarter in 2012 for Customers with member codes C02134 and C04345 from the previous period (pp) within the current selection to the sheet.

BCUBE_K works with the functions "SetSelection" and "SetSelectionwithRefresh".

In the example video below, you can see 3 different syntax using BCUBE and BCUBE_K:

• The first syntax uses BCUBE, specifies the Data model name and Cube while adding a "Coordinate" that acts as a selection

• The second syntax uses BCUBE_K with only the Data model name and Cube name, considering any active selections

• The third syntax uses BCUBE_K and adds a "Coordinate" that acts as a selection in addition to any active selections. The two selections intersect, and the data that displays is an overlap of the two selections considered, like the Keep selection mode

If the coordinates of the formula are not coherent with the active selection (e.g. 2 different elements of the same Entity), the result will be an empty selection and the cell will display "0".

BCUBE & BCUBE_K Functions

In the "Functions" area of the BCUBE_K syntax, you can add time functions, like in the Layout Editor, to adjust the selection based on the certain time variables, as described in the Time functions section of the Data Block settings. The functions you can use are as follows:

• "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" must be used in combination with "ycv", " ymt", "yma", separated by a semicolon (;): e.g. "ycv;fy"

• "cycle" = cycle (works in combination with yearly moving total and yearly moving average

  "cycle" must be used in combination with "py" (previous year), separated by a semicolon (;) and followed by "a space + value": e.g “py;cycle -1”

• "offset" = (-)n offset based on the Time Entity for which the Cube is dimensioned  

  "offset" must be used in combination with "py" (previous year), separated by a semicolon (;) and followed by "a space + value": e.g “py;offset -1”

BSAVE

The BSAVE function writes a new value back into a designated value cell of a Cube.

The BSAVE syntax is as follows:

=BSAVE("New value", "Data_model","Cube_Name","Entity1=member_code;Entity2=member_code1,member_code2")

For example, let's take the Cube "Budget Packs" from the Data model "Echo" and look at the Customer Entity's member ASDA Credit Account (member code "C02312") in 2010.

Let's say that you want to change the value for this Customer in March 2010 (member code "201003").

The new value is 550 and lies in cell B7 of the Excel Sheet.

The BSAVE formula for this example is:

=BSAVE(B7,"Echo", "Budget Packs", "Month=201003";"Customer=C02312")

The BSAVE formula takes the new value "550" from cell B7 and writes it into the Cube, both in Excel but also in the Platform.

When you hit enter, the value is saved although it does not show up in the Excel table. To display it in the Excel table, you must hit "Refresh".

Data entry in Split & Splat at the aggregated level is also supported.

GetSelection

The GetSelection function displays the selection(s) made on the Worksheet where the function is deployed, like the @Selection function in the Board Platform.

The GetSelection syntax is as follows:

=GetSelection("Data_model")

Depending on the Display setting of the Entity (Description, Code, or Description and code), the selection is displayed as follows:

Description: Month: Feb.17, Mar. 17, April 17

Code: Month: 201702,201703,201704

Desc & Code: Month: 201702 Feb.17,201703 Mar. 17,201704 April 17

This function displays up to 12 selected members, then, with more than 12 selections, displays the number of selections like the following: Month: 10/60

SetSelection & SetSelectionwithRefresh

The SetSelection & SetSelectionwithRefresh functions act as a selection on your Excel table, specifying Data model, Cube, and Entity member rather than using a whole Sheet or Workbook selection.

SetSelection

The SetSelection function makes a selection on your Excel table and only displays the selection once refreshing the Sheet.

The SetSelection syntax is as follows:

=SetSelection("Data_model","Entity=member_code")

The SetSelection function will replace any existing active selections.

SetSelectionwithRefresh

The SetSelectionwithRefresh function is the same as "SetSelection" except that it automatically refreshes the Sheet so you don't have to.

The SetSelectionwithRefresh syntax is as follows:

=SetSelectionwithRefresh("Data_model","Entity=member_code")

The SetSelectionwithRefresh function will replace any existing active selections.

To add more select coordinates to SetSelection or SetSelectionwithRefresh, keep Entities and member codes within the same quotation marks and separated by a semicolon (;) as follows:

"Entity1=member_code1,member_code2;Entity2=member_code,Entity3=member_code1, member_code2,member_code3"

For example:
=SetSelection("Echo","Budget Packs","Month=201003,201004;Customer=C02312,C04916")

This syntax will show the selection of the months March and April of 2010 and Customers with the code C02312 and C04916.

MergeMappingDefinition

The MergeMappingDefinition function allows users to create a mapping between an Excel Layout (source data range) and a Board Layout (target data range).

The MergeMappingDefinition syntax is as follows:

=MergeMappingDefinition([SourceDataRange];[TargetDataRange];[MappingRange];[MappingRange];[single horizontal mapping range])

• [SourceDataRange]. This is a range function where the users can select the Excel range to point to the source data.

  Only the values are selected as part of this range and not the actual dimensions.

• [TargetDataRange]. This is a range function where the user selects the entire Board Layout in the Excel sheet.

  The entire Board Layout is selected. The order of the dimensions shown in this setting is the order to be followed for setting the Mapping ranges in the next step. Data entry needs to be enabled to input values into the Board Layout

• [MappingRange 1-N]. Follow the target order and point (map) the source dimensions in order to save data back to the Board Data model.

  The order is important, when the function finds the matching member of the dimensions it will populate the data otherwise it will skip and go to the next cell. Only one column range can be mapped as part of this function and this needs to be the last in the mapping range.

To use the MergeMappingDefinition function, proceed as follows:

1. Connect to Board

2. Add the function to your Excel sheet

3. Select the cell range that contains the source data, the target Board Layout, and select the mapping cell ranges accordingly

   The coordinates mapping needs to be defined following the columns order in the target Board Layout.
   The target Board Layout needs to be flattened and the data entry must be enabled for Data Blocks involved in the process.
   The Excel sheet must have the “Save/Undo Mode” option switched on.
   The mapping function can manage only one horizontal mapping range and it must be the last argument in the function. Please, set up the target Board Layout accordingly.

4. Save to run the function. When the Save button is clicked, the Add-in applies the mapping by transferring data from the source to the target Board Layout. To do this, it scans the source range cells, defines their coordinates, and it searches for the specific row on the target Layout to copy data to

   If no row is found on the target Board Layout the source cell value will be skipped.
   If the source cell contains no value, the function will write a 0 in the target cell.

This process can be triggered also from a macro using VBA: it will be triggered when the save function is called (see the code sample below).

Using the function syntax defined below, let's look at an example:
=MergeMappingDefinition([SourceDataRange]; [TargetDataRange]; [MappingRange]; [MappingRange];[single horizontal mapping range])

Data mapping example:

1. SourceDataRange: indicates the Excel range that contains source data

2. TargetDataRange: indicates the Excel range that contains the target Board Layout

3. MappingRange: you can specify one or more mapping range; you can map single cells, vertical and horizontal ranges (you can define only one horizontal mapping range and it needs to be the last one in the list); the order of the mappings has to be the same as the columns in the target layout

After the definition of source and target, you'll have to select mapping ranges in the exact same order of the columns in the target Board Layout.
In our case, the order must be:

1. Product

2. Customer

3. Month

You can map single cells, vertical ranges, and horizontal ranges (you can define only one horizontal mapping range and it must be the last argument in the function).

Here's an example:

For all Excel Add-in functions, avoid the use of special characters (e.g. P&L) or punctuation when naming Data models, Cubes, and Entities. The use of these special characters could effect functions and their results. Refer to naming convention best practices.