Microsoft Dynamics CRM (Archived)

FetchXML issues with aggregate groupby on an option field

(0) Share

Report

Posted on by PAB_UK

105

I am having issues with a FetchXML query running against Dynamics CRM 2013 (online).

When running this simple query

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" count="5">
  <entity name="activitypointer">
    <attribute name="activityid" alias="activity_id" />
    <attribute name="activitytypecode" alias="activity_type" />
    <attribute name="ownerid" alias="owner" />
  </entity>
</fetch>

I get the expected results for the activity_type column e.g. "email", "opportunityclose" etc.

But when I attempt this aggregate version of the same query

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
  <entity name="activitypointer">
    <attribute name="activityid" alias="activity_count" aggregate="count" />
    <attribute name="activitytypecode" alias="activity_type" groupby="true" />
    <attribute name="ownerid" alias="owner" groupby="true" />
  </entity>
</fetch>

I get the correct results for the activity_count and owner columns, but the "activity_type" column is populated with just "Microsoft.Xrm.Sdk.OptionSetValue" or if ran within a report "#error".

I have also tried adding not-null filters to all fields to ensure that null values are not causing and issue but this makes no difference.

The issue also occurs if the query is run in the Query Designer, which rules out the report config as far as I can tell.

I assume this is something to do with grouping by an option field type.

Does anyone know how to resolve this issue?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Royal King 27,686 on at

Like (0)

Report

I checked the fetchxml you posted and it seems working fine and returns right activity type code name in the result. Probably you may be referring wrong field name in the report cell property. Try to run the fetchxml in the report designer that will show you right field name and refer that in the report field.

Was this reply helpful? Yes No
Suggested answer

ScottDurow 21 on at

Like (0)

Report

It is probably because you are using ToString on the activity_type value when you'll need to use activity_type.Name or activity_type.Value

Aliased values work differently to standard attributes.

Was this reply helpful? Yes No
PAB_UK 105 on at

Like (0)

Report

The issue also appears if I run the query in the "Query Designer" so the report configuration is not relevant.

Was this reply helpful? Yes No
PAB_UK 105 on at

Like (0)

Report

This is not being done in C# code but rather as a reporting studio project

Was this reply helpful? Yes No
Suggested answer

ScottDurow 21 on at

Like (0)

Report

I've just done a quick test on this and it does appear to be a bug in the SSRS data adaptor that incorrectly interprets the aliased value of the entitytypecode attribute - this attribute is a special type rather than a standard optionset. I've done a similar query and grouped by prioritycode for example and it works fine. I would suggest that you contact Microsoft support about this - community.dynamics.com/.../contactus.aspx

Hope this helps,

Scott

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Did you find solution?

Was this reply helpful? Yes No