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

2 comments:

Unknown said...

Keep up the fantastic piece of work, I read few blog posts on this web site and I believe that your site is real interesting and has lots of great information. ERP Software in Mumbai || System Software || CRM Software in Mumbai || MLM Software

Unknown said...

I really appreciate spending some time to talk about that, I believe firmly regarding this and so really enjoy understanding more about this kind of subject.This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something like this. CRM Software || MLM Software in Mumbai || ERP Software || System Software in Mumbai