2

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...???

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862

3 Answers3

2

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.

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
2

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
alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
1
  • 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

emragins
  • 4,607
  • 2
  • 33
  • 48