Contribution Templates

Creating Contribution Templates

Contribution templates define the content and and appearance of input forms for Contribution Campaigns.

Contribution starts with one or more planning report spreadsheets, which might normally be distributed using ReportWORQ. You can turn the reports into Contribution templates by adding one or more formulas that define the input ranges that require data entry. You can also add data validation formulas and associated warning messages.

Most formatting options you set in Excel, such as Freeze Panes, persist when the template is later used to create input forms. When you are finished configuring input ranges and data validations, set the spreadsheet’s Print Area to include only the cells you want Contribution users to see.

This article describes how to use the ReportWORQ add-in for Excel to create input ranges and data validations. Alternatively, you can create and edit formulas manually.

The main topics in this article are as follows:

• Accessing the ReportWORQ Add-in

• View and Create Input Ranges

• Manage Validation Formulas

• Preview the Input Form

• Manage Settings

• Create and Edit Formulas Manually

Accessing the ReportWORQ Add-in

Before you can use the add-in for the first time, you must install it. Each time you want to use the add-in, you must launch it in Excel. For more infomation, see Add-in Setup.

Internet access is required while installing or using the ReportWORQ add-in. You must be logged in to ReportWORQ while you use the add-in.

To access the ReportWORQ add-in, you open the Excel workbook (.xslx file) you want to modify, and then launch the add-in.

If you open a report that has been previously modified by the ReportWORQ add-in, Excel attempts to launch the add-in:

• If the add-in is installed but you are not logged in to ReportWORQ, you are prompted to log in.

• If the add-in is not installed, you are prompted to Accept and Continue, and then you are prompted to log in to ReportWORQ.
  
  

View and Create Input Ranges

When you select View and Create Input Ranges , the Edit Input Formulas area appears.

You can create new input ranges or review and modify existing ones.
As you hover over each item in the list, the corresponding input range is highlighted in the spreadsheet.

To Create or Modify an Input Range:

1. If you are modifying an existing input range, select it in the list, and proceed to Step 4.

2. Select Create a new Input Range.
   The Select Data box appears.

3. Select or specify which empty cell in the spreadsheet will contain the new input formula.
   Tip: Do not place an input formula in a column that contains row headers, or in a row that contains column headers.

4. Configure the properties of the input range:
   
   

• Location —  The cell that contains the input formula. The Location cell displays the Data Model Name.
  If you want to move the input formula to a different cell, select the Location cell reference, for example, , and then select or specify an empty cell in the spreadsheet.
  Tip: Do not place an input formula in a column that contains row headers, or in a row that contains column headers.

• Data Model Name — References the data model that contains all collected data for the input range.

• Input Range — The range of cells to be made editable for Contributors. You can type a cell range reference or select the icon and then drag select a range of cells.

• Allow Editing — Controls whether Contributors can edit data in the input cell(s).
  When TRUE, Contributors can edit the input cell(s). When FALSE, they cannot. If this argument is not specified, editing is allowed.
  Tip: If you want to gather data that is the result of a formula, place the formula in an input cell and set Allow Editing to FALSE so Contributors cannot overwrite it. The calculated result of the formula will be included as Contribution input.

• Filters — Defines filters in the data model. Filters further define the context of the data that is represented by the row/column intersection and are often used for bursting input forms. They are generally placed at the top left of an input form as a summary of the "slice" or "context" of the data. For example, if sales data has salesperson names on rows and months on columns, a region filter could be used to burst out one iteration of the input form per sales region. On each region’s input form, the sales region name would appear in the cell where the filter is located.
  To define a filter, specify a field name to be used in the data model and select a single cell to contain the filter. There can be zero or more filters.

• Row Headers — Defines rows in the data model. Specify a field name and select a single column that contains the row headers. You can type a cell reference or select the icon and then select any cell in the column. There can be one or more Row Headers.

• Column Headers — Defines columns in the data model. Specify a field name and select a single row that contains the column headers. You can type a cell reference or select the icon and then select any cell in the row. There can be one or more Column Headers.

Manage Validation Formulas

Validations test cell values and display messages to Contributors and Approvers if test results are FALSE. You can set up any number of validations for a given cell and can apply the validation message to any cell you specify. On ReportWORQ Contribution input forms in Webview, validation messages appear as cell comments and are listed on the Validations tab of the Details pane.

When you select Manage Validation Formulas , the Edit Validation Formulas area appears. You can create new validations or review and modify existing ones.
As you hover over each validation in the list, the cell that is informed by the validation is highlighted in the spreadsheet.

To Create or Modify a Validation:

1. If you are modifying an existing validation, select it in the list, and proceed to Step 4.

2. Select Create a new Validation Formula.
   The Select Data box appears.

3. Select or specify which empty cell in the spreadsheet will contain the new validation formula.
   Tip: Do not place a validation formula in a column that contains row headers, or in a row that contains column headers.

4. Configure the properties of the validation, as follows:
   
   

   

• Location - The cell that contains the validation formula. The Location cell always displays the current test result (TRUE or FALSE).
  If you want to move the validation formula to a different cell, select the Location cell reference, for example, , and then select or specify an empty cell in the spreadsheet.
  Tip: Do not place a validation formula in a column that contains row headers, or in a row that contains column headers.

• Inform Cell - The cell to which the validation message is applied if the validation test result is FALSE.
  You can type a cell reference or select the icon and then select the cell.

• Validation Formula - The formula that tests the cell value. You can use cell references and Excel functions in the formula.
  The validation test runs whenever you or a Contributor change the cell value. If the test result is FALSE, the validation message is activated.

• Validation Message - The message text that appears as a cell comment in the Inform Cell if the test result is FALSE. On ReportWORQ Contribution input forms in Webview, validation messages appear as cell comments and are listed on the Validations tab of the Details pane.

Preview the Input Form

Before you preview the input form, set the spreadsheet’s Print Area to include only the cells you want Contribution users to see.

Manage Settings

When you select Manage Settings , the following settings appear:

• Enable Debug Logging - If selected, ReportWORQ creates a detailed activity log that ReportWORQ Technical Support can use to diagnose problems. Enable this option only if requested to do so by Technical Support.

• Display editor for active cell - If selected, when you hover over a cell that either contains a validation or is part of an input range, the ReportWORQ task pane displays the settings for the validation or input range. The settings are editable.

• Select ranges while in editor - If selected, when you hover over an input range or validation in the list, the corresponding cell(s) are highlighted.

Create and Edit Formulas Manually

Instead of using the ReportWORQ Excel add-in, you can manually create the formulas that define input ranges and data validations. Some formulas must be entered manually, and cannot be created by using the ReportWORQ addin.

ReportWORQ Contribution templates use two custom functions:

• Input formulas use the RW.INPUT function.

• Validation formulas use the RW.CHECK function.

Tip: Do not place input formulas or validation formulas in rows that have column headers or in columns that have row headers.

The following tables provide information about using RW.INPUT and RW.CHECK in formulas.

RW.INPUT Function

RW.CHECK FUNCTION