Chapter 7. Distribution of Planning Application

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

Now we can distribute the workbooks to our business users.

We have two choices:

1. We can send or publish a ready-to-use workbook.
2. We can 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 can 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 can create a workbook for business users, create worksheets, and connect to tables.

You can 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 can input data.

When you create a new form, you can 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 can 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 can 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 can use the SaveToDB add-in feature designed for this case specially.

You can 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 can send to your users connect credentials shown below, and they can connect themselves.

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

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

Users do this operation once. They can 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 can 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 can use row 1 to place formulas and comments.
2. You can 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 can edit highlighted cells.

You can 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 can copy and paste values if the source data changed.

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

Use Reload to reload data with newly calculated aggregates.

You can select new parameter values using the ribbon.

You can 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 can 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 can 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 can change the default table formatting. Use regular and conditional formatting Excel features.

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