Working with RDLC(2012) and passing parameter to sub report in Report Viewer 2012 control
This is an exact replica of the post: https://blogs.msdn.com/b/selvar/archive/2009/08/30/working-with-rdlc-and-passing-parameter-to-subreport-in-report-viewer-control.aspx
But the difference is, it is customized for Report Viewer 2012 control as it has quite a good amount of modifications from the earlier post. Hence this is a modified version, customized for Report Viewer 2012.
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).
So, I’ll be explaining the concept based on 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 ADVENTUREWORKS2012 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 2012 control.
1. Open Visual Studio 2012 and create an ASP.NET web application. (Choose either VB.NET / C#. I’ll be talking from C# perspective.)
2. If you’d like, delete the existing Default.aspx page and add a new form with the name, Default.aspx.
3. Add a new report item called REPORT1.RDLC.
4. From the project menu, Add a new dataset named DataSet1.xsd.
5. From the Toolbox, drag a TableAdapter, Choose a SQL server that hosts the Adventureworks2012 database and configure it only for select statement against the Department table. The query will look like:
SELECT Name, DepartmentID FROM HumanResources.Department ORDER BY DepartmentID
6. From the project menu, Add another new dataset named DataSet2.xsd.
7. From the Toolbox, drag a TableAdapter, use the same connection string created for Dataset1.xsd in step #5. Configure it only for select statement against the EmployeeDepartmentHistory table. The query will look like:
SELECT DepartmentID, ShiftID, StartDate FROM HumanResources.EmployeeDepartmentHistory order by DepartmentID
8. Now, go back to Report1.rdlc and from ReportData menu, Right click on Datasets, choose Add Dataset and select DataSet1 in Data Source. This will automatically choose the Department dataset that we created in step #5.
9. From the toolbox drag and drop a table component in to REPORT1.
10. From Dataset1, drag and drop the DepartmentID and Name columns in to the report table.
11. Now go to the solution explorer and add a new item, REPORT2.RDLC.
12. From ReportData menu, Right click on Datasets, choose Add Dataset and select DataSet2 in Data Source. This will automatically choose the EmployeeDepartmentHistory dataset that we created in step #7.
13. From the toolbox drag and drop a table component in to REPORT2. Then drag the columns DepartmentID, ShiftID and StartDate.
14. Then add a group based on DepartmentID which displays the DepartmentID in the Group Header.
15. Go back to REPORT1.rdlc and drag a sub report component from the toolbox. Point the sub report to REPORT2.
16. With this we completed the design of the Dataset1, Dataset2, Report1.rdlc and Report2.rdlc. Now let’s design the ASP.NET page.
17. Switch to the Default.aspx page design layout. Drag and drop a ReportViewer control component in to the page. Go to the properties of the ReportViewer1 (Click on the > icon that appears to the top right on the control) and choose Report1.rdlc from Choose Report drop down. This will add an object data source named Objectdatasource1 in the page. Also make sure the ReportViewer1’s property window, LocalReport’s Report path contains Report1.rdlc specified.
18. Go to ObjectDatasource1’s properties (Click on the > icon that appears to the top right on the control), select Configure Data Source… and make sure the table adapter is qualified with your project name / namespace is selected there.
19. Drag and drop a Listbox control, ListBox1 (For multi value parameters).
20. Go to the properties of the ListBox1 (Click on the > icon that appears to the top right on the control) and click on Choose Data Source… link.
21. Choose the following,
Datasource: ObjectDataSource1
Data field for display: Name
Data field for Value: DepartmentID
22. Make sure the selection mode for the ListBox1 is set to Multiple.
23. Now drag a button control, named btnViewReport, adjacent to the ListBox1.
24. Also, drag and drop a ScriptManager from the toolbox in to the page. This is required by the ReportViewer control.
This brings us to the end of the design phase. Let’s 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.
Let’s have a look at the code within the PageLoad event of the default.aspx page. Inline Comments are provided to explain the code pieces.
string defaultValue = "'1'";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ReloadReport();
}
}
private void ReloadReport()
{
//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", 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);
}
Let’s 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("DataSet1", GetEmployeeData()));
}
** To view the name of the data set (i.e DataSet1) Go to the Report1.rdlc -> Report Data -> Under Datasets, you’ll see the list of data sets, 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 for Report2.rdlc.
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=Adventureworks2012;Integrated Security=SSPI"))
{
//Creating a new SQL Adapter along with the SQL query and the //connection.
SqlDataAdapter adap = new SqlDataAdapter("SELECT DepartmentID, ShiftID, StartDate 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;
}
}
Let’s look at the method GetDepartmentData () that fetches the data to the data set DataSet1 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=Adventureworks2012;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, 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)
{
ReloadReport();
}
That pretty much concludes our sample. I’ve uploaded the sample as well. Please change the connections string values and try executing the report. Happy programming!!.
HTH!
Selva.
[All the posts are AS-IS with no warranty]