Create and Send Report

Create And Send Report

In this workflow, we see how to build and send a report via email using KNIME Analytics Platform.

The idea is to automatically send an email with the female gold medallist of the day based on the athlete's birth date. The email body will contain bio-data and a description of the sport, event and location. This data will dynamically change for each athlete.

Also, we are attaching to the email an excel file with all the female gold medallists whose birth date is equal to the current date.

We will see how to do it node by node:

To view the configuration for each node, either double-click or right-click > "Configuration" to open the configuration dialogue.

To open the metanodes: ctrl + double-click.

Excel Reader node:

1 - In the "File and Sheet" tab, the file name and the sheet to read from are already set up (the folder with the data is already included when you download the workflow).

2 - Within the "Data Area" tab, we have selected read the "Whole sheet" since the spreadsheet already has a standard format (non-empty rows or columns, etc.).

Create Date&Time node:

1 - Create a table with one column named "current_date" and one row containing the current date (local date type).

2 - In the configuration window, we select in the "Number of rows" section "Fixed" and set the value to one. As starting point, we flag the "Use execution date&time", and as the ending point, we select "Interval", setting the interval to one day ("1d").

Cell extractor node:

1 - Extract the current date from the table. We will use it in further steps to calculate a new dataset feature.

Extract Date&Time Fields nodes:

1 - Extract the day of the year (integer type) from the top ("born" column) and low ("current_date" column") branches.

Reference Row filter node:

1 - It keeps ONLY the rows where the extracted day of the year from the athlete's birth equals the current date day of the year.

Date&Time Difference:

1 - We add a new feature to the dataset: the "years_from_birth" column. This column counts the years since the athlete won the gold medal to the current date.

2 - In the configuration dialogue, as the "Base column", we choose the "born" one. Then in the section "Calculate difference to", we choose "fixed date&time", and in the "Flow variables" tab, we use the variable "extracted-cell" from the Cell Extractor node. Finally, as Granularity, we select "Years" and give a new name to the column.

Data Wrangling metanode:

1 - We wrapped some nodes: Column Filter, Number to String, Sorter, and Column Resorter to accomplish some cleaning and transformation operations to our dataset.

Table Row to Variable node:

1 - Extract as variables all the features from the first row of the input table. We will use them in the Send Email node as a placeholder in the email body.

Excel Writer node:

1 - We append a new sheet in the "golden_medallist_of_the_day" file using the variable "extracted_cell" from the Cell extractor node. So every day we run the workflow, we append a new sheet with the execution date.

2 - Another necessary action to accomplish in the configuration dialogue is to set a name, "file_path", to the variable "file_selection > path" in this way, we can extract the file path from the Excel Writer node and use it in the Send Email node.

Send Email node:

1 - In the "Mail" tab within the configuration dialogue, you can insert your email to try out and receive the email. In the email body, we write the body content, including some placeholders to render the email dynamic.

2 - In the "Mail Host (SMTP)" tab in the configuration dialogue, it depends on the Internet Service Provider (ISP) that we want to reach. In this example, we use Gmail. Below are the configurations values:

SMTP Host: smtp.gmail.com
SMTP port: 465

We need to flag "SMTP host needs authentication."

Username: your Gmail email account.

Password: your Gmail password.
If you have 2-step Verification, you need to follow the steps explained here: https://support.google.com/accounts/answer/185833?visit_id=638125333963998850-1780931931&p=InvalidSecondFactor&rd=1

Connection Security: SSL

3 - Lastly, in the "Flow Variables" tab, we set as "attachedURLs" the "file_path_location" that we have extracted from the Excel Writer node.
We aim to attach the created file to the email we are sending.