Tuesday, August 07, 2012

MS CRM 2011: Sharing Summary Report

Sharing is amazing feature which is provided with Dynamics CRM. Unfortunately it is not easy to say which record is shared with whom and which privileges are provided. I have created report using which you can get this information.
First part is filters – you can filter by Users/Groups with who record is shared and by type of record:
Users and Groups are populated automatically. Regarding type of record – out of box it contains only 4 types of records (Account, Contact, Opportunity and Lead) but it is possible to extend it with additional types (I will show how to do it in this post).
This is how report looks like in action:
Following part of article describes how to extend this report to include additional entity (entities):
1. Download report and copy it to your development environment where you have BIDS.
2. Create empty reporting project and add this report into it:
3. Expand “Data sources” node and change it to your environment, save “Data Source”:
4. Next step is to get information for new entity. You will need Logical Name of entity, Logical Name of attribute which will be displayed and entity type code (in my sample new_entity and new_name):
To get entity type code I used SQL Query but you can use Metadata Browser to get this information:
5. Expand “Parameters” node and open “EntityType” parameter to include your new entity:
6. Next step is to modify your Fetch Query to fetch results. Expand “Datasets” node and open “Results” dataset:
7. Copy fetch request and modify it in following way:
Here is the pattern that can be used for new entity:
<link-entity name='{Logical Name of new Entity}' 
    from='{Logical Name of new Entity}id' to='objectid' link-type='outer' 
    alias='{Any Unused alias}'>
    <attribute name='{Display Attribute of New Entity}' 
        alias='name{Logical Name of new Entity}'/>
</link-entity>

8. Insert modified fetch to Query and click “Refresh Fields” button. If everything was done properly – you would not get any errors and new attribute will appear at “Fields” section:

9. Click”Ok” to save changes of Dataset. Now you can recheck that report works:

10. To import this report to CRM you should do following steps:
10.1. Navigate to CRM reports.
10.2. Click "New report".
10.3. Choose "Existing File".
10.4. Select the RDL-File.
10.5. Click "Save and close".

10 comments:

  1. Nice work Andrii! I was looking at this via SQL - I like your approach better!

    Are you capturing both the direct shares and inherited shares - is there a way to differentiate between the two?

    @ScottSewell

    ReplyDelete
    Replies
    1. Hello Scott,
      Query of this report fetches only direct shares and ignores inherited. I believe that it would not take a lot of time to modify it to work with both types of shares.

      Delete
  2. Andrii: does this report work with CRM Online?

    ReplyDelete
    Replies
    1. Hello Dave,
      I though that it would not. But.. I tried it with online and it worked without any issues (everything like display, rights, drilldown).
      One thing - that list of entities should be modified manually.

      Delete
  3. Hey Andrii,

    I could not find report on codeplex. It just downloaded process template. Can you please check.

    Thanks,
    Amol

    ReplyDelete
    Replies
    1. Hello Amol,

      Try to check following url - https://sharingsummary2011.codeplex.com/releases/view/92366

      Kind regards,
      Andrii.

      Delete