All About Activity Entity Query Full Guidance in CRM 2011/2013 : Basic–Complex (ActivityPointer, ActivityParty) Queries in One Post

Are you in charge to create reports involving Activity entity?

Or you also have requirement to show Activities with participated Customer or Potential Customer? As far as Activity entity in CRM is concerned, we know that this will not be easy to do query to retrieve Activities List plus the participated Customer since they are not stored in the same entity and in the Activity entity, it has no direct relationship to Account or Contact, and when you take a look deeper, you will be understanding that this is a complex relationship.

We know that activity entity is stored in the FilteredActivityPointer, but from that view, we know that no contact nor account is having relationship with that view. The fact is, the relationship between Activity and Contact/Account/Lead/User are actually involving another party, that is ActivityParty entity.

What is ActivityParty?

If we see the Activity and ActivityParty relationship description in CRM, it was written:

“Unique identifier of the activity associated with the activity party. (A "party" is any person who is associated with an activity.)”

So, I know that we need to link the Activity with the ActivityParty to get the participated related Customer record, or CRM prefers to call it “Party”.

In My Previous Post, I was talking about relationship between Activity entity and the Activity Party as well, so you might refer to that post.

Now in this post, I will talk about SQL Query to retrieve data from Activity and plus the participated parties.

Let’s start with simple query first..Better you follow the steps from beginning until the end to understand by sequence.

*I am recommending to use FilteredView to your reports.

So, enjoying read this long story…

Retrieve All Activity List (All Columns)

It is a simple query to retrieve all activities, we can use FilteredActivityPointer

select * from FilteredActivityPointer

Result:

Retrieve All Activity List (Specific Columns)

select activitytypecodename, subject, statecodename, statuscodename 
from FilteredActivityPointer

Result:

Count All Activities (by Activity Type)

select COUNT(*) as TotalRecord, activitytypecodename  as ActivityType
from FilteredActivityPointer
group by activitytypecodename

Result:

Retrieve All Activities for each Activity Type

You can also retrieve activity from individual activity type table, for example: PhoneCall, Task, Appointment, ServiceAppointment, Email.

Phone Call

select Subject, ActualEnd, ActualStart from filteredPhoneCall

Result:

Task

select Subject, ActualEnd, ActualStart from FilteredTask

Result:

Appointment

select Subject, scheduledstart, scheduledend from FilteredAppointment

Result:

Service Appointment

select Subject, scheduledstart, scheduledend from FilteredServiceAppointment

Result:

Email

select Subject, actualstart, actualend from FilteredEmail

Result:

Retrieve All Activities for each Activity Type (Custom Activity Entity)

I have created a custom activity entity, so-called: SMS

Result:

So now we know that CRM has separated tables as well to store each individual activity type, the problem is how to show all activities from all activity type (combined together) with showing fields that exists only for specific table, let’s follow this section.

Retrieve All Activities with Specific Field from Activity Type Entity

If we see in the SMS entity,

we have two custom fields: Message and Purpose.

Those two fields are only exist in the SMS entity.

We know that each activity record will be stored in the ActivityPointer entity, so to show all activities from all types we should query against this entity and get all of the standard fields, but this is not for custom fields.

but we cannot query using the FilteredActivityPointer

Instead, we should use:

select Subject, new_message, new_purposesname from FilteredNew_Sms

So, what if I want to show the message and purpose field of SMS together will all activities?

This is the query:

select ActPointer.activityid, ActPointer.Subject, 
ActPointer.activitytypecodename,
SMS.new_message, SMS.new_purposesname
from FilteredActivityPointer as ActPointer
left join filterednew_sms as SMS
ON ActPointer.activityid = SMS.activityid
order by ActPointer.activitytypecode 

The key is:

left join filterednew_sms as SMS
ON ActPointer.activityid = SMS.activityid

Result:

You can also join to another activity type:

Just add another Join

left join FilteredPhoneCall as phonecall
ON ActPointer.activityid = phonecall.activityid

Complete query:

select ActPointer.activityid, ActPointer.Subject, 
ActPointer.activitytypecodename,
SMS.new_message, SMS.new_purposesname, phonenumber
from FilteredActivityPointer as ActPointer
left join filterednew_sms as SMS
ON ActPointer.activityid = SMS.activityid
left join FilteredPhoneCall as phonecall
ON ActPointer.activityid = phonecall.activityid
order by ActPointer.activitytypecode 

Result:

Until know, we understand how to combine all of the activities across all activity type and showing the specific field from each Activity type, we understand that in SMS entity, the message was stored in the Message field (I am purposely not using description field), but how about email? Email, by default has description field to store the body content, and it happened to Appointment, Phone Call, Task, etc.

Now, how to combine them together into one field, let’s call the it: “Content”.

Retrieve All Activities combined Specific Fields from Activity Type Entity

SELECT actpointer.subject, actpointer.activitytypecodename,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description, SMS.new_message, ServiceAppointment.description, CampaignResponse.description) 
         as Content
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has 
--(each joint table is Optional, 
--if you need specific field from each Entity, such as Task, Phone Call, SMS, etc)

LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

order by actpointer.activitytypecodename

Result:

So, we’ve known how to retrieve al activities, so how to link to the Contact?

First, let’s we take a look about ActivityParty

Retrieve All Customers Who Participated in Any Activity

select activityid, activitypartyid, partyidname, participationtypemaskname 
from FilteredActivityParty

Result:

We have known about the ActivityPointer and ActivityPointer.

Next question: “I want to show all activities + all of the related customers, how can I achieve that?”

The answer is in this below section…

Retrieve All Activities and Related Participated Customers

The key is adding join to ActivityParty

SELECT actpointer.subject, actpointer.activitytypecodename,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 

Result:

We know that the ‘participationtypemaskname’ is the field to store the ‘To Recipient’, ‘Sender’, etc.

If you have read my previous post, it has been stated about the virtualization or masking to store the ‘From’ and ‘To’, in fact, the record value was stored in different rows for each participation type in this field: ‘participationtypemaskname’, but in CRM UI, it seems to be shown as 1 record which the participation type becomes the field name (From, To, Organizer, etc.)

Retrieve All Activities of a Specific Party

SELECT actpointer.subject, actpointer.activitytypecodename,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
where ActParty.partyid = @partyid

Replace ‘@partyid’ with the party id (remember, party it can be Account, Contact, Lead, SystemUser)

As we know, the SystemUser can be Resources in Service Activity or Service Appointment

For example: Replace by AccountId

SELECT actpointer.subject, actpointer.activitytypecodename, ActParty.partyid,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
where ActParty.partyid = '3B4CDE29-1037-E411-9454-001CC4EECDD6' --replace with accountid
--where ActParty.partyidname = 'Aileen Gusni Corp'

Result:

Retrieve All Activities of Specific Participation Type

We can also query by participation type:

SELECT actpointer.subject, actpointer.activitytypecodename, ActParty.partyid,
ActParty.partyidname, participationtypemaskname
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
where ActParty.participationtypemaskname in ('Sender', 'To Recipient') 

Result:

Because of its design, by joining two entity view, ActivityPointer and ActivityParty, now we can produce a report to show activities and their related parties, but we also know that Parties can be Customer, Prospect, or Resource, so how to differentiate it?

Retrieve All Activities + All Related Customers + Party Type
(Customer <Contact/Account>, Prospect <Lead>, or
Resource/Employee <CRM User>)

SELECT ActPointer.subject, ActPointer.activitytypecodename as ActivityTypeCode,
ActParty.partyidname as PartyName, 
ActParty.participationtypemaskname as ParticipationType,
EntityView.Name as PartyType
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode

Result:

*Owner and Regarding are not always limited to ActivityParty relationship since Owner can be User or Team (Team is not defined in the ActivityParty) and Regarding are related to any entity that can have Activity, so you might found the PartyName is NULL.

So, we’ve known that ActivityParty is basically an intersection table to store the relationship between ActivityPointer and Account/Contact/Lead/SystemUser. (Lead and System Users are usually people don’t care about it, but we should know that the ActivityParty can link to those entities as well).

Now, next issue, what if I want to produce report to show all activities, all related parties, and customer details, such as customer name, customer surname, and customer address.

Retrieve All Activities + All Related Customer + Customer Fields

They key is we have to join table to all of the ActivityParty Type.

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
--where partyidname = 'Aileen Gusni Corp'

Result:

Remember this join:

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid) as d

Well, we have learned how to show activities + customer + customer detail, now we can also go to next section with new challenge, filter by specific customer criteria.

Retrieve All Activities + All Related Customer + Customer Fields
by Specific Customer Criteria

select * from 
(SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid) as joinedtable
where joinedtable.CustomerCountry ='Malaysia'

The ‘where’ clause:

where joinedtable.CustomerCountry ='Malaysia'

Result:

As we know that Activity is not only involving external Party or Customer, some activities such as Phone Call, Email, Service Activity can also involve our resources, so what if we want to show All Activities by Company Resources or Employee?

Retrieve All Activities of All Company Resources

SELECT ActPointer.subject, ActPointer.activitytypecodename as ActivityTypeCode,

ActParty.partyidname as PartyName, 
ActParty.participationtypemaskname as ParticipationType,
EntityView.Name as PartyType, partyobjecttypecode
FROM FilteredActivityPointer AS ActPointer
Inner Join FilteredActivityParty AS ActParty 
on ActPointer.activityid = ActParty.ActivityId 
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode
where partyobjecttypecode = '8' -- 8 it is system user code

Result:

Next section, I will explain about how to retrieve specific activity list with the linked customers.

Retrieve Specific Activity Type + Related Customer

*Email or Phone Call (How to Get ‘From’ and ‘To’)

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActPointer.activitytypecodename,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
where ActParty.participationtypemaskname in ('Sender', 'To Recipient')
and ActPointer.activitytypecode in ('4202', '4210') --Email and Phone Call

Result:

*Service Activity (Customer, Resource)

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
where ActPointer.activitytypecode ='4214' --Service Activity

Result:

*SMS – a Custom Entity (‘From’ and ‘To’ and Custom Field)

SELECT 
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.lastname) as CustomerSurname,
Coalesce(Account.EMailAddress1, Contact.EMailAddress1, SystemUser.InternalEMailAddress, Lead.EMailAddress1, 'Unknown') as CustomerEmail,
Coalesce(Account.Address1_Composite, Contact.Address1_Composite, SystemUser.Address1_Composite, Lead.Address1_Composite, 'Unknown') as CustomerAddress,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry,
ActPointer.activitytypecodename,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
where ActParty.participationtypemaskname in ('Sender', 'To Recipient')
and ActPointer.activitytypecodename = 'SMS'

Result:

So, guys, I believe that we have done about activities and activity party, so I am giving complex queries to summary.

There are two complex queries that you can use to show activities and of course link them to the Contact or Account or Lead or System User.

Complex Query (I)

This is the completed query by joining activitypointer, activityparty, each individual activitytype, and each individual activityparty.

SELECT 
ActPointer.subject AS subject,
ActPointer.activitytypecodename as ActivityType,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
EntityView.Name as PartyType,
ActParty.participationtypemaskname as ParticipationType,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description, SMS.new_message, ServiceAppointment.description, CampaignResponse.description) 
         as Content
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, 
--if you need specific field from each Entity, 
--such as Task, Phone Call, etc, can be used to get custom fields as well.)

LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

--inner join check party type
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode

Result:

You can also utilize this query to display other columns and to define selection to show by specific filter criteria, I give you two examples:

Set only to show Malaysian Customer Activities

select * from 

(SELECT 
ActPointer.subject AS subject,
ActPointer.activitytypecodename as ActivityType,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
EntityView.Name as PartyType,
ActParty.participationtypemaskname as ParticipationType,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description
         --, SMS.new_message --is a custom entity
         , ServiceAppointment.description, CampaignResponse.description) 
         as Content,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry  
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
--left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid --custom activity
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

--inner join check party type
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode) joinedtable

where joinedtable.CustomerCountry ='Malaysia'

Result:

Set only to show Activities to Open Lead

select * from 

(SELECT 
ActPointer.subject AS subject,
ActPointer.activitytypecodename as ActivityType,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
EntityView.Name as PartyType,
ActParty.participationtypemaskname as ParticipationType,
Coalesce(Task.description, Email.description, Appointment.description, PhoneCall.description,
         Letter.description
         --, SMS.new_message --is a custom entity
         , ServiceAppointment.description, CampaignResponse.description) 
         as Content,
Coalesce(Account.Address1_Country, Contact.Address1_Country, SystemUser.Address1_Country, Lead.Address1_Country, 'Unknown') as CustomerCountry  ,
Coalesce(Account.statecodename, Contact.statecodename, SystemUser.isdisabledname, Lead.statecodename) as PartyStatus
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid
--left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid --custom activity
left join FilteredServiceAppointment as ServiceAppointment ON ActPointer.activityid = ServiceAppointment.activityid
left join FilteredFax as Fax ON ActPointer.activityid = Fax.activityid
left join FilteredCampaignResponse as CampaignResponse ON ActPointer.activityid = CampaignResponse.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

--inner join check party type
inner join EntityView EntityView
on EntityView.ObjectTypeCode = partyobjecttypecode) joinedtable

where joinedtable.PartyType ='Lead'
and joinedtable.PartyStatus = 'Open'

Result:



*Remember:

left join filterednew_sms as SMS ON ActPointer.activityid = SMS.activityid

SMS is a custom entity, you might not need it.

We have done the complex query joining all of the tables that you might need, now the problem is for each participation type, will be displayed in separated rows:

How to merge the rows into columns to construct 1 record only, instead of displaying in two records or rows (1 row for ‘From’ and 1 row for ‘To’)?

Here is the answer, yes, we can use ‘Pivot’

Complex Query (II) – With Pivot

select  *
from(
SELECT 
ActPointer.ActivityId,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
participationtypemaskname in
(
 'Customer',
 'Optional attendee',
 'Organizer',
 'Owner',
 'Regarding',
 'Required attendee',
 'Resource',
 'Sender',
 'To Recipient'
   -- you can filter the participation type
)
--and actPointer.activityid = '54C8B7A1-2637-E411-9454-001CC4EECDD6' 

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Customer,
 [Optional attendee],
 Organizer,
 Owner,
 Regarding,
 [Required attendee],
 Resource,
 Sender,
 [To Recipient]
  )
) piv;

Result:

*Show all Roles/Participation Types

*Remember, here I give you trick to perform query based on your requirement, if you only want to show by specific activity or by specific customer then you can filter  using where ‘clause’

where partyid = '54C8B7A1-2637-E411-9454-001CC4EECDD6'

You can also filter the participation type.

To simplify the result, I will pick one activity and only to show From and To fields.

Query:

select  *
from(
SELECT 
ActPointer.ActivityId,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
participationtypemaskname in
(
 --'Customer',
 --'Optional attendee',
 --'Organizer',
 --'Owner',
 --'Regarding',
 --'Required attendee',
 --'Resource',
 'Sender',
 'To Recipient'
   -- you can filter the participation type
)
and actPointer.activityid = '54C8B7A1-2637-E411-9454-001CC4EECDD6' 

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 --Customer,
 --[Optional attendee],
 --Organizer,
 --Owner,
 --Regarding,
 --[Required attendee],
 --Resource,
 Sender,
 [To Recipient]
  )
) piv;

Result:

To achieve that, Adding these lines is the key:

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Customer,
 [Optional attendee],
 Organizer,
 Owner,
 Regarding,
 [Required attendee],
 Resource,
 Sender,
 [To Recipient]
  )
) piv;

You can also utilize the query based on your requirement.

If you want to filter by customer name as recipient:

select * from (
select  *
from(
SELECT 
ActPointer.ActivityId, ActPointer.activitytypecodename,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
 
participationtypemaskname in
(
 --'Customer',
 --'Optional attendee',
 --'Organizer',
 --'Owner',
 --'Regarding',
 --'Required attendee',
 --'Resource',
 'Sender',
 'To Recipient'
   -- you can filter the participation type
)


) act


pivot
(
  max(CustomerName)
  for participationtype 
  in (
 --Customer,
 --[Optional attendee],
 --Organizer,
 --Owner,
 --Regarding,
 --[Required attendee],
 --Resource,
 Sender,
 [To Recipient]
  )
) piv
) as pivottable
where pivottable.[To Recipient] = 'Aileen Gusni Corp' 

Result:



I give you two other examples:

Complex Query special for Service Activity  (Service Appointment) to get the related Customer

Query:

select  *
from(
SELECT 
ActPointer.ActivityId, ActPointer.activitytypecodename,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
 
participationtypemaskname in
(
 'Customer'
   -- you can filter the participation type
)

and actPointer.activitytypecode = '4214'

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Customer
  )
) piv

Result:



*Service Activity is equal to Service Appointment.
*Service Activity just the Activity Type, but the entity name is ServiceAppointment

Complex Query special for Phone Call and Email to get the ‘From’ and ‘To’ fields


Query:

select  *
from(
SELECT 
ActPointer.ActivityId, ActPointer.activitytypecodename,
--distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname, Lead.fullname) as CustomerName,
--Coalesce(Account.name, Contact.lastname, SystemUser.lastname, Lead.fullname) as CustomerSurname,
--ActParty.participationtypemaskname as ParticipationTypeOrigin,
ActParty.participationtypemaskname as ParticipationType
FROM FilteredActivityPointer AS ActPointer

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid
Where --ActParty.PartyIdName is not null 
--you can choose to show by customer/system user participation
 
participationtypemaskname in
(
 'Sender', 'To Recipient'
   -- you can filter the participation type
)

and actPointer.activitytypecode 
in 
 ('4202', 
 '4210')

) act

pivot
(
  max(CustomerName)
  for participationtype 
  in (
 Sender, [To Recipient]
  )
) piv

Result:

Important Key:

You can add or remove each individual activity type from this joining table SQL Query:

--define all of the activity type your Orgaization has (Optional, if you need specific field from each Entity, such as Task, Phone Call, etc)
LEFT JOIN FilteredTask AS Task ON ActPointer.activityid = Task.activityid
LEFT JOIN FilteredEmail AS Email ON ActPointer.activityid = Email.activityid
LEFT JOIN FilteredAppointment AS Appointment ON ActPointer.activityid = Appointment.activityid
LEFT JOIN FilteredPhoneCall AS PhoneCall ON ActPointer.activityid = PhoneCall.activityid
LEFT JOIN FilteredLetter AS Letter ON ActPointer.activityid = Letter.activityid

*You can add FilteredNew_SMS for new custom activity entity, FilteredFax for system activity, etc..

And this for the ActivityParty

--join activity party (Account and Contact)   
Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
left join FilteredAccount AS Account on ActParty.PartyId = Account.AccountId
and ActParty.partyidname is not null
left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
and ActParty.partyidname is not null
--join activity party (System User)   
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
and ActParty.partyidname is not null
--join activity party (Lead)   
left join FilteredLead As Lead on ActParty.PartyId = Lead.leadid

*You can also remove the Lead or System User if you don’t want to include them.

Now, we’re reaching the End and you have learned the secret relationship to link Activity and Customer in detail.

You can also do your own complex query and just contact me if you have any question about basic or advance query related to this post or anything.

I hope my post can help you to finish your report development and to answer all of questions about how to link Activity List with Account or Contact (or Lead or System User) relationship.

Thank you and see you!

Displaying Files or Documents List in Microsoft Dynamics CRM 2011/2013 Dashboard

As a consultant, we will face many clients with their various and unique requirements as well.

Now, they want to upload the files into CRM, then the Management can download the files after they log in to CRM. This circumstance, I’ve experienced before, when customer wants the Dealer after log in to the system can download the files uploaded by Marketing Team, for example, new pricing guide FY 2014, events list, promotion buy 1 get 1 gadget, membership upgrading, new model launching, and many more.

For example, every week or every month for each branch, they create Report using Excel, then they want the Management to see the periodic reports after log in to CRM on the one of the CRM Dashboards. They just go to the Dashboard, then double click and download the files. They don’t integrate CRM with SharePoint, so is that possible to let the users attach documents and download the documents as well? I found this requirement also in the forum.

So, as far as attachment is concerned, I always think to utilize Notes, but… Notes entity is too common and not too specific, and we cannot place Note to the Dashboard, I could not find in the View List that can be added to Dashboard as well. Well..you can still use IFrame. Okay, but I was considering to use Reports.

So, now, my choice…I was doing research to utilize Reports in this case.

So, here are my steps:

1. Create a new Category for Reports:

*For example: Weekly Report

You can refer to this link: 

http://www.powerobjects.com/blog/2012/07/25/adding-new-report-category-crm-2011-2/

This category is so important for grouping purposes and it makes your reports in manageable structure.

2. Create a new View for Reports:

Don’t’ forget to tidy your form, by editing the Filter Criteria

 and doing Configure Sorting

You can add Columns as well.

Save it and Publish it.

3. Go to the Reports, and Select the View (new View)

Then click “Of Current View”

This steps is purposely to get the View Link

With Format:

Weekly Reports <http://servername:port/orgname/main.aspx?etc=9100&extraqs=%3fpagemode%3diframe%26sitemappath%3dSFA%257cTools%257cnav_reports&pagetype=entitylist&viewid=%7bED57501E-1614-E411-944A-001CC4EECDD4%7d&viewtype=1039>

4. Then, just get the characters inside angle brackets <>

http://servername:port/orgname/main.aspx?etc=9100&extraqs=%3fpagemode%3diframe%26sitemappath%3dSFA%257cTools%257cnav_reports&pagetype=entitylist&viewid=%7bED57501E-1614-E411-944A-001CC4EECDD4%7d&viewtype=1039

This will be your IFrame URL

5. Go to Customization and Create a New Dashboard

Inset an IFrame Component:

Then set the URL and uncheck the checkbox in Security section

Increase the Height and Width as well to prettify your Dashboard..

Save it and Publish it

6. You also can use View List as well

*You will know the difference between using IFrame or View/List View

- For the IFrame, I give name ’Weekly Reports Dashboard’

- Then, for List, I give name ‘Reports Dashboard

Set Properties screen:

Click Set...

Save and Publish All Customizations.

7. Go to the Report and add the Reports

Don’t forget to set ‘Categories’ and ‘Display In’ based on your Criteria you’ve set for your View.

You also can attach or upload images, .PDF, .txt, and .rar as well.

8. Go to the Dashboard and see the result:

*Weekly Report Dashboard (IFrame)

*Reports Dashboard (List View)

9. Download the Reports

Now, let’s do testing the result for this user requirement’s solution

*Report Dashboard (List View)

When you do double click, it will Run the Report immediately.



So that, to download this Report, user should go to the View from Dashboard:



Then it will open the View



And by doing double click action on the file link it will automatically download the file



*Weekly Report Dashboard (IFrame)

Since it will directly open the view, then the user just needs to do double click:



10. You can upload files through the Reports and the other users can download the files.

This is what we can do if they want to upload and download reports or files or documents from CRM, especially, can access from Dashboard.