Distribution

Distribution

The distribution step is used to specify options about how the output report is generated, the format(s) it will be converted to and the method(s) to use for distribution. 

Variables

The Variables pane to the right of the Distribution Options section can be used to include variable data in any of the text fields, such as filename, file location, email addresses, email subject, and email body. Drag the desired variable type from this pane to the desired text field. Move the variable into the correct position and then select the variable to edit it. Each variable type provides a different list of options for editing.

Contact

This variable provides access to contact fields and custom fields as defined in the address book. For example, you may drag the contact variable to the Email To field and select the contact's email address as the content. This variable is only valid when using burst sets and a contact was associated with each burst set.

Parameter

This variable provides access to the parameter value(s) that was used to generate this report. For example, if this report was created for the Canada Business Unit, then we can include the title Canada in the filename by dragging this variable to the filename field and selecting Business Unit as the content. If a parameter resulted in a list of values then the content will include a comma-delimited list of those values. This may be useful in the body of the email message, but likely not desired in the filename if it is long.

Timestamp

A timestamp based on the current and local date/time may be generated. A custom timestamp format may be selected or created by using the timestamp parts available in the </> button in this editor. For example, a timestamp in the format MMMM d yyyy will produce September 29 2022 whereas the format MM.dd.yy will produce 09.29.22. Use this tool and the example output in the editor to craft the desired timestamp format. Tip: When distributing reports to the filesystem it's often desired to use timestamp variables in the folder path to organize report outputs by year and month, for example. ReportWORQ will automatically create these folders based on the timestamp values.

Cell Value

This variable permits the user to source variable information from a cell reference or named range from inside the generated report. The worksheet with the desired information does not need to be included in the final output because ReportWORQ will obtain this value after calculation but before removing worksheets. If the worksheet that was referenced by this variable was also used with a parameter that caused the worksheet to be replicated multiple times, then this variable will hold the comma-delimited list of values from this cell location from each of the generated worksheets. This variable allows the user to generate, calculate, or source data from the Microsoft Excel file to be used as part of the distribution step. One example may be to include the title NEEDS REVIEW in the filename if the data in the report exceeded a threshold defined by an IF statement. Another example would be to dynamically load the email address from the report, based on a formula such as a VLOOKUP or element attribute formula for a specific data provider.

Image

This variable may be used to insert a copy of a named image or chart in the body of an email message. The cell reference option in this editor permits the user to select the name of the image or chart from a cell location. This option supports the use case where there are multiple candidate charts for the email message but based on a formula the most appropriate chart will be chosen.

Screenshot

This variable allows for a range of cells to be screenshot and included in the body of the email message. However the worksheet can be excluded form the output by unchecking it in the reports tab.

Display Mode / Formatting Mode

ReportWORQ variables provide an easy-to-use pill that can be moved and edited through simple mouse gestures. However, it can be difficult to format HTML messages using these pills since they are already formatted for user interaction. The Display Mode toggle button will temporarily replace the editor pills with a plain text code that can be selected as text and formatted in the HTML editor. It is important not to edit the code within the << & >> signs to avoid breaking the variable. Any formatting should be applied to the whole of the pill text. After formatting the text turn off Formatting Mode and preview the formatted work on the Test step.

Dynamic Messaging

ReportWORQ 5 uses an HTML editor in the distribution screen for Email, Slack and Teams messages. This WYSIWYG editor allows a user to create and format HTML elements. There are also ReportWORQ variables which can be dragged and dropped into the HTML to make the message dynamic.

This feature is desirable when, for example, a user wants to post a document to a SharePoint site and then send an email with a link to that document, dynamically replicated for multiple recipients.

ReportWORQ supports the use of Markdown syntax, for hyperlinks, in the HTML editor. The syntax is:

[Insert Link Text Here](Insert Link URL Here)

• The link text (what the user sees) is entered in the square brackets.

• The link URL is entered in the parenthesis.

• The (Link URL) must be entered after the [Link Text] .

This text based syntax allows for the use of ReportWORQ variables in the link text and URL:

and displays as:

General Options

File Name:

A filename must be specified for the report to be created. This filename may include variables such as timestamps, parameter values, or cell data from the generated report.

Output Format:

Many or no output formats may be chosen when generating the output file. If no output formats are chosen then the distribution will not contain any files. This is useful when developing an HTML newsletter where all the content is placed in the body of an email. Another example is an Exception report where a message or warning is sent if the report triggers an exception, but the attached report is not required. For example, sending a Slack Message if the trial balance doesn't tie out or data did not load properly from a nightly process. 

Supported formats include:

Excel

This format will produce a Microsoft Excel .XLSX file. When this format is selected the Output Options section will allow the user to choose to keep or remove formulas from the output file. There are also options to remove provider formulas, and remove provider formulas but retain input formulas. See the Data Collection article for more details and uses cases about this option. If any PDF files were used as source files in this job, they will be excluded from the output Microsoft Excel File. ReportWORQ does not convert a PDF file into a Microsoft Excel file. When exporting reports to Google Drive, ReportWORQ automatically converts the format to Google Sheets.

While ReportWORQ does not execute VBA macros, it does have the ability to retain the VBA project and save a file in a Macro Enabled *.XLSM format. There are a few rules that apply to achieve this behavior. First, the source report document must be a .XLSM file. Second, the Job may only contain this single report, ReportWORQ will not merge VBA projects from different files. Finally, the file name field must include the .xlsm extension as part of the file name. Generally, the filename field in the Job excludes a file extension and ReportWORQ will automatically append the appropriate extension, however, ReportWORQ will not automatically append a .xlsm extension.

PDF

This format will produce an Adobe Acrobat PDF file. When this format is selected the Output Options section will allow the user to include a table of contents page at the beginning of the document, and optionally include page numbers in the footer of the file. There is also a low-quality option for PDF output if a smaller file size is desirable.  If a single Microsoft Excel file is used in the source report then the user can leverage the Microsoft Excel footer option for page numbering, however, if multiple source reports are to be combined into a single document then this option will provide consecutive page numbering from one report to the next. Also when this format is selected ReportWORQ will use the sheet name and report name to automatically create a PDF Bookmark tree. The PDF Parent Bookmark option on the Reports step can be used to override the default report name behavior.

CSV

This format will produce a Tab or Comma delimited file from only the first worksheet of the generated report file. The delimiter may be chosen in the Output Options section.

PowerPoint

This option will produce a copy of a specified Microsoft PowerPoint file with certain data fields updated. The template field specifies the Microsoft PowerPoint file to use for generating the PowerPoint output. A copy of this file will be created for a generation.

Destinations

This field provides the list of locations where a report can be distributed. Only configured and enabled distribution methods will appear in this area. Multiple distribution methods can be used in the same job.  For example, it may be desired to email reports to users but also save them in a network folder for archival purposes. Each selected distribution type will display a section on the screen to provide details for sending the reports. Some distribution types, such as Email, Slack, and Teams also have a messaging component where additional information can be provided to the user. ReportWORQ variables (described above) can be used in any of the distribution fields to personalize or provide data-driven values to these fields. For example, the email address may be sourced from a Contact variable or a Cell Value variable which is read from the resulting report. One use case may be to use the Cell Value variable to conditionally CC a reviewer on an email if the report contains data points beyond a certain threshold.

Supported distribution methods include:

Email

Email distribution fields including TO, CC, BCC, From, Subject & Body may contain static text values and/or variables. The priority of an email can be set to Low, Normal or High.

File

A UNC path to a local or network file folder may be supplied. The account that is running the ReportWORQ Bootstrapper Windows Service will be required to have access to write to this directory. Variables such as parameter values and timestamps can be used to generate the file path dynamically.

Google Drive

Reports can be distributed to a user's google drive. Supply the email address of the recipient's Google Drive and optionally a forward-slash delimited path to provide the folder to where the report should be uploaded.

SharePoint

When configuring a SharePoint distribution target, the full path to the specific SharePoint Site folder to which you would like to distribute reports must be entered on the Distribution tab of the Job Editor.  The SharePoint URL on the job and contact screens needs to be in the following form and is case-sensitive:   https://myofficeserver.sharepoint.com/sites/mysite/myfolder1/myfolder2.

• Folders that do not exist in SharePoint will automatically be created.

  • The components of the URL need to include the following...

  •   https://myofficeserver.sharepoint.com: The base URL of the SharePoint instance.

    •  /sites/mysite: The specific SharePoint site where the distributed documents should be stored.

    • /myfolder1/myfolder2: The folder hierarchy under the site where the file should be stored.

    • The URL is case-sensitive.

  •  Note: The URL visible in a browser for any SharePoint folder will not appear in the same format listed above and cannot be used for the distribution field.

Slack 

Reports can be distributed directly to Slack channels and users.  

Teams

Reports can be distributed directly to Microsoft Teams users. To send reports to a Teams group, use the email distribution option and associated email address as documented here: https://support.microsoft.com/en-us/office/send-an-email-to-a-channel-in-teams-d91db004-d9d7-4a47-82e6-fb1b16dfd51e

Output Options

The output options section provides additional settings based on specific output formats that are selected. The format-specific options are described in the Output Format section above. The Enable Manual Collation option is used in conjunction with the Output Order report option described in the reports section. When enabled, ReportWORQ will use calculated numeric values from the Output Order option to determine a custom sort order for the output report.

Distribution Options

Combine email messages when possible

ReportWORQ supports combining generated reports from multiple jobs to be distributed in a single email message. By default, ReportWORQ will send files to recipients as soon as they are generated, which may result in multiple email messages to the same person. To enable combining email messages across job steps you must select the option Combine email messages when possible on each job that should permit this behavior. When this option is enabled ReportWORQ will defer sending email messages until all of the files have been produced. To reduce the potential memory footprint, ReportWORQ will save the files to a temporary folder in the file system while files are queued. Once all of the files have been produced any email messages where the TO, CC, BCC & FROM are identical will be reduced to a single email message with multiple files. Note that the Subject and Body will be used from the first job in the list of job steps. Temporary files will be removed from the file system when email messages are sent.

Zip Files

This option is used to compress and archive files before sending.  This is useful if many files are being sent or if the files are large and compression is preferred.

Exclusion List

This option provides a semi-colon delimited list of filters to exclude files from the zip process, which is useful to zip archive only some of the files being sent to the recipient. The filter is a simple text containing a check.  The item ".pdf" will exclude any files that contain ".pdf" in their name as well as the data.xlsx file from being zipped, in the screenshot example below.

Include Additional Files

This option provides a list of external files that should be included as part of the distribution process.  This is useful if recipients should receive other files alongside the output from ReportWORQ.

Workday Adaptive Options

When a Workday Adaptive Planning connection is present this option becomes visible. This option provides the ability to adjust the Relative Start Date that is used in Workday Adaptive OfficeConnect report specifications. The date can be adjusted to a specific absolute date, or a relative date based on today, for example, beginning of the week or the beginning of last month.