locked
Filtered SQL query to fetch related Contacts of an Account in CRM 2011 ? RRS feed

  • Question

  • Hi,

    As i am new to SSRS, i have a question here that

    how can i fetch related Contacts of a Perticular Account in SSRS Report.

    I have created Account Detail Report in which i am showing my all Account details in one Table through FilteredAccount table..

    Now i want to fetch its related Contacts below to this table(may be in this table or create a new table ,pls suggest).

    So can anyone help me in SQL query for this ?

    I have 3 subgrids in Account form, and the same way i want to fetch all contacts view vise. Is this possible ?


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, August 22, 2013 7:01 AM

Answers

  • Hi,

    Sure. See below.

    SELECT c.contactid, c.firstname, c.lastname, a.accountid, a.name
    FROM FilteredContact c
    INNER JOIN Filterednew_account_contact ac ON c.contactid = ac.contactid --Replace new_account_contact with your relationship intersect name
    INNER JOIN FilteredAccount a ON ac.accountid = a.accountid
    WHERE a.accountid = @accountid


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    • Marked as answer by DynamicsCRM31 Friday, September 6, 2013 5:03 AM
    Tuesday, August 27, 2013 11:33 AM

All replies

  • Hi,

    This can be done quite easily. I suggest you use a new table.

    Use something similar to the SQL below:

    SELECT contactid, firstname, lastname
    FROM FilteredContact
    WHERE parentcustomerid = @accountid

    where @accountid is the parameter holding the id of the account.


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    Thursday, August 22, 2013 7:16 AM
  • If you ever wanted to change the layout of your report, you could also consider performing a JOIN on the Account and Contact filtered views. For example:

    SELECT a.name, c.fullname
    FROM FilteredAccount a
    INNER JOIN FilteredContact c 
    ON a.accountid = c.parentcustomerid
    Of course this is only a simple example that displays the name of the Account and the fullname of any related Contacts.
    Thursday, August 22, 2013 7:22 AM
  • Hello Michael,

    Thank You.

    Can you tell me how to pass this parameter @accountid?


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Thursday, August 22, 2013 10:33 AM
  • Hi,

    You can go with two different ways.

    - You can create a dataset for Account (SELECT accountid FROM FilteredAccount).

    - Create a SSRS report parameter of varchar type and assign the default value of that parameter to Account Dataset fields. you can set the Report parameter value. Right click the parameter and in the default value section you can set the dataset and default field from dataset.

    - After that you can query the contacts

    SELECT *
    FROM FilteredContact
    WHERE parentcustomerid = @accounted

    Another approach will be left outer join.. Left outer join is required when we want account should be returned even if it doesn't have related contact.

    SELECT acc.accountid, con.field
    FROM FilteredAccount acc
    Left Outer JOIN FilteredContact con ON acc.accountid = con.parentcustomerid

    Hope this helps!

    Thursday, August 22, 2013 11:08 AM
  • Hi,

    The @accountid is merely a parameter to to DataSet.

    If your report is displaying a single Account record then you must be passing the AccountId of the account into the report. If you include the @accountid parameter in the SQL for the DataSet retrieving contacts, then it will automatically be added as a parameter to the DataSet. When you add the contact table on the report and select the contact DataSet, you will need to pass it the @accountid parameter which you should has a report parameter. Make sense?


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    Thursday, August 22, 2013 1:03 PM
  • Hi,

    Actually i am having no parencustomerid field on form (removed through customization)

    instead we have created M:M relatioinships between Accounts and Contacts.

    So what would be the Filtered Query in this case ?


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Monday, August 26, 2013 10:54 AM
  • Hi,

    Ok, in that case you will need to join in the intersect table. The name of the intersect table can be found in the "Relationship Entity Name" field of the N:N relationship form. The SQL will be something like:

    SELECT c.contactid, c.firstname, c.lastname
    FROM FilteredContact c
    INNER JOIN Filterednew_account_contact ac ON c.contactid = ac.contactid --Replace Filterednew_account_contact with your relationship intersect name
    WHERE ac.accountid = @accountid


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365


    • Edited by MJFPalmer Monday, August 26, 2013 11:02 AM Omission of Filtered in view names
    • Proposed as answer by MJFPalmer Monday, August 26, 2013 11:02 AM
    Monday, August 26, 2013 11:00 AM
  • Can you provide me the same query combined with FilteredAccount,

    so that i can put that query in one single table and can get Accounts and Contacts in one table.

    Otherwise it would become cumbersome to handle 2 tables in grouping.


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Tuesday, August 27, 2013 11:00 AM
  • Hi,

    Sure. See below.

    SELECT c.contactid, c.firstname, c.lastname, a.accountid, a.name
    FROM FilteredContact c
    INNER JOIN Filterednew_account_contact ac ON c.contactid = ac.contactid --Replace new_account_contact with your relationship intersect name
    INNER JOIN FilteredAccount a ON ac.accountid = a.accountid
    WHERE a.accountid = @accountid


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    • Marked as answer by DynamicsCRM31 Friday, September 6, 2013 5:03 AM
    Tuesday, August 27, 2013 11:33 AM
  • Can you provide me the same query combined with FilteredAccount, then i provide you Oracle Video tutorials of that query.
    Tuesday, August 27, 2013 11:40 AM