SQL SERVER: Develop Reports with RDLC (Asp.Net)

As, I mentioned in my post, Load Reports without SQL SERVER Reporting Service with Asp.Net, we can develop a report without SQL SERVER Reporting Service. That is very simple to do with Microsoft Visual Studio. Please follow the following steps to generate simple report which can be load in browser without SQL SERVER Reporting Service. We need to follow following steps to generate simple report with RDLC: 1. Create RDLC file. 2. Create Dataset from Report Data Source. 3. Design the Report 4. Runtime pass information to Report Data Source, so report will be generated with Data Let's create one simple web application with RDLC report. 1. Create RDLC file: In Solution Explorer, add a new folder and name it as "Reports". Right click on "Reports", select "Add" and click on" New Items". Give preferable name to report with extension RDLC and click Add. 2. Create Dataset from Report Data Source: Click on "Report Data" tab displayed on left side of the screen and in case, it is not displayed go to "View" menu and click on "Report Data". Click on "New" and select "Dataset" as displayed in following screenshot:     3. If your "Data Source" is listed in options, select it from there, else click on "New".      4. If your "Connection" is listed in options, select it from there, else click on "New Connection", to make your connection.     5. Once "Connection" is selected, select your Table/View/Stored Procedure/Function, from which you need to build your report and give appropriate name to Dataset:     6. Once you have selected DB Object in Dataset, you can find out all the columns that are available in selected dataset as follows:      7. Once you have this dataset, you will also find that one XSD file is also created in App_code folder, with the same schema, which is used to provide schema definition to RDLC file 3. Design the RDLC Report:                     In Step #2, we have created Dataset with columns that needs to be displayed on the report.  Now its time to use that Dataset and design the report. You should drag, "Table" from "Toolbox" and assign property "Dataset" created Dataset in Step #2. RDLC, allows you to design your report with schema that you have selected as DataSet 4. Runtime pass information to Report Data Source, so report will be generated with Data:         So far, we have done report designing, now its time to pass actual data to the Report Data Source, so report will be generated with actual Data. To display report on the browser, we have to use "Microsoft Reporting WebForms", which will load the report in the browser with ASPX page. Please follow the steps to pass information to RDLC Report: Add an "aspx" page in the solution. We have to use "Report Viewer" control on that page, in which our Report will be displayed We also need to add few lines of code, to dynamically pass the information (actual data) to the Report Data Source as follows: using System; using System.Configuration; using System.Data; using System.Data.Common; using System.Web.UI; using Microsoft.Practices.EnterpriseLibrary.Data; using Microsoft.Practices.EnterpriseLibrary.Data.Sql; using Microsoft.Reporting.WebForms; using System.Collections.Generic; using System.Linq; using System.Web.UI.WebControls; using System.Data; using DataAccessLayer; public partial class Reports_ProjectDetail : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { string connectionString = ConfigurationManager.ConnectionStrings["PISConnectionString"].ConnectionString; Database db = new SqlDatabase(connectionString); DbCommand command = db.GetStoredProcCommand("ReportProjectDetail"); DataSet dataset = db.ExecuteDataSet(command); ReportDataSource datasource = new ReportDataSource("DataSet1", Dataset.Tables[0]); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(datasource); ReportViewer1.LocalReport.Refresh(); ReportViewer1.Visible = true; } } } Let me explain how it works: 1. On Page load, we call one stored procedure to get data from database. Here I have used Microsoft Application block to connect to database and get the information 2. Once, we have the data in DataSet, we have created new "Report Data Source" with the SAME name: "Dataset1" and assign result table as per #1 and assign it to ReportViewer 3. And at last we refresh ReportViewer to display the information as per Datasource provided. This report is ready and you can export it in  Excel, PDF, Word etc. This example is developed in Microsoft Visual Studio 2010 Let me know your comments and difficulties, if any Reference: Tejas Shah (www.SQLYoga.com)