Sometime users need complex reports, therefore developers or report designer need to use the few collection of tables to build the report. For example one organization need to get details of the timesheet and paid salary. That time we need to use the more than one table to create report and print.
Abstract:
Sometime users need complex reports, therefore developers or report designer need to use the few collection of tables to build the report. For example one organization need to get details of the time sheet and paid salary. That time we need to use the more than one table to create report and print.
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 four 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.
Let us design a report to this table, get ‘House owners’ summary report’ which is using date parameter to process the report.
Note: The database design is only for demonstration purpose.
Let us design a report for these tables, get paid salary and time sheet information report for related employees department wise.
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 “MultiTableReportwithASP”. 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 “MultiTabledReprot.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 four 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 have done necessary effort and finished the final step. As a result, you will get a report like this.
Now you need to add crystal report viewer on the ASP.NET Web page.
Let us see, how we can add the report viewer on the page, just look at these HTML tags.
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 compile this application, it will give a compiler error “CR doesn’t exist”. Therefore, you need to register crystal report namespace on the top of the page as shown below.
Note: when you double click on toolbox in the crystalreportviewer Visual studio will do all registration and etc.
Now write few lines to display report on the viewer.
Here we have to write code under the OnPreRender event,I hope you know about ASP.Net event life cycle. Please visit the following links for more information.
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 report with more than one table’s information. I hope this article helpful to you.
Author:
- B.Sc. degree in Computer Science.
- 4+ years experience in C#.net,VB.net adn JAVA.
- Obsessed in OOP style design and programming.
- Designing and developing Network security tools.
- Designing and developing a client/server application for sharing files among users in a
way other than FTP protocol.
- Designing and implementing GSM gateway applications and bulk messaging.
- Windows Mobile and Symbian Programming
- Having knowledge with ERP solutions
The summary of my skills:
C#, VB.Net#,ASP.net, VC++, Java, WPF,WCF, SilverLight,Oracle,PL/SQL, SQL Server, MS Access
My Web Blogs
http://ravesoft.blogspot.com
http://netcodesnippet.blogspot.com/
[Download Sample]
