SSRS SQL date order off if cross-year RRS feed

  • Question

  • We have situations where using SSRS and SQL on premise, we order by after several unions (set up in proper alignment) by 'createdon' which is type 'datetime.'

    However a user is complaining that when the date is 'cross-year' in the start date and end date parameters, the order is off.  Here is an example of one of the report fetch queries:

    --declare @StartDate as datetime
    --declare @EndDate as datetime

    --set @StartDate = '07/1/2014'
    --set @EndDate = '7/30/2014'

    SELECT        sc.CreatedOn,sc.CreatedByName, CONVERT(nvarchar(10), sc.xxxbase_SalesCallDate, 101) AS xxxbase_SalesCallDate, sc.xxxbase_accountidName, a.Address1_City,
                             a.Address1_StateOrProvince, sm.Value AS xxxbase_accounttype, null as xxxbase_productidName, null AS 'Estimated Sales',
                             sc.xxxbase_Description, NULL AS mileage, 'Sales Call' AS ReasonCode, sc.xxxbase_salescallId
    FROM xxxbase_salescall AS sc
    left outer JOIN Account AS a ON sc.xxxbase_accountid = a.AccountId
    LEFT OUTER JOIN StringMapBase AS sm ON sm.AttributeValue = a.xxxbase_AccountType AND sm.AttributeName = 'xxxbase_accounttype' and sm.ObjectTypeCode = 1
    WHERE        (sc.xxxbase_SalesCallDate BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate))


    SELECT        CreatedOn,CreatedByName, CONVERT(nvarchar(10), xxxbase_weekof, 101) AS date, '' AS Expr1, '' AS Expr2, '' AS Expr3, NULL AS Expr4, '' AS Expr5, '' AS Expr6,
                             NULL, xxxbase_TotalWorkMiles, 'Mileage' AS Expr8, null
    FROM            xxxbase_Mileage AS m
    WHERE        (xxxbase_weekof BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate)) AND (xxxbase_TotalWorkMiles IS NOT NULL)


    SELECT       nst.CreatedOn, nst.CreatedByName, convert(nvarchar(10),nst.xxxBase_Date,101) as [date] , null, Null, Null, Null,
                             '' AS Expr1, '' AS Expr2, nst.xxxBase_Comments, NULL AS Expr3, sm.Value, null
    FROM            xxxBase_NonSalesTime AS nst
    LEFT OUTER JOIN StringMapBase AS sm ON sm.AttributeValue = nst.xxxBase_ReasonCode AND sm.AttributeName = 'xxxbase_reasoncode'
    WHERE        (nst.xxxBase_Date BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate))


    --SELECT       wpb.CreatedOn, wpb.CreatedByName, convert(nvarchar(10),wpb.xxxBase_Date,101) as [date] , null, Null, Null, Null,
    --                         '' AS Expr1, '' AS Expr2, NULL, NULL AS Expr3, 'Weekly Planner', null
    --from xxxbase_weeklyplanner wpb
    --WHERE        (wpb.xxxBase_Date BETWEEN @StartDate AND DATEADD(dd, 1, @EndDate))

    Order By CreatedbyName, CreatedOn
    Monday, March 9, 2015 9:26 PM

All replies

  • Nothing looks particularly wrong here but there may be some abiguity with the column names. Instead of including sc.CreatedOn, nst.CreatedOn and CreatedOn, you might want to try unifying these so the 3 statements begin with

    SELECT sc.CreatedOn as CreatedOn
    SELECT CreatedOn (this one seems fine)
    SELECT nst.CreatedOn as CreatedOn

    and the same with CreatedByName. I'm not sure whether this will work but it may be something to try.

    Tuesday, March 10, 2015 3:31 PM
  • Your query is referencing the underlying SQL views, rather than the filtered views. This means that you're comparing the dates against dates stored as UTC, rather than the user's time zone, which may be the issue. If you use the corresponding filtered views, the date fields will be converted based on the user's time zone

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

    Tuesday, March 10, 2015 7:34 PM
  • How would I locate these other views in the database?   What are they called?
    Tuesday, March 10, 2015 8:04 PM
  • I don't think that's the whole issue.

    All the 2015s will be in front of the 2014s, even though it's ascending order

    The main tablix now has "=Format(Fields!CreatedOn.Value, "yyyyMMdd")"  as sorting but it didn't help.

    Tuesday, March 10, 2015 8:27 PM
  • Sort like this

    =Datepart("yyyy",Fields!Datum.Value) & Datepart("m",Fields!Datum.Value) & Datepart("d",Fields!Datum.Value)

    Also check if createdon field type in all tables is datetime

    Regards, Saad

    Wednesday, March 11, 2015 7:15 AM
  • The issue was malformed XML.  This was a report I inherited.  The XML was wrong and had three attempted sorts, yet in SSRS there was only one sort.
    Wednesday, March 11, 2015 3:19 PM