|
|
You're in the
sql server reporting services
group:2005 reporting services and parameters
sql server reporting services:
I would like to creat a report using this SP. in other reporting tools I have created the report and I had it call an SP instead of using a query. the reporting tools would alway add the paramters to the report and ask for the info. I can't seem to create a report using SQL reporting services. I add "exec ProcNAme" in the report wizard, but it tells me it is expecting paramaters. I can't hard code the paramters, that would defeat the purpose of params. TIA,
Thank you! This was driving me crazy.
[quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Support for sp in the wizards is weak. Hard code the parameter just for the > wizard. > > exec yourproc 'sometext', 0.101,'someothertext' > > After running the wizard go to the dataset, change the command type to > stored procedure and put in just the name of the stored procedure (do not > put exec). > > yourproc > > RS will now recognize the parameters and add report parameters to match the > query parameters and map them. It is important to understand that query > params and report parameters are two different things. The reason it is > important is because you can base a query parameter on a expression instead > of using a report parameter. > > Anyway, that is an aside. Follow the above and it should work for you. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "jaylou" <jaylou@discussions.microsoft.com> wrote in message > news:0E52E72C-190A-4052-BA09-4527EB8831B3@microsoft.com... > >I have created an SP that has 3 parameters. > > I would like to creat a report using this SP. in other reporting tools I > > have created the report and I had it call an SP instead of using a query. > > the reporting tools would alway add the paramters to the report and ask > > for > > the info. > > I can't seem to create a report using SQL reporting services. I add > > "exec ProcNAme" in the report wizard, but it tells me it is expecting > > paramaters. I can't hard code the paramters, that would defeat the > > purpose > > of params. > > TIA, > > Joe > >
Support for sp in the wizards is weak. Hard code the parameter just for the
wizard. exec yourproc 'sometext', 0.101,'someothertext' After running the wizard go to the dataset, change the command type to stored procedure and put in just the name of the stored procedure (do not put exec). yourproc RS will now recognize the parameters and add report parameters to match the query parameters and map them. It is important to understand that query params and report parameters are two different things. The reason it is important is because you can base a query parameter on a expression instead of using a report parameter. Anyway, that is an aside. Follow the above and it should work for you. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "jaylou" <jaylou@discussions.microsoft.com> wrote in message news:0E52E72C-190A-4052-BA09-4527EB8831B3@microsoft.com... >I have created an SP that has 3 parameters. > I would like to creat a report using this SP. in other reporting tools I > have created the report and I had it call an SP instead of using a query. > the reporting tools would alway add the paramters to the report and ask > for > the info. > I can't seem to create a report using SQL reporting services. I add > "exec ProcNAme" in the report wizard, but it tells me it is expecting > paramaters. I can't hard code the paramters, that would defeat the > purpose > of params. > TIA, > Joe
Have you also noticed that SQL 2005 reporting services does not like #temp
tables? I dont understand what Microsoft was thinking, I use temp tables in just about every Stored Proc I write. Thanks,
Not true.
I use temp tables all the time. Do the following. Do not explicitly drop the table in your stored procedure. I.e. do not do this: drop table #temp Let it fall out of scope naturally. Second, do a select statement before exiting your stored procedure. Something like this: select * from #temp Just about every stored procedure I use uses temp tables without any problem whatsoever. If you continue to have problems post some examples of what you are doing. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "jaylou" <jaylou@discussions.microsoft.com> wrote in message news:EF45BAF3-F049-4FF5-B144-30F294B3AE2B@microsoft.com... > Have you also noticed that SQL 2005 reporting services does not like #temp > tables? > > I dont understand what Microsoft was thinking, I use temp tables in just > about every Stored Proc I write. > > Thanks, > Joe
Thank you. I will try this on my next proc. Most of my temp tables are
created using select into statements. This could be my issue. Thanks again for the help. Joe [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Not true. > > I use temp tables all the time. > > Do the following. Do not explicitly drop the table in your stored procedure. > I.e. do not do this: > > drop table #temp > > Let it fall out of scope naturally. > > Second, do a select statement before exiting your stored procedure. > Something like this: > > select * from #temp > > Just about every stored procedure I use uses temp tables without any problem > whatsoever. > > If you continue to have problems post some examples of what you are doing. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "jaylou" <jaylou@discussions.microsoft.com> wrote in message > news:EF45BAF3-F049-4FF5-B144-30F294B3AE2B@microsoft.com... > > Have you also noticed that SQL 2005 reporting services does not like #temp > > tables? > > > > I dont understand what Microsoft was thinking, I use temp tables in just > > about every Stored Proc I write. > > > > Thanks, > > Joe > >
Select into is fine. But, you also need to finish up with a Select * from
#temp. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "jaylou" <jaylou@discussions.microsoft.com> wrote in message news:9743C586-BF52-4FAB-ABF0-84A09F22958D@microsoft.com... > Thank you. I will try this on my next proc. Most of my temp tables are > created using select into statements. This could be my issue. > > Thanks again for the help. > > Joe > > > "Bruce L-C [MVP]" wrote: > >> Not true. >> >> I use temp tables all the time. >> >> Do the following. Do not explicitly drop the table in your stored >> procedure. >> I.e. do not do this: >> >> drop table #temp >> >> Let it fall out of scope naturally. >> >> Second, do a select statement before exiting your stored procedure. >> Something like this: >> >> select * from #temp >> >> Just about every stored procedure I use uses temp tables without any >> problem >> whatsoever. >> >> If you continue to have problems post some examples of what you are >> doing. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "jaylou" <jaylou@discussions.microsoft.com> wrote in message >> news:EF45BAF3-F049-4FF5-B144-30F294B3AE2B@microsoft.com... >> > Have you also noticed that SQL 2005 reporting services does not like >> > #temp >> > tables? >> > >> > I dont understand what Microsoft was thinking, I use temp tables in >> > just >> > about every Stored Proc I write. >> > >> > Thanks, >> > Joe >> >> >>
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 |