C# .NET - Crystal Reports

Asked By prince b

07-Jan-11 06:20 AM

Can anybody plz help me how to work with crystal reports with brief example?

Chowdary YBS replied to prince b

07-Jan-11 06:26 AM

<H1>Working with Crystal Reports in .Net </H1>


Overview

This article will walk through some of the functionalities in crystal reports and how to bind a report in visual studio .Net.

First section covers the functionalities in crystal reports and second section covers how to integrate crystal reports with .Net. 


<H1><A name=_Toc151185801>Create a Simple Application</A></H1>

<H2><A name=_Toc151185802>Create a Web Application and Add a Crystal Report</A></H2>
1. Open the Visual Studio .Net.
2. On the File menu, click New then click Website.
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/CreateApplication1.jpg">
3. Right click on solution, click Add New Item then click Crystal Report.
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/CreateApplication2.jpg"> 
<H1><A name=_Toc150151752></A><A name=_Toc150151852></A><A name=_Toc150152043></A><A name=_Toc151185803>Crystal Reports 10.0</A></H1>

<H2><A name=_Toc150151753></A><A name=_Toc150151853></A><A name=_Toc150152044></A><A name=_Toc151185804>Bind Source to Reports</A></H2>
After creating crystalreport (.rpt) file, the first thing to do is set data source to the report.

1. Click on Field Explorer.
2. Right click on Database Fields, click on Database Expert.
3. Click on Create New Connection and select one of the sources.
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/BindSource1.jpg"> 
Some of the sources for crystalreport
1. Tables in a database
Bind tables of a database as source to a report using wizard in the crystal reports. Data will be automatically populated in the report.
1. Stored Procedure in database
Create a stored procedure and bind the stored procedure to the report using wizard in the crystal reports.
2. Objects in .Net
Create object in .net and this can be passed as a source to the crystal reports.
3. Xmlschema
Create an xmlschema and bind that schema as a source to the crystal reports.

<H2><A name=_Toc151185805>Sub reports</A></H2>
Sub reports are very much useful in building reports. Segregate the main report into various sub reports.

Main advantage of the sub reports is
1. Same sub report can be useful in multiple reports. Re usability of sub reports can be done.
2. If we segregate main report into sub reports, it will be very simpler to build reports.

<H2><A name=_Toc151185806>Formula Fields</A></H2>
This is a very good option provided by the crystal reports. Formula fields are very much useful to format input data.

Example:
There are two fields �state� and �city� in database. In the report if both fields have to be displayed as comma separated

1. Click on field explorer.
2. Right click on formula fields and click new.
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/FormulaField1.jpg"> 
3. Give a name to the formula field.
4. Three sections will be visible in the formula workshop
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/FormulaField2.jpg"> 
4.1. First section contains �Report Fields� and �Database Fields�. �Report fields� contains all the fields which were used in the report. �Database Fields� contains all the fields which were available as data source to the report.
4.2. Second section contains all the functions available.
4.3. Third section contains operators such as �=�,�<� and syntax for variable declaration.

5. In the first section expand data source, table name, and fields will be visible. 
6. Double click on �state� and �city� fields. Selected fields will be visible in the intermediate section. Edit the code as shown below.
{Table.City} & ", " & {Table.State}
7. Save the formula field.
8. Place the formula field on crystal reports.

For formula fields, code can be written in two syntaxes. 
1. Crystal syntax: This is the syntax is provided by crystal reports.
2. Basic syntax: This is VB syntax. 
Change the type of the syntax in the drop down shown in the formula workshop.

<H2><A name=_Toc151185807>Functions in Formula Fields</A></H2>
In the formula workshop one of the sections contains functions available to format formula fields.



Example1:
If input string case has to be changed to proper case use �propercase� function under strings section.
1. Create a formula field.
2. Select �propercase� function under strings section.
3. Double click on the function.
4. Select the �Name� field from the database fields.
5. Double click on the �Name� field.
6. The following code will return input string in proper case.

propercase({Name})

Example2:
If input parameter type has to be converted to currency following convert function will be useful.

1. Create a formula field.
2. Select �ccur� function under strings section.
3. Double click on the function.
4. Select the �Amount� field from the database fields.
5. Double click on the �Amount� field.

ccur({Amount})

Various functions are available according to significance, like mathematical functions were available under �Math� section and strings related functions were available under �Strings� section.

<H2><A name=_Toc151185808>Sections in reports</A></H2>
There are 5 basic sections in reports.
1. Report header
This section will appear on top of the report and only once in a report.
2. Page header
This section will appear on top of the page and only once per page.
3. Detail section
This section allows repetition of data. 
4. Page footer
This section will appear on bottom of the page and only once in a report.
5. Report footer
This section will appear on bottom of the report and only once in report.
Crystal report allows adding multiple sections in a report.
1. Right click on any of the section header. 
2. Click Insert section below.

<IMG style="WIDTH: 519px; HEIGHT: 396px" alt="Sample screenshot" src="Crystal_Reports_in_net/SubReport1.jpg" width=576 height=435> 
<H2><A name=_Toc151185809>Show Data of a sub report in Multiple Pages</A></H2>
Each sub report will be like an object. Sub report which contains data will be in a single page, instead of breaking into multiple pages. 

1. Right click on sub report.
2. Uncheck keep object together.

<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/MultiplePages1.jpg">
By this option data will placed in multiple pages.
<H2><A name=_Toc151185810>Group By</A></H2>
This option is similar to the group by option in sqlserver. If data has to be grouped by a particular field this option will be useful.

Example:
There are two fields in data base �month� and �amount�. Requirement is, in the report amount earned has to be shown per month. 

1. In the main menu click on crystal reports.
2. Click Reports.
3. Click on Group expert option.

<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/GroupBy1.jpg"> 
4. Select �Month� field from the fields shown.

<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/GroupBy2.jpg"> 
Two new sections will appear in the report, group header and group footer. The use of these sections will be same as header and footer as mention in the sections. 

<H2><A name=_Toc151185811>Summary Fields</A></H2>
This is an extended option of group by. We will discuss this option with same example discussed in group by section.

In the example (Discussed in Group By section), after grouping the data according to month, if sum of the amount earned per month has to be displayed this option will be useful. 

1. In the main menu click on crystal reports.
2. Click on insert option.
3. Click on summary option.

<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/Summary1.jpg" width=494 height=432> 
4. Select a database field in the �Choose the field to summarize� drop down.
5. Select an expression from �Calculate the summary� drop down.
6. Click �ok�. Summary field will be added in the report.

<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/Summary2.jpg"> 
Note:
To get sum of a field, it should be numeric, if not other than mathematical expressions can be used. 

<H2><A name=_Toc151185812>Hierarchal Grouping</A></H2>
This option will be useful to group data hieratically.

Example:
Simple example where this option will be useful is employee manager relation. There are two fields in database.

Employee Manager
Emp1 ---
Emp2 Emp1
Emp3 Emp1
Emp4 Emp2
Emp5 Emp2
Emp6 Emp3
Emp7 Emp3
Emp8 Emp1



Requirement: 
Emp1
 Emp2
 Emp4
 Emp5
 Emp3
 Emp6
 Emp7
 Emp8

Above format shows data in hierarchal relation between employee and manager.

1. First group the data (As discussed in Group By section) on employee field.
2. In the main menu click on Crystal Reports.
3. Click on Report option.
4. Click on Hierarchal Grouping Option.

<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/HierarichalGrouping1.jpg"> 
5. Check �Sort Data Hieratically� option.
6. Select a field from �Parent Id field� drop down.
7. Set �Group Indent� as �0.35�.


<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/HierarichalGrouping2.jpg"> 
Note:
Manager data should be sub set of employee data.
Data type of both fields should be same.

<H2><A name=_Toc151185813>Build a Cover Page</A></H2>
A simple example for this is, if report name should come at the starting of the report in a separate page
1. Build a sub report which contains report name.
2. Place the sub report in the main report in Report Header section.
3. Right click on the sub report section header.
4. Click on Section Expert option.

<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/CoverPage1.jpg"> 
5. Check �New Page After� option.
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/CoverPage2.jpg" width=531 height=432>


6. Click �Ok�.
7. This sub report will be added as first page of the report.

<H2><A name=_Toc151185814>Shared Variables</A></H2>
These variables can be accessible globally i.e. through out the report. If a shared variable has been declared in of the sub report, it can accessible in the successive sub reports.

The syntax to declare variables will be available in the operator section of the Formula Workshop.

Example:
1. A report contains two sub reports subreport1, subreport2.
2. Filed amount is useful in subreport2. 
3. Declare shared variable in subreport1. 
4. Create a formula field in subreport1.
5. Write following lines of code.
shared currencyvar x := {Amount};
6. Use the same variable in subreport2. 
7. Declare a shared variable of same data type which was in subreport1.
8. Create a formula field in Subreport2. Write following lines of code.
shared currencyvar z;
shared currencyVar x;
z := x;
9. By above few lines of code value in �x� has been assigned to �z�.

Note:
1. Data type of both the shared variables should be same.
2. Shared variable which are going to access globally should be loaded first. In the above case subreport1 should be loaded first and subreport2 should be loaded later in the main report, because variable declared in the subrerpot1 are going to access globally.

<H2><A name=_Toc151185815>Custom Functions</A></H2>
We can create custom functions apart from the functions available in the formula workshop.

1. In the main menu click on Crystal Reports.
2. Select Report option.
3. Select Selection Formula option.
4. Select Group option. 
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/CustomFunctions1.jpg">

5. Right click on Report Custom Functions.
6. Select New option.
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/CustomFunctions2.jpg">

7. Following screen will be shown.
<IMG alt="Sample screenshot" src="Crystal_Reports_in_net/CustomFunctions3.jpg">

8. Add following lines of code in the Middle section.
Function Mult(i As number, j as number) As Number
Dim i As number
Dim j As number
Dim expr As number
expr = i * j
End Function
9. Above few lines of code will create a function, which have two input parameters and return a value. 

To create functions, code can be written in either crystal syntax or VB syntax. To change the syntax option will be available in the menu of formula workshop.
<H1><A name=_Toc150151754></A><A name=_Toc150151854></A><A name=_Toc150152045></A><A name=_Toc151185816>CrystalReports.Net</A></H1>

<H2><A name=_Toc151185817>Report Document</A></H2>
Namespace required:
Imports CrystalDecisions.CrystalReports.Engine

Reportdocument is the main object through which crystalreports can be accessed. 
Reportdocument object will be useful to 
1. Load a report.
2. Set data source to a report.
3. Export a report.

Dataset, Datatable, datareader or collection any one of the listed objects can be passed as data source to the report.

Dim report As New ReportDocument()
report.Load("C:\report.rpt")
report.SetDataSource(datatable)

<H2><A name=_Toc151185818>Sub Report</A></H2>
A report can have any number of sub reports. Data source can be set to sub report using the reportdocument object of the main report container.

Dim report As New ReportDocument()
report.Load("C:\report.rpt")

Example:
If the main report contains 3 sub reports, 
1. Load report document object with main report (report.rpt) as described in the �Report Document� section.
2. With following lines of code data source can be set to sub reports.

report.Subreports.Item("Subreport1").SetDataSource(datatable1)
report.Subreports.Item("Subreport2").SetDataSource(datatable2)
report.Subreports.Item("Subreport3").SetDataSource(datatable3)

Note:
A sub report can not contain another sub report. 
<H2><A name=_Toc151185819>Crystalreportviewer</A></H2>
Crystalreportviewer is useful to view the report on a webpage.After setting datasource to the report and subreports the following code is required to bind the report to Crystalreportviewer.

crystalreportviewer.ReportSource = report

<H2><A name=_Toc151185820>Export to PDF</A></H2>
Namespace required: 
Imports CrystalDecisions.Shared

To export a report as a pdf document the following code will be useful.
1. Load report document object with main report(report.rpt).
2. Write following lines of code.

Dim exportOptions As New ExportOptions
Dim diskFileDestinationOptions As New DiskFileDestinationOptions()
Dim formatTypeOptions As New PdfRtfWordFormatOptions() 


<SPAN style="FONT-FAMILY: 'Book Antiqua'; FONT-SIZE: 12pt; mso-fareast-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman'; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">
3. �diskFileDestinationOptions� is required to set the path and name of the destination file.<?xml:namespace prefix = o /><o:p></o:p>

 <o:p></o:p>
diskFileDestinationOptions.DiskFileName = "C:\report.pdf"<o:p></o:p>
<o:p></o:p>
4. �exportOptions� is required to set exporttype and destination file options.<o:p></o:p>
 <o:p></o:p>
exportOptions.ExportDestinationType = ExportDestinationType.DiskFile<o:p></o:p>
exportOptions.ExportFormatType = ExportFormatType.PortableDocFormat<o:p></o:p>
exportOptions.DestinationOptions = diskFileDestinationOptions<o:p></o:p>
exportOptions.FormatOptions = formatTypeOptions<o:p></o:p>
 <o:p></o:p>
5. After setting the type of export and destinationfile options following few lines of cede is required to export the report to the specified destation path.<o:p></o:p>
 <o:p></o:p>
report.Export(exportOptions)<o:p></o:p>
 <o:p></o:p>
<H2 style="TEXT-INDENT: 0in; MARGIN: 12pt 0in 3pt"><A name=_Toc151185821>Access Fields in Reports</A><o:p></o:p></H2>
Fields in reports can be accessed through .Net.<o:p></o:p>
 <o:p></o:p>
Example: <o:p></o:p>
 If a field in report has to be enabled or disabled according to input, it can be accessible through code in .Net.
 
report.ReportDefinition.Sections("Section1").ReportObjects("Field1").ObjectFormat.EnableSuppress = True
 
Using Report Definition object, sections in a report can be accessible. Using sections report objects can be accessible. Report object contains all the fields in a section. Object Format contains all the properties available for the fields. 
 <o:p></o:p>
By above few lines of code a field in a report can be enabled or disabled.
<H1 style="MARGIN: 12pt 0in 3pt"><A name=_Toc151185822>References</A><o:p></o:p></H1>
 <o:p></o:p>
<OL style="MARGIN-TOP: 0in" type=1>
<LI style="TEXT-ALIGN: justify; MARGIN: 0in 0in 0pt; tab-stops: list .5in; mso-list: l27 level1 lfo29" class=MsoNormal><A href="http://www.crystalreportsbook.com/CrystalReportsXI.asp">http://www.crystalreportsbook.com/CrystalReportsXI.asp</A><o:p></o:p>
<LI style="TEXT-ALIGN: justify; MARGIN: 0in 0in 0pt; tab-stops: list .5in; mso-list: l27 level1 lfo29" class=MsoNormal><A href="http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_exportandprintreport.pdf">http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_exportandprintreport.pdf</A><o:p></o:p>
<LI style="TEXT-ALIGN: justify; MARGIN: 0in 0in 0pt; tab-stops: list .5in; mso-list: l27 level1 lfo29" class=MsoNormal><A href="http://support.businessobjects.com/communityCS/TechnicalPapers/cr_connection_advantages.pdf">http://support.businessobjects.com/communityCS/TechnicalPapers/cr_connection_advantages.pdf</A><o:p></o:p></LI></OL>

Chowdary YBS replied to prince b

07-Jan-11 06:32 AM

Working with Crystal Reports.zip

Please go thru the atatched document.

Happy Learning

Cheers!!!
Chowdary YBS

Vivek Jagga replied to prince b

07-Jan-11 06:36 AM

Mahesh,

Learn the basic of crystal report with Asp.Net applocation on http://www.codeproject.com/KB/aspnet/Crystal_Reports_in_net.aspx

Wise for Visual Studio.NET

Wise for Visual Studio.NET Wise for Visual Studio.NET By Peter A. Bromberg, Ph.D. To "Print This Page" Link Peter Bromberg Wise for Visual Studio .NET is a total and complete installation development system for creating and editing Windows® Installer MSI) installation packages that is 100% integrated into the Visual Studio.NET IDE. . It is a complete and user-friendly front end for generating Windows

Why are Visual Studio 2005 Start Page events so out of date? .NET Framework

Why are Visual Studio 2005 Start Page events so out of date? .NET Framework Why are Visual Studio 2005 Start Page events so out of date? Is there a way to get a refreshed upto-date list? Visual Studio .NET Discussions Visual Studio 2005 (1) Roughgarden (1) Upto (1) Have you checked your local

Visual STUDIO 2005 is slow .NET Framework

Visual STUDIO 2005 is slow .NET Framework I have migrated my web asp (VB) application to my windows 2003 server, but now when i open the solutuion whit Visual Studio , Visual Studio is very low to open it and to debug it!!! THANK's Visual Studio .NET Debugging Discussions Visual Studio (1) VB (1) Database (1) SourceSafe (1) Yuan (1) Wang

New Features in Visual Studio 11 and .NET Framework 4.5

New Features in Visual Studio 11 and .NET Framework 4.5 The first thing you'll notice after installing the bits is that Visual Studio 11 loads FAST. Very fast, compared to Visual Studio 2010! Below you'll find a summary of some of the new features and enhancements in Visual Studio 11. Visual Studio 11 Developer Preview is out. You can download the web installer here

Visio Installation Visio

Visio Installation Visio I have 1 - Microsoft Visual Studio.Net 2003 Enterprise Architect - Installed 2 - Microsoft Office Visio 2003 for Visual Studio 2005 Enterprise Architect When I try to install the Visio it says What are the "Qualified Visual Studio Editions"? It it not Visual Studio.Net 2003 EA. Does it have to be Visual Studio.Net 2005 EA? Cordially, Jawad