Working with Crystal Reports in .Net
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.
1. Open the Visual Studio .Net.
2. On the File menu, click New then click Website.
3. Right click on solution, click Add New Item then click Crystal Report.
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.
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.
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.
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.
3. Give a name to the formula field.
4. Three sections will be visible in the formula workshop
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.
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.
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.
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.
By this option data will placed in multiple pages.
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.
4. Select �Month� field from the fields shown.
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.
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.
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.
Note:
To get sum of a field, it should be numeric, if not other than mathematical expressions can be used.
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.
5. Check �Sort Data Hieratically� option.
6. Select a field from �Parent Id field� drop down.
7. Set �Group Indent� as �0.35�.
Note:
Manager data should be sub set of employee data.
Data type of both fields should be same.
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.
5. Check �New Page After� option.
6. Click �Ok�.
7. This sub report will be added as first page of the report.
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.
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.
5. Right click on Report Custom Functions.
6. Select New option.
7. Following screen will be shown.
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.
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)
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.
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
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()
3. �diskFileDestinationOptions� is required to set the path and name of the destination file.
diskFileDestinationOptions.DiskFileName = "C:\report.pdf"
4. �exportOptions� is required to set exporttype and destination file options.
exportOptions.ExportDestinationType = ExportDestinationType.DiskFile
exportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
exportOptions.DestinationOptions = diskFileDestinationOptions
exportOptions.FormatOptions = formatTypeOptions
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.
report.Export(exportOptions)
Fields in reports can be accessed through .Net.
Example:
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.
By above few lines of code a field in a report can be enabled or disabled.
- http://www.crystalreportsbook.com/CrystalReportsXI.asp
- http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_exportandprintreport.pdf
- http://support.businessobjects.com/communityCS/TechnicalPapers/cr_connection_advantages.pdf
