Chapter 3. Planning Application Forms
In this chapter, we will discuss configuring forms for business users.
Business users have personal Excel workbooks to work with forms. We will open an example later.
Business analysts can test forms on the USER_FORMS and OFFLINE_FORMS worksheets.
First of all, click the Reload Query List button and select a report from the drop-down Active Query list:
The Active Query list is dynamic and contains preconfigured active forms with the actual select permission.
So, different business users can have different lists.
Let's select parameter values using drop-down lists at the ribbon:
Now, we see an empty form:
We can see accounts in form rows and time periods in columns. This is a preconfigured layout for the Accounts*Times form. You can configure layouts with the desired row and column dimensions.
All application forms use standard Excel formatting features.
For example, the current form has the following formatting rules:
You can 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:
Check the forms you want to save and click Save in Database.
Note that you and other users can also restore formats from a database using the Restore from Database button.
Let's edit several highlighted (editable) cells:
The application updates the source cube data immediately after changes, however, it leaves the form as is.
You can paste values using formulas. However, the application replaces formulas with values later.
Note that you can use Undo (Ctrl-Z) to revert changes.
Click the Reload button to reload data:
As you can see, the application updates the aggregates.
Try to edit any cell with the aggregated value. You will have an error message:
The application controls business logic rules and permissions and can reject user's changes.
You can double-click on any cell or right-click and open on Show source values:
The application shows the underlying values:
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 can use offline forms.
Just load the required data and edit it. Later, you can click the Save button to save the changes.
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:
You can distribute application workbooks to end users with any variant or even for both.
Business users can change query lists quickly using the Query List group button that opens the same dialog box.
An additional ASP.NET application allows getting and editing data online on desktops, tablets, and smartphones.
The web application uses the same settings as the Excel application with the SaveToDB add-in, except the conditional formatting. You can customize formatting using CSS styles.
We have discussed working with forms above. Below we will discuss configuring forms.
As a business analyst, you can 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:
Each form must have a unique code and name.
You can 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 can use the default Accounts*Times+Categories form for this.
We will use the form_dimensions worksheet to configure form layouts and parameters.
You can use the built-in table views to show required columns for every mode:
The form uses Excel validation lists to suggest correct values and the conditional formatting to highlight mandatory and disabled cells:
Use Reload, Reload Data and Configuration to reload validation lists if you add or change dimension members.
You have to specify the axis for every dimension of each form:
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.
This table view shows columns used to configure form rows.
By default, a user can 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 can specify the parent directly.
For example, we can set the CC member (Cost Centers) as a root member:
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 can set 1 in the form_start_level column (to exclude level 0).
If we want to show children of certain levels only, we can 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.
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 can set the years* row set as a root:
We dismiss this change in this example.
The application shows form parameters at the ribbon:
You can configure what parameters in what order to show and what members to use as values.
Activate the Parameters table view to show related columns:
You can reorder parameters using the parameter_index column (shown as param in column 4).
Also, you can 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 can 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 can change these names in the parameter_name field of the dimension configuration discussed above.
Note that to activate translation, users must select data translation in the SaveToDB add-in options:
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 can create a set and assign members to its rows.
You can check and create row sets for any dimension in the rowsets worksheet:
You can 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 can 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:
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:
You can check and configure 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 can do even more.
Select the Data columns table view on the ribbon:
You will see additional columns:
You can rewrite any property of the underlying member.
For example, you can change row codes, row colors, and indents of form rows.
Note that you can add empty lines here (do not add empty members to dimensions).
To change row orders, cut and paste lines to the desired position.