Creating Jobs

Overview

A ReportWORQ Job is developed using one or more source reports that will be configured with data filters, refreshed with the latest data, combined into a reporting packet, converted to a target format, and then distributed through a supported means. Source reports are generally Microsoft Excel files but may also include static PDF files (when exporting to a PDF format). Microsoft Excel files may contain Excel formulas, proprietary ReportWORQ formulas, supported data source formulas, or data source-specific report specifications such as IBM PAfE, Anaplan, Workday Adaptive Office Connect, and Oracle SmartView. A ReportWORQ job is then executed ad-hoc or scheduled so that reporting packets can be created and distributed via File, SharePoint, Teams, Slack, Google Drive, or emailed to specific users.

Navigation

ReportWORQ Jobs may be stored in a tree structure using folders. This structure is useful for organizing reporting jobs and for executing multiple jobs concurrently. Jobs can be run by right-clicking the job or folder and selecting "Run". The navigation tree supports drag & drop to reorganize context menus, and common keyboard shortcuts such as Enter to open a job, Delete, Ctrl+X, Ctrl+C, and Ctrl+V (cut, copy, and paste). Double-clicking on a job will open it in the editor. Folders and jobs can be created using context menu actions or the buttons below the navigation tree. Note that multiple jobs can be open simultaneously with each job appearing on a separate tab at the top of the content pane. The navigation pane can be collapsed to provide more room for editing jobs by using the splitter bar or the left chevron button to the top right of the navigation tree.

In the Report Selection dialog users can:

• Search

• Create new folders

• Upload files

• Navigate the folder structure

• Refresh the view

• Rename or delete files

Editing Jobs

After navigating to the Edit Jobs screen, an existing job can be edited by either double-clicking a job or selecting 'Open' on the context menu. Jobs that are open for editing will appear in the tab view at the top of the page. Any modifications to a job will place the job in an edited state, which is indicated by an asterisk beside the tab name, and the Save Changes option becomes visible. The user must select the Save Changes button at the bottom right of the content pane to make changes permanent. Changes can be discarded by clicking the Discard Changes button via the context menu on the tab. The context menu on the tab provides actions to Save, Discard, Rename, and Close open jobs.

ReportWORQ supports concurrent users. If a job is open in the editor and another user is modifying or has saved changes to the same job, a warning triangle will indicate that the current job editor is displaying stale content. The editor can be closed and re-opened to view the latest copy, or saved to overwrite the other user's work.

Configuring Reports

After a job has been opened, reports can be added by selecting the Reports tab and then clicking the '+' button at the bottom left of the content pane. Select a datasource and navigate to the report you want to add.
Note: For IBM Planning Analytics Report Providers, the navigation tree for selecting a report shows the Database name as configured in Planning Analytics, rather than the Datasource name specified in ReportWORQ.

ReportWORQ will display the worksheets from each added source report. Source reports and their worksheets can be reordered to achieve the desired output. Note that worksheets within one report do not need to be ordered consecutively, rather they can be placed at any location before or after other reports. Worksheets to be excluded from the output file are unchecked. Excluded worksheets will remain in the file during calculation and will be removed before distribution allowing the user to utilize excluded worksheets for data retrieval, commentary, and imagery in the distribution step. PDF reports that are added as source reports are treated as static files which can only be reordered in the reporting packet.

Reports are selected using one of the supported and configured source report providers. Details >adding Source Report Providers can be found in the admin guide.

Each worksheet in the Microsoft Excel file may be configured with options to alter the behavior of the creation, inclusion, and placement of the worksheet in the final output.

Sheet Name

Worksheets can be dynamically renamed with literal text including parameter values or from a calculated cell on the worksheet. This is necessary when parameters are used to replicate worksheets. Literal text can be used to name the worksheet using text combined with the parameter values for the worksheet. A cell reference may be used to rename the worksheet based on an Excel cell formula post-calculation. For example, suppose a parameter for Region is added to this worksheet and this parameter contains values for West, Central, and East. When ReportWORQ creates three copies of this worksheet this option can be used to name those worksheets by the same value as the parameter. If it is preferred to calculate the worksheet name with an Excel formula or load the sheet name from a data source, then use the Cell Reference option. In this scenario, ReportWORQ will name the worksheet based on the value of the specified Cell Range after the calculation has occurred.

PDF Parent Bookmark

A parent bookmark name for a worksheet can be based on literal text including parameter values, or from a calculated cell on this worksheet. When a report is exported to PDF, ReportWORQ will automatically create a 3-tier bookmark tree. The root node is specified in Distribution Options, the second tier, by default, is the name of the Report that the worksheet came from, and the third tier is the name of the worksheet. This option allows you to specify a custom value for the second tier. Siblings with the same parent will be grouped in the PDF bookmark tree.

Sheet Tab Color

The worksheet tab color can be set dynamically based on a calculated cell in the worksheet. The worksheet tab color can be specified based on a cell formula. The formula must return a named color (e.g., Red, Blue) or hex code beginning with a pound sign (e.g., #333333). The supported color names are listed in Microsoft documentation.

Sheet Suppression

Automation can be used to dynamically delete worksheets if they contain a supported planning report or are based on a calculated cell in the worksheet. Worksheets can be removed post-calculation based on a cell formula or automatically if the worksheet contains a provider report which does not return any data. The cell's formula must return TRUE to delete the worksheet or FALSE to retain it. Typically, this formula contains the SUMSQ of a range to determine if there is data on the worksheet. It can however be any condition. For example, the worksheet can be removed if the data does not exceed a certain threshold.

Output Order

Worksheets can be ordered manually using a specified integer value or from a cell on this worksheet. The Manual Output Order feature is an advanced feature designed to provide full control over the order of worksheets. Most ordering requirements are met using the group & collation feature in the Parameters sidebar. This option can be used to provide custom sort sequences, determined using formulas and lookups in Excel.

Exception Check

Exception check will dynamically determine whether to send reports based on calculated cell from a worksheet. If one or more Exception Cells are specified, then at least one must evaluate to a TRUE for the report to be sent. The exception check feature can be used on multiple worksheets. If at least one of the worksheets evaluates to a true, then the report is sent. If they all evaluate to a false, then the report is not sent. An example of why a user may want to do this is that they only want to send reports to managers that are over budget.