locked
2 FetchXML Queries, One Report RRS feed

  • Question

  • Trying to figure out how to run two queries in a Fetch XML report on a Case/Incident and pass the same parameter to both queries.

    The concept is that you are on the Case form, select 'Report' and run a Case Report.  The Case report will have details about the specific case that you are currently viewing, but also list some details about the related activities.

    Incident query:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="incident" enableprefiltering="1" >
    	  <attribute name="incidentstagecode" />
    	  <attribute name="caseorigincode" />
    	  <attribute name="title" />
    	  <attribute name="responsiblecontactid" />
      </entity>
    </fetch>
    

    That seems to work. Then I add a 2nd query to get the related activities where the regarding object id matches the current incident ID.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    	<entity name="activitypointer">
    		<attribute name="subject" />
    		<attribute name="ownerid" />
    		<attribute name="prioritycode" />
    		<attribute name="regardingobjectid" />
    		<attribute name="activitytypecode" />
    		<order attribute="createdon" descending="true" />
    		<filter type="and">
    			<condition attribute="activitytypecode" operator="in">
    				<value>4202</value>
    				<value>4210</value>
    				<value>4212</value>
    			</condition>
    			<condition attribute="regardingobjectid" operator="eq" value="@CRM_incident" />
    		</filter>
    		
    	</entity>
    </fetch>

    This doesn't seem to work, I get errors and it won't show the report.

    Thoughts?  How can I get the IncidentID to both queries?

    Tuesday, May 7, 2013 1:40 AM

Answers

  • Hi ChrisH5491,

    What i understand is you want to show some fields of case entity and its related activities in a report.

    To show related entity field you can use "link-entity".

    for example fetchxml query:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
      <entity name="incident" enableprefiltering="1" >
    	  <attribute name="incidentstagecode" />
    	  <attribute name="caseorigincode" />
    	  <attribute name="title" />
    	  <attribute name="responsiblecontactid" />    
        
        <link-entity name="activitypointer" from="regardingobjectid" to="incidentid" visible="false" link-type="outer" alias="aa">
    	    <attribute name="subject" />
    		<attribute name="ownerid" />
    		<attribute name="prioritycode" />
    		<attribute name="regardingobjectid" />
    		<attribute name="activitytypecode" />
    		<order attribute="createdon" descending="true" />
    		<filter type="and">
    			<condition attribute="activitytypecode" operator="in">
    				<value>4202</value>
    				<value>4210</value>
    				<value>4212</value>
    			</condition>   
            </filter>
    	</link-entity>
    	
      </entity>
    </fetch>

    In the above i used outer join. you can also use inner.

    Please use the above fetchxml in your report.

    hope it will help.

    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"


    Thanks -Abhi

    • Proposed as answer by Crm.Abhi Tuesday, May 7, 2013 8:06 AM
    • Marked as answer by ChrisH5491 Tuesday, May 7, 2013 1:40 PM
    Tuesday, May 7, 2013 8:05 AM

All replies

  • Hi,

         Best way to achieve this would be to use SSRS report and use the concept of sub-report. This way you can pass the case / incident ID from parent report to child and can use different Queries for the same as well.

    Refer to following link for the example for the same:

    http://shamilat.wordpress.com/2011/12/07/master-detail-ssrs-report-with-crm-2011-online-using-fetch-xml/


    Hope this helps.
     
    -----------------------------------------------------------------------
     Minal Dahiya
     
    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    • Proposed as answer by Minal Dahiya Tuesday, May 7, 2013 2:37 AM
    • Unproposed as answer by ChrisH5491 Tuesday, May 7, 2013 1:41 PM
    Tuesday, May 7, 2013 2:37 AM
  • Hi,

    How do you relate your two fetchxml queries?what am asking is your first query retrieves records from case(incident) and if you want to retrieve related record for that cases,you have to link them! 

    I dn't think that you need two queries or subreport in this case. you may create a query which retrieves records from case and using incidentid(lookup) to retrieve related entity record!use link entity in your query to retrieve related entity record!Go through the link,it may help you!!!

    To create link entity,

    https://community.dynamics.com/crm/b/mscrmshop/archive/2012/09/30/outer-joins-in-fetch-xml-queries-in-crm2011.aspx#.UYiGBaJHKu8

    http://www.magnetismsolutions.com/blog/roshanmehta/2012/04/16/dynamics_crm_2011_querying_data_using_fetchxml

    For passing parameter to query,

    http://blogs.msdn.com/b/crm/archive/2011/03/18/parameterizing-fetch-based-reports.aspx

    hope it will help you!


    VidhyaM

    • Proposed as answer by VidhiyaM Tuesday, May 7, 2013 4:52 AM
    • Unproposed as answer by ChrisH5491 Tuesday, May 7, 2013 1:41 PM
    Tuesday, May 7, 2013 4:52 AM
  • Hi ChrisH5491,

    What i understand is you want to show some fields of case entity and its related activities in a report.

    To show related entity field you can use "link-entity".

    for example fetchxml query:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
      <entity name="incident" enableprefiltering="1" >
    	  <attribute name="incidentstagecode" />
    	  <attribute name="caseorigincode" />
    	  <attribute name="title" />
    	  <attribute name="responsiblecontactid" />    
        
        <link-entity name="activitypointer" from="regardingobjectid" to="incidentid" visible="false" link-type="outer" alias="aa">
    	    <attribute name="subject" />
    		<attribute name="ownerid" />
    		<attribute name="prioritycode" />
    		<attribute name="regardingobjectid" />
    		<attribute name="activitytypecode" />
    		<order attribute="createdon" descending="true" />
    		<filter type="and">
    			<condition attribute="activitytypecode" operator="in">
    				<value>4202</value>
    				<value>4210</value>
    				<value>4212</value>
    			</condition>   
            </filter>
    	</link-entity>
    	
      </entity>
    </fetch>

    In the above i used outer join. you can also use inner.

    Please use the above fetchxml in your report.

    hope it will help.

    If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"


    Thanks -Abhi

    • Proposed as answer by Crm.Abhi Tuesday, May 7, 2013 8:06 AM
    • Marked as answer by ChrisH5491 Tuesday, May 7, 2013 1:40 PM
    Tuesday, May 7, 2013 8:05 AM
  • I did a little tweaking, and got the 'join' query to work out.  Thanks!
    Tuesday, May 7, 2013 1:41 PM
  • Hi ChrisH5491,

    Please share the changes you have done to the join query.


    Thanks and Regards. Make sure to "Vote as Helpful" and "Mark As Answer",if you get answer of your question.

    Tuesday, May 7, 2013 1:49 PM
  • I basically took it as is but added some more attributes / fields for the report.  Works great!
    Tuesday, May 7, 2013 1:57 PM