ReportWORQ v5

Contribution Templates

06 Sep 2024
10 Minutes to read

Print
Dark
Light

Contribution Templates

Updated on 06 Sep 2024
10 Minutes to read

Print
Dark
Light

Article summary

Did you find this summary helpful?

Thank you for your feedback

Creating Contribution Templates

Contribution templates are data sources 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.

This article describes how to use the ReportWORQ add-in for Excel to simplify the creation of 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.

Note: Internet access is required when installing or using the ReportWORQ add-in.

Accessing the ReportWORQ Excel Add-in

You must open the ReportWORQ Excel add-in each time you view or edit a Contribution template. Otherwise, you cannot view or use any of the ReportWORQ features.

Microsoft Excel does not include the ReportWORQ add-in by default. It must be installed on your computer. You can install it manually or be prompted to install it when you open a Contribution template file. After the add-in has been installed, the Excel ribbon includes the ReportWORQ tab.

Note: For users of Excel 2016, and for Office 365 users who do not have permissions to install Excel add-ins, the add-in must be deployed by a Microsoft Administrator. For more information, see the Technical Article, Deploying the ReportWORQ Contribution Add-in.

To install the ReportWORQ Excel add-in manually:

On the Home tab, select Add-ins.
A list of add-ins appears.
In the Search box, type ReportWORQ.
The ReportWORQ add-in appears on the list.
Select the ReportWORQ add-in, and then select Add.
When prompted about License terms and Privacy policy, select Continue.
The New Office Add-in task pane appears.
Select Allow and Continue.
The add-in is installed, and the ReportWORQ task pane appears.

Tip: Icons along the top of the task pane correspond to menu items. To return to the menu view of the task pane, select the ReportWORQ icon .

To install the ReportWORQ Excel add-in while opening a Contribution template:

Open the template in Excel.
The New Office Add-in box appears:
Select Accept and Continue.
The add-in is installed, and the ReportWORQ task pane appears.

Tip: Icons along the top of the task pane correspond to menu items. To return to the menu view of the task pane, click the ReportWORQ icon .

To open the ReportWORQ Excel add-in:

Select the ReportWORQ tab, and then select the ReportWORQ command button.

The ReportWORQ task pane appears.

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:

If you are modifying an existing input range, select it in 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, as follows:

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

If you are modifying an existing validation, select it in 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.
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).
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 range of cells name - The Data Model Name [fieldnames] - The set of fieldnames define Column Headers, Row Headers, and Filters, in that order. 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","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 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.

Was this article helpful?

What's Next

Data Collection

Table of contents

Creating Contribution Templates