Data Collection

Overview

ReportWORQ supports distributing Input Forms for data contribution. Recipients receive Microsoft Excel files with special formulas designed to collect contributions that are uploaded later into an IBM Planning Analytics, SQL Table, Anaplan system, or Workday Adaptive Planning system. IBM Planning Analytics supports write-back with the native DBS formula. SQL Data Collection is executed using RWSQLUPDATE or RWSQLUPSERT formulas. Workday Adaptive Analytics Data Collection is supported by the AIINPUT or AIMODELEDINPUT formulas. Excel files are created with these formulas and are then distributed to users via ReportWORQ. For IBM Planning Analytics users it's often desired to use the option to Remove Provider Formulas (except Data Collection) option in the Job Distribution Output Options screen. Generally, the rows/columns containing these formulas are hidden in the input form since they will display as #NAME to the end user. These formulas have meaning to ReportWORQ but will not be useful in Excel directly. Recipients who have entered data in an input form, return the Excel file to ReportWORQ by either uploading the file to the Input folder that ReportWORQ monitors or if enabled, emailing the file to an email inbox that ReportWORQ monitors using POP3.

Email Inbox Monitoring

ReportWORQ can monitor an email inbox using POP3 and process incoming messages as input forms. Configuration for email inbox monitoring is provided in the ReportWORQ Client on the Integrations tab in the Data Collection connection under Automation. When configured, ReportWORQ will periodically check the email inbox for new email messages. If the message contains an excel file as an attachment, the file will be automatically downloaded and saved to the Input folder that ReportWORQ Server monitors. ReportWORQ maintains a folder in its internal database to track processed messages. It is recommended that retention rules be applied to the inbox to archive or delete messages after some time.  

Processing Input Forms

ReportWORQ has the option to automatically process input forms or queue the forms for processing. When automatic processing is enabled, ReportWORQ will immediately process an input form as soon as it is saved to the Input folder. When disabled, the admin must log into the ReportWORQ server and choose the Bulk Process option to process all of the queued forms. After processing the uploaded file, an audit log is saved to the Input/Archive folder. There is also an API method for IBM Planning Analytics users to run the process input forms from a TI Process.

Processing from an IBM Planning Analytics TI Process

ReportWORQ can generate a sample TI process named 'ReportWORQ - Rest API Process Input' that can be used to trigger Processing of Input Submission. This process can be added to any ReportWORQ-connected IBM Planning Analytics system. The user must populate the 'pApiUrl' parameter with the correct URL for the 'run input' REST API endpoint (by default: http://localhost:8300/server/v0/input/run). Also, the pAccessToken parameter needs to be populated with the Rest API Access Token located on the ReportWORQ API Integration Screen. It is possible to embed this TI logic in another process and, if scheduling is required, create a Chore that calls this TI Process.

More information about this and other Planning Analytics automation features can be found in in ReportWORQ Automation Options with Planning Analytics.

Advanced Data Collection Features

ReportWORQ includes some additional options for specific data sources, like Planning Analytics, that can further enhance the data collection feature's capabilities compared to collecting data outside of ReportWORQ.

Proportional Spreading Data Collection with Planning Analytics

A Planning Analytics connection can be configured to optionally allow the submission of data to a consolidated cell in a Planning Analytics cube using the Proportional Spread method. This means that a DBS or DBSW formula can be used to writeback a value in ReportWORQ that will be allocated based on existing data that is currently stored in that cell. When this option is not enabled (the default), ReportWORQ will reject any attempted updates to consolidated cells, which matches the normal Planning Analytics Excel add-in behavior.