Editor's Note: The first edition of this article was written by Ajay Varghese.

Before we started off this small research on Crystal Reports for Visual Studio .NET, my friend and I were inquisitive about the complexity with regard to its implementation into our web application. A week later, with a lot of effort going into hunting for the ‘how-to’ documentation online, we have managed to integrate some simple reports into our ASP.NET application and try some neat tricks with it!!

This article is a compilation of required material to kick-start the process of implementing Crystal Reports into your .NET web application and should reduce your frustrating efforts (spent for the same research that we made) to a trifle by using these step-by-step walkthroughs. To get the best out of this article, the reader should have a basic Knowledge of database connections in ASP.NET and use Visual Studio .NET for the development. Please note that we have tested the below given sample code illustrations with the beta 2 version of Visual Studio .NET only.

The topics that we have covered here are :

1) Introduction

2) Getting a feel of it - Using an existing Crystal Report file in your .aspx page

3) Crystal Reports Walkthrough - using the Pull Model

4) Crystal Reports Walkthrough - using the Push Model

5) Exporting the Report file into other formats

Crystal Report comes in various flavors and the one that is required for building reports for .NET is "Crystal Report for Visual Studio .NET".  It exposes a rich programming model with which we could manipulate its properties and methods during runtime.  If you are developing your .NET applications using Visual Studio .NET then you won’t have to install any additional software as it is already built into Visual Studio .NET.

---- Advantages -----

Some of the major advantages of using Crystal Report for Visual Studio .NET are :

- Rapid report development

- Can extend it to complicated reports with interactive charts

- Exposes a report object model using which it can interact with other controls on the web form

- Can programmatically export the reports into widely used formats like .pdf, .doc, .xls, .html and .rtf

 

---- The Architecture ----

The various components that make up a simple implementation of Crystal Report as a 2-tier architecture, required for web applications are

The Client :

The client only needs a browser to access the reports which are embedded into the .aspx pages.

The Web Server hosts the :

- Crystal Report Engine (CREngine.dll)

Along with other tasks like merging the data with the report file, exporting reports into different formats, etc., it is the Report Engine that converts your Crystal Report into plain HTML that is passed on to your .aspx page.

- Crystal Report Designer (CRDesigner.dll)

The reports are created from scratch using the Crystal Report Designer, with which you could design the titles, insert data, formulas, charts, sub-reports, etc.

- The .rpt Report file

The first step to implement a report into your web application would be to create it using the Crystal Report Designer interface.  You will find some ready-made .rpt samples provided with the default installation.

- The Data Source

The way your .rpt file gets the data depends on which method you choose. You can choose to make Crystal Report itself to fetch your data without writing any code for it or you can choose to manually populate a dataset and pass it on to the report file. We will look at the various possibilities a little later in this article.

- Crystal Report Viewer web form Control (CRWebFormViewer.dll)

The Crystal Report Viewer control is a web form control that can be inserted into your .aspx page.  It can be thought of as a container that hosts the report on the .aspx page. 

Note : In a more complex implementation, the reporting server and the web server could be on different physical servers, where the web server would make an HTTP request to the reporting server.  The Crystal Reports could also be implemented as a web service.

---- Implementation Models -----

Fetching the data for the Crystal Report could be done by using any of the following methods :

- Pull Model :

where in Crystal Report handles the connection to the database using the specified driver and populates the report with the data, when requested.

- Push Model :

where it is the developer who has to write code to handle the connection and populate the dataset, and pass it on to the report.  The performance can be optimized in this manner by using connection sharing and manually limiting the number of records that are passed on to the report.

---- Report Types ----

Crystal Report Designer can load reports that are included into the project as well as those that are independent of the project.

- Strongly-typed Report :

When you add a report file into the project, it becomes a ‘strongly-typed’ report. In this case, you will have the advantage of directly creating an instance of the report object, which could reduce a few lines of code, and caching it to improve performance. The related .vb file, which is hidden, can be viewed using the editor’s ‘show all files’ icon in the Solution Explorer. 

- Un-Typed Report :

Those reports that are not included into the project are ‘un-typed’ reports.  In this case, you will have to create an instance of the Crystal Report Engine’s 'ReportDocument' object and manually load the report into it.

---- Other things you should know ----

-         Though the Crystal Report Viewer control comes with some cool in-built options like zooming, page navigation, etc., it does not have a custom print option. You will have to depend on the browser’s print feature.

-         An un-registered copy of Crystal Report for Visual Studio .NET will remain active only for the first 30 uses, after which the ‘save’ option will be disabled.  To avoid this, all you have to do is register the product with www.crystaldecisions.com for which you are not charged.

-         The default installation will service only 5 concurrent users. To support more users, you will have to buy additional licenses from www.crystalDecisions.com

Lets take a look at how we could get this done the fast way to get a feel of how a Crystal Report file would look like in your web form.

1) Drag and drop the "Crystal Report Viewer" from the web forms tool box on to the .aspx page

 

2) Bring up the properties window for the Crystal Report Viewer control

3) Click on the [...] next to the "Data Binding" Property and bring up the data binding pop-up window

4) Select "Report Source" from the "Bindable properties" section on the left side

 

5) Select the "Custom Binding Expression" radio button, on the right side bottom of the window and specify the sample .rpt filename and path as

"C:\Program Files\Microsoft Visual Studio.NET\Crystal Reports\Samples\Reports\General Business\World Sales Report.rpt"(including the double quotes) and Click "ok"

 

Note : The ‘World Sales Report.rpt’ file is created as a part of Visual Studio .NET installation. If you have specified a different directory during installation then make necessary changes to the above specified path.

In a couple of seconds you should see the Report Viewer Control load a preview of the actual report during design time itself. The reason for the data being loaded during design time is that the report has been saved with the data.

The above steps actually insert the following code into your .aspx page :

<%@ Register TagPrefix="cr" Namespace="CrystalDecisions.Web" Assembly="CrystalDecisions.Web" %>

above the Page Directive and

<CR:CrystalReportViewer

id="CrystalReportViewer1"

runat="server"

Width="350px" Height="50px"

ReportSource='<%# "C:\Program Files\Microsoft Visual Studio.NET\Crystal Reports\Samples\Reports\General Business\World Sales Report.rpt" %>'>

</CR:CrystalReportViewer>

within the <FORM> section of the page.

6) Call the DataBind method, on the Page Load Event of the Code Behind file (.aspx.vb).

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)       

DataBind()

End Sub

 

7) Save, build and run your page.

There !!! You have a web form page with a Crystal Report file embedded into it.

We would be using the following steps to implement Crystal Reports using the Pull Model :

1. Create the .rpt file (from scratch) and set the necessary database connections using the Crystal Report Designer interface.

2. Place a CrystalReportViewer control on the .aspx page and set its properties to point to the .rpt file that we created in the previous step.

3. Call the databind method from your code behind page.

Creating the .rpt File:

1) Add a new Crystal Report to the web form by right clicking on the "Solution Explorer", selecting "Add" --> "Add New Item" --> "Crystal Report".

 

2) On the "Crystal Report Gallery" pop up, select the "As a Blank Report" radio button and click "ok".

 

 

3)This should open up the Report File in the Crystal Report Designer

 

 

4) Right click on the "Details Section" of the report, and select "Database" -> "Add/Remove Database"

 

5) In the "Database Expert" pop up window, expand the "OLE DB (ADO)" option which should bring up another "OLE DB (ADO)" pop up

 

6) In the "OLE DB (ADO)" pop up, Select "Microsoft OLE DB Provider for SQL Server" and click "Next"

 

 

7) Specify the connection information

          Server : HomePC (Make use of your server name here)

          User Id : sa

          Password :

          Database : Pubs

 

8) Click "Next" and then click "Finish"

 

9) Now you should be able to see the Database Expert showing the table that have been selected

 

10) Expand the "Pubs" database, expand the "Tables", select the "Stores" table and click on ">" to include it into the "Selected Tables" section.

 

 

11) Now the Field Explorer should show you the selected table and its fields under the "Database Fields" section, in the left window.

 

12) Drag and drop the required fields into the "Details" section of the report. The field names would automatically appear in the "Page Header" section of the report. If you want to modify the header text then right click on the text of the "Page Header" section, select "Edit Text Object" option and edit it.

 

 

13) Save it and we are through with the creation of the Crystal Report file !!

Creating a CrystalReportViewer Control

14) Back on the web form page, drag and drop a "Crystal Report Viewer" control from the "WebForms" tool box. (image shown in the previous example)

15) Bring up the properties window of the CrystalReportViewer control, Select Databindings and Click on [...]

 

16) In the "Crystal Report Viewer Databinding" pop-up window, select "Report Source" from the "Bindable Properties" section on the left side, Select "Custom Binding Expression" radio button on the right side bottom, and specify the file name with the path of the .rpt file (within double quotes).

 

 

17) You should be able to see the Crystal Report Viewer showing you a preview of actual report file using some dummy data and this completes the inserting of the Crystal Report Viewer controls and setting its properties.

 

Note : In the previous example, the CrystalReportViewer control was able to directly load the actual data during design time itself as the report was saved with the data. In this case, it will not display the data during design time as it not saved with the data - instead it will show up with dummy data during design time and will fetch the proper data only at run time.

 

Code Behind Page Modifications

18) Call the Databind method on the Page Load Event of the Code Behind file (.aspx.vb).

Run your Application

19) Build and run your .aspx page and you should be able to see your own report file embedded into the web page!!!

 

Note that you will have access to the built-in controls of Crystal Report like "Page Navigation", "Zoom", etc in your web page.

We would use the following steps to implement Crystal Reports using the Push Model :

1. Create a Dataset during design time.

2. Create the .rpt file (from scratch) and make it point to the Dataset that we created in the previous step.

3. Place a CrystalReportViewer control on the .aspx page and set its properties to point to the .rpt file that we created in the previous step.

4. In your code behind page, write the subroutine to make the connections to the database and populate the dataset that we created previously in step one.

5. Call the Databind method from your code behind page.

Creating a Dataset during Design Time to Define the Fields of the Reports

1) Right click on "Solution Explorer", select "Add" --> select "Add New Item" --> Select "DataSet"

 

 

2) Drag and drop the "Stores" table (within the PUBS database) from the "SQL Server" Item under "Server Explorer".

3) This should create a definition of the "Stores" table within the Dataset

 

- The .xsd file created this way contains only the field definitions without any data in it. It is up to the developer to create the connection to the database, populate the dataset and feed it to the Crystal Report.

Creating the .rpt File :

4) Create the report file using the steps mentioned previously. The only difference here is that instead of connecting to the Database thru Crystal Report to get to the Table, we would be using our DataSet that we just created.

5)After creating the .rpt file, right click on the "Details" section of the Report file, select "Add/Remove Database"

6) In the "Database Expert" window, expand "Project Data" (instead of "OLE DB" that was selected in the case of the PULL Model), expand "ADO.NET DataSet", "DataSet1", and select the "Stores" table.

7) Include the "Stores" table into the "Selected Tables" section by clicking on ">" and then Click "ok"

 

8) Follow the remaining steps to create the report layout as mentioned previously in the PULL Model to complete the .rpt Report file creation

 

Creating a CrystalReportViewer Control

9) Follow the steps mentioned previously in the PULL Model to create a Crystal Report Viewer control and set its properties.

Code Behind Page Modifications :

10) Call this subroutine in your page load -

    Sub BindReport()

        Dim myConnection As New SqlClient.SqlConnection()

        myConnection.ConnectionString= "server= (local)\NetSDK;database=pubs;Trusted_Connection=yes"

        Dim MyCommand As New SqlClient.SqlCommand()

        MyCommand.Connection = myConnection

        MyCommand.CommandText = "Select * from Stores"

        MyCommand.CommandType = CommandType.Text

        Dim MyDA As New SqlClient.SqlDataAdapter()

        MyDA.SelectCommand = MyCommand

 

        Dim myDS As New Dataset1()

       'This is our DataSet created at Design Time      

 

        MyDA.Fill(myDS, "Stores")  

 

        'You have to use the same name as that of your Dataset that you created during design time

  

        Dim oRpt As New CrystalReport1()

         ' This is the Crystal Report file created at Design Time

  

        oRpt.SetDataSource(myDS)

         ' Set the SetDataSource property of the Report to the Dataset

 

        CrystalReportViewer1.ReportSource = oRpt

         ' Set the Crystal Report Viewer's property to the oRpt Report object that we created

  

    End Sub

Note : In the above code, you would notice that the object oRpt is an instance of the "Strongly Typed" Report file. If we were to use an "UnTyped" Report then we would have to use a ReportDocument object and manually load the report file into it.

 

Run your Application

11) Build and run your .aspx page and you should be able to see your own report file embedded into the web page!!!

You can opt to export your report file into one of the following formats :

             1. PDF (Portable Document Format)

1.                                 2. DOC (MS Word Document)

2.                                 3. XLS (MS Excel Spreadsheet)

3.                                 4. HTML (Hyper Text Markup Language – 3.2 or 4.0 compliant)

4.                                 5. RTF (Rich Text Format)

To see it in action, you could place a button on your page to trigger the export functionality.

Exporting a Report File Created using the PULL Model :

When exporting a report that was created using the PULL Model, Crystal Report takes care of connecting to the database and fetching the required records, so you would only have to use the below given code in the Click Event of the button.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

 

        Dim myReport As CrystalReport1 = New CrystalReport1()

      'Note : we are creating an instance of the strongly-typed Crystal Report file here.

 

        Dim DiskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = New CrystalDecisions.Shared.DiskFileDestinationOptions()

  

 

        myReport.ExportOptions.ExportDestinationType = CrystalDecisions.[Shared].ExportDestinationType.DiskFile

         ' You also have the option to export the report to other sources

         ' like Microsoft Exchange, MAPI, etc.       

 

        myReport.ExportOptions.ExportFormatType = CrystalDecisions. [Shared].ExportFormatType.PortableDocFormat

        'Here we are exporting the report to a .pdf format.  You can

        ' also choose any of the other formats specified above.

 

        DiskOpts.DiskFileName = "c:\Output.pdf"

        'If you do not specify the exact path here (i.e. including

        ' the drive and Directory),

        'then you would find your output file landing up in the

        'c:\WinNT\System32 directory - atleast in case of a

        ' Windows 2000 System

 

        myReport.ExportOptions.DestinationOptions = DiskOpts

        'The Reports Export Options does not have a filename property

        'that can be directly set. Instead, you will have to use

        'the DiskFileDestinationOptions object and set its DiskFileName

        'property to the file name (including the path) of your  choice.

        'Then you would set the Report Export Options

        'DestinationOptions property to point to the

        'DiskFileDestinationOption object. 

 

        myReport.Export()

        'This statement exports the report based on the previously set properties.

 

End Sub

 

Exporting a Report File Created Using the PUSH Model :

When exporting a report that was created using the Push Model, the first step would be to manually create the connections and populate the Dataset, and set the Report’s ‘SetDataSource’ property to a populated Dataset (as shown in the Push Model example previously). The next step would be to call the above given Export code.

______________________________________________________________________________________________

 

Submitted by :

Ajay Varghese & Shankar N.S.

Sr. Software Engineers,

Jarvis Infotech 

Crystal Resources

Crystal Alliance