SSRS with MS CRM 2013 on premises RRS feed

  • Question

  • 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:

    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

    o    statecode_displayname

    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?
    Saturday, October 4, 2014 3:35 AM

All replies

  • hello mate,

    please post the queries that you are using so that we can look at it and compare? Please modify the query to use filtered views if you are not using it.



    Saturday, October 4, 2014 5:53 AM
  • Thanks for the reply, unfortunately all the work I did was at work and I wasn't able to get to it until today. I did find a way around the issue however. I ended up using 4 datasets to pull the numbers I needed and used a lookup to put them in the tablix.
    Tuesday, October 7, 2014 1:40 AM