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 may 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 may have different lists.
Let's select parameter values using drop-down lists at the ribbon:
Now, we see an empty form:
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.
All application forms use standard Excel formatting features.
For example, the current form has the following formatting rules:
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:
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.
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 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:
As you may 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 may reject user's changes.
You may double-click on any cell or right-click and open on Show source values:
The application shows the underlying values:
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.
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.
You select the form behavior in the SaveToDB Data Connection Wizard using the appropriate query list view:
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.
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 may customize formatting using CSS styles.
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:
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.
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:
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 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:
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.
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:
We dismiss this change in this example.
The application shows form parameters at the ribbon:
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:
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.
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 may create a set and assign members to its rows.
You may check and create row sets for any dimension 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:
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 may 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 may do even more.
Select the Data columns table view on the ribbon:
You will see additional columns:
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.