Contribution Templates

Creating Contribution Templates

Contribution templates define the content 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 filters, checkboxes, picklists, and data validation formulas with associated warning messages.

This article describes how to use the ReportWORQ add-in for Excel to create formulas for Contribution templates. Alternatively, you can create and edit the formulas manually.

The main topics in this article are as follows:

When you are finished configuring your Contribution template, set the spreadsheet’s Print Area to include only the cells you want Contribution users to see. Save the Contribution template file in a location that is designated as a Report Provider.

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 information, 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 by selecting the ReportWORQ tab and then selecting the ReportWORQ open button.

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.

After you log in, the ReportWORQ add-in pane appears:

View and Create Input Ranges

You can create new input formulas or review and modify existing ones. When you select View and Create Input Ranges, the Edit Input Formulas area appears.

To create or modify an input range:

If you are modifying an existing input formula, select it from the list, and proceed to Step 4.
Select Create a new Input Range.
The Select Data box appears.
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.
Configure the properties of the input range:

Location — The cell that contains the input formula. The Location cell displays the Data Model Name.
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 to select. If you want to define an input range that includes multiple blocks of cells, enter the RW.INPUT formula manually, setting the inputRange argument to specify multiple cell ranges, for example, (D4:D6,D9:D11,F4:G6,F9:G11). For more information, see RW.INPUT Function.
Note: The input range cannot include Excel control cells, such as ones that contain formulas for filters, checkboxes, or picklists.
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. This setting is True by default.
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.

Tip: If you select a box that contains a cell reference, the cells are highlighted in the spreadsheet view.

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 formula:

If you are modifying an existing validation formula, select it from the list, and proceed to Step 4.
Select Create a new Validation Formula.
The Select Data box appears.
Select or specify which empty cell in the spreadsheet will contain the new validation formula. This is the Location.
Tip: Do not place a validation formula in a column that contains row headers, or in a row that contains column headers.
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).
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.
Require Validation for Submission — Select this checkbox to block the submission of data that does not meet validation requirements.

Tip: If you select a box that contains a cell reference, the cells are highlighted in the spreadsheet view.

Manage Filter Formulas

The Filter formula defines a row filter that appears on the input form as a dropdown list. Contributors and Approvers can apply these filters to view a subset of rows. Multiple filter criteria can be selected.

To define a filter, you specify the filter Location cell that contains the filter formula, a label for that cell (optional), and the filter range (optional). The filter range contains the filter criteria (selectable values to filter on), and is a contiguous block of cells from a single column. If you do not specify a filter range, the range extends from the filter Location cell downwards until an empty cell is encountered.

When you select Manage Filter Formulas, the Edit Filter Formulas area appears. You can create new filters or review and modify existing ones.

To create or modify a filter formula:

If you are modifying an existing filter formula, select it from the list and proceed to Step 4.
Select Create a new Row Filter.
The Select Data box appears.
Select or specify an empty cell to contain the filter formula. This cell is the filter Location.
Configure the properties of the filter formula, as follows:
- If you want the cell that contains the filter to display a label, type the label text in the Filter Label box.
- To define the filter range, do one of the following:
  - To define the exact filter range: In the Filter Range box, specify a contiguous block of cells from a single column.
    You can type a cell range reference or select the icon and then select the cells.
  - To define the filter range as a block of cells immediately below the filter location: Leave the Filter Range box blank.
    The filter range extends from the filter Location cell downwards until an empty cell is encountered.

Tip: If you select a box that contains a cell reference, the cells are highlighted in the spreadsheet view.

Manage Checkbox Formulas

The Checkbox formula adds a checkbox to the input form. Contribution users can select or clear the checkbox, which is clear by default.

When you select Manage Checkbox Formulas, the Edit Checkbox Formulas area appears. You can define new checkboxes or review and modify existing ones.

To create or modify a checkbox formula:

If you are modifying an existing checkbox formula, select it from the list and proceed to Step 4.
Select Create a new Checkbox Formula.
The Select Data box appears.
Select or specify an empty cell to contain the checkbox formula. This cell is the checkbox Location.
Configure the properties of the checkbox formula, as follows:
- In the Checkbox Value Cell box, specify which cell receives the checkbox value.
  When a Contribution user selects or clears the checkbox, the Checkbox Value Cell receives the checkbox value, which is TRUE if the checkbox is selected and FALSE if it is cleared.
  You can type a cell reference or select the icon and then select the cell.
- If you want a text label to be displayed beside the checkbox, type the label text in the Checkbox Label box.

Tip: If you select a box that contains a cell reference, the cells are highlighted in the spreadsheet view.

Manage Picklist Formulas

The Picklist formula adds a dropdown picklist to the input form. Contribution users can select one value from the picklist.

When you select Manage Picklist Formulas, the Edit Picklist Formulas area appears. You can define new picklists or review and modify existing ones.

To create or modify a picklist formula:

If you are modifying an existing picklist formula, select it from the list and proceed to Step 4.
Select Create a new Picklist Formula.
The Select Data box appears.
Select or specify an empty cell to contain the picklist formula. This cell is the picklist Location.
Configure the properties of the picklist formula, as follows:
- In the Picklist Value Cell box, specify which cell receives the value selected by the Contribution user.
  You can type a cell reference or select the icon and then select the cell.
- In the Picklist source range box, specify a cell range that contains the values to appear in the picklist. The cell range must be within a single column.
- Select Picklist source contains a header if you do not want to the first value from the Picklist source range to be included in the picklist.

Tip: If you select a box that contains a cell reference, the cells are highlighted in the spreadsheet view.

Preview the Input Form

You can preview the input form and interact with it, to ensure that it appears as you want it to.

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.

Excel Features and Formatting

Contribution support most Excel features. Most Excel formatting options persist when the Contribution template is later used to create input forms. This topic highlights selected Excel features and formatting options, and provides information about how they can affect input forms.

Excel Features:

Arrow keys and scrolling — Contribution input forms support the use of arrow keys (up, down, left, right). Scroll bars appear if the input form is too large to display in the browser window.

Excel Formatting Options:

Filters — If you apply Excel data filters, the input form will include only the rows that are not hidden by the filters.
Alternatively, you can use the ReportWORQ add-in to create filters that Contributors and Approvers can apply.
Freeze Panes — Freeze pane settings in Contribution templates persist in input forms.
Sparklines — Sparkline charts are supported in Contribution input forms. If the data range for a sparkline includes input cells, the sparkline chart updates whenever a Contributor or Approver changes the value of any such cell.

Reminder: When you are finished configuring your Contribution template, set the spreadsheet’s Print Area to include only the cells you want Contribution users to see. Save the Contribution template file in a location that is designated as a Report Provider.

Create and Edit Formulas Manually

As an alternative to using the ReportWORQ Excel add-in, you can use ReportWORQ functions to create formulas for Contribution templates.

Important: Do not place formulas in rows that have column headers or in columns that have row headers.

This topic describes the following ReportWORQ functions for Contribution templates:

RW.CHECK Function — Defines a data validation test and a result message.
RW.CHECKBOX Function — Adds a checkbox, which Contribution users can select or clear.
RW.FILTER Function — Defines a row filter that Contribution users can apply, to view a subset of rows on the input form.
RW.INPUT Function — Defines an input range, where Contribution users can provide data.
RW.LIST Function — Defines a dropdown picklist from which Contribution users can select a value.

RW.CHECK Function

RW.CHECK Function
Purpose	Defines a data validation test and a result message for ReportWORQ Contribution input forms.
Format/Syntax	RW.CHECK(informCell, logic, message, BlockSubmission)
Arguments	informCell - The cell where the message appears on the input form. Logic - The logical test that determines whether the result message is activated. The test is enclosed in brackets and starts with a reference to the cell being tested. The result message is activated if the validation test result is FALSE. message - The message that is activated if the validation test result is FALSE. BlockSubmission - Determines whether invalid data blocks the Contributor from submitting the input form: If set to FALSE and the validation test result is FALSE, a blue warning marker appears in the Inform Cell to indicate to the Contributor that the value should be reviewed. The Contributor can still submit the form. If set to TRUE and the validation test result is FALSE, a red marker appears in the Inform Cell to indicate to the Contributor that the data is not acceptable. The Contributor cannot submit the form until the validation test result becomes to TRUE. If this argument is omitted, it is considered to be set to FALSE.
Example	=RW.CHECK(E13,ABS(F13)<0.25,TEXT(F13,"%#") & " change is large. Please verify input.",TRUE) In this example: The informCell is E13. If the logic test result is FALSE, the validation message will be displayed in cell E13 as a comment. The logic test uses the ABS function to check whether the absolute value of cell F13 is less than 0.25. The message uses the TEXT function to display the contents of cell F13, followed by normal text. The BlockSubmission argument is TRUE, which means the form cannot be submitted if the cell value fails the validation test.

RW.CHECKBOX Function

RW.CHECKBOX Function
Purpose	Adds a checkbox to the target cell. Contribution users can select or clear the checkbox, which is clear by default. When the user selects or clears the checkbox, the cell where the formula is located receives the checkbox value, which is TRUE if the checkbox is selected and FALSE if it is cleared.
Format/Syntax	RW.CHECKBOX(targetCell,labelName)
Arguments	targetCell — The cell that receives the checkbox value (TRUE or FALSE) when a Contribution user selects or clears the checkbox. labelName — Label text to display beside the checkbox. This argument is optional.
Example	=RW.CHECKBOX($L32,"Extra Cheese") In this example: When the Contribution user selects or clears the checkbox, cell $L32 receives the checkbox value (TRUE or FALSE). The text label “Extra Cheese“ is displayed beside the checkbox.

RW.FILTER Function

RW.FILTER Function
Purpose	Defines a row filter that Contribution users can apply, to view a subset of rows on the input form.
Format/Syntax	RW.FILTER(label, range)
Arguments	label — Text to display in the filter cell. This argument is optional. range — A contiguous block of cells from a single column that define the filter criteria and which rows to filter. This argument is optional. If unspecified, the filter range extends from the filter Location cell downwards until an empty cell is encountered.
Example	=RW_FILTER("myFilter",D7:D35) In this example: The label text that appears in the cell where the filter is located is “myFilter”. The filter range consists of cells D7:D35.

RW.INPUT Function

RW.INPUT Function
Purpose	Defines an input range for ReportWORQ Contribution input forms.
Format/Syntax	RW.INPUT(inputRange, name, [fieldnames], [fieldnames], [fieldnames], [fieldnames]…
Arguments	inputRange — The cell or range of cells that collect Contribution data. Note: The input range cannot include cells that contain formulas for filters, checkboxes, or picklists. name — The Data Model Name. [fieldnames] — The first [fieldnames] argument controls whether Contributors can edit data in the input cell(s). When TRUE, Contributors can edit the input cell(s). When FALSE, they cannot. Tip: If you want to gather data that is the result of a formula, place the formula in an input cell and set this argument to FALSE so Contributors cannot overwrite it. The calculated result of the formula will be included as Contribution input. Subsequent [fieldnames] arguments define Column Headers, Row Headers, and Filters, in that order. There may be more than one of each. Filters are optional. The fieldnames appear in pairs; each text fieldname is followed by a cell or cell range reference: If the cell range is a row, the preceding fieldname’s text defines a Column Header name in the data model. The referenced column contains row names. If the cell range is a column, the preceding fieldname’s text defines a Row Header name in the data model. The referenced row contains header names. If the cell reference is a single cell, the preceding filename’s text defines a filter name in the data model. The filter is based on the referenced cell.
Example	=RW.INPUT('P&L'!E13:E23,"Expenses",FALSE,"Period",'P&L'!11:11,"Account",'P&L'!C:C,"State",'P&L'!D2,"Region",'P&L'!D3) In this example: The P&L worksheet is the template for the input form. All cells referenced in this input formula are on the P&L worksheet. The inputRange consists of cells E13:E23. Tip: You can also define an input range consisting of multiple blocks of cells, for example, (D4:D6,D9:D11,F4:G6,F9:G11). The Data Model Name is Expenses. The argument that controls whether the input cell(s) can be edited is set to FALSE, so Contributors cannot edit the input cell(s). Tip: If this argument is not specified, it appears as empty quotes ("") in the formula, and editing is allowed. The Column Header fieldname is Period, and the header names are in row 11. The Row Header fieldname is Account, and the headers are in column C. There is a filter named State, which is based on cell D2. In Contribution Create Forms, a corresponding parameter can be defined for bursting the input form based on this filter. There is a filter named Region, which is based on cell D3. In Contribution Create Forms, a corresponding parameter can be defined for bursting the input form based on this filter.

RW.LIST Function

RW.LIST Function
Purpose	Defines a dropdown picklist from which Contribution users can select a value. The list appears in the cell where the formula is located.
Format/Syntax	RW.LIST(listCell,listValues,includesHeader)
Arguments	listCell — The cell that receives the value selected by the Contribution user. listValues — Reference to a cell range that contains the values to appear in the picklist. The cell range must be within a single column. includesHeader — TRUE/FALSE value to indicate whether the first element in the ListValues cell range is a header that should be excluded from the picklist.
Example	=RW.LIST(L9, 'Lookups'!$C:$C, TRUE) In this example: When the Contribution user selects a value from the picklist, that value is stored in cell L9. The picklist contains values from column C of the Lookups worksheet. ('Lookups'!$C:$C). Because includesHeader is set to TRUE, the value retrieved from 'Lookups'!$C1, being the first value retrieved from 'Lookups'!$C:$C, is excluded from the picklist.