Report based on Stored Procedure

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,
AlanAre 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
"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
"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
"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
"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
>> > >
>> > >
>> > >
>>