locked
SSRS tool to build report to CRM (many to many relationships>???) RRS feed

  • Question

  • Hi,

    I am creating Account Summary report which is combination of items per account. I have an Account Entity where I also have a subgrid to add my competitors (Competitor entity).

    How can I go about reflecting that competitor list for that account?? Since it is a subgrid it does not show up in my Advanced Find (I use advanced find to get the report then FetchXML and transfer code to my SSRS tool).

    Any help would be much appreciated!

    Thanks so much!

    I use CRM 2013 online
    Saturday, April 5, 2014 1:32 AM

All replies

  • Hi,

    Here is a simple fetchxml to retrieve many to many records. The entity name is the many to many relationship name. Hope this helps. 

    <fetch mapping='logical'>
    	<entity name='new_account_competitor'>
    		<filter type='and'>
    			<condition attribute='accountid' operator='eq' value='817C812B-31B3-E311-B7FF-005056A92F69'/>
    		</filter>
    	</entity>
    </fetch>


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Saturday, April 5, 2014 8:17 AM
  • Hi Eric,

    Thanks so much for above and answering my other questions..little unclear on what I need to modify.. Is it like below? Would I just add all of those lines under my Account XML?

    <fetch mapping='logical'>
    <entity name='competitor'>
    <filter type='and'>
    <condition attribute='accountid' operator='eq' value='name'/>
    </filter>
    </entity>
    </fetch>

    not sure if this will help.. When I created a table in SSRS I have 1 dataset for Account I pull some account details which are all fields in my account entity but then I also want to pull this subgrid of competitors..  competitor subgrid is on my account entity..

    The first XML is for my Account (this works great)

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="account">
    <attribute name="name"/>
    <attribute name="accountid"/>
    <attribute name="parentaccountid"/>
    <attribute name="sp_tieringsegment"/>
    <attribute name="sp_clientsperspective"/>
    <order attribute="name" descending="false"/>
    <filter type="and">
    <condition attribute="statecode" operator="eq" value="0"/>
    <condition attribute="accountid" operator="eq"  value="@name"/>
    </filter>
    </entity>
    </fetch>

    The 2nd XML is for my competitor

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="competitor">
    <attribute name="name"/>
    <attribute name="websiteurl"/>
    <attribute name="competitorid"/>
    <order attribute="name" descending="false"/>
    <link-entity name="sp_account_competitor" from="competitorid" to="competitorid" visible="false" intersect="true">
    <link-entity name="account" from="accountid" to="accountid" alias="ab">
    <filter type="and">
    <condition attribute="accountid" operator="eq" value="@name"/>
    </filter>
    </link-entity>
    </link-entity>
    </entity>
    </fetch>

    • Edited by Donyc Sunday, April 6, 2014 9:07 PM
    Sunday, April 6, 2014 8:36 PM
  • anyone any suggestions? really stuck on this point.......
    Tuesday, April 8, 2014 11:57 AM
  • Hi Donyc,

    I suggest creating advanced find view on competitor with your relevant condition and check if it works once it works you can save this view and download fetchXML related to this view. you need to use this fetchXML in view with only modifying to get accountid dynamically..

    give it try see if above suggestion helps in resolving your issue..


    MayankP
    My Blog
    Follow Me on Twitter

    Tuesday, April 8, 2014 2:46 PM
    Answerer
  • Hi MayankP,

    When I try to start with Competitor entity in Advanced Find and when I go to Add Columns, I do not see a relationship to ACCOUNTS entity.. I only have N:N relationship right now for Competitor and Account.. Any thoughts?

    Tuesday, April 8, 2014 5:53 PM
  • I tried to create N:1 relationship b/w competitor and account.. and when I did advanced find it did show up, but my results for account were empty.........

    Tuesday, April 8, 2014 6:21 PM