Monday, August 5, 2013

Crystal Reports in C# NET

Crystal Reports:
-It was a most popular reporting tool used by many companies for desiging Reports. It belongs to the company Business Objects but was integrated directly with our Visual Studio.

-If we want to add a report under the project open "Add New Item" window -> select Reporting in LHS panel -> then select Crystal Report from RHS panel, which adds a Report file, with extension as .rpt.

-Every Report when opened has 5 sections in it:
-Report Header
-Page Header
-Details
-Report Footer
-Page Footer

-Report Header: content placed under this section gets displayed on top of the report i.e. on top of the first page in report.
eg: Company Name, Address etc.,

-Page Header: content placed under this section gets displayed on top of every page into which the report extends.
eg: Column Names

-Details: content into this section generally comes from DB, this was the actual information which has to be presented to the clients.

 -Report Footer: this was same as Report Header but gets displayed on bottom of the report.
eg: Date, Place, Signatures etc.

 -Page Footer: this was same as Page Header but gets displayed on bottom of every page into which the report extends.
eg: Page No's.
-------------------------------------------------------------------------------------

Creating a Report:
-Open a new project of type Windows -> name it ReportProject, open add new item window -> select Reporting on LHS -> choose Crystal Report on RHS -> name the report as Sample.rpt -> choose blank report option -> click Ok.

-As report has to get it's information from DB first we need to configure the report with appropriate Data Source using the "Field Explorer" window that gets added along with your Report (Ctrl + Alt + T).

Configuring the Report with DB:
-Open the Field Explorer -> right click on DB Fields Node -> select 'Database Expert' which opens a window -> expand Create New Connection node -> double click on 'OLEDB (ADO)' node that displays a list of providers -> choose 'Microsoft OLEDB Provider for Sql Server' -> click next -> specify the connection details like server name, user id, password & DB ->  click Finish which adds a node under OLEDB (ADO) -> expand it ->  expand DB node you specified eg: CSharp4 -> "dbo" -> Tables -> double click on the table we want to consume, eg: Customer -> adds table under selected tables list -> click ok -> adds Table under DB Fields node of Field Explorer.

Desiging the Report:
-Go into Report Header section right click in it and select Insert Text Object that adds an object like a Label, enter content in it & do required alignments using the format toolbar on top.

-Goto Page Header section right click on it & select Insert -> Special Field -> DataDate, place it on LHS of the section, right click on it select format object and choose the format in which we want to display the date, in the same way select DataTime & place it on RHS.

-Now open Field Explorer and expand the table Customer we have selected, that displays the list of columns below, drag and drop each column on to details section as per your design which will create 1 Field on the Page Header (Column Name) and 1 Field on the Details (Data).

-Now go to Report Footer & provide the option for users to enter Date, Place and Signature using the Text Objects and Insert Line Options.

-Now go the Page Footer section right click Insert -> Special Field -> select Page Number and place it on the Center of the section.
-------------------------------------------------------------------------------------
Launching the Report:
-To display the report we need to use the control CrystalReportViewer, which has to be placed on Form, that will by default use Dock property as Fill.

-If we want to show a report under the control first it has to be loaded into the app from its physical location.

-Now place a CrystalReportViewer control on form and write the following code:

Under Form Load:
crystalReportViewer1.ReportSource =
<path of report>;

Note: to get the path of your report goto Solution Explorer, right click on crystal report file & select properties, copy value under Fullpath property and use it.
-------------------------------------------------------------------------------------
Selection-Based Reports:
-These are reports which gets generated based on the selections made by the users which may get the data from single or multiple tables.

Step 1: Creation of Form1
-Open a new project of type Windows -> name it as SelectionReport.
-Add the reference of System.Data.OracleClient to the project.
-Design the form as above & then write following code:

using System.Data.OracleClient;
-------------------------------------------------------------------------------------
Declarations:
   OracleConnection con;
   OracleDataAdapter da;
   DataSet ds;
-------------------------------------------------------------------------------------
con = new OracleConnection(
           "User Id=Scott;Password=tiger");
da = new OracleDataAdapter("Select Empno From                            Emp", con);
ds = new DataSet();
da.Fill(ds, "Emp");
comboBox1.DataSource = ds.Tables[0];
comboBox1.DisplayMember = "Empno";
-------------------------------------------------------------------------------------
Under ShowReport Button:
Note: Write this code after creation of Form2

Form2 f = new Form2();
f.Empno = int.Parse(comboBox1.Text);
f.ShowDialog();

Note: When u call the Show or ShowDialog method on a form it will start execution of the form from the load event.
------------------------------------------------------------------------------------
Step 2: Designing the Report
-Add a Crystal Report under the project.

-Configuring the report with DB:
-Open the field explorer -> right click on database fields -> database expert -> create new connection -> Oledb (ADO) -> Microsoft Oledb Provider for Oracle -> click next -> provide the connection information -> Finish.

-Adds a node under Oledb(ADO) as Connection ->  double click on "Add Command" node under connection  -> opens a window where we can define a Select Stmt on LHS TextArea.

-Our select stmt should execute basing on the selection made with in ComboBox of Form1 which should be sent as a parameter to report, so first we need to create a parameter & then use it under the select stmt.

-To create a param click on "Create" button which opens a window under which we need to specify following details:

1. Parameter Name: Eno
2. Prompting Text: Enter Employee No.
3. Value Type: Number
4. Default Value: 7566 (optional)

-Click ok which adds the param on RHS ListBox, now in LHS TextBox write following Sql Stmt:

Select E.Empno, E.Ename, E.Job, E.Sal, D.Deptno, D.Dname, D.Loc From Emp E Inner Join Dept D
On E.Deptno=D.Deptno Where E.Empno = {?Eno}

Click Ok -> Ok -> Ok which will add the Command under Database Fields, if we expand it will display the columns we mentioned under Select Stmt.

-we can also Add Parameters using Field Explorer window, to create a Parameter right click on the node Parameters and Select Add which will prompt for Name of Parameter, enter a name and click ok.

-Following the above process create 2 parameters CompanyName and Address, drag & drop them on ReportHeader & do necessary alignments.

-Place DataDate & DataTime Fields on PageHeader.

-Drag and Drop Columns under Command into the details section and pull back column names from PageHeader to Details.

-Right click on the ReportFooter secion and select supress, so that our report doesn't have a report footer.

-Place a PageNumber Special Field on PageFooter.
-------------------------------------------------------------------------------------
Step 3: Launching the Report
-Add a New Form under the project, place a CrystalReportViewer control on it, set the Window State property as Maximized & add reference of System.Configuration assembly.

-Open Add New Item window & add "Application Configuration File" item template i.e. app.config & under it write the following under configuration tag.

<appSettings>
   <add key ="Cname" value ="NIT Technologies"/>
   <add key ="Addr" value="Ameerpet, Hyderabad"/>
</appSettings>

-In this case we need to launch the report as well as send few parameters to the report for execution to send the values to parameters we need to make use of the class ReportDocument present under CrystalDecisions.CrystalReports.Engine namespace.

-Write the following code under Form:

using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;
-------------------------------------------------------------------------------------
Declarations:
   internal int Empno;
-------------------------------------------------------------------------------------
Under Form2 Load:
string cname =                        ConfigurationManager.AppSettings.Get("Cname");
string addr =                                              ConfigurationManager.AppSettings.Get("Addr");

ReportDocument obj = new ReportDocument();
obj.Load("<path of the report>");
crystalReportViewer1.ReportSource = obj;

//Sending values to parameters of report:
obj.SetParameterValue("CompanyName", cname);
obj.SetParameterValue("Address", addr);
obj.SetParameterValue("Eno", Empno);
-------------------------------------------------------------------------------------
Note: using the SetParameterValue method of  ReportDocument class we can pass values to Parameter Fields of report.
   SetParameterValue(string pname, object value)
   SetParameterValue(int index, object value)




No comments:

Post a Comment

amazon

Sukanya Samriddhi Account - SBI

SUKANYA SAMRIDDHI Account information by SBI SUKANYA SAMRIDDHI ACCOUNT : FACILITY AVAILABLE AT ALL BRANCHES OF SBI Sukanya Samriddhi ...