Create and Send a 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 containing information about today's female gold medalist. The email body will contain information about the athlete and a description of the sport, event, and location. This data will dynamically change depending on today's date.
In addition, we attach an Excel file to the email with all the female gold medalists whose birthday is equal to the current date.
💡 To view each node's configuration, select the node and see the configuration pane on the right side of the workflow editor.
We will see how to do it node by node:
Excel Reader node:
Since the folder with the data is already included when you download the workflow, in the "File and Sheet" tab, we choose to "Read from" the "Current workflow data area" and select the dataset.
In the "Data Area" tab, we select to read the "Whole sheet" since the spreadsheet already has a standard format (i.e., non-empty rows or columns, etc.).
Date&Time Range Creator node:
To get today's date, we use the Date&Time Range Creator node. We use the execution time as starting and ending point and set Number of rows = 1.
We name the new column "current_date".
Extract month and day of month metanodes:
Both metanodes are identical. Double click the metanode to open it. Inside, you will find two nodes:
Date&Time Part Extractor node to extract the month and day from the date (athletes' birth dates and today's date).
Column Combiner node to create a single cell containing month and day, naming it month_day.
Reference Row Filter node:
It keeps only the rows where the athlete's birthday is on the same day as today (comparing month and day).
Cell Extractor node:
Extract the current date from the table and convert it into a flow variable. We will use it in further steps to calculate a new dataset feature.
Date&Time Difference node:
We add a new feature to the dataset: the "years_from_birth" column. This column counts the years between the athlete's birth and today.
In the configuration dialogue, as the first column, we select the column "born". As second column we choose a "Fixed date&time", however, we overwrite the value with today's date (indicated by our previously extracted cell value). As granularity we select "Years".
Data cleaning metanode:
Double click the metanode to open it. Inside, you will find four nodes:
Column Filter node to keep only the relevant columns.
Number to String node to convert "years_from_birth" from number to string.
Column Resorter node to change the column order.
Sorter node to sort the table by years_from_birth (descending) and athlete (ascending).
Table Row to Variable node:
Convert the first row of each column into a variable (i.e., the values of the first athlete). We will use them in the Send Email node as a placeholder in the email body.
Excel Writer node:
We write our data to a new sheet in the female_gold_medalist_of_the_day.xlsx file. As sheet name we use today's date (i.e., the previously extracted variable "extracted_cell" from the Cell Extractor node). So, every day we run the workflow, we append a new sheet with the execution date.
We also want to convert the file path of the Excel file into a flow variable which we call "file_path". We will use this variable in the Send Email node to attach the Excel file to the email.
Path to String (Variable) node:
The extracted file path variable is a path type variable. However, we need it to be of type string to use it in the Send Email node, which is why we are converting the "file_path" variable from path to string ("file_path_string").
Send Email node:
In the "Mail" tab, you can insert your email to try out and receive the email. In the email body (text field below), we write the body content, including some placeholders (flow variables) to render the email dynamic.
In the "Mail Host (SMTP)" tab, 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
To attach the file, we previously exported with the Excel Writer node, we navigate to the "Flow Variables" tab and use the created "file_path_string" variable to define the "attachedURLs" value.