Designing and Developing Reports in ASP.NET consists of following steps:
- Creating a strongly-typed data set.
- Designing the report on the basis of dataset.
- Displaying the report using Reportviewer.
- Attaching dataset (data) to the reportviewer (report) dynamically at run time.
Let us follow each step in detail now:
To design a report (locally) and embed it within our application, we first need to start with a strongly-typed data set. This data set will act as a data source for the report we are going to develop. At run-time, the data set gets automatically populated from the database, which finally is rendered using the “ReportViewer” control.
- Open your Visual Studio 2005/2008 IDE and create a new web project or open an existing project.
- Right click on the solution and go for “Add New Item.”
- Select “dataset” as the template and click on ‘Add’ to create the data set.
- It prompts you to place the dataset-related code in the “app_code” folder. Click on “OK.” I gave the name of the dataset as AllRecordsWithCondition. Also, the datatable name should be given as dtAllRecordsWithCondition while creating table adapter in steps to follow.
- Once it creates the dataset, you should start the “TableAdapter Configuration Wizard” by right clicking. You can select any existing connection or create a new connection by hitting the “New Connection” button.
- Once you have selected the connection, click on “Next.” At this level, it prompts for the connection string; simply press “Next”. The next section asks for the command type, to which you select “Use SQL statements” and click “Next.”
- Next, you can type your own SELECT statement to retrieve the data related to the report. You can also use the “Query Builder” button if you need to design a query in a GUI environment.
- Click “Next,” make sure that you select both check boxes, then hit “Next” again.
- At this point, it gives you the results of your data set creation; check them and finally click “Finish.”
Now it is time to add a new embedded report to our application:
- Using the Solution Explorer, right click on the project and go for “Add New Item.” Select “Report” as the template, provide a name for your report and click “Add.”
- The name of the report file will end with the extension “rdlc,” which stands for “report definition language for client” (or for local mode). If you deal with SQL Server 2005 Reporting Services, it will have the extension “rdl” only, i.e. server mode.
- You now land up in report designer, where you design/develop the reports. From the Toolbox, drag a table and drop it onto the layout. You can observe that it shows an Excel-like spread sheet with only three rows. The first row is for the header, the second is for detail (or records) and the third is for the footer.
- At this point, you need to drag the columns (of the data set) from “Website Data Sources” onto each cell in the second row of the Excel-type grid. If “Website Data Sources” is not visible, you can make it visible by going to Data -> Show Data Sources.
- Once you drag all the columns, your screen should look something like the following:
- Drag and drop the “ReportViewer” control from the toolbox on your web page.
- From the smart tag of the “ReportViewer” control, select “AllRecordWithCondition.rdlc” from the drop down.
- Now, you can press F5 to execute the solution. You will be prompted for the “Web.config” modification for debugging, to which you respond by clicking on “OK.”
- You should be able to see the report output now.
Attaching dataset (data) to the reportviewer (report) dynamically at run time:
When you want to attach data sources dynamically at run time, you may need to add a few lines of code to your web page.
Following is the code used to achieve this:
ReportViewer1.Visible = true;
ReportDataSource rds = new ReportDataSource();
ReportViewer1.ProcessingMode = ProcessingMode.Local;
LocalReport rep = ReportViewer1.LocalReport;
rep.ReportPath = “AllRecordsWithCondition.rdlc”;
//This name must be in “<datasetname>_<datatablename>” format. This name can also be seen in dataset’s datasource view.
rds.Name = “AllRecordsWithCondition_dtAllRecordsWithCondition”;
// Text in bold should be your datatable’s name from your current dataset i.e. AllRecordsWithCondition.
rds.Value = dtAllRecordsWithCondition.Tables;
- The most important issue to remember from the above code is that the name of the report data source must match with the syntax of “<datasetname>_<datatablename>”.
- At the time of writing this article, It was important to have the dataset created and attched to report at design time even when you want to create dataset and attach it to reportviewer at run time using step 4 above. The report won’t work if you do not create the dataset at design time. The dependency may change in future releases of reportviewer.
Hope it helps 🙂