How can you build sql query in ssrs?How u can pass parameters for stored procedures in reports? [Resolved]

Posted by Chamu under Sql Server on 9/13/2016 | Points: 10 | Views : 286 | Status : [Member] | Replies : 2
How can you build sql query in ssrs?How u can pass parameters for stored procedures in reports?




Responses

Posted by: Rajnilari2015 on: 9/13/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
How can you build sql query in ssrs?

We build query in the Design the Query window. Once we click on Query Builder button , the Query Designer screen opens up. Here we have two options -

a) Text (where we write the normal /inline SQL query)
b) Stored Procedure

How u can pass parameters for stored procedures in reports?

This article of us ( http://www.codeproject.com/Articles/194097/SSRS-Series-Part-I-Various-ways-of-Report-creation ) answers your question. Please look into the Creating a basic Tabular SSRS Report using Report Wizard section where at Step 7 we have demonstrated how to pass parameters through Stored Procedures . The article is a step by step process that describes the way of doing so in a pictorial-textual combinatorial way.

Hope that helps. Let us know in case you have further questions.

--
Thanks & Regards,
RNA Team

Chamu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 9/13/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
You can try with the below implemenation. To code is adjusted to CustomerTable in AdventureWorks datatable. You have to adjust this as per you database tables

HTML
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True" OnTextChanged="TextBox1_TextChanged2"></asp:TextBox>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" WaitMessageFont-Names="Verdana"
WaitMessageFont-Size="14pt" Width="750px">
<LocalReport ReportPath="Report1.rdlc">
<DataSources>
<rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData"
TypeName="EmptyWebApp.CustomersTableAdapters.AddressTableAdapter"></asp:ObjectDataSource>


C#:
protected void TextBox1_TextChanged2(object sender, EventArgs e)
{
//Connection string
string CS = ConfigurationManager.ConnectionStrings["AdventureWorks2008R2ConnectionString2"].ConnectionString;
//Create connection object
using (SqlConnection con = new SqlConnection(CS))
{
//Assign query to SQLDataAdapter
SqlDataAdapter da = new SqlDataAdapter("select * from Person.Address where City = @City", con);
//pass the paramter value
da.SelectCommand.Parameters.AddWithValue("@City", TextBox1.Text);
//Create a dataset object
DataSet ds = new DataSet();
//FIll the dataset
da.Fill(ds);
//Assign the dataset to Report Datasource
ReportDataSource datasource = new ReportDataSource("DataSet1", ds.Tables[0]);
//Clear all assiged datasource in reportviewer
ReportViewer1.LocalReport.DataSources.Clear();
//Assiged the filtered DataSource to ReportViewer
ReportViewer1.LocalReport.DataSources.Add(datasource);
//Refrest the ReportViewer
ReportViewer1.LocalReport.Refresh();
}
}


Thanks,
A2H
My Blog

Chamu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response