Post reply

SSRS 2005 Matrix Date Sort Issue

  • October 7, 2008 at 8:45 am

    #193981

    I am using the expression:

    =Iif(IsNothing(Sum(Fields!DistributionAmount.Value)), 0, Sum(Fields!DistributionAmount.Value))

    I am returning data from Jan 2005 (beginning of data in database) to present. Since there is no data for Jan 2005, I am returning 0.00 for that month. My issue is that I cannot get Jan in the first column AND 2005 in the first row.

    Here is the query:

    SELECT

    i.InvoiceGLDate AS 'GL Date',

    DATEPART(Year, i.InvoiceGLDate) AS 'Year',

    DATENAME(Month, i.InvoiceGLDate) AS 'Month',

    DATEPART(Month, i.InvoiceGLDate),

    ISNULL(gl.DistributionAmount, 0.00) AS 'DistributionAmount'

    FROM

    dbo.SomeTables

    WHERE

    a.GLAccountCodeScrunched = '100252200'

    AND ii.ItemDescription IN ('Unleaded Fuel', 'Diesel Fuel')

    ORDER BY DATEPART(Month, i.InvoiceGLDate), DATEPART(Year, i.InvoiceGLDate)

    If I use this order by clause, I get 2006, 2007, 2008, 2005 for the order of my years. If I reverse the order by clause, the years display correctly, but the month row starts with February and ends with January.

    Am I missing something simple?

    Thanks for your time!

  • October 7, 2008 at 8:53 am

    #881730

    Why not just order by the date instead of the datepart?

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • October 7, 2008 at 8:57 am

    #881731

    I get the same results

  • October 7, 2008 at 8:58 am

    #881732

    Additionally, you could use a numbers/calendar table with a UNION statement or an outer join to force all of the months/years to appear regardless of if they have data or not...

    -Luke.

    EDIT: Here's a link to Jeff's How using a numbers/tally table. In the dozens of other uses section he talks about using it to generate a list including all dates in a range with an outer join which I believe should fit your purposes nicely.

    http://www.sqlservercentral.com/articles/TSQL/62867/

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply