locked
CRM 2011 Fetch XML Reports - UK Format RRS feed

  • Question

  • Hi,

    I need to create a report against a CRM2011 Online organisation, The report is created fine and displays the dates in UK format but when the paramaters are passed to CRM they are converted to US, Is there an extra paramater for the fetchxml to say read in UK format only?

    Thanks


    Pete
    • Moved by SachinW Tuesday, March 8, 2011 7:54 PM Forum consolidation (From:CRM Online)
    Tuesday, February 22, 2011 10:31 AM

Answers

  • Hi Pete

    I am not sure how you are passing the date time value to the query, but you can achieve the conversion using the following:

             <QueryParameter Name="createdonAfter">

                   <

     

    Value>=Parameters!createdonAfter.Value.ToUniversalTime.ToString()</Value>

            </

     

    QueryParameter>

    I hope that helps in what you are looking to achieve. If not, please send me your RDL at abhia_at_microsoft_dot_com.

    Thanks


    Abhishek Agarwal MSFT
    Wednesday, March 2, 2011 1:39 PM

All replies

  • Just a bit more info:

    If I select the date ranges 01-02-2011 to 01-03-2011 It thinks i'm passing US format not UK so its looking for  02-01-2011 to 03-01-2011 i.e 1 day as apposed to 1 month.

    I know this is easy to do in SQL but I cant find a way in fetchxml.

     

    Thanks


    Pete
    Tuesday, February 22, 2011 1:38 PM
  • anyone?
    Pete
    Wednesday, February 23, 2011 10:03 AM
  • Did you try to customize the date time format in system settings?
    ISSY
    Thursday, February 24, 2011 11:26 AM
  • Hi Issy,

    I'm in the UK so my datetime is setup as it should be on my PC, CRM Stores the datetimes in utc, the report is setup with a language of en-GB, the dates/times that it uses for the Report Paramaters are UK and when it uses these to pass back to CRM as a fetchxml query it comes back with their not in the correct format.

    SHouldnt CRM either pick up my language/location from my settings/user or is there a way to convert the datetime from UK to UTC before its passed back in fetchxml - doesnt seem like the best route.

     

    Thanks


    Pete
    Thursday, February 24, 2011 11:42 AM
  • Hi Pete

    Can you please ellaborate on what you are trying to achieve? From what I understand, you are trying to execute a parameterized fetch xml whose date time condition is receiving a date time value at runtime. Is my understanding correct? If yes, then how are the date time values being passed to the fetch xml coming? Are they coming from another fetch xml?

    Thanks


    Abhishek Agarwal MSFT
    Monday, February 28, 2011 5:15 PM
  • Hi Abhishek,

    Here's a cut down version of the fetchxml i'm using to pull back the records I need, the date for createdon comes froma date/time paramater in the report. The report is in the en-GB language and when it passes the date back in the fetchxml im getting an error saying date out of range, if i pass back the date in UTC it works fine though.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="opportunity">
        <attribute name="customerid" />
        <attribute name="campaignid" />
        <attribute name="createdon" />
        <order attribute="customerid" descending="false" />
        <filter type="and">
          <condition attribute="createdon" operator="on-or-after" value="@createdonAfter" />
          <condition attribute="createdon" operator="on-or-before" value="@createdonBefore" />  

        </filter>
      </entity>
    </fetch>

     

     

    Thanks

    Pete


    Pete
    Monday, February 28, 2011 5:31 PM
  • Hi Robert

    There are three different cases of how the date value is parsed by CRM based on what string is passed to it:

    1. "01/03/2011z": In this case, CRM will treat the value as UTC and will try to parse it accordingly. You may use this approach by converting the date time into UTC and then getting a string representation of it that you can pass in the fetch xml.

    2. "01/03/2011+05:30": In this case, CRM will use the string and parse it as Local Time having an offset of + 0530 Hours from the UTC.

    3. "01/03/2011": In this case, CRM will treat this string as Local Time and will convert it to UTC using the time zone settings of the CRM user running the report.

    You may experiment and see what value is getting passed in the report. However, as you have already done I guess, it is best to use UTC time.

    HTH, Thanks


    Abhishek Agarwal MSFT
    Wednesday, March 2, 2011 10:20 AM
  • Hi Abhishek,

    Using SQL its easy to convert the UK Datetime into UTC to be passed to CRM, The issue I have is achieving this with fetchxml... Any ideas?

    If the Report language is en-GB it stores the datetime in the dd/mm/yyy format so when thats passed to CRM is comes back as invalid (only if the value is out of range, some dates work both ways i.e 01/03 (1st March) and 03/01 (1st Jan)).


    Thanks
    Pete


    Pete
    Wednesday, March 2, 2011 11:52 AM
  • Hi Pete

    I am not sure how you are passing the date time value to the query, but you can achieve the conversion using the following:

             <QueryParameter Name="createdonAfter">

                   <

     

    Value>=Parameters!createdonAfter.Value.ToUniversalTime.ToString()</Value>

            </

     

    QueryParameter>

    I hope that helps in what you are looking to achieve. If not, please send me your RDL at abhia_at_microsoft_dot_com.

    Thanks


    Abhishek Agarwal MSFT
    Wednesday, March 2, 2011 1:39 PM
  • Hi Abhishek,

    Using SQL its easy to convert the UK Datetime into UTC to be passed to CRM, The issue I have is achieving this with fetchxml... Any ideas?

    If the Report language is en-GB it stores the datetime in the dd/mm/yyy format so when thats passed to CRM is comes back as invalid (only if the value is out of range, some dates work both ways i.e 01/03 (1st March) and 03/01 (1st Jan)).


    Thanks
    Pete


    Pete

    Pete !

    Did you got it resolved ?

    I am trying to achieve same task.. working it out in fetchxml is a nightmare !!! 

    Kindly revert back

    Cheers Mate !

    Bharat

    Thursday, October 20, 2011 3:10 PM
  • All Right !

    Cheers to Abhishek Agarwal whose solution guided me to resolve my issue !

    First of all the issue I was facing was with the date. The date was always taken in US format irrespective of I sending it in UK format.

    For example, 10/12/2010 (10 Dec 2010) was taken as 12/10/2010 (12 Oct 2010) and was failing for any date like 30/12/2010 (Greater then 12 for month as date).

    So what I did is I right clicked on the rdl and chose an option of view code.

    In the code, I update my parameter value by casting it thr.

    My date variable was @ComplaintDate and here is the code below

     

    <QueryParameter Name="@ComplaintDate">

     <Value>=Format(Parameters!ComplaintDate.Value,"MM-dd-yyyy")</Value>

    I used format to send date in US format as in my case CRM was always expecting date in US format, even I was sending date in UK from my report.

    This can be reverted for those facing US to UK issue ! ;)

    Cheers 

    Bharat 

    P.S Mark this as answer if it resolved your issue

    Thursday, October 20, 2011 3:59 PM
  • Hi Bharat,

    Which section in the data set should the query parameter be placed. I tried adding it before the filter but I am getting an Invalid Node error.

    Thanks,

    Malou

    Tuesday, April 17, 2012 5:08 PM
  • Hi Malou,

    If you create the paramater in the report first, save it then search/edit the rdl file then it will be easier to.

    The only downside i've found to this is that when you publish the report to CRM and run it, you can type in the date in UK format, you have to select the date from the calender only.


    Pete

    Wednesday, April 18, 2012 8:40 AM
  • Hello,

    It can be done by override your @createdonAfter parameter at runtime

    like

    make two parameter

    1.createdonAfter(visible,date/time)

    2.createdonAftermain(internal,text)

    in default value of createdonAftermain Parameters  select specify values option and Add expression

    =Format(Parameters!createdonAfter.Value,"MM/dd/yyyy HH:mm:ss")

    replace your @createdonAfter with @createdonAftermain parameter

    change  "MM/dd/yyyy HH:mm:ss" in your required format

    It Must Work !!

    Thursday, May 31, 2012 5:56 AM
  • All Right !

    Cheers to Abhishek Agarwal whose solution guided me to resolve my issue !

    First of all the issue I was facing was with the date. The date was always taken in US format irrespective of I sending it in UK format.

    For example, 10/12/2010 (10 Dec 2010) was taken as 12/10/2010 (12 Oct 2010) and was failing for any date like 30/12/2010 (Greater then 12 for month as date).

    So what I did is I right clicked on the rdl and chose an option of view code.

    In the code, I update my parameter value by casting it thr.

    My date variable was @ComplaintDate and here is the code below

    <QueryParameter Name="@ComplaintDate">

     <Value>=Format(Parameters!ComplaintDate.Value,"MM-dd-yyyy")</Value>

    I used format to send date in US format as in my case CRM was always expecting date in US format, even I was sending date in UK from my report.

    This can be reverted for those facing US to UK issue ! ;)

    Cheers 

    Bharat 

    P.S Mark this as answer if it resolved your issue

    This one worked for me :)


    Monday, June 18, 2012 10:16 AM
  • 
    

    I add this code but having error and when I add this code in Paramter from BIDS,,When I pereview it,,it is neccessary to put the data For Compaint Date But It haven't any influance...

    <QueryParameter Name="@ComplaintDate">
     
     <Value>=Format(Parameters!ComplaintDate.Value,"MM-dd-yyyy")</Value>
    

    
    Tuesday, February 5, 2013 2:50 PM
  • Very good thought!

    Thursday, October 17, 2013 11:06 AM