Context-Sensitive Reporting - Microsoft Dynamics CRM Team Blog - Site Home

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::

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.
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

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.
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):

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
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