Home Forums Reporting Services Reporting Services Report adds a new year's data to the right of the report - way to automate? RE: Report adds a new year's data to the right of the report - way to automate?

  • Your code seems really unefficient and unconsistent. Here's a better option to do it.

    SELECT client,

    SUM( CASE WHEN year = 2003 THEN fees_billed ELSE 0 END) as fees_billed_2003,

    SUM( CASE WHEN year = 2004 THEN fees_billed ELSE 0 END) as fees_billed_2004,

    SUM( CASE WHEN year = 2005 THEN fees_billed ELSE 0 END) as fees_billed_2005,

    --and so on

    SUM( CASE WHEN year = 2013 THEN fees_billed ELSE 0 END) as fees_billed_2013

    FROM client c

    JOIN whse w on c.client= w.client

    --and begdt>=‘1/1/2004’ and enddt<=‘12/31/2013’

    GROUP BY client

    ORDER BY client

    It's still static code but it will go through your table just once. From here, you can make the T-SQL dynamic, but you might still need to change the report front-end.

    To convert the code into dynamic code, read the following articles:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2