locked
report date issues RRS feed

  • Question

  • I am having problems with date formats in CRM reports. I have created the reports in BIDS and they mostly work OK but there seems to be a confusion between time zones and daylight savings.

    To avoid confusion with dates I want the formats to explicitly mention the month name rather than a number so in the reports I format the date fields as;

    =IIF(IsNothing(Fields!qubic_servicemodule_starttimeValue)
    ,""
    ,IIF(IsNothing(Fields!qubic_servicemodule_starttimeValue.Value)
    ,""
    ,Day(Fields!qubic_servicemodule_starttimeValue.Value) & " "& MonthName(Month(Fields!qubic_servicemodule_starttimeValue.Value), True) & " " & Year(Fields!qubic_servicemodule_starttimeValue.Value) & " " & Right("0" & Hour(Fields!qubic_servicemodule_starttimeValue.Value), 2) & ":" & Right("0" & Minute(Fields!qubic_servicemodule_starttimeValue.Value), 2) ) )

    This works but all dates and times appear as UTC. This is much as I would expect because the internal format is UTC. If I change the formatting to be;

    =IIF(IsNothing(Fields!qubic_servicemodule_starttime)
      ,""
      ,IIF(IsNothing(Fields!qubic_servicemodule_starttime.Value)
        ,""
        ,Day(Fields!qubic_servicemodule_starttime.Value) 
         & " "& MonthName(Month(Fields!qubic_servicemodule_starttime.Value), True) 
         & " " & Year(Fields!qubic_servicemodule_starttime.Value)
         & " " & Right("0" & Hour(Fields!qubic_servicemodule_starttime.Value), 2)
         & ":" & Right("0" & Minute(Fields!qubic_servicemodule_starttime.Value), 2)
        )
      )

    The dates and times are formatted correctly and appear in the correct time zone but it generates an error for dates past the 12th of any month. That suggests a confusion between US and UK date formats but the strange thing is that the dates are presented correctly, i.e. 7/6/2014 appears as 7 June and not 6 July but as soon as it gets to 13/6/2014 it raises an error.

    So how can I format dates correctly and use the user's time zone. I can't use any of the date format options because there isn't enough flexibility in that to produce what I want I could mix the fields but that seems wrong.

    Tuesday, July 1, 2014 9:23 AM

All replies

  • Since it appears you are constructing dates manually, why not just fetch the date components from SQL instead of parsing them in the report?

    For example:

    SELECT qubic_servicemodule_starttime
        ,YEAR(qubic_servicemodule_starttime) AS YearPart
        ,MONTH(qubic_servicemodule_starttime) AS MonthPart
        ,DAY(qubic_servicemodule_starttime) AS DayPart
    FROM qubic_servicemodule

    Then construct your display date based on the date part values SQL returns. Let it sort out the date formats so you don't have to worry about end user locales.

    Tuesday, July 1, 2014 2:26 PM
  • I suspect I'm being a bit ignorant here but where would I put that SQL? The report is created in BIDS and as far as I can see the only options I have are its bespoke language for formatting and FetchXML to determine what to display. If I create a view in SQL I could use that but that's not a readily deployable report for CRM as I would need to tweak the server.
    Wednesday, July 2, 2014 10:01 AM
  • My reply assumes you are using a SQL query as your datasource for the report in BIDS. If you are using fetchXML then you are going to be limited to doing it in code like you are now.

    As for tweaking the server to support SQL based reports in CRM - not at all. CRM natively allows SQL based reports. However in your case you would have redesign your report to work against a SQL datasource instead of fetch. I pretty much only use SQL because it is significantly more powerful in what you can do in fetching/manipulating data compared to fetch.

    Wednesday, July 2, 2014 6:15 PM
  • Have you set the Language property on the Report object (via Properties in BIDS) ? This affects the .Net date functions. By default it is en-US, but you can set it to en-GB for UK dates.

    Re SQL v. Fetch. If you have CRM OnPremise, you can use either SQL or FetchXml. If you have Crm Online, you can only use FetchXml


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, July 3, 2014 8:44 PM
    Moderator
  • In design view of report select the date field and right click Properties. You should be able to format the date according to your requirement in this window.

    Regards Faisal

    Friday, July 4, 2014 1:29 PM
  • In design view of report select the date field and right click Properties. You should be able to format the date according to your requirement in this window.

    Regards Faisal

    That's useful but too restrictive in it's format options. For example it won't allow me to use 24 hour clocks etc. Digging into the code underneath it is not a format string but a code which presumably refers to a limited set of format options.

    Edit to add ...

    My mistake that does allow me to do the formatting but not through its list of date formats. One has to use the custom formats. The weird thing here is that if you format the date through an expression such as

    Day(Fields!qubic_servicemodule_starttime.Value) 
    & " "& MonthName(Month(Fields!qubic_servicemodule_starttime.Value), True) 
    & " " & Year(Fields!qubic_servicemodule_starttime.Value)
    & " " & Right("0" & Hour(Fields!qubic_servicemodule_starttime.Value), 2)
    & ":" & Right("0" & Minute(Fields!qubic_servicemodule_starttime.Value), 2)

    It crashes if the data is null or "". If you use the field and set the custom format to "d MMM yyyy HH:mm" it works although there is still a confusion about times zones and daylight savings.

    • Edited by J N Brand Tuesday, July 8, 2014 9:12 AM
    Monday, July 7, 2014 10:40 AM
  • To avoid this I created a new field in the entity to hold the text of the date.  A plugin is written to update this field with the local time of the particular date field.

    Plugin is registered on create and update. (Pre-operation and synchronous)

     string dateText = ((DateTime)targetEntity["dateField"]).ToLocalTime().Day + " " + GetMonth(((DateTime)targetEntity["dateField"]).ToLocalTime().Month) +
                                " " + ((DateTime)targetEntity["dateField"]).ToLocalTime().Year;
     targetEntity["dateTextField"] = dateText;
    

    By using this new text field you can avoid all the confusions with daylight savings and time zones 


    Sachith Chandrasiri

    Thursday, July 10, 2014 3:14 AM
  • To avoid this I created a new field in the entity to hold the text of the date.  A plugin is written to update this field with the local time of the particular date field.

    Plugin is registered on create and update. (Pre-operation and synchronous)

     string dateText = ((DateTime)targetEntity["dateField"]).ToLocalTime().Day + " " + GetMonth(((DateTime)targetEntity["dateField"]).ToLocalTime().Month) +
                                " " + ((DateTime)targetEntity["dateField"]).ToLocalTime().Year;
     targetEntity["dateTextField"] = dateText;

    By using this new text field you can avoid all the confusions with daylight savings and time zones 


    Sachith Chandrasiri

    Which I have had to do on occasion but that's a bad idea in two respects. First of all is the fact that it breaks database normalisation by duplicating data in a record, ok it may have been reformatted but it is still duplication. The more practical issue is that one has to anticipate all future report requirements in designing the database if someone two months from now asks for a report to include a list of all times of updates in the local time zone then even though I have that data CRM can not reliably report on it.

    I have got around it in the past but only by writing temporary utilities to update all old records but that then creates a spurious entry for the last modified time and it's a messy solution that doesn't deploy well to unmanaged sites. It's fine here where I can sit at a console and type "MakeCRMRuddyWellWork.exe <some whole load of configuration parameters>" but it's not a sufficiently user friendly approach to use evwerywhere.

    Thursday, July 10, 2014 10:16 AM

  • Did you try using the filtered views in the report query instead of the tables?


    Sachith Chandrasiri

    Friday, July 11, 2014 3:54 AM