Building a Better User Account Report

User Account Summary Report (49)
A number of my implementations require a “CRM Account Summary” report be built. This report typically includes information such as Name, Business Unit, Security Roles, Site, Access Mode, Account Status, and other. The below image is a sample of typical “CRM Account Summary” report.

Now most of those fields are found in the FilteredSystemUser view; however, the real backbone of this report is the table that shows a count of the active and inactive records by entity type the user owns. While this table could be accomplished by hard coding a number of UNION statements for each desired entity type, doing so would require additionally development every time a new entity is added to the system. Thus, I have developed a query, shown below, that identifies the user owned entities and then collects counts by statecode.

DECLARE @Sql VARCHAR(MAX)
SET @Sql = '
--Create temp table to store list of entities that where the records are user owned
CREATE TABLE #entities (Name VARCHAR(MAX), OriginalLocalizedName VARCHAR(MAX), ReportViewName VARCHAR(MAX))
INSERT INTO #entities
SELECT
E.Name,
E.OriginalLocalizedName,
E.ReportViewName
FROM EntityView E
WHERE
IsIntersect = 0 --Exclude interest entities
AND
UsesFullnameConventionRules = 0 --
AND
IsLogicalEntity = 0 --Exclude virtual entities
AND
IsMappable = 1 --
AND
OwnershipTypeMask = 1 --Only enties where records are user owned
AND
Name NOT IN (''CustomerRelationship'', ''CustomerOpportunityRole'') --Exclude '

SET @Sql = @Sql + '
--Create table to store counts of records
CREATE TABLE #counts (SystemUserId UNIQUEIDENTIFIER, EntityName VARCHAR(MAX), FriendlyName VARCHAR(MAX), recordType INTEGER, recordCount INTEGER)
DECLARE @Insert VARCHAR(MAX)
DECLARE @Name NVARCHAR(MAX)
DECLARE @OriginalLocalizedName NVARCHAR(MAX)
DECLARE @ReportViewName NVARCHAR(MAX)

DECLARE theCursor CURSOR FOR
SELECT
E.Name,
E.OriginalLocalizedName,
E.ReportViewName
FROM #entities E
OPEN theCursor
FETCH NEXT FROM theCursor
INTO @Name, @OriginalLocalizedName, @ReportViewName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @Insert = ''INSERT INTO #counts SELECT OwnerId [SystemUserId], '''''' + @Name + '''''' [EntityName], '''''' + @OriginalLocalizedName + '''''' [FriendlyName], 0 [recordType], COUNT(*) [recordCount] FROM '' + @ReportViewName + '' WHERE StateCode = 0 GROUP BY OwnerId'';
PRINT @Insert
EXEC(@Insert);

SET @Insert = ''INSERT INTO #counts SELECT OwnerId [SystemUserId], '''''' + @Name + '''''' [EntityName], '''''' + @OriginalLocalizedName + '''''' [FriendlyName], 1 [recordType], COUNT(*) [recordCount] FROM '' + @ReportViewName + '' WHERE StateCode = 1 GROUP BY OwnerId'';
EXEC(@Insert)

FETCH NEXT FROM theCursor
INTO @Name, @OriginalLocalizedName, @ReportViewName
END
CLOSE theCursor
DEALLOCATE theCursor '

SET @Sql = @Sql + '
--Build select statement
;WITH theUsers AS (
SELECT
FSU.SystemUserId
,E.Name
,E.OriginalLocalizedName
,E.ReportViewName
FROM #entities E
CROSS JOIN (' + @FilteredSystemUsers + ') FSU
), theRoles AS (
SELECT DISTINCT
U.SystemUserId,
R.Name
FROM theUsers U
INNER JOIN FilteredSystemUserRoles FSUR ON FSUR.SystemUserId = U.SystemUserId
INNER JOIN FilteredRole R ON R.RoleId = FSUR.roleid
), theRolesCSV AS (
SELECT
SystemUserId,
STUFF((SELECT '', '' + tbl2.Name AS [text()]
FROM theRoles tbl2
WHERE tbl2.SystemUserId = tbl1.SystemUserId
ORDER BY tbl2.Name
for xml PATH('''')),1,1,'''') AS Roles
FROM theRoles tbl1
)
SELECT
U.SystemUserId
,FSU.BusinessUnitId
,FSU.BusinessUnitIdName
,FSU.CreatedBy
,FSU.CreatedByName
,FSU.CreatedOn
,FSU.ModifiedBy
,FSU.ModifiedByName
,FSU.ModifiedOn
,FSU.DomainName
,FSU.FirstName
,FSU.LastName
,FSU.RGA_CountryId
,FSU.RGA_CountryIdName
,FSU.SiteId
,FSU.SiteIdName
,FSU.IsDisabledName
,FSU.AccessModeName
,U.Name EntityName
,U.OriginalLocalizedName
,R.Roles
,C1.recordCount ActiveRecordCount
,C2.recordCount InActiveRecordCount
FROM theUsers U
INNER JOIN FilteredSystemUser FSU ON FSU.SystemUserId = U.SystemUserId
INNER JOIN theRolesCSV R ON R.SystemUserId = U.SystemUserId
LEFT OUTER JOIN #counts C1 ON C1.SystemUserId = U.SystemUserId AND C1.recordType = 0 AND C1.EntityName = U.Name
LEFT OUTER JOIN #counts C2 ON C2.SystemUserId = U.SystemUserId AND C2.recordType = 1 AND C2.EntityName = U.Name
'

SET @Sql = @Sql + '--Remove the temp tables
IF object_id(''tempdb..#entities'') IS NOT NULL
BEGIN
DROP TABLE #entities
END

IF object_id(''tempdb..#counts'') IS NOT NULL
BEGIN
DROP TABLE #counts
END'
EXEC(@Sql)

Once this query is used by a SSRS data source your are ready to build your report. I’ll assume that if you’re reading this you already know how to build reports with SSRS; however, for those don’t check out the Create Reports for Microsoft Dynamics CRM Using SQL Server Reporting Services MSDN article or Gareth Tucker’s Getting Started with CRM 2011 Online Fetch XML Reporting blog post. By the way the above report can be downloaded from the link below.

Title: User Account Summary Report
File Type: rdl
Version: 1.0
Size: 129.7 kB
No. Downloads: 49