Dynamic Recipients

Dynamic Recipients

Dynamic recipients allows you to email report results to a dynamic list of people. This may be useful where the recipients may change from day to day or week to week e.g. sending an email to staff who are low on their targets. It may also be useful to send filtered report results e.g. send to managers only the sales for their branch.

Using the dynamic recipients feature will involve the following process

• Create the main report with the data to be sent to the desired recipients
• Create a report of type Dynamic Job Recipients that will contain details of the recipients, including their email addresses
• Schedule a job for the main report and select the dynamic recipients report you created in the Dynamic Recipients field

There are several ways to use dynamic recipients.

Dynamic Recipients only

If you want all the recipients to get the same data and the same email message, then define a dynamic recipients report with only one column. This column should contain the email addresses of the recipients. The name of the column doesn't matter. Example:

    SELECT email
    FROM employee

Dynamic Recipients + Personalization

If you want all the recipients to get the same data, but you would like some personalization of the email message e.g. having a greeting like Dear John instead of Dear Employee, then define a dynamic recipients report where the first column contains the recipient email addresses, and any additional columns that you would like to use in the email message e.g.

    SELECT email, first_name, other_name last_name, order_count
    FROM employee, orders
    WHERE employee.id=orders.employee_id

When defining the job for your data query, in the email message section, you can then use column labels as placeholders where personalized details will be put. The labels consist of column names from the recipients report surrounded by # signs e.g. you can have a message like

    Dear #first_name# #last_name#
    You are now at #order_count# orders, which is below the target of 100.

Each person in the dynamic recipients report list will get a personalized email with the column labels substituted with his values.

Dynamic Recipients + Filtering

If you want the recipients to get different data, you will define a dynamic recipients report where the first column contains the recipient email addresses, any additional columns with personalization information if you want, and 2 other special, hard coded columns named recipient_column and recipient_id. These will be used to filter the data query. i.e. WHERE <recipient_column> = <recipient_id>. The main, data query will also need to have a special, hard coded label, #recipient#, in the where clause.

If the values in the recipient_id column are numbers, include an additional hard coded column named recipient_id_type, with the value of this column being the string "number". You can include columns named open_password and modify_password to contain dynamic passwords that should be used for the dynamic output if using xlsx or pdf report formats.

Example:
If we want to email users only transactions that they created, our data query can be something like

    SELECT *
    FROM transactions
    WHERE transaction_date = CURDATE()
    AND #recipient#

We can then define a dynamic recipients query like

    SELECT email, "transaction_user" recipient_column, employee_id recipient_id, "number" recipient_id_type
    FROM employee

This will cause the #recipient# placeholder in the data query to be replaced with the condition transaction_user = <employee_id>, where <employee_id> will be different for each recipient, as per the dynamic recipients query. We can then schedule a job for the main report and set the Dynamic Recipients field to the dynamic recipients report, and all the recipients will get a separate email with their specific data only.

If we also want to include personalization fields in the email message body, we can add these to the dynamic recipients query e.g

    SELECT email, "transaction_user" recipient_column, employee_id recipient_id, "number" recipient_id_type, first_name
    FROM employee

and then we can include the personalization placeholders in the email message field as desired.

Note:

• With the personalization and filtering options, you can include columns with the name email_cc, email_bcc or email_reply_to to specify email addresses that should be included in the cc, bcc or reply-to fields of the email sent.
• Email address columns can have multiple email addresses separated by ,