locked
Trouble when sorting by date in Reports RRS feed

  • Question

  • Hi,

    I'm working on building some custom reports for CRM 4.0. I've used Visual Studio to design the reports. Saved the rdl file, and imported to CRM. No trouble there.

    What I can't seem to figure out, is how to get the report to sort by date correctly. I'm using a date column that displays in a format like "March 23, 2010". When building the query in SQL Server, I can order by that column, and it's sorts correctly (oldest day first, most recent day last). The problem is that when I view the report in CRM (or in the Visual Studio Preivew) it sorts the dates alphabetically - so all the August dates would show up first, followed by December, February, etc.

    It's as if once the results are displayed in the report, it ignores the fact that it's a date and treats it as a string.

    Does anyone know what I'm missing?

     

    Thanks.

    Tuesday, March 23, 2010 1:23 PM

Answers

  • If anyone else has the same problem - I did figure it out.

    When designing the report, I edited the properties of the field group. I went to sorting, and removed the single entry that was there.

    What this did, was stopped the report from doing any sorting at the GUI level, and it just defaulted back to the order the data was in from the query. My ORDER BY clause now works as expected.

    Thursday, March 25, 2010 3:49 PM

All replies

  • Did you define the format of the column as a datetime?

    Can you show use the expression for that date cell?


    My blog : http://mscrmtools.blogspot.com
    You will find:
    Bulk Delete LauncherView Layout replicator
    ISV.Config Manager Form Javascript Manager
    Assembly RecoveryAnd others (use tool tag on my blog)
    Tuesday, March 23, 2010 1:27 PM
    Moderator
  • Sorry, should have included this in the original post. (This may be what's causing my problem).

    The report I'm working with has a user selected parameter, to pick annual, monthly, weekly, or daily reports. (Again, have had no trouble making this work). The downside to this was the way I was asked to label the columns when showing the weekly reports. It would look like this:

    "March 22, 2010 - March 28, 2010"

    Obviously this is a string, not a date and because this column (called 'Time_Period') holds the dates I'm trying to sort by (regardless of which type of report it is) I didn't expect sorting to work on the first try.

    What I've used for my ORDER BY clause in SQL SERVER is this:

    ORDER BY CONVERT(datetime,substring(Time_Period,0,12),9)

    This works in SQL SERVER. The substring leaves only the first date in the March 22 - 28 example above.... and doesn't do anything to the column when it's using one of the other 3 formats (could look like: 2010, March 2010, March 22 2010)... then the convert should switch it from a string back to a datetime.

     

    Tuesday, March 23, 2010 1:45 PM
  • Going to add some more information here, hopefully someone knows the answer.

    What this report is supposed to show, is the number of Cases (incidents) for each Site, in a given time period. The user will have to give a value for each of three variables: Start Date, End Date, Report Type.

    The idea is that you could give a Start Date of August 1, 2009 and an End Date of March 24, 2010..... then select the type (Annually, Monthly, Weekly, Daily).

    This means that the row headers are actually a list of Site names, and the column headers. The values filled into what would now look like a grid, are the total count of Cases, created by each site in the given time period.

    Depending on which type of report you want to use - a different number of columns would be generated. For an Annual report - 2009 and 2010 would be the row headers. If you choose monthly AUG 2009, SEP 2009, right through to MAR 2010 would be the headers. It can be broken down into weeks, or even each individual day.

    I think my problem must be coming from the fact that by making the dates row headers, and not actual data in the grid, I'm losing my ability to sort based on their date - and the report just orders them alphabetically. When I query the data in a SQL Server window, using an ORDER BY clause like the one in my last post, the data is sorted the way I want. When I paste that same query into the data tab on my report, it reverts back to the unwanted alphabetical sorting.

     

    Any suggestions?

    Wednesday, March 24, 2010 8:06 PM
  • If anyone else has the same problem - I did figure it out.

    When designing the report, I edited the properties of the field group. I went to sorting, and removed the single entry that was there.

    What this did, was stopped the report from doing any sorting at the GUI level, and it just defaulted back to the order the data was in from the query. My ORDER BY clause now works as expected.

    Thursday, March 25, 2010 3:49 PM