Using Distribution Lists with Velixo NX

We can use the Distribution List feature with a desktop installation of the traditional edition of MS Outlook to quickly distribute reports to a large number of users with a single click.  Reports can be sent by e-mail or simply saved to a folder in a variety of formats.  You have the freedom to include specific content (sheets/tabs) or use different parameters (company, branch, division, financial period, etc.) based on the recipient.  Highly sensitive data can be password protected.

Example use cases:

• E-mail a monthly performance dashboard in PDF format to the manager of every location of your 200-location business.

• Send detailed financials to your area/group managers in Excel format.

• Prepare a complete financial reporting package for your shareholders, removing Velixo formulas for those recipients without access to your ERP system.

• Archive the final version of your monthly reports to a network share with clear and consistent naming.

• E-mail a PDF version of your project progress report to project managers every week.

These are just some examples of what can be achieved — Report Distribution lists are extremely versatile, powerful, yet very simple to use.

Installation

The Distibution List feature is included in the optional Velixo Tools package.  Please see Installing the Velixo Tools for instructions and the download link.

For any workbook, start by creating a blank Distribution list. To do so, click on New List in the Distribution section:

What you will then see is a new spreadsheet that contains all the fields needed to set up report distribution:

Simply fill in the blanks.  Here’s a description of all the different settings:

By default, the distribution list shows 10 (blank) rows, but you are free to add as many as you want.  An Excel workbook can contain multiple distribution lists, so if you have different monthly/quarterly/yearly runs or simply occasional distribution requirements, feel free to add as many lists as you need and give your lists unique and memorable names.

Be sure not to change the order of the columns or introduce any additional columns anywhere except at the end of the Excel table.

Reports can be run with different parameters for each recipient.  You can configure the parameters in the Distribution List by adding new columns to the end of the list.

The name of the column must match a named range in your spreadsheet:

note: the named cells must be scoped to the ENTIRE workbook:

(worksheet-specific regions can only be used with the Velixo Blueprint feature)

Velixo NX will apply the parameters automatically during distribution.

Including temporary information

Occasionally it can be convenient to include a cell containing some temporary data or interim calculation but that you do not want to pass to your report as a parameter.  This can be accomplished by prefixing the name of the column with an exclamation point. In this example... ... the data in the !CompanyStores column could be used in determining the contents of the Stores column but would not be passed to the report.  Like all user-defined columns, these must be located at the end of the Excel table (leaving the standard columns undisturbed).

To start the distribution process, click Distribute All from under the Distribute button on the Velixo NX ribbon.

Note: You may also select one or only a few rows within the Distibution List and use the Distribute Selected Rows option.

When passing report parameters, any cells that are dependent upon those parameters are automatically updated.  If it is desired the ALL cells in the report be updated, select Yes for the Refresh Workbook setting:

When passing report parameters that can impact a pivot table, a Velixo Distribution List can be configured to automatically refresh the pivot tables as part of the Distribution process:

(Note:  when distributed reports contain pivot tables where the data source depends on a Blueprint parameter, such pivot tables will always be refreshed)

The Distribution Tools button provides two additional features.

Validate

This feature:

• Verifies that the worksheet conforms to the distribution template (contains the valid header fields and detail columns)
• Verifies that the detail table contains rows for distribution
• Ensures that all custom columns specified actually exist as named fields in the workbook
• Checks the format of each value in each row (emails, paths, URLs)
• Checks that any Blueprint tabs existent

Insert Folder Path

This feature opens Excel's standard file explorer dialog to allow the user to navigate to the desired output folder (rather than having to type the entire path name by hand).  This helps to avoid typographical errors in the folder path name.