Announcements
2,139
Hi All
I'm creating a report in SSRS that should include all the activities with the name and surname of the client for who they are related. I'm using in my query the views activitypointer but there i cannot find the fields from and to. Any idea how to create correct the query?
*This post is locked for comments
Hi Aileen,
I have a requirement to fetch the Required/ Optional attendees for an appointment against a customer in SSRS Report.
These required and Optional attendees can be a User OR a Contact record in CRM. Once these are fetched i need to further drill down basis the retrieved set to find their respective Role/ Business Unit information from their respective User/Contact record.
So it needs to be in the Tabular Format where Columns would be Attendees then their Role and BU info.
I am using Dynamics 365 v9.0 Online.
Can you please help?
Regards,
Prakhar
Hi Aileen, this is brilliant, thank you as well! I've been tasked to get an activity count for each of our Contacts, with regards to just E-mails and Phone Calls, where the 'Contact' is either on the From, the To, or the Regarding. I have most of this now thanks to you. You made a comment in your query: "you can choose to show by customer/system user participation". So basically I'm only caring about the Contact being in those from/to/regarding fields. Is there a way of qualifying this so that it's an efficient query, and not just doing a where - sender = contact or to recipient = contact or regarding = contact ? Thanks.
Hi Aileen,
What if I have to go one level up?
For example:Show all accounts,its child contacts and all activities related to contacts(including parties)
Hi Aileen, thank you for your help again!
I think you can use this Query rather than grouping in SSRS.
select *
from(
SELECT
ActPointer.ActivityId,
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'--, 'Organizer', 'Owner', 'Regarding', 'Required attendee'
)
--and actPointer.activityid = '54C8B7A1-2637-E411-9454-001CC4EECDD6'
) act
pivot
(
max(CustomerName)
for participationtype in (Sender, [To Recipient])--, Organizer, [Owner], Regarding, [Required attendee])
) piv;
Result:
But, you cannot use the customer surname since they are two different record and will product two different records.
You need to use one of them only (customer name or surname)
Hope this helps!
Thanks.
Thanks for your help again. I have added leads to my reports too.
Aileen i have one more question)
It is possible to merge two rows in SSRS? As you can see below i have for an activityid two records (To and From fields). In SSRS i need that the info was in one line. I'm trying do it with groups but without success yet)
SQL:
SSRS:
Hi Betlerjuice,
Additional Info, Activity Party also involves Lead as well, so if you need to include the Lead, see my below code, I highlighted the Lead section.
SELECT 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 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
and participationtypemaskname in
(
'Sender', 'To Recipient', 'Organizer', 'Owner', 'Regarding', 'Required attendee'
)
Thank you.
Hi Aileen. Thank you a lot. I could create my report with your help!!
Yes, the from and to fields are only virtual and not stored as real column in the SQL Database, so you cannot find using standard query.
Basically, every time a record with type = Activity (can be Phone call, email, task, appointment, etc) is created, the CRM will create records in ActivityPointer table and ActivityParty Table.
CRM has each entity stored in the SQL Table as well for each activity type, such as entity Phone Call, Email, Task, and Appointment, which they are having different fields, From and To fields are not in all of the entity, like Appointment, it has require attendee or organizer. So that, it can be various. But, any related customer or system user for each activity will be stored in the ActivityPointer and ActivityParty.
The 'From', 'To', 'Organizer', etc is just a mask to indicate the participation type of the parties.
It is not an easy query. So that, you should combine the ActivityPointer table with the ActivityParty.
This is the query that can help you.
SELECT distinct(ActPointer.ActivityId), ActPointer.activitytypecodename AS activitytype,
ActPointer.subject AS subject,
Coalesce(Account.name, Contact.fullname, SystemUser.fullname) as CustomerName,
Coalesce(Account.name, Contact.lastname, SystemUser.lastname) as CustomerSurname,
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)
-- you can comment these lines
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
left join FilteredSystemUser As SystemUser on ActParty.PartyId = SystemUser.systemuserid
Where ActParty.PartyIdName is not null
--you can choose to show by customer/system user participation
and participationtypemaskname in
(
'Sender', 'To Recipient', 'Organizer', 'Owner', 'Regarding', 'Required attendee'
)
And here is the result:
Hope this helps!
Thanks.
#1
Mohamed Amine Mahmoudi
83
Super User 2025 Season 1
#2
Community Member
54
#3
dkrishna
6
Share
Report
All responses (
Answers (
