About Data pickers

What is the Board Data picker?

A Data pickers is a Board function that retrieves data from a specific Cube with a selection (Entities and their members) from that Cube. You can use the Data picker syntax for rules in the Rule section and in an API step.

For example, if you are creating a Data View to show a KPI analysis using the Entity “KPI” and you want to select data from a different Cube (for example, “Local Revenue” Cube), you can use the Data picker function in the Rule section to do so.

In this article, you will learn how to create the Data picker syntax for this example, where to add it, how to activate it, and what the results are.

How would you do this?

You can write a Data picker syntax in an API step as you would another formula.

To utilize a Data picker in a Rule, you must write the syntax as a Rule for an Entity member in the Rule section and then activate that Rule in the Data Block settings of the designated Cube in a Layout. To do this, do as follows:

1. Go to the Rules section and create a new rule (associated to a specific Entity) or edit an existing one

   For example, create a Rule on the “KPI” Entity. In the Rule editor, on the line of the member where you want to insert the data from another Cube, write the Data picker syntax.
   

2. Write the syntax in the formula field of the Entity member you would like to add the Data picker syntax in order to pick a specific value from another Cube

   In this example, 3 Data picker syntaxes are written in the formula fields. These 3 are explained in more detail in the How- Data picker syntax section.
   

3. Activate the Rule on the designated Cube within a Layout in the Data Blocks settings.

   In the example below, the Rule “KPI comparison” created with the Entity “KPI” and containing 3 Data picker syntaxes is activated on the second Block in the Data View Layout. This example unfolds in more detail and we will see what the results look like in the next section.
   

   

How – Data picker syntax

To write the syntax for a Data picker, you must know the following:

1. The Cube’s physical name, represented in the syntax as “CUBE_NAME” below, is found in the Cubes section or in the Layout of an Object with that Cube configuration.

2. The Entity’s name, represented in the syntax as “ENTITY” below, is found in the Entities section.

   The Entity must dimension the Cube referenced in #1 or be related to an Entity in the dimension of the Cube referenced in #1.

3. And the member’s code, represented in the syntax as “MEMBER_CODE” below, which is found in the Content tab of the Entity from the Entities section.

Once you have all 3 requirements, insert them into the following syntax formula.

Data picker syntax

The Data picker syntax is:

@[CUBE_NAME]{ENTITY=MEMBER_CODE}

Data picker Legend

• @ indicates that the following syntax is a Data picker formula

• [] holds the Cube’s physical name

• “CUBE_NAME” is the placeholder for the Cube’s physical name, which starts with a “V” and is followed by a set of numbers

• {} holds the Entity name and member code of the selection desired

• “ENTITY” is the placeholder for the Entity name

• = selects the member of the aforementioned Entity

• “MEMBER_CODE” is the placeholder for the member code of which value you are selecting

Example syntax:

Data picker syntax example:

1. The Cube “Local Revenue” has a physical name of V0302

2. the Entity name is “Region”

3. and the member code is “EURO”

The syntax for this example looks like the following:

@[V0302]{Region=EURO}

• @ indicates the Data picker syntax follows

• [V0302] Selects Cube number V0302, which, in this example, is called Local Revenue

• {Region=EURO} identifies the Region Entity from Cube V0302, from which, the value of the Europe member, which has the code “EURO”, is picked and returned

Example syntax result:

Multiple selections and multiple Data pickers in a formula

You can extend the syntax to include multiple selections in 3 ways:

1. Multiple members of an Entity

2. Multiple Entities

3. A combination of the two or multiple Data pickers in a formula

Multiple members of an Entity

You can use the Data picker syntax to select multiple member values of the same Entity.

To select multiple members of an Entity, separate the member codes by a comma (,) within the same set of curly brackets ({}).

For example, let’s take the same syntax as before, selecting for EURO revenue (Europe) and add a selection on AMER revenue (Americas): @[V0302]{Region=EURO,AMER}

Result: This shows the sum of the 2 revenue values, Europe and Americas, which are member codes EURO and AMER.

Multiple Entities

You can use the Data picker syntax to select multiple Entities’ member values.

To select multiple Entities in the Data picker syntax, separate the additional Entity from the first Entity and member code with a semicolon (;) within the same set of curly brackets ({}).

For example, we want to see the revenue of Europe (member code is “EURO” from Entity “Region”) of the month January 2024 (member code is 202401 from the Time Entity “Month”).

Syntax: @[V0302]{Region=EURO;Month=202401}

To find a Time Entity member code, go to Custom Entities in the Time Range section of a Data model, select the Entity, and navigate to the Content tab from the sliding panel.

The result is the value of member EURO, found by drilling down on the member “Jan.24” by Entity “Month”.

Like in any other selection action, when performing multiple selections on the same Tree, the less aggregated Entity takes priority of related Entities.

Similarly to what was explained above, you can continuously add more member selections of each Entity.

You can also combine another Data picker value from another Cube in a formula, as shown in the example below.

Multiple Data pickers in a formula

To continue with this example, the user wants to show the Gross Margin % using the values of the EURO and AMER revenue from one Cube (V0302) and the gross margin values of EURO and AMER from another Cube (V0307). The formula for this is Gross Margin % = (revenue/gross margin)*100

Revenue value of EURO and AMER from Cube V0302:

Gross margin value of EURO and AMER from Cube V0307:

The Gross Margin % formula of the two values used as a Data picker syntax:

Below, you can see two Rules with a Data picker syntax for “Revenue” and “Gross Margin”, that both are used in a formula for Gross Margin % in the Rules section, and their results in a Data View.

Syntax dos, don’ts, and compatibilities

How to and how not to write the Data picker syntax is outlined below.

Examples of Data picker syntax that is not acceptable:

• 

  [V001]

• [V002]{Currency=USD}

• @[V002](Currency=USD)

• @{Currency=USD}[V002]

• @[V003]{Currency=USD};{Product=001,002}

Examples of acceptable Data picker syntax:

• @[V001]

• @[V002]{Currency=USD}

• =@[V002]{Currency=USD}

• If @[V002]{Currency=USD}>1,1,0
  (“If, then, else” formula)

• @[V003]{Currency=USD;Product=001,002}

Syntax

• is not case sensitive: (@[v001]{currency=usd} is acceptable

• is not affected by spaces within the curly brackets (for e.g., @[V003]{Currency = USD ; Product = 001, 002}

• is affected by spaces elsewhere: between @ and [], within [], between [] and {}

• only applies to Rules and API step calls

• does not affect drilling down and results default to the original values

• does not work with Data entry and Data entry values do not save when using Data pickers

• does not work on text Cubes/only works on numeric Cubes

• works with Time Entities and Custom Entities. You need to acquire the Time or Custom Entity member code, which is found in the Time Range section of the Data model

• does not work with Entities that have special characters (e.g. P&L) or punctuation. Refer to naming convention best practices.

You can reference a Data picker result within a Rule

For example, if you want to predict the operating profit based on the data picked value for Revenue (member code 140160) where the profitability ratio is a 200% increase, you need to write the rule as follows: revenue*3

The second Rule references the value picked from the Data Picker syntax used on the the member code 140160 line in the first Rule.

Results of the Data picker referenced by the rule member code in a new rule formula:

The result of the second rule is a 200% increase of the Data Picker value for member 140160.

In some cases, you may find a Dataflow step more applicable to your needs. To find out more about Dataflows, read the Dataflow best practices.

Troubleshooting Data picker syntax

When no result displays:

• double check the syntax

• confirm Entity's or Time Entity’s member code

• make sure that the Entities picked are a dimension of the Cube used in the syntax or related to an Entity of that Cube

Using Selections with Data pickers

Selections interact with and apply to Data picker values in the same manner as they do globally across the Platform, including Database security selections. Read more about the Select.