Mar 31, 2014

Run SSRS report against specific CRM record

When we write SSRS reports to Dynamics CRM, it may need to write a report against opened record. For example, order report against selected account. Consider my example for custom entity called office. (Schema name: new_office).

Our usual select statement which returns all the offices;

new_name as OfficeName,
new_code as OfficeCode

Special query to return the current record specific data;

CRMAF_Filterednew_office.new_name as OfficeName, 
CRMAF_Filterednew_office.new_code as OfficeCode
FROM  Filterednew_office as CRMAF_Filterednew_office

Prefix “CRMAF_Filtered” seems to be doing the filtering. What you really have to do is do your query this way and configure the report so that you can run from opened form of selected entity type. (In my example its Office) Please make sure you select below option so that report is ready to be run from opened record.

By the way, if the query I need to write is complex I read the Guid of current record to a variable so that I am free to use it for any other complex queries without any issue as below;

DECLARE @OfficeId uniqueidentifier

SELECT @OfficeId =CRMAF_Filterednew_office.new_officeid
FROM  Filterednew_office as CRMAF_Filterednew_office

No comments:

Post a Comment