Contribution Templates
  • 18 Feb 2025
  • 11 Minutes to read
  • Dark
    Light

Contribution Templates

  • Dark
    Light

Article summary

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 input 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.

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:

IMPORTANT: 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.

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. 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

Purpose

To define 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.

  • 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. If this argument is not specified, it appears as empty quotes ("") in the formula, and 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 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.

  • 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.CHECK Function

Purpose

To define 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.PICKLIST Function

Purpose

Adds a dropdown to the target cell

Format/Syntax

RW.PICKLIST(ListCell,ListElements,IncludesHeader)

Arguments

  • ListCell - cell location to place the dropdown in

  • ListElements - column reference that includes the list of values to populate the dropdown with

  • IncludesHeader - TRUE/FALSE value to indicate if the first element in the above list is a header and should be excluded from dropdown options

Example

=RW.PICKLIST

RW.CHECKBOX Function

Purpose

Adds a checkbox to the target cell

Format/Syntax

RW.CHECKBOX(TargetCell,LabelName)

Arguments

  • TargetCell - cell location to place the dropdown in

  • LabelName - text label to add to the checkbox

Example

=RW.CHECKBOX

RW.LIST Function

Purpose

Adds a dropdown to the same cell that the formula is placed in

Format/Syntax

RW.CHECKBOX(ValueCell,ListElements,IncludesHeader)

Arguments

  • ValueCell - after updating the dropdown, set the value in this cell location to the selected dropdown value

  • ListElements - column reference that includes the list of values to populate the dropdown with

  • IncludesHeader - TRUE/FALSE value to indicate if the first element in the above list is a header and should be excluded from dropdown options

Example

=RW.LIST


Was this article helpful?