Advanced Options
Advanced Options allow you to merge multiple source files into a single output, set worksheet level password protection, hide zeroes upon distribution, and publish hidden worksheets.
By default, the only Advanced Option that is selected is Publish Hidden Worksheets. The following is a description of each option:
Merge File Name
The output file name can be different from the source file name when distributed. If the same Merge File Name is specified for multiple reporting permutations, the output files will be merged into a single output file. If a file exists in the Output Folder you selected with the same Merge File Name, Reports Distribution Manager (RDM) will append to the file if the Replace Existing Workbook option is not selected.
If a Merge File Name is not specified a new file name will be automatically generated, the name will be constructed by combining the Source Report file name and Parameter permutation(s).
Worksheets from different source files, containing either GL Wand functions or Reports Wand functions, can be appended together into the same output file.
Worksheet Password
RDM can protect worksheets in Excel output files with a password. You can set a password within the source file or through the RDM Template Wizard. To set a password for an output file, when the source file is not password protected, enter a password in the Password field. Recipients of the output file will need the password you entered in RDM to unprotect the worksheet. If the source workbook contains protected sheets the password must be entered in the worksheet password field to allow RDM to execute and distribute the report.
Use the same password for all protected worksheets. RDM does not allow different passwords to be used for each worksheet.
Workbook Password
RDM supports workbook password protection where a password is required to open an Excel file. If you have set a workbook password on the source file enter the password in the Workbook Password field to allow RDM to execute and distribute the report. Recipients of the output file will need the the same password to unlock and open the file.
If you set either a worksheet or workbook password in the source file, RDM will need the password in order to distribute the report. Enter the existing password of the source file in the relevant password field. The password will be visible within the RDM Template. Remove the password before saving to the Shared Templates Drive or in any other location. Only enter the password when you are ready to execute the RDM Template.
Hide Zeros Range(s)
Select a named range or multiple named ranges to hide zeros when the report is distributed. RDM will use the Hide Zeros functionality to hide rows that only contain zero values within the named ranges specified in the source report. Multiple named ranges across the entire source workbook can be specified. Hide Zeros is performed after each permutation of report parameters.
When using Hide Zeros with RDM, leave all rows in the source report visible. Use Hide Zeros for source files that are being distributed periodically or to multiple cost centers and may contain rows that only contain zero values for a varying number of accounts.
Replace Existing Workbook and Replace Existing Worksheet
RDM can replace or append to an existing output file. Replace Existing Workbook, when selected, will replace an existing output file with the same Merge or RDM generated output file name in the Output Folder. When Replace Existing Workbook is not selected, RDM will append any additional distribution worksheets to an existing output file with the same Merge or RDM generated file name.
Replace Existing Worksheet, when selected, will replace existing worksheets, with the same parameter values. Essentially, this will refresh existing worksheets in a workbook with the latest data. When Replace Existing Worksheet is not selected, RDM will append any additional distribution worksheets to an existing output file. If the worksheet name exists in the output file, RDM will increment the worksheet name by adding a counter to the end of the worksheet name.
The following matrix explains some of the report distribution use cases and the appropriate settings for RDM. Each use case assumes the same Merge File Name and Output Folder:
| Use Case | Parameter Values | Replace Existing Workbook | Replace Existing Worksheet |
|---|---|---|---|
| Replace existing workbook | Different or same | Y | N |
| Replace worksheets | Different | Y | Y |
| Append additional worksheets | Different or same | N | N |
| Refresh worksheets | Same | N | Y |
Replace Existing Workbook will supersede Replace Existing Worksheet. If a workbook is replaced, additional worksheets will be inserted to the new workbook.
Publish Hidden Worksheets
If you have hidden worksheets in your source file, you can instruct RDM to publish the hidden worksheets in the output file. Alternatively, if Publish Hidden Worksheets is not selected, the hidden worksheets in the source file are excluded when distributed.
If you decide to publish hidden worksheets, the worksheets will remain hidden in the output file. If hidden Reports Wand drilldowns worksheets are included in the source file and you select Publish Hidden Worksheets, the hidden drilldown worksheets will not be refreshed.
If you enabled Reports Wand drilldowns to a hidden worksheet, you may encounter an error, even if the Publish Hidden Worksheets has been selected. Reports Wand does not drilldown to hidden worksheets. If a published worksheet contains a cell reference to a hidden worksheet in the source file and Publish Hidden Worksheets has not been selected, the cell reference will be broken in the output file.
Click Finish to create or add to the RDM Template, once the RDM Template is created you can now edit the RDM Template.
Distributed Worksheet(s)
RDM will only distribute the specified worksheets. The unselected worksheets will not be included in the output file. If no worksheet is selected, RDM will ignore this feature and distribute all worksheets.
RDM only supports the visible/hidden worksheets.
Add Parameters To Worksheet(s)
This option allows you to control the worksheet names generated in the distributed workbook. When checking this option RDM will append the parameter value specified to the worksheet name that is displayed on the tab at the bottom of the workbook. If this option is unchecked then the worksheet name remains the same as the original source workbook.