locked
Field showing in query browser does not show in report RRS feed

  • Question

  • I'm using the following FetchXML to get a case and it's resolution activity so I can generate a monthly report for clients on time spent on their cases.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="activitypointer">
        <attribute name="activityid" />
        <attribute name="instancetypecode" />
        <attribute name="community" />
        <attribute name="actualdurationminutes" />
        <attribute name="activitytypecode" />
        <order attribute="instancetypecode" descending="false" />
          <filter type="and">
            <condition attribute="activitytypecode" operator="eq" value="4206" />
            <condition attribute="statecode" operator="eq" value="1" />
          </filter>
    	 <link-entity name="incident" from="incidentid" to="regardingobjectid" visible="false" link-type="outer" alias="a_4ae6af13ffd6441fb2c1aacb4f41f52c">
          <attribute name="createdon" />
          <attribute name="casetypecode" />
          <attribute name="title" />
          <attribute name="ticketnumber" />
        </link-entity>
    	<link-entity name="incidentresolution" from="incidentid" to="regardingobjectid">
    		<attribute name="timespent" />
    	</link-entity>
      </entity>
    </fetch>

    It's working fine in query browser (timespent shows the correct values) but once I finish stepping through the new report wizard in Visual Studio and it generates my report, all the fields fill in fine, apart from that timespent field. It's returning as blank in preview.

    I also tried to create a calculated field (timespent/60 = HoursSpent) but when I try to preview the report it says:

    The field expression for the dataset 'DataSet1' refers to the field 'incidentresolutions676e0e9c_timespent'. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

    I'm very new to SSRS and CRM so it's entirely possible I'm missing something very simple! Any help or pointers in the right direction are appreciated :) 

    Thursday, September 18, 2014 4:02 AM

Answers

  • you got to delete the field that is removed.

    Basically, your field mapping of the dataset should match the fields returned by the query/fetch execution. computed columns should use aliases. - no blank field (no name ones) allowed or no extra fields allowed (not returned by the dataset).

    HTH.

    Regards,

    Jithesh 

    • Marked as answer by dan_nz Thursday, September 18, 2014 4:36 AM
    Thursday, September 18, 2014 4:32 AM

All replies

  • Hi Dan,

     Could you please check if you have created field mapping for the dataset (especially for the computed field)? Please scroll to the last post to see what I am trying to say.

    http://social.microsoft.com/Forums/en-US/c2ef49c9-4f05-4697-895f-d746de29fa27/crm-2011-neglected-leads-report-change-neglected-days?forum=crmdevelopment#8dec04b5-db12-4911-bb0f-1ae160b684f4

    Regards,

    Jithesh

    Thursday, September 18, 2014 4:21 AM
  • You were right, the calculated field was using the wrong field name.

    Now I'm getting #error in the report and it says "This field is missing from the returned result set from the datasource" in the warnings, which agrees with the blank column in the standard report

    Thursday, September 18, 2014 4:28 AM
  • you got to delete the field that is removed.

    Basically, your field mapping of the dataset should match the fields returned by the query/fetch execution. computed columns should use aliases. - no blank field (no name ones) allowed or no extra fields allowed (not returned by the dataset).

    HTH.

    Regards,

    Jithesh 

    • Marked as answer by dan_nz Thursday, September 18, 2014 4:36 AM
    Thursday, September 18, 2014 4:32 AM
  • Aliases was the answer. I figured it out on my own. woohoo!

    My XML now looks like this and it works!

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="activitypointer">
        <attribute name="activityid" />
        <attribute name="instancetypecode" />
        <attribute name="community" />
        <attribute name="actualdurationminutes" />
        <attribute name="activitytypecode" />
        <order attribute="instancetypecode" descending="false" />
          <filter type="and">
            <condition attribute="activitytypecode" operator="eq" value="4206" />
            <condition attribute="statecode" operator="eq" value="1" />
          </filter>
    	 <link-entity name="incident" from="incidentid" to="regardingobjectid" visible="false" link-type="outer" alias="a_4ae6af13ffd6441fb2c1aacb4f41f52c">
          <attribute name="createdon" />
          <attribute name="casetypecode" />
          <attribute name="title" />
          <attribute name="ticketnumber" />
        </link-entity>
    	<link-entity name="incidentresolution" from="incidentid" to="regardingobjectid" alias="IncidentData">
    		<attribute name="timespent" />
    	</link-entity>
      </entity>
    </fetch>

    Thursday, September 18, 2014 4:36 AM