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’
Here for @CRM_FilteredContact we would define a report parameter with
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