Sometimes you have a requirement to distribute to a group of people a report with individual data for each person. This can be easily accomplished with a publication and a dynamic recipients feature. However what if you need to divide a list of people into groups and apply a different filter on a report for each group. If you have only few groups you can
create a report with a prompt on an object which you need to apply a filter on and create a separate report schedule instance each with a different prompt value selected and with a different list of emails in TO: field. The challenge might be when you have hundreds of emails divided into dozens of groups. Creating a bunch of instances is not easy and requires tedious maintenance. We can utilize a publication features and satisfy the requirement. In this example I will use eFashion data and an excel spreadsheet with custom data for only few recipients.
This is how my spreadsheet looks like:
This is my webi report in which I retrieved only 2 states and put them in a section. Within a section I will put only a table with a City object.
I want to send a report with only CA data to John and Mary and with TX data only to Mark and Ken.
The first step is to plug my spreadsheet into my report.
When you run it BO will merge all common names dimensions. We need only one merged dimension on State.
Save the report and export it to CMS. Now we are ready to create a publication. As a source document and a dynamic recipients report we will use the same report However only first data provider DPI will be used in a source document and only the second data provider Recipients will be used in Dynamic Recipients setup. We could also save Recipients
data provider in a separate report which would be used as a dynamic recipients report however we still need this extra query (with recipients list) in the main report to specify a relationship in Personalization. For this reason I keep only one report. Pros and cons of keeping dynamic recipients query in the source document will be discussed in a separate post.
On Dynamic Recipients page we choose data provider “Recipients” from our webi report We set Name as Identifier, State as Full Name and Email as Email. By doing this we make sure that a publication will be applying filters on both Name and State. We don’t use Name anywhere in the report so the filter will have no impact on it besides generating a separate email for each recipient.
Now we have to match States and Names in Personalization. Yes, we are matching the same objects from the same queries.
It’s up to you what format you specify on Format and Destination pages, just make sure you put %SI_EMAIL_ADDRESS% placeholder in To: field.
What happens when you schedule a publication? BO refreshes the report and tries to split the output for each recipient. It takes the first Name from the recipients list, in our case John, applies filters on Name and State (because both fields are specified on Dynamic Recipients and Personalization pages) using values “John” and “California”. State from Dyn Recip. Query is merged with State from DP1 so it will affect the report by applying a filter on it and showing only a section for “California”. Field Name is not merged that’s why the applied filter on it won’t affect the report This way each recipient we get his own state data. There is no option to send only one email with all email addresses in To: field unless we do it manually or using SDK.