Skip to content

VB.NET – Crystal Reports

October 8, 2005

‘Crystal Report’ in VB.NET are a replacement of ‘Data Reports’ in VB6. Report generation is done at design-time or at runtime. It is simple to design the report at design-time as the programming part is very less during design-time and you just have to follow the simple wizards. Whereas if you design the reports at runtime, you have to program it completely. We recommend that you design the reports at runtime. Though the programming part is more but you will have a full control over the report design, layout and it’s data sources. You will find many articles on designing the report at design-time and so we are going to discuss report generation at runtime in this article.

Report Generation

Report generation in VB.NET at runtime is divided into two steps:

  1. Create and save the report on the disk.
  2. Load the report and display it in the ‘CrystalReportViewer’ component.

For creating and saving the report at runtime you have to add references of two objects: adodb and Crystal Reports .NET ActiveX Designer Design and Runtime Library

For adding a reference to adodb, follow the steps below:

  1. Select Project >> Add Reference
  2. Select .NET tab and double-click ‘adodb’ in the list to add it’s reference to the project.

For your convenience, below is a picture of VB.NET IDE on how to do this.

Adding a reference to ADODB

Adding a reference to ADODB

For adding a reference to Crystal Reports .NET ActiveX Designer Design and Runtime Library, follow the steps below:

  1. Select Project >> Add Reference
  2. Select COM tab and double-click ‘Crystal Reports .NET ActiveX Designer Design and Runtime Library’ in the list to add it’s reference to the project.

Have a look at the picture of the IDE below to accomplish this task

Adding a reference to Crystal Reports .NET ActiveX Designer Design and Runtime Library

Adding a reference to Crystal Reports .NET ActiveX Designer Design and Runtime Library

Now as you have added the references to the project, you can start writing the code for creating and saving the report.

Create and save the report.

For creating and saving the report, you have to write the code as follows:

Dim Rep As CRAXDDRT20.Report
Rep = New CRAXDDRT20.ApplicationClass().NewReport

'Link to database
Rep.Database.AddOLEDBSource("Provider=Microsoft.Jet.OleDB.4.0;Data Source=d:/dbs.mdb", "Data")

'Setup the heading
Rep.Sections(1).AddTextObject("Report on the table 'Data'", 100, 200)

'Setup field names
Rep.Sections(2).AddTextObject("Name", 4000, 200)
Rep.Sections(2).AddTextObject("Age", 7000, 200)

'add field data objects
Rep.Sections(3).AddFieldObject(Rep.Database.Tables(1).Fields(1), 4000, 200)
Rep.Sections(3).AddFieldObject(Rep.Database.Tables(1).Fields(2), 7000, 200)

'Save report
Rep.SaveAs("D:\report.rpt", CRAXDDRT20.CRReportFileFormat.cr80FileFormat)

In this example, we are using the Microsoft Access Database named ‘dbs.mdb’ which is located in the root drive D. It has one table named ‘Data’ which has two columns named ‘Name’ and ‘Age’.

This code creates and saves the crystal report to drive D. You must know that the structure of a crystal report is almost the same as the structure of a Data Report in VB6. Crystal Report is divided into sections on which controls are embedded. First section normally includes the header of the report. The second one includes the column names. The third section includes the data and the fourth one has footers. This is the structure that we have created in the above code but the structure really depends upon the layout you choose.

The code first initializes a ‘Report’ object which lies in ‘CRAXDDRT20′ package. ‘CRAXDDRT20′ is the name for ‘Crystal Reports .NET ActiveX Designer Design and Runtime Library‘ object.

Then it attaches a data source by calling ‘AddOLEDBSource’ function it’s ‘Database’ object. This function takes two parameters: First one is the ‘ConnectionString’ and the second one is the name of the table whose data you want to display in the table.

Then we have added a text object to the first section of the report through ‘AddTextObject’ function to display the heading of the report . This function takes three parameters: First one is the text that you want to display, second one is it’s left location and third one is it’s top location. Similarly, we have added two more text objects to the second section to display the names of the columns: ‘Name’ and ‘Age’.

Then, we have added field object to the third section. A field object is different from text object. A text object contains a text specified by you whereas a field object displays the text value of the column of the report’s data source. This object takes three parameters: First one is the field whose data you want to display in this field object. We have used these objects to display the data of two columns: ‘Name’ and ‘Age’ with their indexes as 1 and 2. The second one is the text object’s left location and third one is it’s top location.

Like text object and field object, you have many types of objects that you can place at the report. You can experiment those object on your own.

Finally, we save the report to ‘D:/report.rpt’ through ‘Report’ object’s ‘SaveAs’ function. This function takes two parameters: First one is the name and path of the report where you want to save the report and second one is the format in which you want to save the report. You can choose from the two formats ‘CRAXDDRT20.CRReportFileFormat.cr70FileFormat‘ and ‘CRAXDDRT20.CRReportFileFormat.cr80FileFormat‘.

Loading and displaying the report

As we have created and saved the report, now is the time to load and display the report to the user through VB Form. VB.NET provides a control called ‘CrystalReportViewer’ which provides us the ability to display a saved report as well as reports generated at design-time. This control is in the toolbox and you have to place it from the toolbox to the VB Form. Place it on the VB Form and rename is to ‘CRV’. Once you do this, you just have to place this single line of code in either the form’s load event or any command button’s event as per your wish to display the report.

CRV.ReportSource = "D:\report.rpt"

This single line loads a report that we created and saved in “D:/report.rpt”.

Filtering records in a Crystal Report

The crystal report created above displays all the entries or records of a table in the database. But consider a situation where you want to display specific records in a report. For e.g. In the above report, you might be interested in displaying the name and age of only those persons who are 25 years of age. In a database, you use SQL queries to filter out the records but here, you will define a ‘SelectionFormula’ to filter the records. ‘SelectionFormula’ is an element of ‘CrystalReportViewer’ component and you can define it as follows:

CRV.SelectionFormula = "{Data.Age}='25'"

This line of code should be placed before defining the ‘ReportSource’ for the report. This code will filter out the records and display only the records where a person’s age is ’25′ in the table named ‘Data’. You can use ‘AND’ or ‘OR’ operators to define multiple fields selection formula. For e.g.:

CRV.SelectionFormula = "{Data.Name}='John' AND {Data.Age}='25'"

This code will filter out entries where the person’s name is John and his age is 25.

Comments

Tell us what do you think.

There are no comments on this entry.

Trackbacks

Websites mentioned my entry.

There are no trackbacks on this entry

Add a Comment

Fill in the form and submit.