December 15, 2011 at 12:51 pm
Hello,
I'm trying to develop a report using Dynamics CRM tables using BIDS connecting to a SQL Server.
It's a marketing report using the following entities:
Campaign
Campaign Response
Activity Party
Contacts
Leads
Accounts
Opportunities
For those not familiar with Dynaimcs CRM entities and their relationships activity partty is essentially a bridge table linking one entity to another. In this case campaign response is connected to contacts, leads and accounts.
I would like to report on all entites in 1 dataset but i'm unsure how I'd do this and I'm looking for some guidance.
If I could have a UNION subquery that looks something like this:
SELECT accountid AS entityid FROM FilteredAccount
UNION
SELECT contactid AS entityid FROM FilteredContact
UNION
SELECT leadid AS entityid FROM FilteredLead
within the existing SQL i think that would work, though i'm not even sure that this is possible, hence why I'm asking here.
Any ideas?
December 15, 2011 at 1:11 pm
I'm not familiar enough with Dynamics databases specifically to say yes/no on what you're doing.
However, the basic practice of what you're doing looks sound. Do that in a CTE or regular derived table (we need a bacronym for those!), and then use that for the rest of the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 1:46 pm
Thanks, That seems a very obvious answer once I read it. D'oh! Which leads me to see a fatal floor in my plan and subsequently onto my next question.
So for contactid and accountid link to customerid within an Opportunity record but leads link to another field within that record - originatingleadid. I guess that I need some sort of OR clause...
Any further suggestions?
Here's the SQL so far with the emboldened text highlighting the problem that needs resolving:
SELECT FilteredCampaign.name AS CampaignName, FilteredOpportunity.estimatedvalue, FilteredOpportunityProduct.synergy_monthlyrevenue,
FilteredOpportunityProduct.baseamount, FilteredOpportunityProduct.productdescription, FilteredOpportunityProduct.productidname,
FilteredOpportunityProduct.synergy_quantity, FilteredOpportunityProduct.synergy_stagename, FilteredOpportunityProduct.synergy_tenuremonths,
FilteredOpportunityProduct.synergy_statusname, FilteredSystemUser.fullname, derivedtbl_1.entityid
FROM FilteredCampaign INNER JOIN
FilteredCampaignResponse ON FilteredCampaign.campaignid = FilteredCampaignResponse.regardingobjectid INNER JOIN
FilteredActivityParty ON FilteredCampaignResponse.activityid = FilteredActivityParty.activityid INNER JOIN
FilteredOpportunity ON FilteredCampaign.campaignid = FilteredOpportunity.campaignid INNER JOIN
FilteredOpportunityProduct ON FilteredOpportunity.opportunityid = FilteredOpportunityProduct.opportunityid INNER JOIN
FilteredSystemUser ON FilteredOpportunityProduct.ownerid = FilteredSystemUser.systemuserid INNER JOIN
(SELECT accountid AS entityid
FROM FilteredAccount AS FilteredAccount_1
UNION
SELECT contactid AS entityid
FROM FilteredContact AS FilteredContact_1
UNION
SELECT leadid AS entityid
FROM FilteredLead) AS derivedtbl_1 ON FilteredActivityParty.partyid = derivedtbl_1.entityid AND FilteredOpportunity.customerid = derivedtbl_1.entityid AND
FilteredOpportunity.originatingleadid = derivedtbl_1.entityid
December 15, 2011 at 2:01 pm
I'm not clear enough on the data structure you're dealing with.
You don't have to link a table to every other table with a matching column. Are you trying to match some rows to one part of the query and other rows to another part?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 2:20 pm
Yes and no.
I'll try to explain. The way our sales process works after a marketing campaign is like this:
Campaign is sent out to multiple entities(essentially a different tables for each type in this case accounts, contacts and leads). Campaign responses are generated for interested parties. When campaign responses are converted to opportunities a unique link is created in the activityparty table between campaign response and either a lead, contact or account.
With me so far? I hope so. I need data from the campaign, opportunity and opportunityproduct tables to show in the report but I need the closed loop from opportunity to campaign and that's where the campaign response comes in.
Thanks for your time btw.
December 15, 2011 at 2:46 pm
This seems to work:
SELECT DISTINCT
FilteredCampaign.name AS CampaignName, FilteredOpportunity.estimatedvalue, FilteredOpportunityProduct.synergy_monthlyrevenue,
FilteredOpportunityProduct.baseamount, FilteredOpportunityProduct.productdescription, FilteredOpportunityProduct.productidname,
FilteredOpportunityProduct.synergy_quantity, FilteredOpportunityProduct.synergy_stagename, FilteredOpportunityProduct.synergy_tenuremonths,
FilteredOpportunityProduct.synergy_statusname, FilteredSystemUser.fullname, derivedtbl_1.entityid
FROM FilteredCampaign INNER JOIN
FilteredCampaignResponse ON FilteredCampaign.campaignid = FilteredCampaignResponse.regardingobjectid INNER JOIN
FilteredActivityParty ON FilteredCampaignResponse.activityid = FilteredActivityParty.activityid INNER JOIN
FilteredOpportunity ON FilteredCampaign.campaignid = FilteredOpportunity.campaignid INNER JOIN
FilteredOpportunityProduct ON FilteredOpportunity.opportunityid = FilteredOpportunityProduct.opportunityid INNER JOIN
FilteredSystemUser ON FilteredOpportunityProduct.ownerid = FilteredSystemUser.systemuserid INNER JOIN
(SELECT accountid AS entityid
FROM FilteredAccount AS FilteredAccount_1
UNION
SELECT contactid AS entityid
FROM FilteredContact AS FilteredContact_1
UNION
SELECT leadid AS entityid
FROM FilteredLead) AS derivedtbl_1 ON FilteredActivityParty.partyid = derivedtbl_1.entityid AND FilteredOpportunity.customerid = derivedtbl_1.entityid OR
FilteredOpportunity.originatingleadid = derivedtbl_1.entityid
I don't totally understand SQL so I'm curious to know whether this is sound code?
Lea
December 16, 2011 at 6:19 am
I really can't speak to the validity of the code. Yes, it's a valid query, in that in compiles and returns results. But as far as being business-correct, there's no way I can speak to that.
I suggest testing it by breaking it in three parts, one for each "union" query, and running each of those. That way, you make sure you're getting results for each. Otherwise, you might be masking an internal error. If you're only getting expected results from one of the queries, and one or both of the others are empty, you won't see that when they're all unioned together, but you will if you isolate them for testing.
That'll at least help you validate the results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply