CRM Report for Entity and Attribute Details

Quite often I have encountered a need to see a list of entities, along with their attributes, and in particular the details of the attributes, so for example the Account entity along with all Attributes including custom and their details – Display Name, Database Name, Field Type etc…..

In CRM 4.0 there were a few applications that could take the Customisations XML and feed it to Word to deliver as a document set out with all the details. I haven’t found one in CRM 2011, and want to build one slightly differently using a SQL report.

I decided to include a parameter that would let you choose which entities to report on.

In brief:

Create 2 datasets.

1st dataset will be used as the parameter:

   1:  SELECT 

   2:  distinct EntityView.Name AS EntityName 

   3:   

   4:   

   5:  FROM LocalizedLabelView AS LocalizedLabelView_2 

   6:  INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId 

   7:  RIGHT OUTER JOIN EntityView 

   8:  INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON AttributeView.EntityId = EntityView.EntityId

   9:  WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName' 

  10:  AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName' 

  11:  AND LocalizedLabelView_1.LanguageId = '1033' 

  12:  AND LocalizedLabelView_2.LanguageId = '1033'

  13:   

  14:  order by EntityView.Name asc

Then create a main dataset that will pull in the details from the entities and attributes:

 

   1:  SELECT EntityView.Name AS EntityName, 

   2:  LocalizedLabelView_1.Label AS EntityDisplayName, 

   3:  AttributeView.Name AS AttributeName, 

   4:  LocalizedLabelView_2.Label AS AttributeDisplayName,

   5:   

   6:  column_name as columnname, 

   7:  data_type as datatype,

   8:  table_name as tablename

   9:   

  10:  FROM LocalizedLabelView AS LocalizedLabelView_2 

  11:  INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId 

  12:  RIGHT OUTER JOIN EntityView 

  13:  INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON AttributeView.EntityId = EntityView.EntityId

  14:  left outer join INFORMATION_SCHEMA.COLUMNS as infoschema on attributeview.Name = COLUMN_NAME and EntityView.Name = table_name

  15:   

  16:  WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName' 

  17:  AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName' 

  18:  AND LocalizedLabelView_1.LanguageId = '1033' 

  19:  AND LocalizedLabelView_2.LanguageId = '1033' 

  20:  AND table_Name IN (@Entity)

  21:  --AND EntityView.Name IN ('Account')

  22:  ORDER BY EntityName, AttributeName

Once this is done, design your report in some sort of table layout, and the results will be as follows:

Rob.