Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jonleikvold
Starting Member
1 Post |
Posted - 2012-10-10 : 14:03:46
|
My sales manager wants a report from our invoice table that would look like thisCustomer FY2010 FY2011 FY2012 FY2013ABC Inc $20,000 $18,000 $$$$$ $$$$...ZZZ Inc $$$$$ $$$$ $$$$$ $$$$2 complicating factors: Our fiscal year runs from Oct 1 - Sep 30I need to have this report run this year next year and 5 years from now without having to change the program. ie the titles must be variable.The data comes from an invoice table with field names as follows:customer (customer name)document_date (the date of the invoice)amount (the invoice amount)I have achieved some success by adding 3 months to the document_date to get a field with the correct fiscal year, But my crosstab attempts have all failed. Any help would be greatly appreciated. I am running SQL server 2008 r2Jon L |
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-10 : 15:01:47
|
Why not use SSRS, and let the report handle the formatting?-Chad |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-10-10 : 16:06:48
|
if you want to do this with t-sql AND your column names will change depending on the daterange then you will need to implement a "dynamic cross tagb" or "dynamic pivot". Search this site for lots of examples.>>But my crosstab attempts have all failedIf you need customized help then post your attempt(s)Be One with the OptimizerTGEDIT:spelling correction |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|