August 8, 2011 at 8:27 am
Is it possible to name a column based on a field value. I want to name a column based on the current period number. So the current period is 8 I want the report to look like this.
[font="Courier New"]
PRODUCT DESC AUG JUL
AAAAAAAA YYYYYY 500 150
BBBBBBBBB YYYYYY 1500 2000
......
ZZZZZZZZ YYYYYY 50 50[/font]
Cols AUG & JUL are calculated based on the period number which is a look up on another table. AUG & JUL are summed calculations.
My current report looks like this. I want to replace "TOTALP" with "AUG" and delete col "PERIOD" and replace "TOTALLP" with "JUL"
[font="Courier New"]
PRODUCT____DESC________________PERIOD TOTALP LAST PERIOD TOTALLP
GR71080700 AAAAAAAAAAAAAAAAAAAAAUG1266JUL 28427
FD60461900 BBBBBBBBBBBBBBBBBBBBAUG5744JUL 19866
4004115000 CCCCCCCCCCCCCCCCCCCCAUG5916JUL 16380
FL50080001 DDDDDDDDDDDDDDDDDDDDAUG2558JUL 11415
PK22400300 EEEEEEEEEEEEEEEEEEEEAUG5400JUL 11100[/font]
August 8, 2011 at 8:41 am
Where are these results appearing - in SSMS, in an application, or in Reporting Services? This is the kind of thing that is best handled by the application (presentation) tier. If you don't have the option of changing the application, you can calculate the names of the columns and then use dynamic SQL to make those names appear at the top of the result set.
John
August 8, 2011 at 8:47 am
I want a select statement I can use in a Excel.
August 8, 2011 at 8:51 am
I think I'd do it by running the query in SQL and then using an Excel macro to change the name of the column.
John
August 8, 2011 at 9:11 am
I've sorted this by not returning the column names in Excel and hiding the cols with AUG & JUL in them and then on the cols with figures in I've set the column heading as a formula to equal row 2 of the months.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply