locked
multiple entity report RRS feed

  • Question

  • Hello,

    I am trying to make a report in CRM but am having difficulties linking entities and getting the data grouped correctly. I need to create a report where all invoice products are pulled from CRM and then grouped by productID so i can aggregate how many of each product have been sold. This then needs to be filtered by account so I can see which products are popular with who. My fetch xml is as follows

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="invoicedetail">
      <attribute name="productid" /> 
      <attribute name="productdescription" /> 
      <attribute name="priceperunit" /> 
      <attribute name="quantity" /> 
      <attribute name="extendedamount" /> 
      <attribute name="invoicedetailid" /> 
     <filter type="and">
      <condition attribute="quantity" operator="gt" value="0" /> 
      </filter>
      <order attribute="productid" descending="false" /> 
    
    <link-entity name="invoice" from="invoiceid" to="invoiceid" alias="aa">
      <link-entity name="account" from="accountid" to="customerid" alias="ab" /> 
      </link-entity>
      </entity>
      </fetch>

    I'm not sure quite on how too go about the groupings and linking the entities together in Visual Studio, as I am pretty new to it all.

    if anyone knows a good resource that could help me with this I would be very grateful.



    Friday, February 8, 2013 1:23 PM

Answers

  • Hi,

    I build this FetchXml but i'm not sure that work, so use as a guide:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
     <entity name="invoice" enableprefiltering="true" prefilterparametername="InvoiceFilter">
      <attribute name="name" /> 
      <attribute name="customerid" /> 
      <attribute name="invoiceid" /> 
      <order attribute="name" descending="false" /> 
      <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="a_account">
        <attribute name="accountnumber" />
        <attribute name="name" />
        <attribute name="address1_line1" />
      </link-entity>
      <link-entity name="invoicedetail" from="invoiceid" to="invoiceid" alias="invoicedetail">
        <attribute name="productid"/>
        <attribute name="quantity"/>
      </link-entity>
     </entity>
    </fetch>

    This will generate a unique dataset with columns of account and invoicedetail.

    Hope this helps.
    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    • Proposed as answer by Azevedo PedroMVP Friday, February 8, 2013 5:56 PM
    • Marked as answer by harriowen Monday, February 11, 2013 12:32 PM
    Friday, February 8, 2013 5:56 PM

All replies

  • Hi,

    Check this articles, are very complete with a lot of samples:

    http://msdn.microsoft.com/en-us/library/gg309565.aspx

    http://mileyja.blogspot.pt/2011/06/aggregation-count-max-min-etc-and.html

    Another good resource is the advanced find to create FetchXml, you do a advanced find after you can download the FetchXml.

    Hope this helps.
    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    Friday, February 8, 2013 2:46 PM
  • Hi thanks for the reply, this is helpful but am more confused about pulling the information from the linked entities. For example if I take an account I want to link all the invoice product to that account that it was bought under, I am confused at how to add the fields from the linked entity to the report. I have tried using linked entity withing XML and then adding the fields to the form in VS but it is not quite that simple it seems. I have also tried adding extra data sets with no success either. I have been able to pull info from the linked entity with this =First(Fields!productidValue.Value, "ContractLines") but only pulls the first value in array it seems.

    • Edited by harriowen Friday, February 8, 2013 3:30 PM
    Friday, February 8, 2013 3:26 PM
  • Hi,

    I build this FetchXml but i'm not sure that work, so use as a guide:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
     <entity name="invoice" enableprefiltering="true" prefilterparametername="InvoiceFilter">
      <attribute name="name" /> 
      <attribute name="customerid" /> 
      <attribute name="invoiceid" /> 
      <order attribute="name" descending="false" /> 
      <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="a_account">
        <attribute name="accountnumber" />
        <attribute name="name" />
        <attribute name="address1_line1" />
      </link-entity>
      <link-entity name="invoicedetail" from="invoiceid" to="invoiceid" alias="invoicedetail">
        <attribute name="productid"/>
        <attribute name="quantity"/>
      </link-entity>
     </entity>
    </fetch>

    This will generate a unique dataset with columns of account and invoicedetail.

    Hope this helps.
    If i answered your question, please mark the response as an answer and also vote as helpful.


    Pedro Azevedo Crm Specialist 4.0\2011

    • Proposed as answer by Azevedo PedroMVP Friday, February 8, 2013 5:56 PM
    • Marked as answer by harriowen Monday, February 11, 2013 12:32 PM
    Friday, February 8, 2013 5:56 PM
  • Hi , Yes this is perfect all the fields are now available. Am still a bit confused as how this all works but i will have a google of the bits you changed and see what theyre doing. Thanks for the help Pedro
    Monday, February 11, 2013 12:55 PM