locked
Custom SSRS Report for CRM 2013 for single record. RRS feed

  • Question

  • I need to create  a SSRS report with SQL Queries in context of a single record like i need to run the report in the context of a single contact record.

    I also need to use CRMAF for this as my query will be simple.

    and i also need to get the attributes value from the lookup in this record i know that can be done by using joins but can u give me an example.

    Thats i need to do in CRM 2013 on premise.

    For example i need to run this for contact record on which i need to get company name, fullname and from owner lookup i need to get the owner internal email address and i need to show them on my report.

    I will really appreciate your help.

    Regards

    Deepak Jangra


    Deepak Jangra

    Monday, August 11, 2014 7:23 PM

Answers

  • Hi Deepak,

    While not exactly the direct sample for your scenario, here is a related example that may be of some help as it shows you how to the use CRMAF correctly.

    In this example, I'm referencing the quote entity but also pulling in data from the contact, and system user entity (similar to your scenario above where you need to pull in an attribute (email) based on the system user who is the owner of the record).

    Another useful piece of information which you may already know is that the Account and Contact entities are linked via the 

      [PrimaryContactId] which is held on the AccountBase and looksup the contactid which is the primary key of the contact entity.

    A drawback of the CRMAF** approach is that it can only filter on a single dataset so if you need to use multiple datasets in your SSRS (it doesnt sound like that's needed in your scenario) each of which is to be contextually filtered when the report is run, you then create multiple datasets and separately declare a parameter variable which is then used to filter each of the datasets with a where constraint linked to the parameter variable.

    SELECT     CRMAF_Filteredquote.quoteid, CRMAF_Filteredquote.quotenumber, qdetail.productidname, qdetail.quantity, qdetail.extendedamount, qdetail.priceperunit, 
                          contact.fullname AS ContactFullName, ISNULL(contact.address1_line1, '') + ' ' + ISNULL(contact.address1_line2, '') + ' ' + ISNULL(contact.address1_line3, '') 
                          + ' ' + ISNULL(contact.new_countyname, '') AS ContactFullAddress, ISNULL(u.address1_line1, '') + ' ' + ISNULL(u.address1_line2, '') + ' ' + ISNULL(u.address1_line3, '') 
                          + ' ' + ISNULL(u.address1_city, '') AS workaddress, u.mobilephone, u.fullname, u.internalemailaddress, u.title, u.address1_fax, u.address1_telephone1
    FROM         FilteredQuote AS CRMAF_Filteredquote LEFT OUTER JOIN
                          FilteredQuoteDetail AS qdetail ON CRMAF_Filteredquote.quoteid = qdetail.quoteid INNER JOIN
                          FilteredContact AS contact ON CRMAF_Filteredquote.customerid = contact.contactid INNER JOIN
                          FilteredSystemUser AS u ON CRMAF_Filteredquote.ownerid = u.systemuserid
    

    Hope this helps


    John



    Monday, August 11, 2014 9:16 PM

All replies

  • Hi Deepak,

    While not exactly the direct sample for your scenario, here is a related example that may be of some help as it shows you how to the use CRMAF correctly.

    In this example, I'm referencing the quote entity but also pulling in data from the contact, and system user entity (similar to your scenario above where you need to pull in an attribute (email) based on the system user who is the owner of the record).

    Another useful piece of information which you may already know is that the Account and Contact entities are linked via the 

      [PrimaryContactId] which is held on the AccountBase and looksup the contactid which is the primary key of the contact entity.

    A drawback of the CRMAF** approach is that it can only filter on a single dataset so if you need to use multiple datasets in your SSRS (it doesnt sound like that's needed in your scenario) each of which is to be contextually filtered when the report is run, you then create multiple datasets and separately declare a parameter variable which is then used to filter each of the datasets with a where constraint linked to the parameter variable.

    SELECT     CRMAF_Filteredquote.quoteid, CRMAF_Filteredquote.quotenumber, qdetail.productidname, qdetail.quantity, qdetail.extendedamount, qdetail.priceperunit, 
                          contact.fullname AS ContactFullName, ISNULL(contact.address1_line1, '') + ' ' + ISNULL(contact.address1_line2, '') + ' ' + ISNULL(contact.address1_line3, '') 
                          + ' ' + ISNULL(contact.new_countyname, '') AS ContactFullAddress, ISNULL(u.address1_line1, '') + ' ' + ISNULL(u.address1_line2, '') + ' ' + ISNULL(u.address1_line3, '') 
                          + ' ' + ISNULL(u.address1_city, '') AS workaddress, u.mobilephone, u.fullname, u.internalemailaddress, u.title, u.address1_fax, u.address1_telephone1
    FROM         FilteredQuote AS CRMAF_Filteredquote LEFT OUTER JOIN
                          FilteredQuoteDetail AS qdetail ON CRMAF_Filteredquote.quoteid = qdetail.quoteid INNER JOIN
                          FilteredContact AS contact ON CRMAF_Filteredquote.customerid = contact.contactid INNER JOIN
                          FilteredSystemUser AS u ON CRMAF_Filteredquote.ownerid = u.systemuserid
    

    Hope this helps


    John



    Monday, August 11, 2014 9:16 PM
  • Thanks a lot John for your reply,

    It helps let me try it if it resolve my issue.


    Deepak Jangra

    Monday, August 11, 2014 9:46 PM
  • Thanks a lot John,

    It works for me



    Deepak Jangra

    Tuesday, August 12, 2014 8:13 AM