|
|
You're in the
sql server reporting services
group:Dataset problems with Stored Procedures.
sql server reporting services:
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
No answers? Is there really no way to solve this? This problem could be a
real show stopper for our current project... [quoted text, click to view] "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
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 [quoted text, click to view] "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? > >
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 [quoted text, click to view] "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?
[quoted text, click to view] "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.
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 [quoted text, click to view] "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
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 [quoted text, click to view] "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??
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 |