Hi everyone,
I am new to SSRS and trying to create a report using a CRM database that we have on site. We are using MS CRM 2013 and we have a local DB that is copied from the cloud DB. The report that I am trying to create is supposed to count the number of campaign
responses and pull all of the passed leads, accounts, leads, opportunities, and appointments related to the campaign response. I am not very familiar with the tables so ill do my best to explain. I have the campaignresponse table joined to the activityparty
table on activityid. From the activityparty table I have the account and lead tables unioned on accountid and leadid, called accounted. its linked to activityparty from accountid to partyid. From that union, it splits off to appointment, opportunity, and new_passedlead.
I tried to get everything from one base query and use the expressions in SSRS to filter what I needed but im not getting the correct data. The number of campaign responses and passed leads I can get easy enough, but I run into issues with
wrong data in the appointment and opportunity tables. The fields that im using in those two tables are:
Appointment
o
statecode_displayname [“Completed”]
o
new_tertiaryapptpurposenam ["Additional Demonstration", "First Demonstration"]
o
owneridname
·
Opportunities
o
Actualclosedate
o
Statecode [=1]
o
Actualvalue
o
new_saletypefield_displayname [new]
o
new_productinterest_displayname
o
name
o
owneridname
The fields with the [] are the ones im mainly looking at to get the correct numbers. if i run the whole query then the numbers from these two tables are way off and they aren't correct. I know this might not make sense seeing as i am
not very familiar with the DB. Any ideas on what im doing wrong or what might be a better way to do this?
