Context-Sensitive Reporting


V3 has a nifty reporting feature that allows users to access reports inside of MSCRM application via the grid reports menu and to run those reports only on the records of interest.


 




 


We built this feature after our usability studies showed that business users consistently want to run report within the context of their every day work environment, rather than navigating to a separate reporting area and setting the report filter criteria so that the report runs on the right set of records.


 


Many customers and partners have asked us how to create reports that are context-sensitive. Let me first break down context-sensitive reports into 2 categories:


 


I. Filterable


 


Filterable reports are SRS reports that have the SQL statements structured in a certain way that allows the reports to be filtered to run on a specific set of records.  These reports show up under the “Select Records” section on the report context menus:


 




 


II. Non-filterable


 


Non-filterable reports can be SRS reports or non-SRS reports (i.e. Excel) and cannot be filtered to run on a specific set of records.  When SRS reports do not have the SQL structured to allow filtering, they are also non-filterable.  Non-filterable reports can still be accessed via context-menus show up under the “Run Report” section on the report context menus:


 




 


Making SRS Reports Filterable


 


Now for the fun part of making reports filterable.  As mentioned above, in order for a report to be filterable, it must be an SRS report.   The following steps are taken from the Report Writer’s Guide of the Microsoft CRM 3.0 SDK and slightly modified by me::



  1. Create a report that contains fields from a Filtered<entity> database view and establish one or more SQL JOINs with other related filtered views as required.
  2. Use CRMAF_Filtered<entity> as an alias name in the report’s SQL query statement. For example:
SELECT CRMAF_FilteredActivityPointer.activitytypecodename as
activitytypecodename,
CRMAF_FilteredActivityPointer.regardingobjectidname as regardingobjectidname,
CRMAF_FilteredActivityPointer.subject as subject,
CRMAF_FilteredAccount.name
FROM FilteredActivityPointer AS CRMAF_FilteredActivityPointer
INNER JOIN FilteredAccount As CRMAF_FilteredAccount on
CRMAF_FilteredAccount.accountid = 
CRMAF_FilteredActivityPointer.regardingobjectid


  1. When you upload the report to Microsoft CRM through the Report: New form, select all entities in the Related Record Types categorization whose filtered views are referred to in the report’s SQL code.  You don’t have to associate the report with every referred entity, just the ones where you want the report to show up and filter in context.
  2. In the Display in field of the form, include Forms for related record types if you wish the report to be accessible from detailed record forms and Lists for related record types if you want the report accessible from entity grids (as is shown in the Accounts grid screenshot earlier)



For example, if a report displays all activities for a particular account and you would like to show this report on the Microsoft CRM Account form, you must include Activities and Accounts in the Related Record Types categorization and specify Forms for related record types in the Display in field of the Report: New form.


Making non-SRS Reports Appear in Report Context Menu


 


As mentioned before, a report doesn’t have to be filterable (i.e. SRS report with the right SQL) to appear in the report context menus.  When you upload an Excel or Word report via report manager, and wish to have those reports appear on the report context menus, follow these steps (similar to #3 and #4 above):



  1. When you upload the report to Microsoft CRM through the Report: New form, select all entities in the Related Record Types categorization where you want this report to appear
  2. In the Display in field of the form, Forms for related record types must be one of the selected values.

Videos of Context-Sensitive Reporting!


 


Yes, a picture is worth a thousand words, but videos are worth even more.  The following are videos posted by our dev manager, Ed Martinez, on the Microsoft CRM Sandbox site.  (Shhhh… He’s aspiring to be a voice actor).  The videos can be downloaded at:


http://www.gotdotnet.com/codegallery/releases/viewuploads.aspx?id=b6499eb0-0eae-4597-b38e-ee9cbc71a404


 



  • Report PreFiltering and Context Sensitive Reports
  • Report Creation Using Visual Studio
  • Publishing a Report

Wan Li Zhu


Comments (5)

  1. chuck says:

    What would be a really cool example is to show how to filter a report based on the current user that is logged in.  

    We have a  need to filter accounts based on the owning user.  At this point I have no clue on how to get the current user’s guid and to pass that to the report engine so that I can query accountbase based on the owner’s guid.

    We have a bunch of reports that we will need to do based on this.

  2. Microsoft Dynamics CRM 3.0 a report prefiltering capability. In this post I will describe the process

  3. Kieran says:

    the video’s aren’t up anymore and the link just goes to a new site, any chance of getting them re added?

Skip to main content