locked
fetch XML help for a report RRS feed

  • Question

  • Hi com,

    I'm having trouble with a fetch dataset in one of our reports.

    We use SSRS reports via fetch XML in CRM 2011 for generating our quotes.

    In my fetch XML, I need to have all the data/fields that I need on the final quote.

    So I have to work with link-entity a lot.

    The problem is, that none of the "manual products", which you can create "on the fly" in Opportunity(Product) are included in this dataset.

    Can anyone see why? 

    I'm also converting the picture attached to the product form in a picture on the quote.

    (That's the link-entity: "annotation", where I use the picture attached to the productform via note.)

    This is my fetch:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="quote" enableprefiltering="1" >
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="totalamount" />
    <attribute name="new_angebotsnummer" />
    <attribute name="opportunityid" />
    <attribute name="createdon" />
    <attribute name="new_beschreibung" />
    <order attribute="name" descending="false"/>
    <link-entity name="quotedetail" alias="aa" to="quoteid" from="quoteid">
    		<attribute name="lineitemnumber"/>
    		<attribute name="extendedamount"/>
    		<attribute name="productid"/>
    		<attribute name="priceperunit"/>
    		<attribute name="quantity"/>
                    <attribute name="description"/>
    		<attribute name="new_position"/>
    		<attribute name="new_option"/>
    		<attribute name="new_oberflaeche"/>
    		<attribute name="new_festigkeit"/>
    		<attribute name="new_werkzeugkosten"/>
    		<attribute name="new_gewindebeschichtung2"/>
    <link-entity name="product" from="productid" to="productid" alias="ab">
    		<attribute name="productnumber"/>
    <link-entity name="annotation" from="objectid" to="productid" alias="ac">
    		<attribute name="mimetype"/>
    		<attribute name="documentbody"/>
    </link-entity>
    </link-entity>
    </link-entity>
    <link-entity name="account" from="accountid" to="customerid" alias="ad">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="address1_city" />
    <attribute name="address1_country" />
    <attribute name="address1_name" />
    <attribute name="address1_postalcode" />
    <attribute name="telephone1" />
    <attribute name="address1_line1" />
    </link-entity>
    <link-entity name="opportunity" from="opportunityid" to="opportunityid" alias="ae">
    <attribute name="parentcontactid" />
    <attribute name="name" />
    <link-entity name="systemuser" from="systemuserid" to="new_bearbeiter" alias="af">
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="internalemailaddress" />
    <attribute name="address1_telephone1" />
    <attribute name="address1_fax" />
    </link-entity>
    <link-entity name="systemuser" from="systemuserid" to="new_nachfasser" alias="ah">
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="internalemailaddress" />
    <attribute name="address1_telephone1" />
    <attribute name="address1_fax" />
    </link-entity>
    <link-entity name="contact" from="contactid" to="new_kontakt" alias="ag">
    <attribute name="new_anrede" />
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="emailaddress1" />
    <attribute name="telephone1" />
    </link-entity>
    </link-entity>
    </entity>
    </fetch>

    many greetings and sorry for my bad english

    jon


    • Edited by jon-snow Monday, August 4, 2014 12:15 PM
    Monday, August 4, 2014 12:13 PM

All replies

  • May be because your link to Product entity is not an Outer Join. Manual products I suppose dont get created in the products table they are stored as text information on the opportunity product table itself. So I suggest you use Outer Join on your Product LinkEntity

    <link-entity name='product' from='productid' to='productid' link-type='outer'/>
        <filter operator='and'>
          <condition entityname='product' attribute='productid' operator='null'/>
        </filter>

    Monday, August 4, 2014 2:16 PM
  • Thanks for your tip.

    <link-entity name="product" from="productid" to="productid" alias="ab" link-type='outer'>
    	<filter operator='and'>
    		<condition entityname='product' attribute='productid' operator='null' />
    		<attribute name="productnumber"/>
    	</filter>
    <link-entity name="annotation" from="objectid" to="productid" alias="ac">
    		<attribute name="mimetype"/>
    		<attribute name="documentbody"/>

    won't work though.

    "Invalid Child Node. There can only be condition and filter nodes.NodeName = attribute NodeXml = <attribute name = "productnumber" />" is the error msg.

    greets and many thanks

    jon 

    Monday, August 4, 2014 3:23 PM
  • Selection attributes must be outside of the filter expression so change your above code as follows

    <link-entity name="product" from="productid" to="productid" alias="ab" link-type='outer'>
        <attribute name="productnumber"/>
    <filter operator='and'>
    <condition entityname='product' attribute='productid' operator='null' />
    </filter>
    <link-entity name="annotation" from="objectid" to="productid" alias="ac">
    <attribute name="mimetype"/>
    <attribute name="documentbody"/>

    Monday, August 4, 2014 3:53 PM
  • Thanks again for the heads up.

    The code can be executed but won't show me any results anymore. 

     
    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="quote" enableprefiltering="1" >
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="totalamount" />
    <attribute name="new_angebotsnummer" />
    <attribute name="opportunityid" />
    <attribute name="createdon" />
    <attribute name="new_beschreibung" />
    <order attribute="name" descending="false"/>
    <link-entity name="quotedetail" alias="aa" to="quoteid" from="quoteid">
    		<attribute name="lineitemnumber"/>
    		<attribute name="extendedamount"/>
    		<attribute name="productid"/>
    		<attribute name="priceperunit"/>
    		<attribute name="quantity"/>
           		<attribute name="description"/>
    		<attribute name="new_position"/>
    		<attribute name="new_option"/>
    		<attribute name="new_oberflaeche"/>
    		<attribute name="new_festigkeit"/>
    		<attribute name="new_werkzeugkosten"/>
    		<attribute name="new_gewindebeschichtung2"/>
    <link-entity name="product" from="productid" to="productid" alias="ab" link-type='outer' >
    		<attribute name="productnumber" />
    		<filter operator='and'>
    		<condition entityname='product' attribute='productid' operator='null' />
    		</filter>
    <link-entity name="annotation" from="objectid" to="productid" alias="ac">
    		<attribute name="mimetype"/>
    		<attribute name="documentbody"/>
    </link-entity>
    </link-entity>
    </link-entity>
    <link-entity name="account" from="accountid" to="customerid" alias="ad">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="address1_city" />
    <attribute name="address1_country" />
    <attribute name="address1_name" />
    <attribute name="address1_postalcode" />
    <attribute name="telephone1" />
    <attribute name="address1_line1" />
    </link-entity>
    <link-entity name="opportunity" from="opportunityid" to="opportunityid" alias="ae">
    <attribute name="parentcontactid" />
    <attribute name="name" />
    <link-entity name="systemuser" from="systemuserid" to="new_bearbeiter" alias="af">
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="internalemailaddress" />
    <attribute name="address1_telephone1" />
    <attribute name="address1_fax" />
    </link-entity>
    <link-entity name="systemuser" from="systemuserid" to="new_nachfasser" alias="ah">
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="internalemailaddress" />
    <attribute name="address1_telephone1" />
    <attribute name="address1_fax" />
    </link-entity>
    <link-entity name="contact" from="contactid" to="new_kontakt" alias="ag">
    <attribute name="new_anrede" />
    <attribute name="firstname" />
    <attribute name="lastname" />
    <attribute name="emailaddress1" />
    <attribute name="telephone1" />
    </link-entity>
    </link-entity>
    </entity>
    </fetch

    • Edited by jon-snow Tuesday, August 5, 2014 7:30 AM
    Tuesday, August 5, 2014 6:55 AM