Emailing reports using a single analysis

This example uses an analysis containing sales data for an organization, with different data for multiple users and groups. Based on the data, PDF reports will be created for various sales regions and will be sent to the different sales managers.

The job contains the tasks:

Open Analysis from Library(/Distributions/Sales Figures Template)

ForEach (columns: FilterValue;e-mail)

Execute Script (uses row value from {forEachProperty:FilterValue})

Export Report to PDF (Report SalesReport to C:\Temp\PdfReport_{jobid}.PDF)

Send Email (Sends email to {forEachProperty:e-mail}, attach C:\Temp\PdfReport_{jobid}.PDF)

ForEach End

Open Analysis from Library

This task opens the analysis from the library to access the sales report containing sales data for all sales managers, across different regions.

ForEach

The ForEach task holds the table to iterate over. The table consists of two columns: one for the filter value used to specify the sales manager's region, FilterValue, and another for the sales manager's email address, e-mail. The embedded table looks like this:

FilterValue;e-mail

CENTRAL;sales.east@domain.com

EAST;sales.east@domain.com

WEST;sales.west@domain.com

ForEach End indicates the end of the ForEach loop.

Execute Script

The IronPython script uses the CheckBox filter Region to show only the data related to a specific sales region in the analysis. It fetches different region values using Tools > Insert Field functionality ({forEachProperty:FilterValue}). The scripts have a predefined object called AutomationContext, which helps access the entire Spotfire API using AutomationContext.Application.

from Spotfire.Dxp.Application.Filters import CheckBoxFilter

# Constants for the name of the data table, and filter
dataTableName = "SuperStore Sales"
columnName = "Region"

# Get the filter value to set from the ForEach table.
filterValueToSet = "{forEachProperty:FilterValue}"

# Get main objects from execution context
application = AutomationContext.Application
document = application.Document

# Get the filter to filter the regions
dataTable = document.Data.Tables[dataTableName]
column = dataTable.Columns.Item[columnName]
filter=document.FilteringSchemes[0].Item[dataTable].Item[column].As[CheckBoxFilter]()
if filter is None:
 raise LookupError("Checkbox filter not found for column '{0}' in data table '{1}' in analysis '{2}'.".format(columnName, dataTableName, application.DocumentMetadata.LoadedFromLibraryPath))

# The filter "Check" will not raise an error if it is not found, so make sure the value exists
if not filter.Values.Contains(filterValueToSet):
 raise LookupError("Filter value '{0}' not found in column '{1}' in analysis '{2}'.".format(filterValueToSet, columnName, application.DocumentMetadata.LoadedFromLibraryPath))

# Check only the region from the current row in the ForEach table
filter.UncheckAll()
filter.Check(filterValueToSet)

Export Report to PDF

This task exports the predefined PDF report, SalesReport, to a temporary PDF file located at C:\Temp\PdfReport_{jobid}.PDF. The task uses the field {jobid} to make sure the filename is unique.

Note: Both the Analyst client and the machine running the Automation Services service must have the folder C:\Temp to run the job, and the accounts must be allowed to write files there.

Send Email

This task retrieves the exported PDF report using Attached files at the bottom. To add the file, select Add and copy the path from the Export Report to PDF task: C:\Temp\PdfReport_{jobid}.PDF. Check the box Delete the attachment after sending the mail to remove the temporary file. In the field, To, use Tools > Insert field to insert the email value {forEachProperty:Email}.