Developing a simple context sensitive report for CRM

Suppose we want to develop a context sensitive report for contact entity. This report, i should be able run on selected records and it should show information related to those records only.

Lets take an very simple example,

We could develop a report for contacts entity that would display firstname and lastname of the selected records from the grid within CRM.

So we could have our dataset like this

select  CRMAF_FilteredContact.FirstName, CRMAF_FilteredContact.LastName
from FilteredContact as CRMAF_FilteredContact

Then while uploading we would select

“Conacts” for Related Record Types

“Related record types” for Display In.

Another way of creating the above report could be this

Our query for the dataset could be

declare @sql nvarchar(2000)

set @sql=’SELECT  CO.firstname, CO.lastname FROM  (‘+@CRM_FilteredContact +’) CO’

exec(@sql)

Here for @CRM_FilteredContact we would define a report parameter with

name=CRM_FilteredContact

DataType= String

Checked Internal option

Non queried for Available Values

Non queried for Default Values with the following value

select firstname,lastname  from FilteredContact

Than we would upload it in CRM the way described above.

 

For a report that shows parent and child records information for e.g. contact and all its activities we can use the following query.

 

SELECT activitytypecodename, regardingobjectidname, subject, fullname
FROM FilteredActivityPointer AS CRMAF_FilteredActivityPointer
INNER JOIN FilteredContact As CRMAF_FilteredContact on
CRMAF_FilteredContact.contactid = CRMAF_FilteredActivityPointer.regardingobjectid

Bye..