locked
How to create a report that references Accounts, Opportunities, and Activities. RRS feed

  • Question

  • Hi everyone,

    This must be more simple that it's turning out to be... I am trying to create a report that shows accounts with open opportunists (via filtering, which works fine), the actual opportunities that are open, and any activities that are related to that account.

    The most successful attempt to accomplish this so far was to select "Accounts" as the primary entity, and "Activities" as the related. I am able to filter accounts with open opportunities by choosing "Opportunity" as the related entity, but I am unable to find a way to add a column that would display those opportunities on the actual form. That's what doesn't make sense to me.. I can use the relationship between Accounts and Opportunities when I filter data, but not when I am selecting columns.

    Is this a relationship problem? If so, is it configurable? Are there any other ways to accomplish this?

    Any help at all would be greatly appreciated.

    Thank you,

    Victor

    Thursday, August 29, 2013 1:22 PM

Answers

  • The problem comes with needing the Activities that are related to the Account. As there is a 1:N relationship between Account and Opportunity and also a 1:N relationship between Account and Activities, there's no way to simply produce a report in CRM with both Opportunities and Activities. As I see it, you have 3 options. either:

    • Have all Activities relating to Opportunities rather than Accounts - all 3 entities could then be searched for in the report's criteria.
    • Produce 2 reports for Activities relating to Account and Opportunities relating to Account and, once produced, amalgamate them or summarise them in Excel.
    • Write a custom report in BIDS or Report Builder but this is not an easy option. This can then be imported.
    • Proposed as answer by Jon_Evans Thursday, August 29, 2013 2:32 PM
    • Marked as answer by IT_Initiate Thursday, August 29, 2013 4:01 PM
    Thursday, August 29, 2013 2:32 PM

All replies

  • The problem comes with needing the Activities that are related to the Account. As there is a 1:N relationship between Account and Opportunity and also a 1:N relationship between Account and Activities, there's no way to simply produce a report in CRM with both Opportunities and Activities. As I see it, you have 3 options. either:

    • Have all Activities relating to Opportunities rather than Accounts - all 3 entities could then be searched for in the report's criteria.
    • Produce 2 reports for Activities relating to Account and Opportunities relating to Account and, once produced, amalgamate them or summarise them in Excel.
    • Write a custom report in BIDS or Report Builder but this is not an easy option. This can then be imported.
    • Proposed as answer by Jon_Evans Thursday, August 29, 2013 2:32 PM
    • Marked as answer by IT_Initiate Thursday, August 29, 2013 4:01 PM
    Thursday, August 29, 2013 2:32 PM
  • This is what I sort-of figured..

    Thanks!


    • Edited by IT_Initiate Thursday, August 29, 2013 4:01 PM Typo
    Thursday, August 29, 2013 4:01 PM