)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Salesforce’s native reporting can’t effectively combine Permission Set License Assignment data with User object fields, leaving you without critical details like department, role, or manager information for license audits.
Here’s how to create comprehensive reports that show exactly who has which licenses assigned, along with all the user context you need for proper license management.
Get complete license assignment data with user details using Coefficient
Coefficientsolves this cross-object reporting challenge through custom SOQL queries that join your permission set license assignments directly with user data. Instead of wrestling with Salesforce’s limited report types, you can pull all the information you need in a single import.
How to make it work
Step 1. Connect to your Salesforce org and set up a custom SOQL query.
SalesforceIn, navigate to Coefficient’s import menu and select “Custom SOQL Query.” This bypasses all the relationship limitations you’d encounter with standard report types.
Step 2. Build your query to join permission set license assignments with user data.
Use this SOQL structure to combine both objects: `SELECT PermissionSetLicenseAssign.Id, PermissionSetLicenseAssign.PermissionSetLicense.MasterLabel, PermissionSetLicenseAssign.AssigneeId, User.Name, User.Email, User.Department, User.Title, User.Manager.Name, User.IsActive, User.LastLoginDate FROM PermissionSetLicenseAssign JOIN User ON PermissionSetLicenseAssign.AssigneeId = User.Id`. This gives you license details alongside complete user context.
Step 3. Apply filters and schedule automated refreshes.
Add dynamic filters for active users, specific departments, or date ranges. Set up automated refreshes (daily or weekly) so your license compliance data stays current without manual intervention.
Step 4. Create pivot tables for license distribution analysis.
Use your spreadsheet’s pivot table functionality to analyze license distribution across departments, identify users with multiple assignments, and track usage patterns over time.
Keep your license audits current and comprehensive
Try CoefficientThis approach gives you the complete license assignment visibility that Salesforce’s native reporting simply can’t provide.to streamline your permission set license reporting and compliance monitoring.
