locked
Remove Time from Date/Time Field on Report RRS feed

  • Question

  • Part 1: I have a custom expiration date for Opportunities. When I run a report that includes that field, it shows up with both the date and the time. This occurs regardless of whether the field is "Date Only" or "Date & Time". How do I remove the time so that it does not appear in the report?

    Part 2: Even better, since the date is all I really want, how do I strip the time from the date field to begin with? I still need to use the field as an actual date field (not a text string) so that I can perform calculations on it. I also want to avoid problems with users in different time zones. (E.g., when a "Date Only" field is entered, the 00:00 time is automatically attached. When a user in a later time zone reads the date, the date is always one date earlier because CRM adjusts the 00:00 time for the true date to be 23:00 on the previous date.)

    Many thanks,
    Monday, April 27, 2009 7:32 PM

Answers

  • Hi David,

    Yes, you will have limited ability to format data using the Report Wizard.  If you want more control, you should create the report in Visual Studio.  there will be a little bit of a learning curve but I am sure you will pick it up quickly.

    Install Visual Studio and create a CRM Reports project (Report Server Project).  Once the project is created, ensure it is open and add a new report to it by right-clicking the Project and select Add New Item, select Report.  You can use the Report Wizard within VS to get started until you have a better understanding of what is going on. 

    I would create a simple query for my first report something like the below

    Select opportunityid, CustomExpirationDatefield, topic, statecodename from filteredOpportunity (nolock) for your dataset.  ensure you dataset does not have Shared Datasource selected as CRM will complain when you try to upload the report.

    Next, go to the Layout tab of your report and add a table.  Drop your fields into the table cells.  In the field where the CustomExpirationDate exists, right-click it and select properties.  Go to the format tab and select the data format you want.  There is another way to do the same thing and that is by selecting the field in the table and viewing the property window if you have that window open in your report environment but you don't have to worry about that for now.

    Select the Preview tab to view your report and you will see the date format you selected.

    Here are some links to Report tutorials for VS to get you started.  Work through this and let me know if you have any questions.

    Report Writers Guide

    http://msdn.microsoft.com/en-us/library/bb955081.aspx


    Create a report in 15 minutes or less

    http://www.microsoft.com/dynamics/crm/using/customizing/reporttutorial.mspx

    CRM 4.0 Reporting with Barry Givens and Phil Richardson

    http://channel9.msdn.com/posts/Charles/CRM-40-Reporting-with-Barry-Givens-and-Phil-Richardson/


    Best Regards, Donna
    • Proposed as answer by Donna EdwardsMVP Wednesday, April 29, 2009 5:32 PM
    • Marked as answer by Davidgm Wednesday, September 16, 2009 5:19 PM
    Tuesday, April 28, 2009 2:00 PM

All replies

  • 1. You can format the field on your report to display date only.  I assume you built your report in VS?  If so, right-click the field and select properties, select the format property and choose the data format you want

    2. change the attribute to format to Date Only, in Customization, Opportunity entity.

    To avoid the below, query from filteredviews and not the base tables

    I also want to avoid problems with users in different time zones. (E.g., when a "Date Only" field is entered, the 00:00 time is automatically attached. When a user in a later time zone reads the date, the date is always one date earlier because CRM adjusts the 00:00 time for the true date to be 23:00 on the previous date.)


    Best Regards, Donna
    Monday, April 27, 2009 7:57 PM
  • Donna, thanks for replying to my inquiry. Let me answer some of your questions:

    1. Actually, no, I created the report from within CRM, not using Visual Studio. Does this restrict my ability to format the data fields? I can find the report within SQL Server Management Studio, but on the report properties page I am not able to change the format (only the fields "Has Default", "Null", "Hide", etc.). Do i need to create the eport within VS (and not CRM) in order to make the changes I need?

    2. This does not remove the time stamp from the report. I have tried this already and even if the date attribute on the Opportunity entity is customized to "Date Only", it still appears on the reports. Is this an error? Further, when using CRM's built-in reporting tool, I do not see a way to query data from filtered views instead of base tables.

    Thanks, David
    Monday, April 27, 2009 8:42 PM
  • David,

    You can edit the report without Visual Studio, but it can be overwhelming.  You have to edit the report in CRM, then choose download.  Open the rdl file in notepad and search for the name of your field.  You should find something like this:
    <Textbox Name="new_somedatefield">
                          <rd:DefaultName>new_somedatefield</rd:DefaultName>
                          <Style>
                            <FontFamily>Tahoma</FontFamily>
                            <Format>MM/dd/yyyy</Format>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                          <ZIndex>5</ZIndex>
                          <CanGrow>true</CanGrow>
                          <Value>=Fields!new_somedatefield.Value</Value>
                        </Textbox>
    The line you want to add/edit is the Format line, but make sure you use uppercase M's, since lower case m's are milliseconds.

    Save the rdl and then upload it and you should be all set.
    Monday, April 27, 2009 11:11 PM
  • What I usually do to only get the date and not the time is this:

    1. Go to the properties of the field (in visual studio)
    2. Format
    3. Put this in the expression:   =d/MM/yyyy


    Then it'll only show the date and not the time...
    • Proposed as answer by ThunderSon Tuesday, April 28, 2009 9:16 AM
    • Marked as answer by Davidgm Wednesday, September 16, 2009 5:19 PM
    • Unmarked as answer by Davidgm Wednesday, September 16, 2009 5:19 PM
    Tuesday, April 28, 2009 9:16 AM
  • Hi David,

    Yes, you will have limited ability to format data using the Report Wizard.  If you want more control, you should create the report in Visual Studio.  there will be a little bit of a learning curve but I am sure you will pick it up quickly.

    Install Visual Studio and create a CRM Reports project (Report Server Project).  Once the project is created, ensure it is open and add a new report to it by right-clicking the Project and select Add New Item, select Report.  You can use the Report Wizard within VS to get started until you have a better understanding of what is going on. 

    I would create a simple query for my first report something like the below

    Select opportunityid, CustomExpirationDatefield, topic, statecodename from filteredOpportunity (nolock) for your dataset.  ensure you dataset does not have Shared Datasource selected as CRM will complain when you try to upload the report.

    Next, go to the Layout tab of your report and add a table.  Drop your fields into the table cells.  In the field where the CustomExpirationDate exists, right-click it and select properties.  Go to the format tab and select the data format you want.  There is another way to do the same thing and that is by selecting the field in the table and viewing the property window if you have that window open in your report environment but you don't have to worry about that for now.

    Select the Preview tab to view your report and you will see the date format you selected.

    Here are some links to Report tutorials for VS to get you started.  Work through this and let me know if you have any questions.

    Report Writers Guide

    http://msdn.microsoft.com/en-us/library/bb955081.aspx


    Create a report in 15 minutes or less

    http://www.microsoft.com/dynamics/crm/using/customizing/reporttutorial.mspx

    CRM 4.0 Reporting with Barry Givens and Phil Richardson

    http://channel9.msdn.com/posts/Charles/CRM-40-Reporting-with-Barry-Givens-and-Phil-Richardson/


    Best Regards, Donna
    • Proposed as answer by Donna EdwardsMVP Wednesday, April 29, 2009 5:32 PM
    • Marked as answer by Davidgm Wednesday, September 16, 2009 5:19 PM
    Tuesday, April 28, 2009 2:00 PM