web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Season of Giving Solutions is Here!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Issue with FetchXML SS...
Microsoft Dynamics CRM (Archived)

Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

(0) ShareShare
ReportReport
Posted on by Dynamotion

I have a custom FetchXML based SSRS report, where I have global optionsets as the parameters. The paramaters populate fine as dropdowns on the report but when I click View Report to see the results (by feeding those selected parameters), I get this error.

It looks like the optionset values are being passed with "," instead of the numbers. I am pretty sure I didn't have this issue with local optionsets instead of global optionsets.

Is this a known issue or am I doing something wrong?

SSRS_5F00_Issue.png

I would appreciate any help with this. Thank you!

*This post is locked for comments

I have the same question (0)
  • a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    Hello,

    Can you please provide your fetchxml query and how you pass parameters inside it from report parameter?

  • Dynamotion Profile Picture
    Dynamotion on at

    Sure here's how the FetchXML that get data from D365 using report parameter looks like:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
  <entity name="contact" >
    <attribute name="fullname" />
    <attribute name="middlename" />
    <attribute name="firstname" />
    <attribute name="governmentid" />
    <attribute name="lastname" />
    <filter type="and" >
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <order attribute="fullname" />
    <link-entity name="cust_applicationregistration" from="cust_individual" to="contactid" alias="registration" >
      <attribute name="cust_expirationdate" />
      <attribute name="cust_registrationnumber" />
      <attribute name="cust_applicationtype" />
      <attribute name="cust_boardactiondate" />
      <attribute name="cust_currentstatus" />
      <attribute name="cust_discipline" />
      <filter type="and" >
        <condition attribute="cust_registrationnumber" operator="not-null" />
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="cust_currentstatus" operator="in" value="@LicenseeStatus" />
        <condition attribute="cust_discipline" operator="in" value="@Discipline" />
      </filter>
    </link-entity>
    <link-entity name="cust_address" from="cust_individual" to="contactid" alias="address" >
      <attribute name="cust_zip" />
      <attribute name="cust_addressline1" />
      <attribute name="cust_preferredemailaddress" />
      <attribute name="cust_primarynumber" />
      <attribute name="cust_fax" />
      <attribute name="cust_county" />
      <attribute name="cust_addressline2" />
      <attribute name="cust_state" />
      <attribute name="cust_addresstype" />
      <attribute name="cust_country" />
      <attribute name="cust_city" />
      <filter type="and" >
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="cust_addresstype" operator="in" value="@AddressType" />
      </filter>
    </link-entity>
  </entity>
</fetch>

    The FetchXML for one of the global optionset looks like this

    <fetch distinct="true" mapping="logical" output-format="xml-platform" version="1.0">
  <entity name="stringmap" >
    <attribute name="attributevalue" />
    <attribute name="value" />
    <filter type="and" >
      <condition attribute="attributename" operator="eq" value="cust_addresstype" />
      <condition attribute="objecttypecode" operator="eq" value="10005" />
    </filter>
  </entity>
</fetch>


  • Suggested answer
    a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    I didn't know that it's possible to use stringmap in datasource. Thanks for a tip! I utilized that feature in my SQL-reports heavily and if I'm not wrong after 2011 was released and FetchXml report were introduced - stringmap was not available for fetch. Cool that is it available now!

    I believe I know why you experience your issue. You fetch attributevalue field and as any other int field (attributevalue is int) fetchxml authoring extension returns 2 fields - attributevalue that contains formatted value according to your locale settings (and this is why you get this error) and attributevalueValue (this is the real int). So the workaround for you - in your parameter using attributevalueValue instead of attributevalue.

    Good luck.

  • Dynamotion Profile Picture
    Dynamotion on at

    Hi Andrew,

    I tried using the "attributevaluevalue" but apparently that field doesn't exist for the "stringmap" entity, so my FetchXML gives me an error. It is kind of weird, because I used this query for local optionsets and it worked, just stopped working when I used global optionsets (unless it was a co-incidence with some update rolled out).

    Also, when I execute this query using FetchXML builder it doesn't show me any commas for those numbers.

    Any ideas how to proceed next?

  • Suggested answer
    Thomas David Dayman Profile Picture
    Thomas David Dayman 11,323 on at

    Try following this - http://www.kentom.co.uk/?p=337

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    It doesn't exist in CRM. But it exists in report:

    StringMap.png

    Microsoft's report extension for report gives possibility to use it. At least I was able to use it. I believe you can make it as well.

  • a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    And article that Thomas provided proves that I'm right.

  • Verified answer
    Dynamotion Profile Picture
    Dynamotion on at

    I don't have it, I'm baffled. I'm using VS 2015.

    SSRS_5F00_Issue_5F00_02.png

    EDIT: So this is what I had to do, add a calculated field where I replaced those commas. And then use that calculated field in my FetchXML, that seemed to work. Why didn't I think calculated field before *facepalm moment*.

    SSRS_5F00_Issue_5F00_02.png

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,331 Most Valuable Professional on at

    That field should not be calculated. I believe you use latest version of FetchXml authoring extension and it seems it doesn't work the same way with version for 8.x.

  • Dynamotion Profile Picture
    Dynamotion on at

    You are correct, I am using Report Authoring Extension v9.0 and I installed this a few weeks back so that could be the cause.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
Shidin Haridas Profile Picture

Shidin Haridas 2

#2
Abdullah13 Profile Picture

Abdullah13 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans