Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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

  • Dynamotion Profile Picture
    Dynamotion on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    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.

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,323 Most Valuable Professional on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    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.

  • Verified answer
    Dynamotion Profile Picture
    Dynamotion on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    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

  • a33ik Profile Picture
    a33ik 84,323 Most Valuable Professional on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

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

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,323 Most Valuable Professional on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    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.

  • Suggested answer
    Thomas David Dayman Profile Picture
    Thomas David Dayman 11,323 on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

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

  • Dynamotion Profile Picture
    Dynamotion on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    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
    a33ik Profile Picture
    a33ik 84,323 Most Valuable Professional on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    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
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    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>


  • a33ik Profile Picture
    a33ik 84,323 Most Valuable Professional on at
    RE: Issue with FetchXML SSRS report with Global Optionset for D365 v8.2

    Hello,

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

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans