|
|
You're in the
sql server reporting services
group:Trouble Displaying Parameter Input
sql server reporting services:
inputs is a date which I am trying to display in the report. Since the parameter is declared in the SP and not the report, I am having trouble figuring out how to display the parameter input in a textbox in the report. If I try to select the paramter from expressions it is not there, since it isn't declared in the report. Does anyone know how I might display a parameter's input if the parameter is from a stored procedure I'm executing
The only data that is available from a stored procedure is the data that is
returned. If you aren't passing the parameter from the report then you will probably need to dump the parameter value into a variable in your stored procedure and then return it at the end. [quoted text, click to view] "Jacob A" wrote:
> I am making a report which uses a stored procedure. One of the parameter > inputs is a date which I am trying to display in the report. Since the > parameter is declared in the SP and not the report, I am having trouble > figuring out how to display the parameter input in a textbox in the report. > If I try to select the paramter from expressions it is not there, since it > isn't declared in the report. Does anyone know how I might display a > parameter's input if the parameter is from a stored procedure I'm executing
Thanks for the reply bclay. I am hoping I can manage to do this without
actually altering the stored procedure since I have around 300 of them I need to do similar tasks with. Is it possible to have the parameters in the stored procedure declared in the report so I can use them directly in the report (ex. I want to input the parameters when the report is generated, or display the inputs similar to a field value?) [quoted text, click to view] "bclay" wrote:
> The only data that is available from a stored procedure is the data that is > returned. If you aren't passing the parameter from the report then you will > probably need to dump the parameter value into a variable in your stored > procedure and then return it at the end. > > > "Jacob A" wrote: > > > I am making a report which uses a stored procedure. One of the parameter > > inputs is a date which I am trying to display in the report. Since the > > parameter is declared in the SP and not the report, I am having trouble > > figuring out how to display the parameter input in a textbox in the report. > > If I try to select the paramter from expressions it is not there, since it > > isn't declared in the report. Does anyone know how I might display a > > parameter's input if the parameter is from a stored procedure I'm executing
Jacob,
You can, most definitely, declare and pass the stored procedure variables from the report. When you are in the LAYOUT tab in Visual Studio click the REPORT menu and chose REPORT PARAMETERS. Enter the parameters you want to pass to the stored procedure. This screen can also be used to setup up Available Values, Default Values and an onscreen prompt to the user. Once you've entered these parameters then click the DATA tab and edit your stored procedure dataset. In the edit window click on the Parameters tab. Enter the name of the stored procedure parameter (example @StartDate) then click in the value field and it will give you the list of report parameters that you created above to match to the desired stored procedure parameter. Once you've done that then you will have that parameter field available for your report via the field choser. Took me a while to get this working myself so feel free to post any further questions. Peace, Dan [quoted text, click to view] "Jacob A" <JacobA@discussions.microsoft.com> wrote in message news:067B3B4A-E385-4B89-BFD2-72912A013F51@microsoft.com... > Thanks for the reply bclay. I am hoping I can manage to do this without > actually altering the stored procedure since I have around 300 of them I > need > to do similar tasks with. Is it possible to have the parameters in the > stored > procedure declared in the report so I can use them directly in the report > (ex. I want to input the parameters when the report is generated, or > display > the inputs similar to a field value?) > > "bclay" wrote: > >> The only data that is available from a stored procedure is the data that >> is >> returned. If you aren't passing the parameter from the report then you >> will >> probably need to dump the parameter value into a variable in your stored >> procedure and then return it at the end. >> >> >> "Jacob A" wrote: >> >> > I am making a report which uses a stored procedure. One of the >> > parameter >> > inputs is a date which I am trying to display in the report. Since the >> > parameter is declared in the SP and not the report, I am having trouble >> > figuring out how to display the parameter input in a textbox in the >> > report. >> > If I try to select the paramter from expressions it is not there, since >> > it >> > isn't declared in the report. Does anyone know how I might display a >> > parameter's input if the parameter is from a stored procedure I'm >> > executing >> > within a report?
Thanks Dan. I have managed to display a date which is selected before the
report is generated but I'm running into other problems. The report will not run without values for the parameters in the SP specified in the query string. So my query string looks like this: EXEC LTCTx.DBO.SPRPT_CLM_FacilityLOS '1', '1', '1', 'All', '10/1/2004', '12/31/2004' So the values that I select when the report is ran don't do anything but change the value displayed in the textbox, not actually sending the value to the stored procedure to retrieve data. I added the parameters to the report paramters window then in the field menu clicked Add.. Then selected the Calculated Field radio button. I click the expression button and then select the paramter from the list. I know this must be wrong since the textbox always defaults to writing =Sum(Fields!FromDate.Value). I have to manually delete the Sum and parentheses. It does the same thing if I select Database Field but will not display the value when the report is run. Sorry if my questions seem obvious, I have been working in RS for a week now, and this is my first encounter with databases and reports. Thanks for all your input.
I see your problem now and to be honest I had the same issue the first time
I tried to use a stored procedure in a report and the solution isn't exactly obvious. The problem for me was using the wizard to create a report and then putting in a line like you have below as my query (i.e. exec mystoredprocedure my parameters) and that isn't the proper way to set it up. With your rdl open in Visual Studio click on the DATA tab. At the top of that screen you'll see Dataset: with your dataset listed in a dropdown menu. With SQL reporting services you can have multiple datasets for a datasource and that's the part that confused me. In that dataset dropdown chose <New Dataset...> . Once you are in the Dataset window on the first tab there is a dropdown field called Command Type. Chose StoredProcedure for this field and then put in the name of your stored procedure for the Query String without using the exec and without including any parameters (example: nameofmystoredprocedure). Once you have that set you can click on the Parameters tab and set up the parameters and map them to the proper report parameters. Let me know if that works and if you need any more help. Peace, Dan [quoted text, click to view] "Jacob A" <JacobA@discussions.microsoft.com> wrote in message news:F5FDAD2B-4CF1-4830-9C8A-9BFCB5857AB9@microsoft.com... > Thanks Dan. I have managed to display a date which is selected before the > report is generated but I'm running into other problems. The report will > not > run without values for the parameters in the SP specified in the query > string. So my query string looks like this: > > EXEC LTCTx.DBO.SPRPT_CLM_FacilityLOS '1', '1', '1', 'All', '10/1/2004', > '12/31/2004' > > So the values that I select when the report is ran don't do anything but > change the value displayed in the textbox, not actually sending the value > to > the stored procedure to retrieve data. I added the parameters to the > report > paramters window then in the field menu clicked Add.. Then selected the > Calculated Field radio button. I click the expression button and then > select > the paramter from the list. I know this must be wrong since the textbox > always defaults to writing =Sum(Fields!FromDate.Value). I have to manually > delete the Sum and parentheses. It does the same thing if I select > Database > Field but will not display the value when the report is run. Sorry if my > questions seem obvious, I have been working in RS for a week now, and > this > is my first encounter with databases and reports. Thanks for all your > input. >
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 |