Planning Application with Microsoft Excel and SQL Server

Planning Application with Microsoft Excel and SQL Server

This e-book shows how to configure a planning application built with Microsoft Excel and SQL Server.

You may download the source codes, install the application into your database, and repeat all the steps.

If your company uses Microsoft Excel to get and share business data, you may save a lot of time and money.

Download PDF Version Download Application

Introduction

If your company uses Microsoft Excel to get and share business data, you have to continue reading.

You may get great benefits and save a lot of time and money.

This e-book shows how to configure a planning application built with Microsoft Excel and SQL Server.

The application uses the SaveToDB Enterprise add-in for Microsoft Excel to work with SQL Server.

You may download the trial version of the SaveToDB add-in and the source code of the planning application to install it in your environment.

We will do typical steps for such class applications:

  1. Configure dimensions.
  2. Configure members.
  3. Configure forms.
  4. Configure permissions.
  5. Import budget and actual data.
  6. Copy budget and actual data to forecast.
  7. Distribute workbooks to users.

As a result, your business users may use forms like this to work with source and consolidated data:

User forms of planning application in Excel

 

I hope you will enjoy reading and become a fan.

Best regards,

Sergey Vaselenko

November 3, 2017

Chapter 1. Scenario

Business Case

We will configure the application for the following business case.

A company has a budget for the current year in a Microsoft Excel workbook.

The company has actual data for January - October in an accounting system.

CFO needs an updated cost forecast for November - December and the entire year.

CFO wants to see detailed and consolidated data, actuals versus forecast and budget versus forecast.

The company has three cost centers with hierarchical structures.

The company operates in Germany, Austria, Switzerland, and France.

The company has four key account managers (KAM) responsible for each country.

The desired workbook should look like this:

Cost center model

The table contains Budget vs. Actual for January - October, and Budget vs. Forecast for November - December.

Key account managers must have the possibility to see the budget and actual data by countries in a read-only mode and edit forecast data for November - December.

 

 

This example is based on the model created by MultiBase GmbH (www.multibase.de).

MultiBase GmbH has a great team of experts in reporting and analytics, ETL, and database management.

I appreciate the permission to use their model.

Model

We may define the following model to implement business case requirements.

The target database must contain the following dimensions:

  1. Accounts
  2. Times
  3. Categories
  4. Entities
  5. Regions

The Accounts dimension must contain members like these for each cost center:

  • Cost Center 1 (CC1)
  • CC11
  • CC111
  • CC112
  • CC113
  • CC12
  • CC121

The Times dimension must contain months and a year total like

  • 2017
  • 2017.01 ...
  • 2017.12

The Categories dimension must contain the following categories:

  • Budget
  • Actual
  • Forecast

The Entities dimension must contain the following members:

  • All KAMs
  • KAM (Germany)
  • KAM (Austria)
  • KAM (Switzerland)
  • KAM (France)

The Regions dimension must contain the following members:

  • All Countries
  • Germany
  • Austria
  • Switzerland
  • France

At this time we would combine the Entities and Countries dimensions. However, these are different business entities. So, we have two dimensions to have the possibility to extend the model in the future.

Chapter 2. 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:

  • TIMES
  • OPENING.BALANCE
  • YEARS
  • YEARS.YTD
  • YEARS.Q

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.

Chapter 3. 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.

Chapter 4. Permissions

Microsoft Excel tables connect directly to a Microsoft SQL Server database using user credentials.

Microsoft SQL Server controls user permissions.

So, a database administrator must add application users to a database and assign application roles.

The planning application has four built-in roles:

  1. planning_app_users - Business users
  2. planning_app_analysts - Business analysts
  3. planning_app_administrators - Application administrators
  4. planning_app_developers - Application developers

Each role has specific predefined permissions for application objects.

In addition to Microsoft SQL Server permissions, the planning application checks additional internal permissions on the forms and dimension members for business users (members of the planning_app_users role).

Application administrators manage such permissions using the planning-app-admin.xlsx workbook.

It is easy. So, business managers may manage application permissions with no requests to IT.

Database Permissions

This topic is for database administrators.

To add an application user:

  1. Create a login.
  2. Create a user in the application database.
  3. Assign an application role to the user.

For example, you may use the following code to create an SQL Server login (in the master database):

CREATE LOGIN pa_user_01 WITH PASSWORD=N'Dev_2011#_Xls4168'

You may use the following code to create a user (in the application database):

CREATE USER pa_user_01 FOR LOGIN pa_user_01

You may use the sp_addrolemember procedure to assign a role:

EXEC sp_addrolemember 'planning_app_users',  'pa_user_01'

As talked above, you may add a user to one of the application roles:

planning_app_users, planning_app_analysts, planning_app_administrators, and planning_app_developers.

Form Permissions

To manage form permissions, open the planning-app-admin.xlsx workbook and the form_permissions worksheet:

Managing form permissions using the form_permissions worksheet

You see a form list configured by business analysts in rows and usernames in columns.

Just set 1 to allow executing the form and 0 to deny executing.

Member Permissions

To manage member permissions, open the member_permissions sheet and select a dimension on the ribbon:

Managing member permissions using the member_permissions worksheet

Here is a legend of used symbols:

R - select (read), specified for this member;

r - select (read), inherited from a parent member;

W - select and update (read and write), specified for this member;

w - select and update (read and write), inherited from a parent member;

D - deny, specified for this member;

d - deny, inherited from a parent member.

In the screenshot above, we may see:

The administrator denies read and write operations for Company Total directly and for its children by inheritance.

However, he allows reading and writing for KAM (Germany) and reading for KAM (Austria).

As a result, the user may see only two entities and may edit data for the first one only.

Type "r", "w", or "d" to change permissions.

The application applies for new permissions immediately after changes.

Base Settings

The application verifies member permissions in accordance with the base dimension settings:

Default member permissions with base dimension settings

In this example, we see five active dimensions.

The first four are protected. The application checks permissions for these dimensions only.

The Times and Categories dimensions allow selecting and updating any member by default.

So, you need to deny access to such dimension members if required.

The Accounts and Entities dimensions deny access by default.

So, you need to allow access for such dimension members.

You may find this form in the dimensions worksheet in the planning-app-analyst.xlsx workbook.

Account Permissions

Here is an example of account permissions:

Example of account permissions

Typically, you set permissions for upper-level members, and the application applies permissions to child members.

You may tune permission for any child member. Just change its permissions.

Entity Permissions

Here is an example of entity permissions:

Example of entity permissions

Typically, this is a protected dimension, and you have to allow access directly.

Time Permissions

Here is an example of time dimension permissions:

Example of time permissions

Typically, this is a protected dimension that allows reading and writing by default. So, you may leave this as is.

Category Permissions

Here is an example of category dimension permissions:

Example of category permissions

Typically, this is a protected dimension that allows reading and writing by default. So, you may leave this as is.

Note that you see dimension members that may contain input data. You do see calculated members.

Region Permissions

Here is an example of region dimension permissions:

Example of region permissions

In this example, the region dimension is not protected. So, the application does not control access, and you do not need to change its permissions.

Closing Periods

Use the closed_periods worksheet to prevent changes in closed periods:

Using the closed_periods worksheet to prevent changes in closed periods

For example, in this case, we closed all periods for all categories except the November and December forecast.

The application checks these settings for all users including business analysts and application developers.

The planning-app-analyst.xlsx workbook also contains this form, and business analysts may close periods too.

Chapter 5. Integration

You may use two basic methods to import data into the planning application:

  1. Copy and paste data from existing Excel workbooks.
  2. Import data using SQL commands.

If you have a lot of data, you may contact us to discuss other methods.

Copying Data

It is a simple method as it requires time only.

You may use a trick. Create a row set with accounts to import and a form that uses this row set.

In this case, you may import data for all periods and any entity and category at once.

Also, you may create an export form in the source workbook and paste data into existing forms.

Note that you have to open the periods for changes to insert data.

Importing Data

You may use the dbo25.usp_import_fact procedure to import data easily.

For example, you may use commands like this:

EXEC dbo25.usp_import_fact 200, 'CC111', '2017.01', 'ACTUAL', 'KAM.DE', 'COUNTRY.DE';

EXEC dbo25.usp_import_fact 200, 'CC111', '2017.02', 'ACTUAL', 'KAM.DE', 'COUNTRY.DE';

EXEC dbo25.usp_import_fact 200, 'CC112', '2017.01', 'ACTUAL', 'KAM.DE', 'COUNTRY.DE';

EXEC dbo25.usp_import_fact 200, 'CC112', '2017.02', 'ACTUAL', 'KAM.DE', 'COUNTRY.DE';

You may export such commands from your accounting system and import using the sqlcmd utility.

Note that the procedure accepts member codes as parameters.

You may contact us to discuss other methods.

Chapter 6. Data Management

Periodically, we need to create a copy of the current data.

For example, we may fill the forecast data with actual data for past months and with budget data for future ones.

Or, we may create several budget and forecast versions to make a decision about right numbers.

You may copy data between periods and categories using the data_management worksheet:

Using the data_management worksheet to copy data

Just right-click on the desired cell and choose the operation (the menu is dynamic):

Example of the Excel context menu in the data_management worksheet

For example, we may use these operations to prepare forecast data for this case.

Chapter 7. Distribution

In the previous steps, we have created dimension members, configured forms and permissions, imported data.

Now we may distribute the workbooks to our business users.

We have two choices:

  1. We may send or publish a ready-to-use workbook.
  2. We may send an email with database connection credentials.

In any case, the application uses the configuration stored in a database. So, both ways have the same features.

Moreover, you may change settings later. Business users just update data and configuration.

So, you distribute workbooks once.

Note that your users must have the SaveToDB Enterprise 7.13 or higher installed.

Distributing Workbooks

You may create a workbook for business users, create worksheets, and connect to tables.

You may leave parameters empty. So, users will not see sensitive data.

Distributing workbooks with empty parameters

When a user opens the workbook, he or she must click Reload, Reload Data and Configuration.

This action updates parameter values actual for the user.

Then, a user selects parameter values and get the form with actual data, and may input data.

When you create a new form, you may notify your users. They just click Reload Query List and get new forms.

Protecting Connect Credentials

Usually, you create workbooks using your credentials.

If you use the Windows authentication, you may send the workbook as is.

When a user reloads data, the application connects with user's credentials, not yours.

If you use the SQL Server authentication, you must protect your password.

You may clear it. This is a standard Excel feature.

In this case, Excel prompts a user for a password, and the user uses his login and password.

However, you may use the SaveToDB add-in feature designed for this case specially.

You may open SaveToDB options and check Encrypt connection string passwords in this workbook:

Encrypting connection string passwords in Excel workbooks

In this case, the SaveToDB add-in encrypts passwords using Windows encryption features, and no one can see or use your passwords.

Distributing using Connect Credentials

The planning application loads application settings from a database. This is a SaveToDB add-in feature.

So, you may send to your users connect credentials shown below, and they may connect themselves.

Required connect credentials (a server, username, password, and a database)

Below is a complete instruction. You may send a link to the online version also.

https://www.savetodb.com/connecting-to-planning-app.htm

Users do this operation once. They may save a workbook and use it further.

Connecting to Planning Application

To connect to the application, run SaveToDB, Wizards, Data Connection Wizard:

SaveToDB Data Connection Wizard

Select Microsoft OLE DB Provider for SQL Server and click Next:

Using Microsoft OLE DB Provider for SQL Server

Fill-in the server name, your username and password, and select a database. Click Next.

Using connect credentials (a server, username, password, and a database)

Select xls23.view_query_list_form in the Query List and leave all check-boxes checked. Click Finish.

Using the Query List of the Data Connection Wizard

You may select parameter values here. Or just click OK.

Using form parameters with the Data Connection Wizard

You will see a dialog box to choose a cell to insert a table.

Initial address to paste a connected table

Select cell B3 in the worksheet:

When you insert a table into cell B3, you have the following benefits:

  1. You may use row 1 to place formulas and comments.
  2. You may use row 2 to filter rows using auto-filters. Just type filter values like >10000 or *CC11*.
    Delete the cell value to clear the auto-filter.
  3. You have an empty column A. The table looks better.

The wizard inserts a table, with empty data columns if you do not specify the parameter values.

Empty budget form with empty parameters

Increase the window width to see the complete ribbon:

Budget form parameters at the ribbon

You see parameter names used in a database if you do not turn on the translation.

 

To turn on the translation, open SaveToDB Options and select the desired language:

Turning on translation in the SaveToDB Options dialog box

Click OK. Then click Reload, Reload Data and Configuration.

You will see the translated parameter names:

Translated parameter names at the ribbon

 

Select parameter values and voilá!

Example of a budget form

You may edit highlighted cells.

You may paste formulas into form cells. However, the application replaces them with values while reloading data.

Place your formulas under the table. In this case, you may copy and paste values if the source data changed.

You may use Undo (Ctrl-Z) to undo changes.

Use Reload to reload data with newly calculated aggregates.

You may select new parameter values using the ribbon.

You may change forms using the Active Query drop-down list.

Use Reload Query List to reload the list and get new forms configured in a database.

You may copy worksheets. Just copy the worksheet and click Reload, Reload Data and Configuration.

Use Reload, Reload All Workbook Tables to reload all tables at once.

Sometimes, you may see incorrect column orders because of Excel adds new columns at the end.

Just select an empty value in any parameter to clear columns and then select the desired value.

You may change the default table formatting. Use regular and conditional formatting Excel features.

You may restore the default formatting from a database using Wizards, Table Format Wizard.

Conclusion

We have configured the planning application. Our users may work in Microsoft Excel and online.

Final budget form in Microsoft Excel

We have made the following steps:

  1. Configure dimensions.
  2. Configure members.
  3. Configure forms.
  4. Configure permissions.
  5. Import budget and actual data.
  6. Copy budget and actual data to forecast.
  7. Distribute workbooks to users.

We have made these steps in Microsoft Excel. And you may repeat these steps for your case.

You may have great results and satisfied users spending less money and time.

I hope to see you as our valuable customers.

Best regards,

Sergey Vaselenko

FAQ

We need more than seven dimensions. How to add a dimension?

Adding a dimension requires changes to the application database. See a developer guide for this.

 

We need change or add features. How to do this?

The application has open source codes. So, database developers may do this.

Moreover, you may add a lot of features just configuring the SaveToDB add-in.

Please do not hesitate to ask us about your demands.

 

We have a lot of accounts. Will your application work fast?

The application uses standard joins with linear relations to calculate aggregates. So, it should work fast.

 

We have a lot of business users. Will your application work fast?

Business users execute stored procedures to select data on demand and to update the source cube data on cell changes within Excel. These operations are short.

Microsoft SQL Server works with thousands of users. The application should work fast.

 

Can business users work offline?

Yes. See Offline User Forms.

 

Can we use Azure Active Directory integrated authentication?

Yes.

 

Can you port your application to MySQL or PostgreSQL?

This is possible. However, we do not have such plans. Note you may use Microsoft SQL Server Express for free.

 

Can we operate with prices and amounts?

Yes. Just use the Average calculation type for such accounts.

 

Can we operate with the product of prices and amounts?

In the current version, no. The application uses the linear model to calculate aggregates.

However, you may add formula lines below the forms in Excel and just copy results into forms.

Can we operate with conditional formulas like IF THEN ELSE? To calculate taxes, for example.

In the current version, no. The application uses the linear model to calculate aggregates.

However, you may add formula lines below the forms in Excel and just copy results into forms.

 

How to calculate VAT?

Just set the VAT account as a parent and set the factor value as the tax rate in the usp_members form.

 

How to use different calculation models for budgets and actuals? For example, to calculate VAT.

You may use three account members like these:

VAT - set this member as a parent for VAT planned and VAT actual

    VAT planned - set this member as a parent for underlying budget accounts

    VAT actual - import the actual data to this account

 

We need to have the same editable account in several forms. How to do this?

You have two options:

  1. Split the account into multiple accounts for different forms and set the master account as a parent.
  2. Create different row sets for different forms and link the rows to a single underlying account.

 

A business user does not see the required form in the Query List. How to fix this?

First of all, click the Reload Query List button at the ribbon.

If the required form is absent, set the permission to execute the form in the usp_form_permissions form (in the planning-app-admin.xlsx workbook) and then reload the query list.

 

A business user does not see the required rows in a form. How to fix this?

Check and change permissions in the usp_member_permissions form (in the planning-app-admin.xlsx workbook).

 

Sales managers must have cost accounts from production in the same form. How to do this?

A typical structure and permissions for this case are:

TOTAL - no permissions

    SALES - read and write permissions

    PRODUCTION - read permissions

Accounts of sales - read and write permissions

Accounts of productions - read permissions

In this case, sales managers may see data from production.

Also, place the Accounts and Entities dimensions in the row axis of the forms.

Otherwise, if the Entities dimension is on the page axis, the user can work with the sales or production data separately, but not in the same screen form.

 

How to change form formatting for all users?

You may change the default form formatting in Excel.

The conditional formatting rules use values from the following columns:

is_editable, decimal_places, is_percent, row_color, row_bold, and row_indent.

To save formats to a database, use SaveToDB Table Format Wizard.

Users may run the same wizard and load new formats from a database.

See details at https://www.savetodb.com/docs/savetodb-wizards.htm#table-format-wizard.

Appendix A. Installation

Download

You may download the planning app at https://www.savetodb.com/download.htm.

The application also requires SaveToDB Enterprise 7.13 or higher. You may download the add-in here too.

Download Package

Unzip the downloaded package to a local drive.

Open the PlanningApp folder. It has the following content:

PlanningApp folder of the downloaded package

The folder contains application workbooks, this e-book, and folders with SQL scripts.

Installation

You may find the installation scripts in the install-scripts folder:

Planning application installation scripts

To install the application:

  1. Create a database like PlanningApp.
  2. Open and execute (F5) the savetodb-framework-install-en.sql script.
  3. Open and execute (F5) the application-install.sql script.

You may do these steps using SQL Server Management Studio (SSMS).

If you do not have SSMS installed, you may use gsqlcmd scripts discussed below.

Installing Examples

You may install the Cash Flow and Cost Centers examples using the Actions menu in the MEMBER worksheet in the planning-app-analyst.xlsx workbook.

Installation using gsqlcmd

You may visit https://www.gsqlcmd.com, download and install the gsqlcmd utility.

This free utility allows executing SQL scripts using connections configured in a visual mode.

Open the gsqlcmd-scripts folder:

Planning application installation scripts for gsqlcmd

And execute the following batch files one-by-one:

  1. 1-edit-config.cmd
  2. 2-edit-grants.cmd
  3. 3-install-savetodb-framework.cmd
  4. 4-install-application.cmd
  5. 5-clear-credentials.cmd

The 1-edit-config.cmd open a visual connection string editor, gConnectionManager:

Editing the connection string using gConnectionManager

Click on the ... button and edit connection strings. Then click the Save button and close the editor.

You may use gsqlcmd scripts to uninstall the application also.

Just use application-remove.cmd instead of 4-install-application.cmd and savetodb-framework-remove.cmd instead of 3-install-savetodb-framework.cmd.

Test Users

If you install the application for test purposes, you may create test users used in the application workbooks.

Open the test-users folder:

Installation scripts for test users

You have to open and execute master-logins-install.sql first against the master database.

Then open and execute database-users-install.sql against the planning application database.

The gsqlcmd scripts discussed above include these operations.

You may use database-users-remove.sql and master-logins-remove.sql files to remove test users.

Uninstalling

You may use the following files from the remove-scripts folder to uninstall the application:

Uninstalling scripts

Open and execute application-remove.sql first. Then open and execute savetodb-framework-remove-en.sql.

If you do not have SQL Server Management Studio (SSMS) installed, you may use gsqlcmd scripts discussed above.

Appendix B. Connections

The download package includes configured workbooks:

  • planning-app-admin.xlsx
  • planning-app-analyst.xlsx
  • planning-app-developer.xlsx
  • planning-app-facts.xlsx
  • planning-app-user.xlsx

Workbooks use connections to a local SQL Server Express instance with the test usernames and passwords.

When you open a workbook for the first time, you have to change connections to your database.

To change connections, run SaveToDB, Wizards, Change Connection Wizard.

You will see the first screen like this:

Change Connection Wizard - Database connection

Change the server name and your user credentials. Click Next.

 

In this step, leave all tables checked, and click Finish:

Change Connection Wizard - Objects to change connection strings

You will see the message like this:

Change Connection Wizard - Final message

Click OK.

In these steps, we have changed connections.

Now you have to reload all data from your database using new credentials.

 

Click SaveToDB, Reload, Reload All Workbook Tables. You will see the wizard window like this:

Reloading data and configuration of all workbook tables

Leave all tables checked, and click Load. The wizard will reload all tables and show the reload status:

A final step of reloading data and configuration of all workbook tables

Click Finish.

That's all. You may save the workbook and continue work.