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

3 comments:

naher88 said...

This is a good article & good site.Thank you for sharing this article. It is help us following categorize:
digital supply chain management, Delivering high-quality service for your business applications,
Solutions for all Industries,
Getting your applications talking is the key to better business processes,
Rapid web services solutions for real business problems,
Understanding Your Data is the Key to Good Decision-Making,
Web-based Corporate Document Management System,
Outsourcing Solution,
Financial and Operations Business Intelligence Solution,

prologic-corp

Omega software 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

Omega software 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