Design CRM Custom Report/ SSRS with Multiple Record - Multiple Pages

You might have requirement to show report in CRM and we know we can use filteredView + pre-filtering report in CRM. We can construct our dataset with CRMAF_FilteredEntity as the alias name. And we can place the report into the Report Area, List, or Form.

We know that the report can be saved in other format, such as PDF or Word and even you can print it.

We also know that by using the pre-filtering report in CRM, you can have report with reliable contextual data based on the selected records through your selection in the User Interface and based on the location you place the records.

*Form

We know when you enable this pre-filter report, we can run the report based on the record we opened to that form.

*See the ‘Run on Current Record’ context.

Yes, it has pre-filtered by default, by the current record you run.

*List Area

When you enable to display the record in the List Area for related record, then you can select records that you want to include in the report.

*Report Area

You run a report in Report Area, then you can call the ‘Filter function’ which you can select multiple records or all records at once.

So, it seems not too complicated if you know how to use this pre-filtering.

But, have you realized if you want to print out multiple records using pre-filtering report, you want to print those records into multiple split pages, it can be difficult.

Now the problem is coming when you want to show the selected records into different or multiple split pages, that is one record one page.

For example, you want to create a Quote from an Opportunity.

An Opportunity can have more than 1 Quote, let’s say 1 Opportunity 1 Quotes then you want to print all of the 3 quotes from the Opportunity and break them into 3 pages, one record one page. Not only 1 page then show those 3 records in one page.

And another example:

You want to run a report from Quote from the Report Area, which can have hundred records and you want to split them into hundred pages. So one quote, one page.

Everything is fine when you run the report, you can see page header with body split into different page.

Page 1 of 36...

Page 2 of 36

You can see the Header and The body look perfect, just after the header and repeat for each record.

And so on.. Until..the last.

Page 36 of 36

*You notice the first line after the header, It is always consistent shown after the header for each page.

But, what happened if you save to PDF and print it..

It is totally changed, it becomes mix in one page and will continue put next record just after the previous record.

See this picture:

The red block is showing the part belongs to previous record

The blue block is showing the part belongs to the current record which is defined before in the Page 2 of 36.

The first line after the page header now is in the middle of the page and not just after the Page Header part.

Now, you see the total page is 83, it is caused by the Report Page Size and you can see the report is becoming very unstructured.

*Remember, the page size will determine the space that can be used and how many pages will be splitted based on the page size + margin (make sure your Body size + Margin is not larger than the page size), or you might be shocked realizing where the empty whitespace was coming from...

It will show non-stop, no break, and continuously showing the record without any break, which is not what I expected.

Here, you can see the inconsistency when you run the report on the spot or when you save it to PDF. Don’t believe with what you can see before you save it to PDF or print it, because basically the customer will receive the paper with hardcopy and then sign it.

Therefore, you need to tweak your SSRS setting manually to break them into multiple pages based on the record..

Solution:

1. Back to your SSRS BIDS

2. Create a group

I am sure that you should have created the ‘Group’ before, if not then you might need to create a grouping, it can be using List or Tablix. Set the group based on your criteria, for example you want to print 1 Quote one page, then you might need QuoteId as the grouping expression, or you want to print 1 Account 3 Quotes, then you also need to group by QuoteId for each Account.

This group is so important.

*This example is to run report of multiple record of Quote and show the result, both in preview and in print mode as multiple split pages, each Quote will appear in one page, and so on.

Thus, I try to group by QuoteId because QuoteId is I believe it as the Unique Identifier to identify each record in order to split each identity to each page.

3. Group Properties

4. Turn 'the magic' on..

5. Here is how the magic works

Next record

Next record..

Until last record.

*You can see, your data is 'being forced' to split across multiple pages, both in Preview or in Printing Mode.

*This is what I expected..It does not enlarge the data inside and fit it into one page, instead, it is printed in multiple different pages.

If you converted to Excel you can also have different sheet nam (provided you have set the different page name in your SSRS Editor)

You can also reset the page number:

So, if you want to display the the Report in Form, list, and Report Area and you want to split them into different page for each record as advance report then can be printed to customers, you need to set your own Page Break, because after you upload the report to CRM, CRM will not split into different pages, it will show you in the report preview, but when you want to print it, you will not get what you wanted, you need to set your own ‘Page Break’.

*And another tip, I tried to create report with nested group and then I give page break to both of them, it was running well in the ‘Preview’ mode in the BIDS, but when I upload to CRM and run it, I got error, such as ‘Invalid Argument’. Then, I was ruined by this issue, and then actually that was caused by I turn ‘Yes’ the Page Break for each group, including child group resulted error on page when I run the report. Thus, I just use only one group, I choose the smallest group as my grouping, and the only one, no more nested, and ignore the big brother, then set the Page Break to this group and it worked.

Summary:

The key things are:
1. Create a Group, refer to the above solution number 2
2. Apply a Page Break for that Group.

*This post might be useful if you want to print out multiple record or hundred and more records in CRM but you want to display it into split or separate pages and repeated for each page, each record each page.

In the next post I will explain how to handle multiple split pages in SSRS with dynamic Page Header in line with current data/record based on the each record with same dataset using pre-filtering CRM Report.

Hope this helps!

Thanks.

CRM 2011/2013 Understanding The Privilege to Show and Hide 'Write-In Product"

We often have requirement to hide the ‘Write-In Product’ choice for Salesperson to insist them to use Existing Product from Product Master records.

So the first way is by removing the inline grid, change to custom grid which doesn’t have editable in-line grid functionality, so that it will open a new window to Opportunity Product/Quote Product/Order Product/Invoice Product and then you can hide or make the write in Product as read-only field or disable the ‘Select Product’ to make it always ‘Existing’ using Form Editor or can use Javascript onLoad function.

Then there is now way to choose the Write In.

But, what if the users want to hide through this inline grid?

And there is no supported customization using Javascript to hide this.

Actually, there is a privilege managed in Security Role to control this visibility.

Go to the Security Role and go to Sales Tab.

In the Misc. Privileges, you can see the Override ‘entity’ Pricing.

So, for example you want to hide the ‘Write-in Product’ under Opportunity, then don’t give permission to ‘Override Opportunity Pricing’.

And here is the result..

But, unfortunately, this is also make the person assigned to that security role also cannot override the Price, it means always ‘Use Default’ pricing.

*If you want to show the 'Write-in Product' then you just give full access to 'Override Opportunity Pricing'.

*For another entity, you can give or not give permission to Override Invoice Pricing and so on based on your expected entity.

I hope in the next release, the Microsoft product team will split them into two different privileges.

It will work for clients who don’t want to the Salesperson using price different from the Price List assigned to them, some customers will agree to block those two functions.

I hope this helps you if your customer doesn’t allow the Salesperson to overwrite the price.

Thanks!

Create a ‘Current/This Year and Last Year’ Comparison Chart in CRM 2011/2013

Often, we have requirement to have chart showing comparison of sales performance (can be Closed Opportunity, Won Quotation, Paid Invoice, Monthly Sales Result or other parameters of performance) between Last Year and This Year.

So, this is a simple post explaining how to create Comparison chart comparing Last Year This Year Performance without any coding.

Steps:

1. So now I have a custom entity, Monthly Sales Result.
2. Then, let’s create a new view and give criteria for the date field you want to measure, for example Sales Date, CreatedOn, ModifiedOn, Actual Close Date, etc, set to This Year Or Last Year



So now we have a filtered view only showing data in This Year and Last Year.

3. Create a Chart

Then, create a new Chart using that newly created view using this setting:



Save it, Publish and see the result.



Now, you can have a simple comparison chart

Hope this helps!
Thanks.

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!