I've noticed that SP:s that are more complex than just a simple
SELECT-statement don't work correctly when creating new datasets. The dataset
can't seem to populate the Fields when the SP does some additional work
before the SELECT. I know it's possible to create all the Fields manually,
but that isn't an option for us since we have a ton of reports with lots and
lots of fields. Is there another workaround for this? Maybe in SQL Server
2005?No answers? Is there really no way to solve this? This problem could be a
real show stopper for our current project...
"Patrik Ylén" wrote:
> I've noticed that SP:s that are more complex than just a simple
> SELECT-statement don't work correctly when creating new datasets. The dataset
> can't seem to populate the Fields when the SP does some additional work
> before the SELECT. I know it's possible to create all the Fields manually,
> but that isn't an option for us since we have a ton of reports with lots and
> lots of fields. Is there another workaround for this? Maybe in SQL Server
> 2005?|||Have you tried the refresh fields button (to the right of the ...)?
I have SP that do a lot (put data into multiple temp tables etc). The very
last statement is the select. So having stuff above the select matters not
at all. Sometimes I have to click on the refresh fields.
How are you calling the SP (i.e. are you doing an exec or have you set the
command type to stored procedure).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> No answers? Is there really no way to solve this? This problem could be a
> real show stopper for our current project...
> "Patrik Ylén" wrote:
>> I've noticed that SP:s that are more complex than just a simple
>> SELECT-statement don't work correctly when creating new datasets. The
>> dataset
>> can't seem to populate the Fields when the SP does some additional work
>> before the SELECT. I know it's possible to create all the Fields
>> manually,
>> but that isn't an option for us since we have a ton of reports with lots
>> and
>> lots of fields. Is there another workaround for this? Maybe in SQL Server
>> 2005?|||Hmm, it works now. I don't know what I did when I tried it the last time, but
it must've been wrong! :-)
Thanks,
Patrik
"Bruce L-C [MVP]" wrote:
> Have you tried the refresh fields button (to the right of the ...)?
> I have SP that do a lot (put data into multiple temp tables etc). The very
> last statement is the select. So having stuff above the select matters not
> at all. Sometimes I have to click on the refresh fields.
> How are you calling the SP (i.e. are you doing an exec or have you set the
> command type to stored procedure).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> > No answers? Is there really no way to solve this? This problem could be a
> > real show stopper for our current project...
> >
> > "Patrik Ylén" wrote:
> >
> >> I've noticed that SP:s that are more complex than just a simple
> >> SELECT-statement don't work correctly when creating new datasets. The
> >> dataset
> >> can't seem to populate the Fields when the SP does some additional work
> >> before the SELECT. I know it's possible to create all the Fields
> >> manually,
> >> but that isn't an option for us since we have a ton of reports with lots
> >> and
> >> lots of fields. Is there another workaround for this? Maybe in SQL Server
> >> 2005?
>
>|||"Bruce L-C [MVP]" wrote:
> Have you tried the refresh fields button (to the right of the ...)?
> I have SP that do a lot (put data into multiple temp tables etc). The very
> last statement is the select. So having stuff above the select matters not
> at all. Sometimes I have to click on the refresh fields.
> How are you calling the SP (i.e. are you doing an exec or have you set the
> command type to stored procedure).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
> > No answers? Is there really no way to solve this? This problem could be a
> > real show stopper for our current project...
> >
> > "Patrik Ylén" wrote:
> >
> >> I've noticed that SP:s that are more complex than just a simple
> >> SELECT-statement don't work correctly when creating new datasets. The
> >> dataset
> >> can't seem to populate the Fields when the SP does some additional work
> >> before the SELECT. I know it's possible to create all the Fields
> >> manually,
> >> but that isn't an option for us since we have a ton of reports with lots
> >> and
> >> lots of fields. Is there another workaround for this? Maybe in SQL Server
> >> 2005?
> HI!
>I have a related problem. My stored procedure returns a different dataset based on an entered parameter - it returns a particular set of data for display in the report detail (@.showdetail) and another set for display in the report footer (@.showfooter)
When I call the stored procedure from within reporting services (either by
exec spname @.parameter as a text entry, or by referencing it's name) I can
retrieve the 2 separate datasets fine (ie the results sets show up in the
grid). HOWEVER, changing the parameter to 'showfooter' instead of
'showdetail' does not update the associated field list. Which means I can't
drag the second dataset's fields onto my layout.
Any ideas?|||RS only supports one dataset being returned from the SP. If you have a
single stored procedure that returns multiple datasets then you either need
to modify the stored procedure to return the appropriate dataset based on a
parameter or you need to split it into two stored procedure.
Note that you can have many many datasets in your report. You just cannot
have a stored procedure return more than one dataset. And, no, this has not
changed with 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:28DDEFA3-2903-4E8B-B56E-15757E303832@.microsoft.com...
>
> "Bruce L-C [MVP]" wrote:
>> Have you tried the refresh fields button (to the right of the ...)?
>> I have SP that do a lot (put data into multiple temp tables etc). The
>> very
>> last statement is the select. So having stuff above the select matters
>> not
>> at all. Sometimes I have to click on the refresh fields.
>> How are you calling the SP (i.e. are you doing an exec or have you set
>> the
>> command type to stored procedure).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Patrik Ylén" <PatrikYln@.discussions.microsoft.com> wrote in message
>> news:FB603E2E-CBD1-4773-BF3C-7B4EC6B328A6@.microsoft.com...
>> > No answers? Is there really no way to solve this? This problem could be
>> > a
>> > real show stopper for our current project...
>> >
>> > "Patrik Ylén" wrote:
>> >
>> >> I've noticed that SP:s that are more complex than just a simple
>> >> SELECT-statement don't work correctly when creating new datasets. The
>> >> dataset
>> >> can't seem to populate the Fields when the SP does some additional
>> >> work
>> >> before the SELECT. I know it's possible to create all the Fields
>> >> manually,
>> >> but that isn't an option for us since we have a ton of reports with
>> >> lots
>> >> and
>> >> lots of fields. Is there another workaround for this? Maybe in SQL
>> >> Server
>> >> 2005?
>> HI!
>>I have a related problem. My stored procedure returns a different dataset
>>based on an entered parameter - it returns a particular set of data for
>>display in the report detail (@.showdetail) and another set for display in
>>the report footer (@.showfooter)
> When I call the stored procedure from within reporting services (either by
> exec spname @.parameter as a text entry, or by referencing it's name) I can
> retrieve the 2 separate datasets fine (ie the results sets show up in the
> grid). HOWEVER, changing the parameter to 'showfooter' instead of
> 'showdetail' does not update the associated field list. Which means I
> can't
> drag the second dataset's fields onto my layout.
> Any ideas?|||Thanks Bruce
My stored procedure is actually coded to return an appropriate dataset based
on a parameter which is supplied when calling the sp in RS. It returns the
appropriate dataset to the results grid fine in RS using the supplier
parameters. The fields returned in both datasets are completely different.
The problem is that the field list is not updated when I change the
parameter, it remains the list associated with the first parameter supplied
even if click 'refresh fields' after changing the parameter
I need my report to use the two separate datasets without having to re-write
the sp which is pages long.
Is this related to the fact that you can't have a sp return more than one
dataset in rs'|||Logically, you are returning two datasets. Depending on the parameter you
get a different field list. What you need to do is call the SP two times in
design mode with different parameters to get the different field list. Then
design your form with these and hide the one you don't want based on the
parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F34F84BB-226F-4A34-A0ED-551137DCA065@.microsoft.com...
> Thanks Bruce
> My stored procedure is actually coded to return an appropriate dataset
> based
> on a parameter which is supplied when calling the sp in RS. It returns
> the
> appropriate dataset to the results grid fine in RS using the supplier
> parameters. The fields returned in both datasets are completely different.
> The problem is that the field list is not updated when I change the
> parameter, it remains the list associated with the first parameter
> supplied
> even if click 'refresh fields' after changing the parameter
> I need my report to use the two separate datasets without having to
> re-write
> the sp which is pages long.
> Is this related to the fact that you can't have a sp return more than one
> dataset in rs'sql
