Working with RDLC and passing parameter to subreport in Report Viewer control
Recently I was looking for a sample that will help me with the concept of Passing parameters to sub reports in RDLC. I didn’t get one. So I thought of exploring more on to this and started off the journey.
The tricky part with the RDLC is, even though it gives you the option of creating report parameters, you cannot assign values from a query.
More over you cannot make the parameter prompts visible as you do in RDL.
So the trick here is, the person who is designing the report should use the ASP.NET controls for parameters and pass it along with the query that you’re using it for filing your report data set.
The other way is to define parameters, pass the values to parameters from the ASP.NET control to the Report Viewers Local report parameter collection and then just create filters in the table / matrix / tablix. But the problem here is, your performance of the report can be impacted as the query might bring large number of data for each request (based on the query).
I’ll be explaining both the concepts. But let’s get started with the passing the parameters from the ASP.NET controls to the query and get the filtered data for the report data set.
In our sample we’ll be using ADVENTUREWORKS database. The tables which I’ve selected are Department and EmployeeDepartmentHistory.
The objective here is, the main report REPORT1.rdlc will show the Department table details and contains a sub report component with REPORT2.rdlc which displays EmployeeDepartmentHistory. All the data are based on the parameters that we choose in the ASP.NET page which contains the ReportViewer control.
1. Open VS 2005 / 2008 and create a ASP.NET web application. (Choose either VB.NET / C#. I’ll be talking from C# perpective.)
2. Add a new report item called REPORT1.RDLC.
3. In the Website Data Sources, click on the Add New Datasource icon.
4. Leave the dataset name as Dataset1.
5. Point the connection to the appropriate SQL Server and map it with the Department table.
6. Create another new datasource (step 3) and name it as Dataset2.
7. Map it to the same SQL Server as in step 5 and map it with the table EmployeeDepartmentHistory table.
8. Now from the toolbox drag and drop a table component in to REPORT1.
9. From Dataset1, drag and drop the DepartmentID and DepartmentName columns in to the report table.
10. Now goto the solution explorer and add a new item, REPORT2.RDLC.
11. Drag and drop a table component in to REPORT2. Then drag the columns DepartmentID, ShiftID and ShiftDate.
12. Then add a group based on DepartmentID which displays the DepartmentID in the Group Header.
13. Go back to REPORT1.rdlc and drag a sub report component from the toolbox. Point the sub report to REPORT2.rdlc.
14. With this we completed the design of the Dataset1, Dataset2, Report1.rdlc and Report2.rdlc. Now lets design the ASP.NET page.
15. Switch to the Default.aspx page design layout. There you’ll already find Objectdatasource1 and Objectdatasource2.
16. Drag and drop a Listbox control, ListBox1 (For multi value parameters).
17. Go to the properties of the ListBox1 (Click on the > iconthatappears to the top right on the control) and click on Choose datasource link.
18. Choose the following,
Datasource: ObjectDataSource1
Data field for display : Name
Data field for Value : DepartmentID
19. Make sure the selection mode for the ListBox1 is set to Multiple.
20. Drag and drop the report viewer control, ReportViewer1 from the tool box.
21 . Go to the properties of the ReportViewer1 (Click on the > iconthatappears to the top right on the control) and choose Report1.rdlc from Choose Report drop down.
22. Now drag a button control, btnView adjacent to the ListBox1.
This brings us to the end of the design phase. Lets step in to the coding part. The coding part needs to be done little carefully. The reason, it has to follow a specific order to get the report properly. The sequence is as follows,
1. Get the list of parameters selected.
2. Build the SQL query with the parameters and get the result in to the corresponding datasets.
3. Bind the datasets back to the reports (Main and sub report).
4. Refresh the control to show the updated report.
Lets have a look at the code within the PageLoad event of the default.aspx page. Inline Comments are provided to explain the code pieces.
protected void Page_Load(object sender, EventArgs e)
{
//Eveytime you add a data source to the report, it adds itself in to //a collection. Reason, report allows more than one data source to be //used within itself.Unless you specify the data source with index, //DataSources[0] will be taken automatically since our report has only //one data source. In our case for every refresh we re-execute the //query and get different result set. So to make sure we keep the //current dataset as default and to prevent data sets getting bulged //up, we’re making sure to remove the last available data set and then //add the current data set.
if (this.ReportViewer1.LocalReport.DataSources.Count > 0)
{
this.ReportViewer1.LocalReport.DataSources.RemoveAt(0);
}
//Adding the newly filled data set to the report. After this the Data //Source count will show 1. The first parameter is the name of the data //set bound with the report** and the next is the call to method which //will execute the QUERY and return a data table that contains the //current data.
this.ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1_Department", GetDepartmentData()));
//For the sub report to be processed, we need to add the sub report //processing handler to the main report and that is what is shown //below. The data set processing definition for the sub report will be //defined with in that method.
this.ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(localReport_SubreportProcessing);
}
Lets have a look at the SubreportProcessing event handler:
//All we’ve done here is, we’re binding the data set to appropriate report data source**.
void localReport_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
{
e.DataSources.Add(new ReportDataSource("DataSet2_EmployeeDepartmentHistory", GetEmployeeData()));
}
** To view the name of the data set (i.e DataSet1_Department) Go to the Report1.rdlc -> Report menu -> Data Sources.. -> Under Report Data Sources, you’ll see the list of data sources, if the report contains more than one.
Now we’re going to look at the method GetEmployeeData () that fetches the data to the data set DataSet2_EmployeeDepartmentHistory for Report2.rdlc / ObjectDataSource2.
private DataTable GetEmployeeData()
{
//Since string is immutable, we’re using StringBuilder as we’ll be //changing the data frequently based on the parameter selection.
StringBuilder param = new StringBuilder();
//We need to get the selected parameters seperated by “,” and enclosed //within the “’” (i.e ‘1’,’3’,’7’). This is to make sure we‘ve the //proper list of multi value parameters.
foreach (ListItem list in ListBox1.Items)
{
if (list.Selected)
{
param.Append("'" + list.Value + "',");
}
}
string paramvalues = string.Empty;
//Just initializing the parameter values with a default value. This //will come in to picture only during the first exectution as we need //to show some data as soon as the report loads for the first time.
if (param.Length == 0)
{
paramvalues = defaultValue;
}
else
{
//Making sure the last “,” which got added in the above foreach loop is //removed before the values are passed to the queries.
paramvalues = param.ToString().Remove(param.ToString().LastIndexOf(",")).ToString();
}
//Creating a new SQL server connection object.
using (SqlConnection sqlConn = new SqlConnection("Data Source=localhost;Initial Catalog=Adventureworks;Integrated Security=SSPI"))
{
//Creating a new SQL Adapter along with the SQL query and the //connection.
SqlDataAdapter adap = new SqlDataAdapter("SELECT EmployeeID, DepartmentID, ShiftID, StartDate, EndDate, ModifiedDate FROM HumanResources.EmployeeDepartmentHistory where DepartmentID IN (" + paramvalues + ")", sqlConn);
//Create the typed data set of EmployeeDepartmentHistory and fill it //with the data.
DataSet2 ds = new DataSet2();
adap.Fill(ds, "EmployeeDepartmentHistory");
//Return the filled Data Table.
return ds.EmployeeDepartmentHistory;
}
}
Lets look at the method GetDepartmentData () that fetches the data to the data set DataSet1_Department for Report1.rdlc / ObjectDataSource1.
private DataTable GetDepartmentData()
{
//Since string is immutable, we’re using StringBuilder as we’ll be //changing the data frequently based on the parameter selection.
StringBuilder param = new StringBuilder();
//We need to get the selected parameters seperated by “,” and enclosed //within the “’” (i.e ‘1’,’3’,’7’). This is to make sure we‘ve the //proper list of multi value parameters.
foreach (ListItem list in ListBox1.Items)
{
if (list.Selected)
{
param.Append("'" + list.Value + "',");
}
}
string paramvalues = string.Empty;
//Just initializing the parameter values with a default value. This //will come in to picture only during the first exectution as we need //to show some data as soon as the report loads for the first time.
if (param.Length == 0)
{
paramvalues = defaultValue;
}
else
{
//Making sure the last “,” which got added in the above foreach loop is //removed before the values are passed to the queries.
paramvalues = param.ToString().Remove(param.ToString().LastIndexOf(",")).ToString();
}
//Creating a new SQL server connection object.
using (SqlConnection sqlConn = new SqlConnection("Data Source=Localhost;Initial Catalog=Adventureworks;Integrated Security=SSPI"))
{
//Creating a new SQL Adapter along with the SQL query and the //connection.
SqlDataAdapter adap = new SqlDataAdapter("SELECT DISTINCT DepartmentID, Name FROM HumanResources.Department where DepartmentID IN (" + paramvalues + ")ORDER BY DepartmentID", sqlConn);
//Create the typed data set of Department and fill it
//with the data.
DataSet1 ds = new DataSet1();
adap.Fill(ds, "Department");
//Return the filled Data Table.
return ds.Department;
}
}
Finally, whenver whenever we click on the View Report button, the query has to be executed with the new set of parameters and then the entire above operation as to be executed. The below code invokes it.
protected void btnView_Click(object sender, EventArgs e)
{
this.ReportViewer1.LocalReport.Refresh();
}
That pretty much concludes our sample. I’ve uploaded the sample please change the connections string values and try executing the report. Happy programming!!.
ReportViewer2005WithSubreportProgram.rar
Comments
Anonymous
February 11, 2010
Great post. I just had this need come up and this was the key. Thanks.Anonymous
February 25, 2010
The comment has been removedAnonymous
February 26, 2010
All you need to do is to pass '%' to your where clause. The query for the first request will look something similar to this, select * from emp where firstname LIKE '%'. So for the first request it will return all the data. Subsequent ones will be based on the filter passed. To determine if this is a first request we can always use the IsPostBack property. If the value is false, this is indeed a first request. If not this is not the first request. The property will be FALSE when the browser is refreshed using F5 / CTRL + F5 apart from the first request through URL. HTH! Selva. [Posted AS-IS with no warranty]Anonymous
October 07, 2010
Thanks for your article. It's useful for me. ^__^Anonymous
August 19, 2012
Brilliant. Thanks for taking the time to post this.Anonymous
October 22, 2012
Hey i apply same in my code... but my sub report giving same data in every page...plz help meAnonymous
October 22, 2012
@Pratik: If the sub report is bound to the correct data source, i don't thing you should run in to this issue. Can you please debug the method that bring the data to your sub report and verify whether that is the right query and dataset that you want the sub report to be bound with?Anonymous
November 26, 2012
Hi, Please let me know how the same can be implemented on Windows FormsAnonymous
December 02, 2012
@Sumit: Here the sample uses the LocalReport namespace which belong to the ReportViewer control. Hence i don't see any reason why this code cannot be implemented for Win Form control. Are you running in to any specific issues?