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 |
HughM
Starting Member
3 Posts |
Posted - 2013-02-18 : 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 Regionfrom 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.NameFROM dbo.SYSTraderAnalysisHeading WHERE (dbo.SYSTraderAnalysisHeading.Name = 'PO Status'))from SOPOrderReturnLine"I get the following error, Unable to Parse QueryAll help gratefully received and dutifully acknowledged |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 07:13:29
|
you need to use dynamic sql to add dynamic alias like this in your sql queryBut 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 doIf you could post some sample data and show how you want report to display, we will be more clear.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
HughM
Starting Member
3 Posts |
Posted - 2013-02-18 : 07:43:11
|
quote: Originally posted by visakh16 you need to use dynamic sql to add dynamic alias like this in your sql queryBut 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 doIf you could post some sample data and show how you want report to display, we will be more clear.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/How can I upload some simple data so that you can look at it?
|
|
|
HughM
Starting Member
3 Posts |
Posted - 2013-02-18 : 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
52326 Posts |
Posted - 2013-02-18 : 08:26:10
|
nope view cant have dynamic column namesAs suggested you should be doing this at reporting end------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|