Chapter 7. Distribution of Planning Application

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.

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:

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.

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

https://www.savetodb.com/kb/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:

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

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

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

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

You will see a dialog box to choose a cell to insert a 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.

Increase the window width to see the complete 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:

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

You will see the translated parameter names:

Select parameter values and voilá!

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.