Dynamically passing parameter to DrillThrough report in RDLC - Part 1
Recently I was looking for a sample that will help me with the concept of Passing parameters to DrillThrough reports in RDLC. I didn’t get one. So I thought of giving a try on the same and it came up pretty well.
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.
There are couple of ways to pass parameters from one report to the other.
Handle the ReportViewer DrillThrough event, capture the paremeters and their values, pass it to the query that you’re using to fill your report data set for the target report.
The other way is to directly create filters in the table / matrix / tablix for the target report. Though this is much simpler it comes with an inherent problem.
Your performance of the report can be impacted as the query might bring large number of data for each request (based on the query) and filtering happens in the web server which can add more memory pressure. But this will be very handy when dealing with less data and relatively small dataset's.
I’ll be explaining both the concepts. But let’s get started with the passing the parameters, Handling it in the DrillThrough event and pass it to the query to 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 Employee and EmployeeDepartmentHistory.
The objective here is, the main report MainReport.rdlc will show the Employee table details and contains Navigation (Jump to Report) on the EmployeeID column. When clicked, it will render JumpReport.rdlc which displays EmployeeDepartmentHistory table based on the selected EmployeeID.
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 MainReport.RDLC.
3. In the Website Data Sources, click on the Add New Datasource icon.
4. Leave the dataset name as AdventureWorksDataSet.
5. Point the connection to the appropriate SQL Server, Choose AdventureWorks database and map it with the Department table.
6. Create another new datasource (step 3) and name it as EmpHistDepartmentDataSet.
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 MainReport.rdlc.
9. From AdventureWorksDataSet, drag and drop the EmployeeID, ManagerID, Title columns in to the table.
10. Now goto the solution explorer and add a new item, JumpReport.rdlc.
11. Drag and drop a table component in to JumpReport. Then drag the columns EmployeeID, DepartmentID, ShiftID and ShiftDate.
12. Add a parameter EmployeeID (Design view -> Report menu -> Report Parameters), which accepts NULL and BLANK values.
13. Go back to MainReport.rdlc and Right Click on the EmployeeID column, =Fields!EmployeeID.Value textbox in the table and choose properties.
14. Go to Navigation tab, choose JumpReport.rdlc in the Jump to Report section and click on parameters.
15. Choose EmployeeID for Parameter Name and Choose =Fields!EmployeeID.Value as the value. This step will make sure the parameter is passed from the main report to the DrillThrough report.
16. With this we completed the design of the DataSets and Reports. Now lets design the ASP.NET page.
17. Switch to the Default.aspx page design layout. There you’ll already find Objectdatasource1.
18. Drag and drop the report viewer control, ReportViewer1 from the tool box.
19 . Go to the properties of the ReportViewer1 (Click on the > iconthatappears to the top right on the control) and choose MainReport.rdlc from Choose Report drop down.
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. First, the MainReport.rdlc is displayed with the Employee table data.
2. Once you click any EmployeeID, then you need to Handle the DrillThrough event for the Report Viewer.
3. With in that, build the SQL query with the parameters passed from MainReport and get the result in to the corresponding dataset (EmpHistDepartmentDataSet).
4. Bind the dataset to the JumpReport.
Lets have a look at the code within the PageLoad event of the default.aspx page. We don't need to touch the page load event as the main report doesn't use any filter. Inline Comments are provided to explain the code piece
protected void Page_Load(object sender, EventArgs e)
{
}
In our case as i mentioned earlier, we're concerned about the Jump To Report. For that we need to concentrate only on the DrillThrough event handler.
protected
void ReportViewer1_Drillthrough(object sender, Microsoft.Reporting.WebForms.DrillthroughEventArgs e)
{
//Variable to store the parameter value passed from the MainReport.
int val = 0;
//Get the instance of the Target report, in our case the JumpReport.rdlc.
LocalReport report = (LocalReport)e.Report;
//Get all the parameters passed from the main report to the target report.
//OriginalParametersToDrillthrough actually returns a Generic list of
//type ReportParameter.
IList<ReportParameter> list = report.OriginalParametersToDrillthrough;
//Parse through each parameters to fetch the values passed along with them.
foreach (ReportParameter param in list)
{
//Since i know the parameter is only one and its not a multivalue
//I can directly fetch the first value from the Values array.
val =
Convert.ToInt32(param.Values[0].ToString());
}
//Create the instance for the EmpDeptHistoryDataSet which will
//hold the EmployeeDepartmentHistory table details.
EmpDeptHistoryDataSet ds = new EmpDeptHistoryDataSet();
//Making sure the dataset doesn't contain any constraints.
ds.EnforceConstraints =
false;
//Create a SQL Connection to the AdventureWorks database using Windows Authentication.
using (SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=Adventureworks;Integrated Security=SSPI"))
{
//Building the dynamic query with the parameter of EmployeeID contained in
//Variable val.
SqlDataAdapter adap = new SqlDataAdapter("select EmployeeID, DepartmentID, ShiftID from HumanResources.EmployeeDepartmentHistory where EmployeeID = " + val, sqlconn);
//Executing the QUERY and filling the dataset with the Resultset.
adap.Fill(ds,
"EmployeeDepartmentHistory");
}
//Binding the DataTable to the JumpReport's dataset.
//The name EmpDeptHistoryDataSet_EmployeeDepartmentHistory can be found from
//Go to Design view of JumpReport.rdlc, Click Report menu -> DataSources...
//You'll see this under Report Data Sources section.
report.DataSources.Add(
new ReportDataSource("EmpDeptHistoryDataSet_EmployeeDepartmentHistory", ds.EmployeeDepartmentHistory));
}
Finally, run the project, You'll see MainReport rendering in the ReportViewer control.
Click on any of the EmployeeID and you'll see the JumpReport displayed for the selected EmployeeID.
That pretty much concludes our sample. I’ve uploaded the sample please change the connections string values in the web.config and in the Default.aspx.cs files and try executing the report. Happy programming!!.
HTH!
Selva.
[All the Posts are AS-IS with no warranties]
Comments
Anonymous
July 05, 2010
Hi Could you please provide the VB.NET Coding for the sameAnonymous
July 08, 2010
I used a free tool available in the internet to convert the code to VB.NET. Hope this helps. Selva. Protected Sub ReportViewer1_Drillthrough(sender As Object, e As Microsoft.Reporting.WebForms.DrillthroughEventArgs) 'Variable to store the parameter value passed from the MainReport. Dim val As Integer = 0 'Get the instance of the Target report, in our case the JumpReport.rdlc. Dim report As LocalReport = DirectCast(e.Report, LocalReport) 'Get all the parameters passed from the main report to the target report. 'OriginalParametersToDrillthrough actually returns a Generic list of 'type ReportParameter. Dim list As IList(Of ReportParameter) = report.OriginalParametersToDrillthrough 'Parse through each parameters to fetch the values passed along with them. For Each param As ReportParameter In list 'Since i know the parameter is only one and its not a multivalue 'I can directly fetch the first value from the Values array. val = Convert.ToInt32(param.Values(0).ToString()) Next 'Create the instance for the EmpDeptHistoryDataSet which will 'hold the EmployeeDepartmentHistory table details. Dim ds As New EmpDeptHistoryDataSet() 'Making sure the dataset doesn't contain any constraints. ds.EnforceConstraints = False 'Create a SQL Connection to the AdventureWorks database using Windows Authentication. Using sqlconn As New SqlConnection("Data Source=.;Initial Catalog=Adventureworks;Integrated Security=SSPI") 'Building the dynamic query with the parameter of EmployeeID contained in 'Variable val. Dim adap As New SqlDataAdapter("select EmployeeID, DepartmentID, ShiftID from HumanResources.EmployeeDepartmentHistory where EmployeeID = " & val, sqlconn) 'Executing the QUERY and filling the dataset with the Resultset. adap.Fill(ds, "EmployeeDepartmentHistory") End Using 'Binding the DataTable to the JumpReport's dataset. 'The name EmpDeptHistoryDataSet_EmployeeDepartmentHistory can be found from 'Go to Design view of JumpReport.rdlc, Click Report menu -> DataSources... 'You'll see this under Report Data Sources section. report.DataSources.Add(New ReportDataSource("EmpDeptHistoryDataSet_EmployeeDepartmentHistory", ds.EmployeeDepartmentHistory)) End SubAnonymous
July 18, 2010
Dear Selva, Thks for your code.. ll try it..