Reports on a stored procedure result-set instead of table

Hi all,
Is it possible to run reports on stored procedure result-set instead of a
table or view? If so, how can it be done.
AviOn Nov 25, 9:26 am, "Avi" <m...@.m.com> wrote:
> Hi all,
> Is it possible to run reports on stored procedure result-set instead of a
> table or view? If so, how can it be done.
> Avi
Yes. If you are using the Report Wizard, first use the main query in
the stored procedure to get you started and then once you have
finished creating the report, select the Data tab and change the
'Command type:' from 'Text' to 'StoredProcedure' and replace the query
in the window with the stored procedure name. Then select the Refresh
button and exclamation point (!) to run it. The Report Wizard was just
created this way (most likely due to the query having a set number of
columns and structure and a stored procedure not having this until it
is ran). Otherwise, the same kind of process can be used if the report
has already been created. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Don't forget to set your parameters if your stored proc has any. To do so,
go to the parameters tab in the edit dataset window.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:4d7d840c-981f-4d61-985f-29ee99d75df9@.l1g2000hsa.googlegroups.com...
> On Nov 25, 9:26 am, "Avi" <m...@.m.com> wrote:
>> Hi all,
>> Is it possible to run reports on stored procedure result-set instead of a
>> table or view? If so, how can it be done.
>> Avi
>
> Yes. If you are using the Report Wizard, first use the main query in
> the stored procedure to get you started and then once you have
> finished creating the report, select the Data tab and change the
> 'Command type:' from 'Text' to 'StoredProcedure' and replace the query
> in the window with the stored procedure name. Then select the Refresh
> button and exclamation point (!) to run it. The Report Wizard was just
> created this way (most likely due to the query having a set number of
> columns and structure and a stored procedure not having this until it
> is ran). Otherwise, the same kind of process can be used if the report
> has already been created. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||If you use temp tables here is a handy list on how to write your SP to use
temp tables in a RS compatible way:
1. Click on the refresh fields button (to the right of the ...) if your
field list is not showing up.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables. Let the temp tables just fall out
of scope. SQL Server will properly dispose of them when they are no longer
needed. I think people explicitly drop them due to habit. Most likely at one
time it was the proper thing to do. It is not necessary and if you drop them
then stored procedures will not work with RS.
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I have found this to only be an issue when you create a temp table in your
stored procedure that is then filled with data from another stored
procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Avi" <m@.m.com> wrote in message news:fic421$8h3$1@.news2.netvision.net.il...
> Hi all,
> Is it possible to run reports on stored procedure result-set instead of a
> table or view? If so, how can it be done.
> Avi
>