Develop CRM SSRS Report in Local using “Windows Authentication” without really using the Joined Domain Windows Account

Hi all,

Just want to share how to develop SSRS Report that connect to the CRM Database, but using Windows Authentication.

Background

Sometimes we need to develop custom report because report wizard cannot support the complex report and complex UI or filter or grouping, so that is the reason why we still need to rely on this SSRS.

As we know that using FilteredView is the recommended way query CRM Database that using Security Layer applied on the query, but this FilteredView we can only run if we use the valid CRM User, which is not other than using Windows AD Account registered in AD and CRM. Then, we found problem that mostly developers are using their own local Visual Studio, of course we cannot use the server to install VS and let developers to use that imagine we have more than 10 developers trying to access and remote desktop to server? So troublesome.

And here are the list that what developers usually do to develop report:

1. Access the Server (remote Desktop), installing VS and develop there, using Windows Authentication, so that you can do query using FilteredView as usual

It is a good idea to settle everything, but will make burden on the server and cannot imagine if many developers want to develop and access at the same time, who will kick and be kicked everytime.

2. Other way is we use SQL Authentication using “sa” account to access, but this can be troublesome because as mentioned before, FilteredView, you can only access using CRM AD User. What you need for this is just access to the SQL Server Database.

3. Then, try another way is you restore the database locally to your SQL Server Express probably, then run the query locally, but, again, you cannot use FilteredView in your Query. This also needs you to do regular backup if there is changes in the database schema

For point number 2 and 3, you can do that and can run the report so long you have access to the Database, but the bad thing is you cannot run FilteredView and you must using standard View then before you deploy to CRM you must manually adding the “filtered” keyword before your View name and you cannot run the report view the data act on behalf of a CRM User based on the security because you are not CRM user running that time.

Solution

As we know that solution number 1 is good for faster development without manually changing, but might result other weakness in other areas, so we need to combine those 3 items.

Now this is what you need to do:

1. Run the cmd (CTRL + R) then type “cmd”
2. And run this command:

runas /netonly /user:yourdomainname\yourusername "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe"

3. Example:



Then it will prompt you to fill in password:

Result

I found this solution is quite useful because we can develop and run preview the report locally without remote desktop-ing to server and can use the recommend FilteredView query.
Then here is the result:

*Before:

Keep loading database names



And in the end prompting this error message or when you click the “Test Connection” as well:



*After

Now we can successfully login and can retrieve the database



Hope this helps!

How to Show the Missing ‘Report Data’ in SSRS Development

A quick post, just in case you are creating SSRS or continue from anyone project and you found that you lost the ‘Report Data’ properties box, so here I am, facing the same issue.

Solution

A simple solution: Just press: CTRL + ALT + D

Result

Hope this can save your day.
Thanks!

Handle Page Header Data in CRM Custom Report/SSRS with Multiple Split Pages

In my previous post, I was talking about how to design CRM Report for Multiple Records appearing in Multiple Split Pages for each record. 

Now, how to make the header follow record for each page?

If you are using Pre-Filtering report, you can filter the data you want to display in to the report, but how to handle the Page Header (if you use Page Header instead of using Tablix Row Header).

As we know, in SSRS we have 3 parts: Report Header, Body, and Report Footer.

So let’s say you have pre-filtering report, you only display the report by record through record Form, it means that the record that you want to display is only one record.

Then, you notice the Page Header, Body, and Page Footer will display correctly.

*You have Quote Id + Revision in the Header.

Now, what if you select multiple record as filter?

For example you edit the Filter to show all Quotes from this Potential Customer

Or you select multiple Quote records.

Result:

Using my explanation in my previous post, you can have multiple separated page for each Quote record (not 1 report for all Quote records) as per your requirement.

First Page:

*You notice the Quote ID that will appears in the Report Header.

Second Page:

Last Page:



If you notice, the Page Header will display the same thing even though the records are in split page and in fact, in body it shows different record.

So what to do?

Basically, in the page header if you just use the field from DataSet and drag it to the field, it will give you only the first one.

This is the expression:

="Proposal Ref: " & 
First(Fields!quotenumber.Value, "DataSet1") & "-" &
First(Fields!revisionnumber.Value, "DataSet1") &

The problems are:

1. You cannot use DataSet1 anymore as your scope because as mentioned in my previous post, you need a Page Break set by Group. So that if you use DataSet as your scope, it will get the wrong data since your purpose is to break the multiple records to multiple page using Page Break by each Quote Id.

2. SSRS does not allow you to set the tablix group name as your scope, you can only use DataSet.

Using the pre-filtering record, your dataset can be grown if you select many records not a single record.
It is similar to IN Clause in SQL if you are familiar.

So example your Quote record is 23 records with multiple Quote ID:

No	Quote ID	Revision Number	Potential Customer
1	QUO-01104-H1R4G3	0	A. Datum Corporation (sample)
2	QUO-01105-A1S5U5	0	A. Datum Corporation (sample)
23	QUO-01135-C1B7U7	0	A. Datum Corporation (sample)

If you use ‘first’ in syntax expression in the header it will give you the first record only (QUO-01104-H1R4G3-0)

It will be safe if you only show report in the each record Form, but it won’t if you show the report in Report Area or List Area.

So, how to make the Header to show correct Quote ID for each record same as shown in the body?

You can do these following tips and tricks based on my experience.

1. You can use Tablix Header instead of using Page Header

Tablix Header has each Row Header that you can repeat, but sometimes you found it is not easy, because maintaining page header is easier and you can have 3 different parts of layout that will not affect each other if got any changes.

2. You can use Report Parameter

If you know the parameter sent from the CRM Report that you can use it, but you need to Query with Where Clause to your data set, which if you are using pre-filtering report (standard one), you cannot control the parameter sent by CRM, so this is cannot be used, unless you pass a custom parameter to the report or you use javascript to open the report or you use Report Parameter and let the users to choose when run the SSRS.

3. You can use a hidden textbox.

You can use a hidden textbox, placing it into the Report Body.





And use the Expression (but don’t use ‘FIRST’)



And Make sure your scope is not DataSet, but you should use the Group Name as scope, see this.



*Now, you notice the group name can be the scope, different from PageHeader’s textbox.

Please refer to my previous post to get more detail info about this.

Then, set this expression into your page header textbox.

=ReportItems!textBoxQuoteNumberBu.Value

Change to your textbox Name.

*But, this tip got weakness, if your data is too long shown in report body into one page each Quote record (if you convert to PDF or Word, etc), it will not give you the value in next page because in second page and so on, the hidden textbox does not exist to the second page of each Quote record, etc.

Example:



*First Page only fits until 10 lines for example:

Second Page:

(To show next item number 11 and 12)



*Because your hidden text only occupied one page each group, then in the second page if your data having more than 1 page, it will show blank value.

4. You can utilize the Page Name.

Go to your Row Group and go to Page Properties that you set the Group to have page break for each record.



Then use your PageName, set the expression for example:

=First(Fields!quotenumber.Value, "list1_quote") & "-" &
First(Fields!revisionnumber.Value, "list1_quote") 

*Now, you notice the group name can be the scope, different from PageHeader’s textbox.

Now, change your textbox in the Page Header and set this expression:



Set to:

=Globals!PageName

And see the result after you do those mentioned ways.

Page 1



Page 11



And so on…

I think it is the best idea, beside you can have your expected header, you can also have advantage once you converted the report to excel file, it will give you the different sheet name:



Hope this helps!

My blog is not only talking about CRM and its UI or features, you can see my other posts about SSRS here:

http://missdynamicscrm.blogspot.com/search/label/SSRS


Thanks.

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.

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!