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.

Create a Simple Application

Create a Web Application and Add a Crystal Report

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.

Crystal Reports 10.0

Bind Source to Reports

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

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.

Formula Fields

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.

Functions in Formula Fields

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.

Sections in reports

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.

Show Data of a sub report in Multiple Pages

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.

Group By

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.

Summary Fields

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.

Hierarchal Grouping

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.

Build a Cover Page

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.

Shared Variables

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.

Custom Functions

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.

CrystalReports.Net

Report Document

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)

Sub Report

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

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

Export to PDF

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.<o:p>

 <o:p>

diskFileDestinationOptions.DiskFileName = "C:\report.pdf"<o:p>

<o:p>

4.      “exportOptions” is required to set exporttype and destination file options.<o:p>

 <o:p>

exportOptions.ExportDestinationType = ExportDestinationType.DiskFile<o:p>

exportOptions.ExportFormatType = ExportFormatType.PortableDocFormat<o:p>

exportOptions.DestinationOptions = diskFileDestinationOptions<o:p>

exportOptions.FormatOptions = formatTypeOptions<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>

report.Export(exportOptions)<o:p>

 <o:p>

Access Fields in Reports<o:p>

Fields in reports can be accessed through .Net.<o:p>

 <o:p>

Example: <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>

By above few lines of code a field in a report can be enabled or disabled.

References<o:p>

 <o:p>

1. http://www.crystalreportsbook.com/CrystalReportsXI.asp<o:p>
2. http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_exportandprintreport.pdf<o:p>
3. http://support.businessobjects.com/communityCS/TechnicalPapers/cr_connection_advantages.pdf<o:p>

 

 <o:p>