CODEWITHSUNDEEP
sql-serverstored-proceduresreporting-servicesconnection-stringbids
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

How can I specify a Stored Procedure to be used in the SSRS Report Wizard?

In a BIDS project, after selecting Reports > Add New Report, and adding a connection string as the Shared Data Source in the Report Wizard, the wizard next prompts for a query (in the "Design the Query" page of the wizard).

I want to specify an existing Stored Procedure. How can I do that? Is it possible to specify it directly in the connection string? e.g, I provided something like this as the connection string:

SERVER=GoCards2016;DATABASE=platypusdata;UID=duckbill;PWD=dannyAndTheJuniors;Connection Timeout=0

Can I also insert the Stored Procedure to use in there somewhere?

Or do I need to enter a bogus query in the "Design the Query" page of the wizard and later replace it in the .rdl (xml) file with the Stored Procedure name? Or...???

Upvotes: 3

Views: 3708

Answers (3)

emragins
emragins

Reputation: 5157

  • Open Reports
  • In Solution Explorer Right Click on Reports
  • Click on Add New Report
  • Click on Next
  • Select the Data source and click on Next
  • Click on Query Builder button
  • Click on "Edit As Text"
  • In Command Type Select StoredProcedure
  • Enter the Stored Procedure Name ONLY in the text box
  • Click OK. The window should close
  • Click Next
  • Configure
  • Finish

From: https://social.msdn.microsoft.com/Forums/en-US/7ddcb6a4-03f8-461e-9caa-01c058812f01/using-stored-procedure-with-report-wizard?forum=sqlreportingservices

Upvotes: 1

alejandro zuleta
alejandro zuleta

Reputation: 14108

The Report Wizard will let you only use Query strings so you can't select a stored procedure, but You can use the EXEC statement to specify a stored procedure to run.

exec my_stored_procedure_name

Upvotes: 2

B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

Click through the wizard, then, in the "Data" tab, change the Command Type from Text to Stored Procedure and enter the name of your Stored Procedure.

So, with a little more specific instructions:

In the “Design the Query” page of the wizard, either enter “Exec [sp name]” or just enter some temporary query such as “select * from [tableName]” and then, once the Report Wizard has completed, in the data tab of the report, change the “Command type:” dropdown from Text to “Stored Procedure” and then enter the name of the Stored Procedure below.

Mash the “!” (Run) button to generate the data.

Upvotes: 3

Related Questions