Chapter 2. Planning Application Members

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.

Units

The application uses USD as the base currency (id 0). We need to change it to EURO.

Configuring units and currencies

Right-click on EUR and execute "Set as the base currency."

Setting EURO as a base currency

Now we have EURO as the base currency:

Configuring units and currencies - New currency applied

You may 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.

Dimensions

The application has the following default dimension configuration:

Configuring dimensions

The application has seven dimensions. The first four are mandatory. Dimension 5-7 are optional.

You may 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.

Configuring dimensions - Adding the Region dimension

The table contains several parameters related to permissions. We will configure this later.

Accounts

Let's open the MEMBERS worksheet and select the Accounts dimension:

Members worksheet - Selecting the Accounts dimension

The Accounts dimension contains a single member at the beginning.

Let's add the first members:

Configuring dimension members - Adding account 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:

Formatting dimension members

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 may change the default conditional formatting using Home, Conditional Formatting, Manage Rules...

Before the next step we need to click Reload Data and Configuration:

Reloading Data and Configuration

This step is required to update validation lists with new members:

Using validation lists in Excel for new dimension 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:

Configuring units, calculation types, parents, and factors

You may 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 may 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.

Updating dimension member hierarchies

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:

Copy and Insert Rows button

You will have a copy of selected rows:

Pasted copy

 

Change the sort_order values using the auto-fill feature and change CC1 to CC2 using search and replace:

Using Search and Replace to change dimension members

You may 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 may use search and replace in the parent column to change parents:

Using Search and Replace to change dimension member parents

Click Save and then click Actions, Update hierarchies.

Now you may 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:

The final configuration of account members

Times

Let's open members of the Times dimension. Select Times in the dimension_id parameter at the ribbon:

Opening the Times dimension

 

Also, apply the Times view using the Table Views drop-down menu:

Applying a special view for the Times dimension

This view shows columns related to the Times dimension and hides others.

You may use this feature on other worksheets also.

Moreover, you may 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:

Complete Excel ribbon of the planning application

 

Initially, the Times dimension contains only root members:

Click Actions, Add members for a year to add time members on monthly bases:

Adding Times members using the Actions menu

The application adds the complete set of configured members to get data for months, YTD, quarters, and years.

Added Times members

Use members like 2017.00 to input opening balance values in your applications. We do not use it in this example.

Categories

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:

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 may add and configure additional members in your applications (for example, for different versions).

In this example, we will use the predefined members.

Entities

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:

Initially configured members of the Entities dimension

Let's add the required members:

Adding members of the Entities dimension

We have used the existing member TOTAL as a parent instead of creating a new member All KAMs.

We may create it later if we need a separate aggregate for it.

Do not forget to update hierarchies after saving changes.

Regions

Select the Regions in the dimension_id parameter at the ribbon. You will see default members:

Default members of the Regions dimension

Let's add the desired members:

Adding members of the Regions dimension

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.

Hierarchies

You may check generated application rules in the hierarchies worksheet.

Use parameters to change dimensions. Here are the Entities dimension members:

Example of dimension member hierarchies

You may 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 may update rules using the Update hierarchies item in the Actions menu.

Relations

Configuring member relations solves two tasks:

  1. It prevents incorrect input.
  2. It helps to show meaningful rows in forms with multiple dimensions in rows.

To edit member relations open the RELATIONS worksheet.

For example, we may configure the following relations between cost centers and key account managers:

Example of relations between cost centers and key account managers

In this example, all managers may input data for the cost center 1, only managers from Germany and Austria may input data for the cost center 2, and only managers from Switzerland and France may 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 may 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:

Example of relations between accounts and regions

In this example, the tables are similar.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK