Abstract: Most of the time, summary reports required by users from their information systems. Therefore developers or report designers need to know how to create summary reports. For example one organization needs a Department wise payroll summary report. In this case we need to generate a summary report from details in their existing tables. Technologies: Crystal Report and ASP.NET Language: C#.net Prerequisite: SQL Server 2005, Visual studio 2005/2008, Crystal report Before start to design a report, you need a database which is going to use by report. Here I have designed a small database with four tables for this demonstration. The created database name is "PayrollDemo" with three tables called Employee, Department, Salary, Time_Sheet_Summary. Employee Table: There are four columns in employee table. Department Table: There are two columns in the department table 
There are three columns in Time_Sheet_summary table and seven columns in the Salary table. 
Note: The database design is for demonstration purpose only. Let us design a report to this table, get 'Department wise Payroll summary Report'. Let us start designing the report. For that, there are two standard ways. The first way is we can use the crystal report and second one is we can use the Visual studio. Here I have used Visual Studio since I prefer that. First open Visual studio 2008 or 2005 and create new web project called "SummaryReportwithASP". Then right click the project in 'Solution Explorer' and select 'Add New Item' and select 'Reporting' from the categories. Then select 'Crystal Report' from the templates list window. When you select crystal report, give a meaningful name to the report. Here I have given "PayrollSummaryReprot.rpt". Then click Add button. Once you click the Add button, you will get a welcome window as shown by the following figure. 
Keep the Standard report format and click OK button. Then you need to create the database connection to access tables within the database. Once you click OK button, system gives a window to select the connection type as shown below 
This demonstration report is to work with SQL Server 2005 database. So you need to select SQL native client as provider (As above figure shows). Then click Next button, to continue report creation process. Then you need to assign details of SQL Server and user credential to access the database from the report. Then you need to expand current connection tag, select your database connection then select the relevant table as shown by the following figure. 
Now you can select the tables which you need to retrieve data to the report. Here I have selected all three tables. Then click Next button to continue. There you need to select fields from selected tables to design report as shown by the following figure. 
Here you can see the selected tables with their links. If you need, you can click 'Link Options' button and set link options as well. Now click Next button to continue. System will give a screen like below to select fields from selected tables. 
Then click Next button. Now system will give a window like below to grouping. Here I grouped this report by department. Then click Finish button. 
Now you can see a report like this. 
Now right click on Basic salary field in the detail section and select Insert then select summary. Once you select that, system will give a small window like follow. 
Select Sum from the Calculate this summary combo box and Group from the Summary location. Again you need to add Grand total for the same field. Follow the same way to all fields you need to show in the report. Now you need to hide the details section and Group header section. For that right click on the details section on the report and select hide. Again right click on the group header section select hide. Now you have done necessary efforts and finished the final step. As a result, you will get report like this. 
ow you need to add crystal report viewer on the ASP.NET Webpage. Let us see, how we can add the report viewer on the page, just look at these html tags. {codecitation class="brush: c#; gutter: true;" width="700px"} {/codecitation} Create a table in form object. Then add a html row and a column. Then place the crystalReportViewer to the column as shown above html tag. If we complie this application, it will give a complier error "CR doesn't exist". Therefore, you need to register crystal report namespace on the top of the page as shown below. {codecitation class="brush: c#; gutter: true;" width="700px"} {/codecitation} Note: when you double click on toolbox in the crystalreportviewer Visual studio will do all registeration and etc. Now write few lines to display report on the viewer. {codecitation class="brush: c#; gutter: true;" width="700px"} protected override void OnPreRender(EventArgs e) { // attached our report to viewer and set database login. ReportDocument report = new ReportDocument(); report.Load(Server.MapPath("PayrollSummaryReprot.rpt")); report.SetDatabaseLogon("username", "pwd", @"server", "database"); rptviewer.ReportSource = report; } {/codecitation} Here we have to write code under the OnPreRender event,I hope you know about ASP.Net event lifecycle. Please visit the following links for more informations. 1.http://msdn.microsoft.com/en-us/library/ms178472.aspx 2.http://msdn.microsoft.com/en-us/library/ms178473.aspx The reason for using "PreRender" event is this is the most suitable event for value setting in asp.net, Otherwise we can set in "PageLoad" event too. Here I did not use a button to load report on the viewer, therefore I have used "PreRender" event. On my code just create a report document object and load report from the path. MapPath() method to get absolute path from the report file name. Then build the web application and run, you will get result like follow. 
Conclusion: This article is focused about creating summary report with crystal report in asp.net. I hope this article is helpful to you. |