|
|
You're in the
sql server reporting services
group:Report based on Stored Procedure
sql server reporting services:
I have a report based on a stored procedure. The stored procedure does some calculations and finally returns a query based on a temporary table: Select * from #Tmp The problem is Reporting Services doesn't see the results based on a #temporary table (no error, no rows!). If I change the stored procedure to return an actual table in database, reporting services can see the results. Ho can I have a report based on a stored procedure that returns a query based on a temporary table? Any help would be appreciated, Alan
Are you using the report designer and the data tab? Does the stored
procedure execute and return data from the data tab? If so, sometimes executing the stored procedure does not fill the field list. Try clicking on the refresh fields button (look to the right of the ... , it looks like the fresh button for IE)
You know, I have a similar problem. We have a stored procedure which returns
what is essentially a "data dictionary" with extended properties in a temporary table. If I click the ! in the data tab, I see the full results of the stored procedure. If I go to the preview tab, RS only sees the first table and the first column of that table. I am jumping through hoops here trying to get RS to see everything from this stored procedure. I did see a note in a RS book somewhere that says RS can't see anything beyond one record in a multi-record returned set. If that is so, though, why can I see everything on the data tab? My next attempts have centered around trying to create cascading parameters, but though the first stored procedure returns me a drop down table list, the next stored procedure which requires a tablename for input and outputs a column list for that table keeps saying there are no fields for me to choose from to place on the report. Not to mention that I can't figure out how to get the table name from the first dataset to the second dataset for input. ARGH! Can anyone give me some ideas or point me in a better direction than these lousy RS Online help files? They're practically worthless! They don't even have a searchable Index tab like BOL has. Thanks in advance, Catadmin [quoted text, click to view] "A.M" wrote:
> Hi, > > > > I have a report based on a stored procedure. > > The stored procedure does some calculations and finally returns a query > based on a temporary table: > > > > Select * from #Tmp > > > > The problem is Reporting Services doesn't see the results based on a > #temporary table (no error, no rows!). If I change the stored procedure to > return an actual table in database, reporting services can see the results. > > > > Ho can I have a report based on a stored procedure that returns a query > based on a temporary table? > > > > Any help would be appreciated, > > Alan > >
RS can handle one resultset. When you click on the !, the resultset you see
is what you have to work with. On the left should be a field list, if the fields are not showing there that is the first thing you need to do (try the refresh fields button, to the right of the ...). The dataset needs to be associated with something. Drag a table onto the form and then drag fields over to the table columns. I think you need to back up and just try some simple queries and make sure you know how to create a report. SP complicate things and you are try to simultaneously learn too many things at once. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Catadmin" <Catadmin@discussions.microsoft.com> wrote in message news:5058D8C3-F2B9-4964-9D64-96849FEFC3DE@microsoft.com... > You know, I have a similar problem. We have a stored procedure which returns > what is essentially a "data dictionary" with extended properties in a > temporary table. If I click the ! in the data tab, I see the full results of > the stored procedure. If I go to the preview tab, RS only sees the first > table and the first column of that table. I am jumping through hoops here > trying to get RS to see everything from this stored procedure. > > I did see a note in a RS book somewhere that says RS can't see anything > beyond one record in a multi-record returned set. If that is so, though, why > can I see everything on the data tab? My next attempts have centered around > trying to create cascading parameters, but though the first stored procedure > returns me a drop down table list, the next stored procedure which requires a > tablename for input and outputs a column list for that table keeps saying > there are no fields for me to choose from to place on the report. Not to > mention that I can't figure out how to get the table name from the first > dataset to the second dataset for input. > > ARGH! Can anyone give me some ideas or point me in a better direction than > these lousy RS Online help files? They're practically worthless! They don't > even have a searchable Index tab like BOL has. > > Thanks in advance, > > Catadmin > > "A.M" wrote: > > > Hi, > > > > > > > > I have a report based on a stored procedure. > > > > The stored procedure does some calculations and finally returns a query > > based on a temporary table: > > > > > > > > Select * from #Tmp > > > > > > > > The problem is Reporting Services doesn't see the results based on a > > #temporary table (no error, no rows!). If I change the stored procedure to > > return an actual table in database, reporting services can see the results. > > > > > > > > Ho can I have a report based on a stored procedure that returns a query > > based on a temporary table? > > > > > > > > Any help would be appreciated, > > > > Alan > > > > > >
Bruce,
Thank you for your response. You are correct, I am trying to learn the whole thing at once. My problem is that I went from one job that used Crystal to another job that doesn't, they only use RS, and I'm expected to finish this project that the previous DBA left unfinished. Given the time limit I'm on, I'm not sure they're going to let me take my time and learn it. Also, I can do simple queries. I just did two of them today. I think my problem is in the grouping, if that makes sense. In Crystal, you could create groups to do for a report what a cursor does for SQL, loop back and catch the rest of the data. I've seen how to do so using the Report Wizard, but, as I posted in another post just a little while ago, I can't figure out how to do groups from a blank report screen. On top of that, I'm learning how to use the fn_listextendedproperty function to pull the table and column descrips, and my day has been a serious exercise in frustration. I think I'll go try to drown myself in the gigantic fountain out front of the office. @=P If I can just figure out how to pull the fn_listextendedproperty function into the report with the other fields without having to use the stored procedures, AND/OR how to group on a blank report, I might have a work around that should keep the boss happy for a while so I can go and learn things the proper way. Ah, well. Such is the life... @=) Thanks, again. Catadmin [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> RS can handle one resultset. When you click on the !, the resultset you see > is what you have to work with. On the left should be a field list, if the > fields are not showing there that is the first thing you need to do (try the > refresh fields button, to the right of the ...). The dataset needs to be > associated with something. Drag a table onto the form and then drag fields > over to the table columns. > > I think you need to back up and just try some simple queries and make sure > you know how to create a report. SP complicate things and you are try to > simultaneously learn too many things at once. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Catadmin" <Catadmin@discussions.microsoft.com> wrote in message > news:5058D8C3-F2B9-4964-9D64-96849FEFC3DE@microsoft.com... > > You know, I have a similar problem. We have a stored procedure which > returns > > what is essentially a "data dictionary" with extended properties in a > > temporary table. If I click the ! in the data tab, I see the full results > of > > the stored procedure. If I go to the preview tab, RS only sees the first > > table and the first column of that table. I am jumping through hoops here > > trying to get RS to see everything from this stored procedure. > > > > I did see a note in a RS book somewhere that says RS can't see anything > > beyond one record in a multi-record returned set. If that is so, though, > why > > can I see everything on the data tab? My next attempts have centered > around > > trying to create cascading parameters, but though the first stored > procedure > > returns me a drop down table list, the next stored procedure which > requires a > > tablename for input and outputs a column list for that table keeps saying > > there are no fields for me to choose from to place on the report. Not to > > mention that I can't figure out how to get the table name from the first > > dataset to the second dataset for input. > > > > ARGH! Can anyone give me some ideas or point me in a better direction > than > > these lousy RS Online help files? They're practically worthless! They > don't > > even have a searchable Index tab like BOL has. > > > > Thanks in advance, > > > > Catadmin > > > > "A.M" wrote: > > > > > Hi, > > > > > > > > > > > > I have a report based on a stored procedure. > > > > > > The stored procedure does some calculations and finally returns a query > > > based on a temporary table: > > > > > > > > > > > > Select * from #Tmp > > > > > > > > > > > > The problem is Reporting Services doesn't see the results based on a > > > #temporary table (no error, no rows!). If I change the stored procedure > to > > > return an actual table in database, reporting services can see the > results. > > > > > > > > > > > > Ho can I have a report based on a stored procedure that returns a query > > > based on a temporary table? > > > > > > > > > > > > Any help would be appreciated, > > > > > > Alan > > > > > > > > > > >
I have some udf's but I have only used them from within my sp. I'll try
using one from RS query window and let you know how it goes. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Catadmin" <goldpetalgraphics@yahoo.com> wrote in message news:EE98488F-5D23-4BA6-9239-AB950052B508@microsoft.com... > Bruce, > > Thank you for your response. You are correct, I am trying to learn the > whole thing at once. My problem is that I went from one job that used > Crystal to another job that doesn't, they only use RS, and I'm expected to > finish this project that the previous DBA left unfinished. Given the time > limit I'm on, I'm not sure they're going to let me take my time and learn > it. > > Also, I can do simple queries. I just did two of them today. I think my > problem is in the grouping, if that makes sense. In Crystal, you could > create groups to do for a report what a cursor does for SQL, loop back and > catch the rest of the data. I've seen how to do so using the Report > Wizard, > but, as I posted in another post just a little while ago, I can't figure > out > how to do groups from a blank report screen. On top of that, I'm learning > how to use the fn_listextendedproperty function to pull the table and > column > descrips, and my day has been a serious exercise in frustration. I think > I'll > go try to drown myself in the gigantic fountain out front of the office. > @=P > > If I can just figure out how to pull the fn_listextendedproperty function > into the report with the other fields without having to use the stored > procedures, AND/OR how to group on a blank report, I might have a work > around > that should keep the boss happy for a while so I can go and learn things > the > proper way. > > Ah, well. Such is the life... @=) Thanks, again. > > Catadmin > > "Bruce L-C [MVP]" wrote: > >> RS can handle one resultset. When you click on the !, the resultset you >> see >> is what you have to work with. On the left should be a field list, if the >> fields are not showing there that is the first thing you need to do (try >> the >> refresh fields button, to the right of the ...). The dataset needs to be >> associated with something. Drag a table onto the form and then drag >> fields >> over to the table columns. >> >> I think you need to back up and just try some simple queries and make >> sure >> you know how to create a report. SP complicate things and you are try to >> simultaneously learn too many things at once. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Catadmin" <Catadmin@discussions.microsoft.com> wrote in message >> news:5058D8C3-F2B9-4964-9D64-96849FEFC3DE@microsoft.com... >> > You know, I have a similar problem. We have a stored procedure which >> returns >> > what is essentially a "data dictionary" with extended properties in a >> > temporary table. If I click the ! in the data tab, I see the full >> > results >> of >> > the stored procedure. If I go to the preview tab, RS only sees the >> > first >> > table and the first column of that table. I am jumping through hoops >> > here >> > trying to get RS to see everything from this stored procedure. >> > >> > I did see a note in a RS book somewhere that says RS can't see anything >> > beyond one record in a multi-record returned set. If that is so, >> > though, >> why >> > can I see everything on the data tab? My next attempts have centered >> around >> > trying to create cascading parameters, but though the first stored >> procedure >> > returns me a drop down table list, the next stored procedure which >> requires a >> > tablename for input and outputs a column list for that table keeps >> > saying >> > there are no fields for me to choose from to place on the report. Not >> > to >> > mention that I can't figure out how to get the table name from the >> > first >> > dataset to the second dataset for input. >> > >> > ARGH! Can anyone give me some ideas or point me in a better direction >> than >> > these lousy RS Online help files? They're practically worthless! They >> don't >> > even have a searchable Index tab like BOL has. >> > >> > Thanks in advance, >> > >> > Catadmin >> > >> > "A.M" wrote: >> > >> > > Hi, >> > > >> > > >> > > >> > > I have a report based on a stored procedure. >> > > >> > > The stored procedure does some calculations and finally returns a >> > > query >> > > based on a temporary table: >> > > >> > > >> > > >> > > Select * from #Tmp >> > > >> > > >> > > >> > > The problem is Reporting Services doesn't see the results based on a >> > > #temporary table (no error, no rows!). If I change the stored >> > > procedure >> to >> > > return an actual table in database, reporting services can see the >> results. >> > > >> > > >> > > >> > > Ho can I have a report based on a stored procedure that returns a >> > > query >> > > based on a temporary table? >> > > >> > > >> > > >> > > Any help would be appreciated, >> > > >> > > Alan >> > > >> > > >> > > >> >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |