locked
(ssrs) Knowing the Parent Account on an activity Set regarding to a Contact/Opportunity RRS feed

  • Question

  • We are making ssrs reports and we need to group activities by their parent account, even if they are directly set regarding an opportunity or contact instead (child's of the account).

    If account 'company a' and contact 'person b' and opportunity 'sale c' all have the same parent, the fetchxml should know it.

    Is this possible through fetchxml alone, or must I use a workflow to flag a value so that the report can run?

    Is this possible relatively conventionally through fetchxml without any workarounds?


    Monday, January 12, 2015 10:25 PM

All replies

  • I'll ask another way.

    How could I basically select this (not official sql) in fetchxml:

    select account.accountid as accoutname from activities join account on set regarding

                       Union

    select contact.parentaccountid as accountname from activities join contact on set regarding

                       Union

    select opportunity.parentaccountid as accountname  from activities join opportunity on set regarding

    grouped by accountname

    It's possible SSRS can handle the grouping part as opposed to the fetchxml.

    Tuesday, January 13, 2015 3:34 PM
  • You can't achieve this in FetchXml. It doesn't support Unions, or any form of sub-queries. If you have to use FetchXml, then you could create an additional lookup from the activity to account entity, and use workflows or plugins to update it from the relevant contact, account etc. However, you can't customise the base activitypointer entity, so you'd have to add the lookup to each activity type, which may just move the problem to a different place if you need to report on all activity types

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, January 13, 2015 5:01 PM
    Moderator
  • Thanks for the reply. We're online so that rules out SQL and we're stuck with fetchxml.

    Another possibility would be to pull down two different datasets - one for Account Activities and another for Opportunity/Contact Activities. Then the dataset could be grouped in the report itself, not in the fetchxml.

    If possible it seems like a good solution.   Is it possible?  If not, it seems we're stuck with workflows.

    Tuesday, January 13, 2015 8:07 PM