The key to creating an effective personalized report distribution begins with ReportWORQ Variables. In this post, I’ll cover several examples of how you can use variables in your ReportWORQ jobs.
A ReportWORQ variable contains information about each specific report output that the job creates. For example, if a job bursts reports on a parameter called Business Unit, then you might want to use the Parameter variable (Business Unit) in the subject line of the email to indicate which Business Unit’s data is in each email message. One of the most powerful and flexible options is the Cell Value variable. What’s interesting about the Cell Value variable is that it allows you to source information from an Excel cell or range after the report is calculated, even if that worksheet is not included in the final output. Since variables are not limited to commentary and can be used in any textual field in the Job Distribution screen, you can use variables to drive conditional or dynamic distribution options. We often say, If you can get the information into a cell inside Excel, then you can use it in ReportWORQ. This opens a whole host of use cases for conditional and rich distribution.
Here are a few examples:
Organizing reports into a dynamic folder structure
The Timestamp variable may be used in the output folder path so that ReportWORQ creates and stores reports in a folder by year and month name. This is a great way to organize files by date but could also be stored by parameter value or something else.
- \\Company Reports[Year][Month][Geography][Geography]P&L.pdf
becomes - \\Company Reports\2023\July\Northeast Region\Northeast P&L.pdf
Conditional distribution based on data metrics.
The Cell Value variable can be used in the CC line of an email message to conditionally CC a manager if data is not within a certain threshold (see #2 in the screenshot below). This can be done with a simple Excel IF formula to provide an email address if a certain condition is not met or blank if it is not. Another variation of this use case would be to store report outputs in a “final” folder if the data satisfies a condition or in a “review” folder if it does not.
-
Cell RNGEMAIL: =IF(RNGBUDGET > 1, “manager@company.com”, “”)
– Include a manager’s email on CC if the budget exceeds 100% -
Cell RNGFOLDER: =IF(RNGBUDGET > 1, “Needs Review”, “Archive”)
– Place this output in a Needs Review folder if the budget exceeds 100%
Sourcing distribution information from your planning data
Cell Value variables combined with provider formulas in Excel can be used to source bursting information directly from the planning system. For example, the Planning Analytics DBRA formula, which loads attribute information for a dimension element, may be used to source the email address associated with the report’s data filter into a cell in Excel, which is then piped into the ReportWORQ Email To field using the Cell Value variable.
- Cell RNGEMAIL: =DBRA(“server:cube”, “dimension”, RNGBUSINESSUNIT, “EmailAddress”)
– Dynamically load this report’s email address from an attribute on the business unit dimension from the planning system. Now we can manage burst information in the metadata!
Dynamic commentary with conditional formatting
The Cell Value variable is excellent for commentary in the email body and can support conditional formatting. A common example would be to create an Excel formula that summarizes some data points as a sentence and then place that information in the body of the email message (see #3 in the screenshot below). In this case, we may want to enable the Include Cell Formatting option on the Cell Value variable which copies the text color and bold formatting options from Excel into the HTML email message. When used in conjunction with Conditional Formatting we can include in our message a green sentence that data is within budget or a bold red sentence to call out that data is over budget.
- Formula
=“Revenue of " & TEXT(D16,”#,.##") & "M " & IF(F16>0,“up”, “down”) & " by " & TEXT(F16,“0.0%”) & “.” - Produces
Revenue of 5.47M up by 5.5%. - TIP: Excel’s TEXT() formula is very useful for dynamic commentary.
Embed an entire HTML Dashboard in an email with no attachment.
Recently we added an additional Cell Value option called Export as HTML Table. When this option is used, ReportWORQ will export a range of Excel data as HTML and embed it directly in the body of the email message. While not all Excel formatting translates well to HTML, this option is very useful to embed part or the entire report as an email dashboard. Building on this example, you may also choose to disable the Include Attachments option so that the email message contains the report in the body, with no attachments (see marker #1 in the screenshot below). See the screenshot of the email message at the top of this post.
Additional documentation on ReportWORQ Variables can be found here:
https://docs.reportworq.com/docs/configuring-distribution#variables
