| Author |
 Topic  |
|
|
HughM
Starting Member
Ireland
3 Posts |
 Posted - 02/18/2013 : 07:07:17
|
I am looking to write a report which is to be used on multiple instances of SQL 2008. One of the tables I am writing my view against is an analysis table, the heading of 6 of the columns are Analysis A, Analysis B, Analysis C, Analysis D, Analysis E and Analysis F. A separate table holds the values of these fields. The users being able to decide on what these analysis are named through the application program.
I have tried to write a view which uses the "as" function to populate the column header. While this works with a fixed value such as
"select SopOrderReturnID, ItemCode, ItemDescription, LineTotalValue, AnalysisCode1 as Region
from SOPOrderReturnLine"
it does not work when I try and use a subquery (which only has a single valid value).
"select SopOrderReturnID, ItemCode, ItemDescription, LineTotalValue, AnalysisCode1 as (SELECT dbo.SYSTraderAnalysisHeading.Name
FROM dbo.SYSTraderAnalysisHeading
WHERE (dbo.SYSTraderAnalysisHeading.Name = 'PO Status'))
from SOPOrderReturnLine
"
I get the following error, Unable to Parse Query
All help gratefully received and dutifully acknowledged
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 02/18/2013 : 07:13:29
|
you need to use dynamic sql to add dynamic alias like this in your sql query
But as you've suggested that values come from a table, what might be worth is to add a matrix container in report to get dynamic column headers if you're looking at some kind of crosstabing solution.
To suggest more, I need to understand what you're trying to do
If you could post some sample data and show how you want report to display, we will be more clear.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
HughM
Starting Member
Ireland
3 Posts |
Posted - 02/18/2013 : 07:43:11
|
quote:
Originally posted by visakh16
you need to use dynamic sql to add dynamic alias like this in your sql query
But as you've suggested that values come from a table, what might be worth is to add a matrix container in report to get dynamic column headers if you're looking at some kind of crosstabing solution.
To suggest more, I need to understand what you're trying to do
If you could post some sample data and show how you want report to display, we will be more clear.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
How can I upload some simple data so that you can look at it?
|
Edited by - HughM on 02/18/2013 07:43:56 |
|
|
|
HughM
Starting Member
Ireland
3 Posts |
Posted - 02/18/2013 : 07:45:24
|
| I am really trying to write a view where the column header will change based on the value the users put into their analysis field. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 02/18/2013 : 08:26:10
|
nope view cant have dynamic column names
As suggested you should be doing this at reporting end
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Dynamic Column Header
