My Business Intelligence team was recently tasked with building a report of employee contact information. A database already existed of employee emergency contact information. In the database an employee may have zero or more people that may be listed as an emergency contact. Contacts may have multiple means of contact, phone, email, mailing address, etc. Each means of contact may have multiple types e.g. home phone, mobile phone, work phone, home address, work address, etc.
The report must display all employees and their emergency contacts with all available contact information displayed for each person. The report must be able to accommodate the fact that contacts may not have all contact types and must easily accommodate any new contact types that may be added to the database.
Building a Query
Since the business may add or remove contact types from the database entirely and the report must continue to function and some contact types may or may not be available I figured this report would require a matrix for the variability in the columns returned for each contact.
My team built a view that would gather all the contact information available for the employees and their contacts. However, we return the contact data as though it is a fact table. The person’s contact type information is a name/value pair on a row of properties about the person and each contact is returned with the EmployeeID to whom he is related, for example:
|1||John Doe||Self||Mobile Phone||111-222-3333|
|1||John Doe||Self||Home Phone||444-555-6666|
|1||John Doe||Self||Home Emailemail@example.com|
|1||Jane Doe||Spouse||Mobile Phone||222-333-9999|
|1||Jane Doe||Spouse||Home Address||123 N South St|
Building a Report
The team originally built a matrix report that used a column group grouped by ContactType but that resulted in lots of confusing empty columns and an ugly, unworkable layout.
The details group was converted to use a group expression on the EmployeeID and that eliminated many of the empty cells, the report was better but still not right. The Contact Type columns were listed alphabetically so the columns read looked like:
|Contact Name||Cell Phone||Home Address||Home Email||Home Phone|
We wanted all the phones listed first then emails then mailing addresses.
To make this happen we made three column groups adjacent to each other. Each column group is grouped by Contact Type and has a filter applied so only the correct contact type is displayed in the column group. The filters look like:
ContactType Like *phone*
ContactType Like *email*
ContactType Like *address*
And now all appeared to be well, employees are listed immediately followed by their emergency contact people and the columns show all the contact types in an order that made sense and was legible.
But all wasn’t well. Some people had multiple contact values for the same contact type, a common scenario, a person may have two mobile phone numbers or multiple home email addresses. In these cases only the first record of each type was displayed on the report.
To resolve this problem we needed to be able to display the Contact Type detail rows but only in the correct column groups. Since our report was grouped by both column and row we needed a way to find the detail rows only where the column and row groups intersected.
The solution was to place a table each ContactData textbox. We could then filter the table that is inside the main table’s textbox the same as we did at the column group so only phone number would appear in the phone column group, only emails in the email column group, etc.
In the screen shot below each textbox bordered in blue is a textbox containing a table of one cell. The Tablix Properties window displays the filter that is applied to the table in the textbox which is the same filter applied to the column group.
The result is a nicely formatted report that displays all contact information for all employees and their emergency contacts.