Chapter 3. Planning Application Forms

Chapter 3. Planning Application Forms

In this chapter, we will discuss configuring forms for business users.

User Forms

Business users have personal Excel workbooks to work with forms. We will open an example later.

Business analysts may test forms on the USER_FORMS and OFFLINE_FORMS worksheets.

Using USER_FORMS worksheet to test forms

First of all, click the Reload Query List button and select a report from the drop-down Active Query list:

Changing forms using the query list

The Active Query list is dynamic and contains preconfigured active forms with the actual select permission.

So, different business users may have different lists.

Let's select parameter values using drop-down lists at the ribbon:

Changing form parameters using the ribbon

Now, we see an empty form:

Example of a form with applied parameters

We may see accounts in form rows and time periods in columns. This is a preconfigured layout for the Accounts*Times form. You may configure layouts with the desired row and column dimensions.

Conditional Formatting

All application forms use standard Excel formatting features.

For example, the current form has the following formatting rules:

Example of Conditional Formatting Rules Manager

You may change rules including fonts, colors, sizes, borders, and number formats.

If you want to set new formatting as default for other users, click SaveToDB, Wizards, Table Format Wizard:

Example of Table Format Wizard

Check the forms you want to save and click Save in Database.

Note that you and other users may also restore formats from a database using the Restore from Database button.

Editing Data

Let's edit several highlighted (editable) cells:

Editing budget data in Microsoft Excel

The application updates the source cube data immediately after changes, however, it leaves the form as is.

You may paste values using formulas. However, the application replaces formulas with values later.

Note that you may use Undo (Ctrl-Z) to revert changes.

Click the Reload button to reload data:

Refreshing budget data

As you may see, the application updates the aggregates.

 

Try to edit any cell with the aggregated value. You will have an error message:

A warning message for aggregate data

The application controls business logic rules and permissions and may reject user's changes.

Drill-Down

You may double-click on any cell or right-click and open on Show source values:

Using the Excel context menu to drill-down source values

The application shows the underlying values:

Example of underlying budget data

Offline User Forms

In the previous topic, we have discussed forms that update the underlying database immediately after changes.

Also, forms control user values using business rules and permissions.

However, sometimes we need the possibility to work outside of the corporate network.

In this case, we may use offline forms.

Just load the required data and edit it. Later, you may click the Save button to save the changes.

Example of an offline budget form

This is a useful scenario. However, note that the form ignores the most of the business rules during editing.

For example, you edit data offline. CFO has closed the period for changes.

When you click Save, the application will not save your data into closed periods.

Note that you have to use SaveToDB 7.13 or higher to work with offline forms.

Online and Offline Forms

You select the form behavior in the SaveToDB Data Connection Wizard using the appropriate query list view:

Using Data Connection Wizard to select online and offline forms

You may distribute application workbooks to end users with any variant or even for both.

Business users may change query lists quickly using the Query List group button that opens the same dialog box.

Using the Query List group button to select online and offline forms

Web Forms

An additional ASP.NET application allows getting and editing data online on desktops, tablets, and smartphones.

Using browsers for working with budget data

The web application uses the same settings as the Excel application with the SaveToDB add-in, except the conditional formatting. You may customize formatting using CSS styles.

Form List

We have discussed working with forms above. Below we will discuss configuring forms.

As a business analyst, you may create and configure as many forms as your company need.

First of all, you need to define a form in the Forms table. The default app includes the following forms:

Default forms of the planning application

Each form must have a unique code and name.

You may change form names anytime. However, try to define form codes correctly before activating.

Do not use spaces in codes.

Default forms use the * and + signs in the codes and names. This is not necessary but useful.

The * sign splits row and column dimensions. The + sign splits dimensions in the same axis.

Use friendly codes and names for business forms. For example, CostCenters*Times or just CostCenters.

In this example, we need a form to show Budget vs. Actual and Budget vs. Forecast by months.

We may use the default Accounts*Times+Categories form for this.

Form Settings

We will use the form_dimensions worksheet to configure form layouts and parameters.

You may use the built-in table views to show required columns for every mode:

Built-in table views for confuguring forms

The form uses Excel validation lists to suggest correct values and the conditional formatting to highlight mandatory and disabled cells:

Using Excel validation lists and conditional formatting

Use Reload, Reload Data and Configuration to reload validation lists if you add or change dimension members.

Form Layouts

You have to specify the axis for every dimension of each form:

Example of the form axis configuration

The form shows only active dimensions. You activate or deactivate dimensions in the dimensions worksheet.

You have to specify the header order for dimensions used in rows and columns.

For example, the Accounts*Times+Categories form shows columns with times followed by categories like

2017.01 Budget, 2017.01 Actual, 2017.02 Budget, 2017.02 Actual.

Spaces separate member codes in column headers. That because of you must not use spaces in member codes.

Form Rows

This table view shows columns used to configure form rows.

By default, a user may select a parent member for the row axis. The application will include all children.

This is a good solution for universal forms.

However, if we need to create a specific form with the predefined row set, we may specify the parent directly.

For example, we may set the CC member (Cost Centers) as a root member:

Example of a root member configuration

In this case, the form would contain only the CC member and its children (see Hierarchies).

If we want to exclude the CC member, we may set 1 in the form_start_level column (to exclude level 0).

If we want to show children of certain levels only, we may set the end level in the form_end_level column.

Another way to configure row sets is using Rowsets. We will discuss this below.

However, we specify the row set member in the same field, root_member_id.

In this example, we reject the change for the default form. Just click Reload and do not save changes.

Form Columns

This table view shows columns used to configure form columns.

It has the same logic as described above for rows.

For example, to show only year totals without monthly details, we may set the years* row set as a root:

Example of the form column configuration

We dismiss this change in this example.

Form Parameters

The application shows form parameters at the ribbon:

Example of budget form ribbon parameters

You may configure what parameters in what order to show and what members to use as values.

Activate the Parameters table view to show related columns:

Example of the Parameters table view

You may reorder parameters using the parameter_index column (shown as param in column 4).

Also, you may remove the parameter from the ribbon clearing the parameter_index value.

In this case, you must specify the root member (column 6 shows as root).

Also, you may change the root member, parameter_start_level, and parameter_end_level to show children of the specified parent with required levels in hierarchies.

The example shows using the years* row set as a root member (we dismiss this change).

The application shows parameters using business names.

You may change these names in the parameter_name field of the dimension configuration discussed above.

Using the Dimensions table to edit parameter names

Note that to activate translation, users must select data translation in the SaveToDB add-in options:

Activating the translation in the SaveToDB Options dialog box

Form Rowsets

As shown above, by default, the application uses hierarchies to filter members in rows, columns, and parameters.

For example, when we select 2017 as a parent, the application includes 2017 and all months of 2017.

If we select the YEARS member as a parent, the application includes all year totals and all months.

Row sets allow defining sets of members to use in forms.

For example, the application contains the built-in years* row set that includes only year totals.

The second reason to use row sets is creating sets of members from different hierarchies.

For example, we have parents: Cost Centers, Sales, Cash Flow, and HR.

To have a form that uses members from these hierarchies, we may create a set and assign members to its rows.

You may check and create row sets for any dimension in the rowsets worksheet:

Create row sets in the rowsets worksheet

You may see the asterisk at the end of codes and names.

This is just a trick used to distinguish hierarchy members and row sets in parameters and validation lists.

You may create your rule.

The application automatically updates these row sets when you add new time members using the Actions menu.

 

Below are the predefined row sets for the Categories dimension:

Predefined row sets for the Categories dimension

Row sets with one asterisk contain two columns only. For example, Budget and Actual.

Row sets with two asterisks contain base columns and differences.

For example, BUDGET_VS_ACTUALS** includes the following columns:

Columns of the BUDGET_VS_ACTUALS** row set

You may check and configure row set rows in the ROWS worksheet:

Working with row set rows in the ROWS worksheet

To add a member, copy the line in the desired position using the Copy and Insert Rows in the Table Views group, change the member, and save the changes.

You may do even more.

Select the Data columns table view on the ribbon:

Applying the Data columns table view

You will see additional columns:

Columns of the Data columns table view

You may rewrite any property of the underlying member.

For example, you may change row codes, row colors, and indents of form rows.

Note that you may add empty lines here (do not add empty members to dimensions).

To change row orders, cut and paste lines to the desired position.