Saturday, April 13, 2013

Drill through rdlc report with empty dataset

 Hi,
    Here i will explain how to create a simple rdlc drillthrough report with filtering option and dynamic datasource/dataset updated with custom object collection programmatically.

Master Report
Drillthrough Report
Below are the step by step explanation to accomplish:
  • Create a web project from VisualStudio.
  • Add a rdlc file and name it LocalReport.rdlc
  • Add a typed dataset (.xsd) and name it DataSet1.xsd
  • Add two tables manually (right-click select from designer) with schema as in below image.


  • Now open LocalReport.rdlc designer, Add a Table Control by dragging it from toolbox onto report body.
  • From Report Data pane, click New -> Dataset, from the wizard, set: Name: DataSet1    Data source: DataSet1    Available datasets: Emp      then click ok. (check image below)

  • Now Report Data pane shows DataSet1 with its fields. Drag these 3 fields onto report table one by one and set its (first-row) header with background color.
  • Add textbox control for Report Title with value - Emp Details and set its background, foreground colors from its properties.
  • Add a parameter: from Report Data pane, click New -> Parameter and name it - pDesignation

  • Select report table, right-click for Tablix properties, select Filters option and set its fields as per below image.

  • Add a class file to the project and name it Entities.cs
  • Add 2 classes - Emp, EmpDetails with properties similar to tables schema in DataSet1.xsd
  • Also add static class DataProvider with 2 methods which returns collection of above types.(check code in download files)

  • Add an aspx file to the project and name it LocalReport.aspx, add a dropdownlist for filtering and ReportViewer controls.
  • Assign the LocalReport.rdlc to ReportViewer from webpage in designer mode.
  • At code-behind, update the Datasource with custom Emp Collection from DataProvider and assign it to report.
  •  Update the dropdownlist with unique Emp Designations and handle change-event to update report.
Till now we have completed single master report with filtering option.
You can give it a Run to test filtering.

Adding Drillthrough feature:
  • For Drillthrough we need another report, add a report to the project and name it SubReport.rdlc
  • Now open SubReport.rdlc designer, Add a Table control by dragging it from toolbox onto report body.
  • From Report Data pane, click New -> Dataset, from the wizard, set Name: DataSet1    Data source: DataSet1    Available datasets: EmpDetails           then click ok.
  • Now Report Data pane shows DataSet1 with its fields.
  • Drag 3 fields except EmpID onto report table one by one and set its (first-row) header with background color.
  • Add textbox control for Report Title with value - Emp Details (sub Report) and set its background, foreground colors from its properties.
  • Add a parameter: from Report Data pane, click New -> Parameter and name it - pEmpID
  • Select report table, right-click for Tablix properties, select Filters option and set its fields: Expression: [EmpID]      Operator: =     Value: [@pEmpID]
  • Now update the master report LocalReport.rdlc to support drillthrough
  • Open LocalReport.rdlc designer, right-click EmpID cell (not header), select Textbox properties -> Action and set fields as per below image.
  • Style the EmpID to blue color and decorate it with underline. 
  • Open LocalReport.aspx (in source mode) and add this attribute to the ReportViewer: ondrillthrough="ReportViewer1_Drillthrough" 
  • From its code-behind, write this code to handle drillthrough link.

Thats all, Now Run the application.

Note: Drillthrough reports are not linked reports.
Download: The Zip file and check the code.

No comments:

Post a Comment