Creating a User Security Templates Report

This guide will build on the principles from the “Connecting to the Database via Excel” guide and also expect you to connect to the BI.UserTemplateAssociations view and create a pivot table as described in the principles of that document. It also assumes you have familiarized yourself with the Introduction document.

1. Follow the Connecting to the Database via Excel guide to create a connection to the DSS database. In a slight departure from that document, we would connect to the BI.UserTemplateAssociations view instead of BI.DataSecurityAssignedToUser view.

2. After the data is pulled into a pivot table, there are numerous ways to view this particular report. The below example would show all templates assigned to a user. Using Excel’s delivered pivot table and standard filters, we could filter this report in numerous ways to glean insights in the data. In the example below, a few active users have been filtered for templates that contain the string “A/P":