¶ 1. Overview
Calculated members are "fictitious" members created from the application's dimensions within a single form and used in the same form, without the need to alter the application's model structure. These members are used to perform calculations within the form, without the need to create additional members in the application dimensions.
¶ 2. Functionality
Among the functionalities of calculated members are calculations performed directly from the created calculated members, value aggregation, changes in data display format, and creation of rows and columns in the form.
¶ 3. Functions in calculated members
Calculated members can be created using the following functions:
Click here to display the available functions.
| Functions |
|---|
| Blank |
| Comment |
| Header |
| Parent |
| PrevMember |
| Property |
| TimeRelative |
| TimeSpan |
| YTD |
Below is the description, syntax, and parameters for each of them:
¶ 3.1. Blank
Description:
Creates a row or column with blank cells. This member adds a blank row, creating space to better separate a group of rows or columns. The cells in this row or column will remain blank.
Syntax:
Blank()
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Blank | Blank row or column | {} | Yes | No |
Example: In the expression field, the text should be as follows:
Blank{}.
¶ 3.2. Comment
Description:
Creates a member where it is possible to insert comments to be replicated. This member will create a row or column for adding comments that will be saved in the cell comments within the correct scope.
The dimension used to select this member must be filtered with N members using the member selection popup, including with parameters. This selection will be used to know in which members we should save the comment within this dimension. The comment member can only be used in a single dimension per form and only once.
Syntax:
Comment([context])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Context | Comment context | Dimension, Member | Yes | No |
To allow text characters to be entered in comments, it is necessary to change the calculated member type to Text.
Example: We can place it in a column after the 12 month columns and save the comment in all 12 columns by replicating or just in the year, so the filter in the dimension on the comment member is used to know where to look and where to save the comment.
¶ 3.3. Header
Description:
Creates a header with blank cells.
Syntax:
Header([Title])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Title | Header title | "Text" | Yes | No |
To insert the Title parameter, the required text must be in quotation marks (""). This member only serves as a header; its cells will always remain blank.
Example: In the expression field, we will have a text similar to this:
Header("Example Header").
¶ 3.4. Parent
Description:
Fetches the value of a member in a given dimension. The quantity identifies how many levels up the hierarchy tree of the selected dimension it should go.
Syntax:
Parent([Input], [Dimension], [Quantity])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Input | Value | Dimension, Member | Yes | No |
| Dimension | Dimension to traverse up hierarchy | Dimension | Yes | No |
| Quantity | Number of levels to go up | Number | Yes | No |
Example: In the expression field:
Parent([Conta].[Financeiro], [Tempo], 1)
In this expression, the values of the Financeiro member from the Conta dimension will be replicated at the next hierarchical level of the Tempo dimension.
If the value added in the "Quantity" parameter were 2, the values would be replicated 2 hierarchical levels above. Going up one level replicates the values in the quarter; going up two levels replicates the value in the semester, and so on.
¶ 3.5. PrevMember
Description:
Fetches the value of the previous member.
Syntax:
PrevMember([Base])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Base | Base Member | Dimension, Member | Yes | No |
Example: In the expression field we will have a text like:
PrevMember([Conta].[Total dos Itens Vendidos])
This example will display the previous value of the member in the next period.
If we have the value "2,500" in January/24 and "3,000" in February/24, the PrevMember member will show "2,500" in February/24 and "3,000" in March/24, and so on.
¶ 3.6. Property
Description:
Fetches the property of a member.
Syntax:
[Function Result] = Property([Property], [Context])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Function Result | Property | Dimension | Yes | No |
| Property | Value to be fetched from the attribute | Dimension | Yes | No |
| Context | Context (member) whose value will be fetched | Dimension, Member | Yes | No |
To properly use the Property function, the desired property to display from the member in the selected dimension must be filled in, and the type of the calculated member must be changed to text. In our example, we will use the "Descrição" property from the "Conta" dimension.
Example: If we want to display the description of the members in the Conta dimension, we will have the following expression: Property([Conta].[Descrição], [Conta].[GetDataTableData]).
In the "Function Result" parameter of this calculated member, we can select any dimension — the selection will not affect the final result.
¶ 3.7. TimeRelative
Description:
Function used in the time dimension to fetch the value of a time relative to the parameter.
Syntax:
TimeRelative([Base], [Shift], [Relative])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Base | Base value to be accumulated | Member (time dimension) | Yes | No |
| Shift | Number of periods added | Number | No | No |
| Relative | Time relative to base | Member (time dimension) | No | No |
Example: If we want to display the value of January 2024 and compare it with the value of January 2025, we will use the following expression:
TimeRelative([Tempo].[Todos].[2024].[1S/24].[1T/24].[Janeiro/24], 0, [Tempo].[Todos].[2025])
Let’s create a form with the time dimension in columns and insert the member "Janeiro/24" together with the calculated member with the expression above. This calculated member will always show the value for "Janeiro/25".
The shift parameter will always be applied horizontally to the resulting member. If we have Janeiro/25 with shift 1, the resulting member will actually be Fevereiro/25. If shift is -1, the resulting member will be Dezembro/24.
We can make the TimeRelative function more flexible. To do so, we need to create some parameters in our form.
Let’s create 3 parameters in the time dimension of our form:
- Create a parameter to display the Year (for example, 2024);
- Create a parameter to display the leaf members of the previously created parameter, which we will use as the Base parameter in our function;
- Create a parameter to display the year to be compared, which we will use as the Relative parameter in our function (for example, 2025);
- Add a calculated member using the parameters created. Our expression will look like this:
TimeRelative({Meses 2024}, 0, {Ano 2025}); - In the layout section of the form, under columns, add the parameter
Meses 2024and the created calculated member; - Still in the form layout section, under pages, add the parameters created:
Ano 2024,Ano 2025, andMeses 2024; - Save the form and open it;
- You will be able to change the displayed months of 2024 in pages, thus comparing them with the calculated member value, which will always show the selected month’s value, but for the following year, as defined in the expression.
¶ 3.8. TimeSpan
Description:
Calculates the aggregated value over a specific time period.
Syntax:
TimeSpan([Initial], [Final])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Initial | Start of the period | Member (time dimension) | Yes | No |
| Final | End of the period | Member (time dimension) | Yes | No |
Example: If the initial member is Janeiro/2024 and the final Junho/2024, the expression will be:
TimeSpan([Tempo].[Todos].[2024].[1S/24].[1T/24].[Janeiro/24], [Tempo].[Todos].[2024].[1S/24].[2T/24].[Junho/24])
The system will display the sum from January to June 2024.
¶ 3.9. YTD
Description:
The YTD (Year-To-Date) function accumulates the base value over periods, starting at the beginning of each year or fiscal year.
Syntax:
YTD([Base])
Parameters:
| Name | Description | Parameter types | Required | Repeats |
|---|---|---|---|---|
| Base | Value to be accumulated | Member (time dimension) | Yes | No |
Example: If the base member or parameter is Março/24, it means we must calculate the YTD up to Março/24. The expression will be:
YTD([Tempo].[Todos].[2024].[1S/24].[1T/24].[Março/24])
¶ 4. Creating Calculated Members
To create calculated members, we need to follow these steps:
- First, we need an object of type Form. We can create a new form or use an existing one in our application;
- Select the form object and click Edit;
- In the form edit screen, go to the Calculated Members tab;
- Select the dimension in which the calculated member will be created by clicking on it once;
- Click
;
A new screen will appear, where we will configure the calculated member. This screen contains the following fields: Dimension, Type, Name, Dynamic Name, and Expression.
- Dimension: Click the dropdown to select which dimension the calculated member will be created in;
- Type: Click the dropdown to select the data type for the calculated member. Options include: Currency, Percentage, and Text;
- Name: Define the name for the calculated member being configured;
- Dynamic Name: If filled in, this name will be displayed in the header where the calculated member is placed (row or column). If not filled, the Name field will be shown. Optional field;
- Expression: Field where the calculated member's logic is written. It resembles a mathematical expression, containing one operand equated to a sequence of operands and operators.
Example:
[Conta].[Membro1] + [Conta].[Membro2]
[Conta]: The dimension where the members used in the expression reside;
[Membro1]: Operand used in the calculation and a member of the Conta dimension;
+ : Operator used in the expression;
[Membro2]: Operand used in the calculation and a member of the Conta dimension.
In this example, the calculated member value will equal the value of [Membro1] plus the value of [Membro2].
Operators include: Addition (+), Subtraction (-), Multiplication (*), Division (/), and Power (^).
In the expression field, we can use autocomplete by typing at least 3 characters (suggestions appear after 1 second) or pressing CTRL + SPACE to display suggestions.
There is a button to display the available Functions:
{.no-margin-img}
Functions assist in building calculated members, allowing for more advanced calculations and access to model information.
- After completing the configuration, click OK;
- To display the created calculated member, we must add it to the form. Switch to the Layout tab;
- In the layout tab, distribute the application dimensions across Columns, Rows, and Pages of the form;
- After the dimension distribution, select the dimension in which the calculated member was created and click Edit;
- Click the calculated member listed among the available members to select it, then click
to move it to the selected members list; - After selecting the members to display in the form, click OK;
- To finalize, click Save;
If your form requires calculated members using the
TimeSpan,TimeRelative, orYTDfunctions, go to the properties tab and uncheck the "Show year filter on page" checkbox.
Leaving this checkbox enabled may interfere with the correct behavior of these functions.
¶ 4.1 Deleting Calculated Members
To delete a calculated member, follow these steps:
- Select the form where the calculated members were created and click Edit;
- Go to the Calculated Members tab;
- In the list of created calculated members, click once on the member to select it;
- After selecting the member you want to delete, click
; - Delete the desired calculated members and click Save to finish.