Wednesday, May 22, 2013

Show Members n Target Product of a Campaign in MS CRM 2011


Very common requirement to show all members of a campaign and the target products of a campaign in Report. Below query returns the same.

-- Get Members of a Campaign

SELECT DISTINCT EntityId,
CASE
WHEN LM.entitytype=1 THEN 'ACCOUNT'
WHEN LM.entitytype=2 THEN 'CONTACT'
WHEN LM.entitytype=3 THEN 'Lead' END AS 'CUSTOMER TYPE',

CASE
WHEN LM.entitytype=1 THEN ACCOUNT.Name
WHEN LM.entitytype=2 THEN CONTACT.FullName
WHEN LM.entitytype=3 THEN Lead.FullName END AS 'CUSTOMER NAME'

FROM FilteredListMember LM
LEFT JOIN Account ON ACCOUNT.AccountId=EntityId
LEFT JOIN CONTACT ON CONTACT.ContactId=EntityId
LEFT JOIN Lead ON Lead.LeadId=EntityId
WHERE ListId IN
(Select entityid From FilteredCampaignItem where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28' and entitytype=4300)

-- Get Target Products of a campaign.

SELECT DISTINCT entityid, Campaign = (SELECT Name FROM Campaign where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28'),
P.ProductNumber, P.name as productname
FROM
FilteredCampaignItem CMP
JOIN Product P ON P.ProductId=CMP.entityid
where campaignid = 'B5FCD75A-A1AD-E211-854C-000C29749A28' and entitytype=1024