How to build a report in SSRS using stored procedure with parameters

Question

Post reply

How to build a report in SSRS using stored procedure with parameters

neneco56

Ten Centuries

Points: 1232
More actions
August 28, 2008 at 12:28 pm

#382453

I'm trying to built a report with a store procedure with a parameter and on the data tab it's working fine
but on layout I have no idea how to set up a parameter, somebody can help me please
thanks
Nelson

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

In the Data Tab enter something like this:

="EXEC dbo.rpt_MyProcName @param=" & Parameters!MyParam.Value

Command type should be text.

Then in your layout, you can set your parameter, MyParam, in the Action property of the object that is supposed to initiate the sproc execution.

You also need to define your parameter under Report->Report Parameters in the top menu (when in Layout).

Sorry, this is not a great explanation, but if you give me more details of what you are trying to do, I may be able to help more.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

neneco56 Ten Centuries Points: 1232 More actions · Answer 2

OK let me explain what I need to do:

I need to build a menu to choose the report, I did but when I call the report whit this command ( working on C# ok )

I Create the connection and run the stored procedure works fine but when I call the report I can't pass the parameter to the report

conn = new SqlConnection("Server=servername;database=dataname;UID=;;PWD=");

SqlCommand cmd = new SqlCommand("DBA.PR_SEL_PTFOL_CASH", conn);

cmd.Parameters.Add("@portfolio", System.Data.SqlDbType.NVarChar, 9);

cmd.Parameters["@portfolio"].Value = this.DropDownList1.Text;

cmd.CommandType = CommandType.StoredProcedure;

conn.Open();

SqlDataAdapter executive = new SqlDataAdapter(cmd);

cmd.CommandTimeout = 900;

executive.SelectCommand.ExecuteNonQuery();

conn.Close();

string portfolio = this.DropDownList1.Text;

string query = "?" + portfolio;

Response.Redirect("http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fBRADESCO%2fExtrato+Consolidado+do+Portfolio"+ query);

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

I haven't tried this before, but have you specified the ItemPath parameter under Report Parameters in the report?

That would be a good place to start.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

neneco56 Ten Centuries Points: 1232 More actions · Answer 4

This works fine to call the report with the parameter

Response.Redirect("http://localhost/ReportServer?%2fBRADESCO%2fExtrato+Consolidado+do+Portfolio&rs:Command=Render&rs:param="+@portfolio)

BUT

The report itself on SSRS 2005 it's not taking the parameter now.

so How I fix that? any idea?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

neneco56 (8/29/2008)
BUT
The report itself on SSRS 2005 it's not taking the parameter now.
so How I fix that? any idea?

Can you elaborate? What is the error you get?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

neneco56 Ten Centuries Points: 1232 More actions · Answer 6

there is no error it open the report with any parameter

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

Sorry, I don't follow. Have you specified your parameter in the Report Parameters menu?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

neneco56 Ten Centuries Points: 1232 More actions · Answer 8

yes I specified the parameteres but that is my problem, it ran the report with any record on the table

it's no taking my parameter

ie.. I choose parameter 490490 and it runs and bring 521170