Prerequisites
Filtering by email address works by comparing the address of the logged in viewer to a field in your data source that contains valid email addresses. For each row in your data, the filter checks to see if the viewer's email matches the address in that row.
Consider the following data:
| Data | |
|---|---|
| [email protected] | abc |
| [email protected] | cde |
| [email protected] | efg |
| [email protected] | ghi |
If this data was filtered by email address, and [email protected] viewed the filtered report, he'd only see the data
"abc" and "efg." If [email protected] viewed the same report, she'd see the data "cde" and "ghi."
This is fine if you have a 1:1 relationship between viewers and the data. But what if you wanted also wanted [email protected] to see the data? That is, you want many people to see the same rows of data. (This is known as a many:many relationship.)
Filter by email only works on one address per row, so you can't simply put a list of addresses in your email field. For example, this won't work:
| Data | |
|---|---|
| [email protected], [email protected], [email protected], [email protected] | abc |
Solution: Use data blending
You can create a many:many relationship between email addresses and your data by blending a table of addresses with your data, using any common field as a join key.
Fruit stand example
You manage a produce company, and want show your sales representatives how they are performing at the various fruit stands they service. Multiple sales people can service multiple stands. To filter the data so your sales people only see their data, you could do the following:
Step 1: Create an access control list table
In this step, you'll create an access control list (ACL) table that contains the email addresses of the authorized sales people, paired with a data field (a fruit stand name) that can be used as a join key in the blend.
| Sales Rep Email | Join key |
|---|---|
| [email protected] | Fruit Stand A |
| [email protected] | Fruit Stand A |
| [email protected] | Fruit Stand B |
| Fruit Stand C |
Access control list (ACL) table
Note that salesrep1 can see data for Fruit Stand A and Fruit Stand B, while salesrep2 can see data for Fruit Stand A and Fruit Stand C.
Step 2: Create the data table
The data table tracks the sales each representative made to each fruit stand.
| Fruit Stand | Fruit | Sales |
|---|---|---|
| Fruit Stand A | Apple | 50 |
| Fruit Stand A | Banana | 26 |
| Fruit Stand A | Orange | 20 |
| Fruit Stand A | Pear | 93 |
| Fruit Stand B | Apple | 98 |
| Fruit Stand B | Banana | 86 |
| Fruit Stand B | Orange | 7 |
| Fruit Stand B | Pear | 85 |
| Fruit Stand C | Apple | 21 |
| Fruit Stand C | Banana | 61 |
| Fruit Stand C | Orange | 3 |
| Fruit Stand C | Pear | 78 |
Data table
Step 3: Apply the email filter to the ACL table
Edit the data source for the ACL table and choose the Sales Rep Email field as the filter.
Step 4: Blend the ACL table with the data table
Finally, blend the data table with the ACL table. Since we want to effectively add the sales rep email as a column to the data table, we will start from the data table and then left-join the ACL table.
With no email filter applied, you'll see all the records for both sales representatives. However, with the email filter applied, blending the ACL table with the sales data table filters that data according to which sales person is viewing the report. Here's what each representative would see when they view the report:
|
Sales Rep 1 sees this:
|
Sales Rep 2 sees this:
|