2005 reporting services and parameters

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,
JoeSupport 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|||Thank you! This was driving me crazy.
"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
>
>|||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|||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|||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
>
>|||Select into is fine. But, you also need to finish up with a Select * from
#temp.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"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
>>