Chapter 2. Planning Application Members
We will use the planning-app-analyst.xlsx workbook from the downloaded package to configure the application.
Open the workbook and change connections to your database first.
The application uses USD as the base currency (id 0). We need to change it to EURO.
Right-click on EUR and execute "Set as the base currency."
Now we have EURO as the base currency:
You can add other currencies and units later. Set is_currency = 1 for currencies.
Set is_base_currency = 1 for base currencies (the first currency in pairs like EUR/USD).
Do not forget to click the Save button on the SaveToDB tab to save changes.
The application has the following default dimension configuration:
The application has seven dimensions. The first four are mandatory. Dimension 5-7 are optional.
You can change codes, names, and other parameters. However, do not change id values.
Also, do not change the meaning of the Accounts, Times, Categories, and Entities dimensions.
In this step, let's activate the Regions dimension and click the Save button to save the changes.
The table contains several parameters related to permissions. We will configure this later.
Let's open the MEMBERS worksheet and select the Accounts dimension:
The Accounts dimension contains a single member at the beginning.
Let's add the first members:
First of all, we have added the root member for our reports, CC - Cost Centers.
Also, we have added items to the first cost center only (we will use these rows for other centers as templates).
We have filled the sort_order, code, and name columns.
The code field values must be unique. The application uses sort_order values to sort members.
Click the Save button to save new members.
In the next step, let's customize row_color, row_bold, and row_indent values to get the desired format:
The application outputs these values into hidden columns of forms and reports.
The forms use standard Excel conditional formatting to format rows, columns, and cells.
You can change the default conditional formatting using Home, Conditional Formatting, Manage Rules...
Before the next step we need to click Reload Data and Configuration:
This step is required to update validation lists with new members:
Use Reload Data and Configuration whenever you need to update lists or parameters.
Let's configure next properties: units, calculation types, parents, and factors:
You can select values from drop-down lists or just copy and paste values.
In this example, we assign EURO and the Total calculation type to all members. You can use any value later.
Take a look at the parents and factors. These values are used to calculate aggregates and to create hierarchies.
The example has simple rules for members like CC11 or CC111.
We also need to set parents for upper-level members like CC with factor 0 to create hierarchies.
Click Save to save changes and then click Actions, Update hierarchies to update required rules for new members.
Do not forget to update hierarchies after changes in members. Try this first if you have any issue with members.
Now we will add members for cost centers 2 and 3 using a trick.
Select cells in rows to copy and click Copy and Insert Rows:
You will have a copy of selected rows:
Change the sort_order values using the auto-fill feature and change CC1 to CC2 using search and replace:
You can use this trick with the SaveToDB add-in anywhere.
Click Save and then Reload Data and Configuration to update validation lists in the parent columns.
Now you can use search and replace in the parent column to change parents:
Click Save and then click Actions, Update hierarchies.
Now you can repeat the steps to the cost center 3. Just copy rows of the cost center 2, change sort orders and replace CC2 to CC3.
The final configuration of account members looks like this:
Let's open members of the Times dimension. Select Times in the dimension_id parameter at the ribbon:
Also, apply the Times view using the Table Views drop-down menu:
This view shows columns related to the Times dimension and hides others.
You can use this feature on other worksheets also.
Moreover, you can create your personal views. Just click the Save Table View button in the Table Views group.
The screenshots above have a compacted ribbon.
Usually, you will see a complete ribbon like this:
Initially, the Times dimension contains only root members:
Click Actions, Add members for a year to add time members on monthly bases:
The application adds the complete set of configured members to get data for months, YTD, quarters, and years.
Use members like 2017.00 to input opening balance values in your applications. We do not use it in this example.
Select the Categories in the dimension_id parameter at the ribbon and apply the Categories table view.
You will see the initially configured members of the Categories dimension:
The dimension contains three basic members that contain data: Budget, Forecast, and Actual.
The Same Period is a special member that shows actual data for the same period of the previous year.
Also, the dimension contains several calculated members that show differences in comparison reports.
You can add and configure additional members in your applications (for example, for different versions).
In this example, we will use the predefined members.
Select the Entities in the dimension_id parameter at the ribbon and apply the Entities table view.
You will see the initially configured members of the Entities dimension:
Let's add the required members:
We have used the existing member TOTAL as a parent instead of creating a new member All KAMs.
We can create it later if we need a separate aggregate for it.
Do not forget to update hierarchies after saving changes.
Select the Regions in the dimension_id parameter at the ribbon. You will see default members:
Let's add the desired members:
In this case, we have created a separate member, All Countries, to get totals.
Do not forget to save the changes and to update hierarchies.
You can check generated application rules in the hierarchies worksheet.
Use parameters to change dimensions. Here are the Entities dimension members:
You can see source parents from the MEMBERS worksheet, created hierarchies, and calculation factors.
The application uses these data to filter members by parents and hierarchy levels and to calculate aggregates.
You can update rules using the Update hierarchies item in the Actions menu.
Configuring member relations solves two tasks:
- It prevents incorrect input.
- It helps to show meaningful rows in forms with multiple dimensions in rows.
To edit member relations open the RELATIONS worksheet.
For example, we can configure the following relations between cost centers and key account managers:
In this example, all managers can input data for the cost center 1, only managers from Germany and Austria can input data for the cost center 2, and only managers from Switzerland and France can work with the cost center 3.
Note that these are not permissions. These are business rules.
That means that the company does not have cost center 2 operations in Switzerland and France, and cost center 3 operations in Germany and Austria.
To set the relation, type 1. To clear the relation, delete the value. You can copy and paste values.
The application changes the configuration immediately after the change. You do not need to click the Save button.
Below is the same table for relations between accounts and regions:
In this example, the tables are similar.