| Author |
Topic |
|
evan_7117
Starting Member
5 Posts |
 Posted - 2004-11-15 : 19:15:52
|
| This part of the code needs to be a very generic middle layer...I need to take the results from a Stored Procedure (and also an OPENQUERY) and rename the columns to:Column1, Column2I wish this would work:Select * from (exec StoredProcedure)Finally, I have no idea what column names will be returned from the Stored Procedure. I need to name the first column 'Column 1' etc.I wish this would work:Select Column(1) as Column1 from (exec StoredProcedure)Thanks for helping. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-15 : 19:30:47
|
| Since you're doing this in a middle layer and therefore using ADO, ADO.Net or some similar data access method, it would be better to rename the columns in your application code, instead of trying to do it with SQL. |
 |
|
|
evan_7117
Starting Member
5 Posts |
Posted - 2004-11-15 : 19:56:45
|
| It is a Stored Procedure because we are using Reporting Services. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-15 : 20:01:18
|
| Then why can't you just change the caption in the report to use whichever name you like? Are you trying to create a generic report? |
|
|
|
evan_7117
Starting Member
5 Posts |
Posted - 2004-11-15 : 20:11:52
|
| I could do that but I'm creating a report template. Each report will be driven by a database table with report names and stored procedure names. I need a way to get the data from a Stored Proc or OLAP open query into standard set of names so that my single Reporting Services template can bind the data to a field. |
|
|
|
evan_7117
Starting Member
5 Posts |
Posted - 2004-11-15 : 20:19:47
|
| I had given thought to creating a temporary table but there must be a better way.---------------------------------------------CREATE TABLE #Table (Dimension varchar(100), SubDimension varchar(100), Measure varchar(100), DimensionName varchar(100))INSERT #TableEXEC QuarterlyReportsSELECT *FROM #TableDROP TABLE #Table |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-15 : 20:21:30
|
| I believe you can bind the report columns using DataSource.Fields(1), DataSource.Fields(2) references. That way you won't need the names at all. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-15 : 20:41:57
|
quote:
Originally posted by evan_7117
It is a Stored Procedure because we are using Reporting Services.
You should always strive for stored proceduers, not just because it is reporting services.Just loop through the column collection of the resultset and assign names:could be something like this:for(int i = 0; i < columns.cóunt; i++ ) columns[i].name = "Column" + i.ToString()"I could do that but I'm creating a report template. Each report will be driven by a database table with report names and stored procedure names."How about extending the metadata for the reports to column names?Or is that what you are doing?, then you just need the position of the column as it is retrieved by the sp, no need to explicitly name them "Column1",2,3,4,,rockmoose |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-15 : 20:48:50
|
| To me, a report template is the page header/footer and font names & sizes, graphics and logos, parameter prompting specs, and margins and stuff like that -- and I would stop there. the data for each report will probably be unique, in terms of both the columns returned and the amount of data and/or grouping and summarization needed. not to mention subreports and other more complicated stuff.Trying to put everything into 1 generic template, and forcing too much into that template, will not only complicate things (as you have seen so far) but ultimately make your reports very inflexible -- you'll end up with 100 useless "Mickey Mouse" reports that no one will ever use.- Jeff |
|
|
|
evan_7117
Starting Member
5 Posts |
Posted - 2004-11-15 : 20:54:28
|
If this is possible it would be a very slick answer to the problem. It seems that it should be possible.I am editing the field as shown in this picture  I edit the field and select caclulated field. I have tried serveral variations, but I am not having any luck... =DataSet1.Fields(1).value=DataSet1.Fields(1)=DataSet1!Fields(1).value=DataSet1!Fields(1)Thanks for your help. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-15 : 21:34:34
|
quote:
Originally posted by jsmith8858
To me, a report template is the page header/footer and font names & sizes, graphics and logos, parameter prompting specs, and margins and stuff like that -- and I would stop there. the data for each report will probably be unique, in terms of both the columns returned and the amount of data and/or grouping and summarization needed. not to mention subreports and other more complicated stuff.Trying to put everything into 1 generic template, and forcing too much into that template, will not only complicate things (as you have seen so far) but ultimately make your reports very inflexible -- you'll end up with 100 useless "Mickey Mouse" reports that no one will ever use.- Jeff
Not every report has to be a MonaLisa.A lot of reports are just thread of the mill reports presenting rows and columns of data to the user.I see no harm in extending the metadata of the report to encompass presentation names for the columns, sorting ability, hidden columns, summation, editable etc.A lot of the reports are "Mickey Mouse" reports anyway and would fit into a reporting framework.I see this as preferable to "handcrafting" every single report that the user requests.rockmoose |
|
|
|
|
Rename columns returned from stored proc