)
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 standard reporting can’t effectively join Permission Set License Assignment and User data due to relationship limitations, making it impossible to analyze license usage patterns across departments or user attributes.
Here’s how to create comprehensive reports that combine license assignment details with complete user context for proper utilization analysis.
Create seamless cross-object joins for license and user data using Coefficient
Coefficientexcels at cross-object reporting through custom SOQL functionality, enabling seamless joins between permission set license assignments and user data that Salesforce’s native reporting simply can’t handle.
How to make it work
Step 1. Set up a custom SOQL query to join both objects.
SalesforceIn yourconnection, use this SOQL structure: `SELECT p.Id, p.PermissionSetLicense.MasterLabel, p.AssigneeId, u.Name, u.Email, u.Department, u.Title, u.IsActive FROM PermissionSetLicenseAssign p, User u WHERE p.AssigneeId = u.Id`. This creates a unified dataset with both license and user information.
Step 2. Include comprehensive data from both objects.
From PermissionSetLicenseAssign, pull license assignment details, assignment dates, and license types. From the User object, add demographics like Name and Email, organizational data like Role and Manager, and activity metrics like LastLoginDate and IsActive status.
Step 3. Apply dynamic filtering across multiple object criteria.
SalesforceFilter by active users, specific departments, license types, or assignment date ranges. Use dynamic filters that reference spreadsheet cells so you can change criteria without editing the import settings in.
Step 4. Create automated refresh schedules for current data.
Set up daily or weekly refreshes to keep your cross-object reports synchronized with current Salesforce data. This ensures your license utilization analysis stays accurate without manual intervention.
Step 5. Build pivot tables and charts for license utilization analysis.
Use your spreadsheet environment to create matrix reports showing Users vs. License Types, department-level utilization breakdowns, and historical trending by combining login data with assignment dates.
Get the comprehensive license utilization insights you need
Start buildingThis approach overcomes the fundamental limitations of Salesforce’s native reporting architecture for cross-object analysis.your comprehensive license assignment reports with complete user context today.
