10 years ago, I wrote 2 articles about Crystal Reports for .Net (Feeding Crystal Reports from your application and Crystal Reports – Part II). At that time, it was for the Universal Thread magazine which has stopped publishing since then but I never stopped.
Without any doubt, these 2 articles have been in the top 5 of my most visited and they are still today attracting readers even after 10 long years. Since I continue to receive questions about the use of CR in .Net, I decided to revisit that topic.
Do I still use it?
Yes, I still use it in some applications that have been existing for years and that I still maintain. Of course there are some alternatives but I have some complex reports in production and there is no need to start a (manual) conversion of all the reports to a newer reporting platform. Nothing that would justify (per user perspective) a rewriting using another report platform.
Who now owns it?
For about the last 5 years, SAP SE owns Crystal Reports. All kind of speculations were said in 2011 when SAP acquired Business Objects but the product is still available, and still for free for many projects so we cannot complain too much.
If you are interested in the Crystal Reports history, have a look at this Wikipedia article.
Where to download CR from?
The developer version of Crystal Reports is available from the SAP web site. They are releasing a quarterly package including various patches.
From that link, you will find many links to download the reporting engine.
The developer should always download from the first column (Install Executable) to install on its on development computer.
The package to distribute to user is in the columns is contained in one of the MSI/ClickOnce/Web depending on the mechanism and the platform to which you deploy your application to.
This month downloadable solution
This month solution contains both VB and C# projects. The solution was created using Visual Studio 2015 but code is surely reusable in much older version of Visual Studio (current version of Crystal Reports supports Visual Studio 2010 and more recent).
You should also notice that even if the product is free, you cannot use it with Visual Studio Express but it will work with the Community edition (which is also free for a great number of people).
Figure 1: The demo application in action
My way of using Crystal Reports
As I was suggesting in my original articles, I have always been a big fan of pushing a fully loaded dataset to my reports. This allows me to do something like:
Creating the demo dataset
For the purpose of this article, I create an in-memory dataset from scratch so you don’t have to deal with a database creation and the connections.
But the way your dataset is filed is not important. For the report, it is just a dataset and the origins of it are really not important.
So my demo application has a button titled “Create data source” that calls a method named CreateDataSet. This button creates a dataset that contains 2 datatables (one for countries and one for states), loads data into both datatables and creates a relation between these two tables.
Whenever you run the demo application, you need to click this button first so data gets loaded.
Creating the dataset schema
When we build a report using Crystal Reports, we need to build against a structure. Since we are not connecting to a database to get the data, we need to provide the structure of our dataset. This structure needs to be in the form of a XML Schema Definition (XSD) file.
Luckily, it is really easy to create such a schema. There is a method called WriteXmlSchema on the dataset that generates it for you. You will find the following code behind the “Create schema” button:
Dim strFullPath As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Temp, "rptTest.xsd") mdsData.WriteXmlSchema(strFullPath)
string strFullPath = System.IO.Path.Combine(System.IO.Path.GetTempPath(), "rptTest.xsd"); _dsData.WriteXmlSchema(strFullPath);
That snippet of code will generate your XSD file into your temp folder. I try to keep the filename of the schema as close as possible to the name of the report so they can easily be tied together.
Notice that the creation of the schema is only required for the developer. You don’t need to distribute this code and the schema file to your users.
If your dataset has a dataset name (like mine is called TestDataSet – value provided to the constructor of the Dataset), the id and name properties of the generated schema will have this name. This name will be visible later in the wizard and it has to be unique amongst the schema you are creating.
If you are not providing any name to your dataset, the generated schema will contain NewDataSet as the value of id and name. It is strongly encouraged to modify this value to something more meaningful.
Here is an example of the first few rows of the generated schema in which you would need to change NewDataSet for both the id and the name properties *using your favorite text editor):
<?xml version="1.0" standalone="yes"?> <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="Countries">
Keep that file handy, we will get back to it later.
Creating a project dedicated for your reports
I still like to deploy my reports as standalone files. This let me modify many of the reports feature without having to recompile and redistribute the full application.
So in my solution I always create a regular Class Library project that I call Reports.
And because I deploy the .rpt files, you don’t even have to build the project containing your reports. This project is simply use to edit the reports in the editor.
Figure 2: Preventing the project from being compiled
Creating the report
Now that we a project to add our reports to and we also have schema file, we are ready to create the report.
The first thing to do is to add your schema to the report project. I usually just do a drag and drop from the temp folder of my Windows Explorer to the project in Visual Studio.
Once I dragged the schema file, I always open it just to be sure it is representing what I have in mind for my dataset (see figure 3).
Figure 3: The schema in the designer
We are now ready to create the report. To get the Crystal Reports designer, you need to add a report to a project. This is by using the “Add New Item…” from the “Project” menu. The “Add New Item” dialog will then be shown to you (see figure 4). If you look under “Common Items”, you should see a section named “Reporting”. From that section, select “Crystal Reports”, change the Name to something meaningful (I have set it to rptTest.rpt) and click the “Add” button.
Figure 4: Adding the report to the project
As soon as you hit the Add button, a dialog (see figure 5) will pop up offering you to start a wizard to help you quickly build the basis of your report.
Figure 5: The Crystal Reports Gallery
Make sure that “Using the Report Wizard” and “Standard” are selected and click OK.
Then the “Standard Report Creation Wizard” dialog will be shown. This is where you can bind your report to your data source or to your schema. In an earlier step, we have prepared a schema file. This schema has been added to the same project that contains the report. This schema is now available under “Project Data – ADO.NET DataSets” (see figure 6). Using the arrows in the middle of the dialog, add both tables (countries and states) to the list of selected tables.
Figure 6: Selecting your data source
After having clicked the Next button, the wizard will get you to the Link step (see figure 7). In this demo, because relations are already correctly created in the dataset, you simply need to click the Next button.
Figure 7: Setting links between the tables
In the Fields step of the wizard (see figure 8), simply add all fields to the list of “Fields to Display” and click the Next button.
Figure 8: Selecting fields to display
In the Grouping step (see figure 9), select “Countries.Description” as the field to group by and click the Next button.
Figure 9: Selecting the grouping fields
The Summaries step (see figure 10), you can select the fields on which you want summaries. Since it does not really make sense to sum IDs, remove the ID fields from the right part of the screen (to keep only the population) and click the Finish button.
Figure 10: Selecting the summary fields
The Crystal Reports designer will finally display your report (see figure 11).
Figure 11: The report designer
Testing your report
Before starting to customize the report, I always like to test it.
You need to add a viewer to the main application. From your toolbox, find the Reporting tab (or simply use the filter feature of the toolbox), select the CrystalReportViewer (see figure 12) and drag one on your form.
Figure 12: Adding the viewer to your form
A couple of lines have to be written to load the report, to feed the report with data and finally to show the report. It may sound a lot of work but it is not. We first need to declare a ReportDocument variable at the form level:
Private mrptDoc As New ReportDocument
private ReportDocument _rptDoc = new ReportDocument();
This line requires to import a namespace in order to compile. Add this row at the top of your file;
Imports CrystalDecisions.CrystalReports.Engine
using CrystalDecisions.CrystalReports.Engine;
Finally, you can add this code to the click event handler of the view report button.
'Load the standalone report mrptDoc.Load(IO.Path.Combine(Application.StartupPath, "rptTest.rpt")) 'Pass the dataset to the report mrptDoc.SetDataSource(mdsData) 'Pass the Report Document object to the viewer CrystalReportViewer1.ReportSource = mrptDoc
//Load the standalone report _rptDoc.Load(System.IO.Path.Combine(Application.StartupPath, "rptTest.rpt")); //Pass the dataset to the report _rptDoc.SetDataSource(_dsData); //Pass the Report Document object to the viewer crystalReportViewer1.ReportSource = _rptDoc;
But wait before running the sample application! If you carefully look at the call to the Load method of the ReportDocument object, you will see that the application is expecting to find a file named rptTest.rpt in the startup folder (usually bin\debug at this time). You can set this path to be anywhere but the point is that you need to copy your standalone report files to that folder! Otherwise, a load report exception will be raised. You also need to ensure that the dataset is filled otherwise you will get a login prompt on the screen.
You are now ready to test your report. Hit F5 to run it, click the Create data source button and then the View report button. At this point, I expect 2 results. The first one would the report showing. But you might also get an error as shown in figure 13. The error is complaining for FileNotFoundException on a Crystal Report file.
Figure 13: A strange file not found exception!
If you get this error, there is an obscure setting you need to add to your App.config file that will solve the issue. Just add this section somewhere inside the configuration section:
<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" /> </startup>
Now if you rerun your application, you should see your report coming to viewer with the data created manually.
Now that the report is shown correctly, you can now customize the report to be more good looking.
One really nice thing about using standalone reports is that you can edit your reports using the designer of Visual Studio without having to stop your application, save it, recopy it to the right folder and finally get back to your application to press the preview button. That’s make the process of testing changes to reports a lot faster than having to recompile and redeploy the application each time!
Passing a value to a report object
It is really easy to fill an object like the ITextObject with a value from your application. I often do that for values such as the report title. I am often able to reuse the same report layout as the basis for many reports. In that case, I need a way to provide a distinct title to each reports. The trick here is to use the ReportsObject collection to access one element of the report and give a value to it.
Before looking at the code that does that, add a ITextObject to your report (by right-clicking the report, selecting Insert, then Text Object and clicking on your report to position it. Like any other object, and because you want to access through code, you should give this object a significant name by setting the (Name) property (something like txtTitle).
Here is a helper method that can be used to set the Text property of any ITextObject of a loaded report:
Private Sub ApplyTextObject(ByVal pTextObject As String, ByVal pTextValue As String) Try CType(mrptDoc.ReportDefinition.ReportObjects(pTextObject), TextObject).Text = pTextValue Catch ex As Exception MessageBox.Show("ApplyTextObject" + Environment.NewLine + "There is no >>" + pTextObject + "<< object in this report!") End Try End Sub
private void ApplyTextObject(string pTextObject, string pTextValue) { try { ((TextObject) _rptDoc.ReportDefinition.ReportObjects[pTextObject]).Text = pTextValue; } catch (Exception) { MessageBox.Show("ApplyTextObject" + Environment.NewLine + "There is no >>" + pTextObject + "<< object in this report!"); } }
This method can now be called like this before setting the ReportSource of your viewer:
ApplyTextObject("txtTitle", "Title of the report provided dynamically by the application")
ApplyTextObject("txtTitle", "Title of the report provided dynamically by the application");
The first parameter is the name of the object found in your report. The second parameter is the value to give to this object. This method is currently setting the Text property but just about any property could be set.
The downloadable demo even contains a similar method that can be used to set a ITextObject from a sub-report (the method is named ApplySubReportTextObject). When you call this method, you need to specify the name of the sub-report in addition to the name of the object and the value.
Passing a value to a parameter of the report
You might also want to pass values to a report that comes from the user interface or a database (or any other source) and use that value to filter or format your report.
For example, we will add a threshold parameter to the report and use it to set the BackColor property of the population field.
We first need to add a parameter to our report. To do this, insure that your report is currently visible in the designer and that you can see the “Field Explorer” of Crystal Reports (if you don’t see it, select the Crystal Reports menu and pick Field Explorer). In the Field Explorer, right-click “Parameter Fields” and select New. A dialog titled “Create Parameter Field” will be displayed (see figure 14). In this dialog, set the Name field to something meaningful (like PopulationThreshold) and select the correct value type (a number in this case). When you are done, click the OK button.
Figure 14: Creating a parameter field
We will now use this parameter to change the BackColor of the Population field.
Start by right-clicking the Population field on your report and selecting “Format object” from the context-menu. From the Format Editor, open to the Border tab. To the right of “Background” there is a button (on which you can see X+2) that allows you to enter a formula, click on it. In the bottom pane of the Formula Workshop dialog (see figure 15), enter this formula and click the “Save and close” button:
if {States.Population} > {?PopulationThreshold} then crRed else crWhite
This formula sets the background color to red if the value of the Population field is higher than the parameter value otherwise, the background is set to white.
You also need to ensure that the syntax combo of the Formula Editor is set to “Crystal Syntax”.
Figure 15: Entering a formula
The last thing you need to do is to pass a value to the parameter. A single line of code is required to do that. This line must be called before setting the ReportSource of your viewer like this:
mrptDoc.SetParameterValue("PopulationThreshold", 5000000)
_rptDoc.SetParameterValue("PopulationThreshold", 5000000);
The first argument is the name of the parameter found in your report. The second argument is the value to give to this parameter.
If you run your application again (don’t forget to copy your report), you should be able to see that some states have their background color red.
Exporting a report
This is a cool feature that I use a lot.
I have applications that run on schedule early in the morning while people are still in bed. These applications are producing reports but needs to export them instead of viewing them. I export them as PDF documents (and email them) so people can watch them on their phone or tablet on their way to the office.
Sure users can select the “Export report” button (the first one from the left of the toolbar of the Crystal Reports viewer) but you may want to do it programmatically. It is almost identical as previewing a report. Only the last step varies.
There are a couple different methods that export a report but the one you will surely rely the most on is the ExportToDisk method. As shown in figure 16, many formats are supported. This method only has 2 arguments: the format and the filename.
Figure 16: Some of the available export formats
Notice that the report does not have to be shown or previewed in order to export it using the ExportToDisk method. The reason is that the method is a member of a ReportDocument object (and not of the CrystalReportViewer object).
The complete code to export is, again, the same as when you want to preview a report. The only thing that is modified is the last line where we actually export the report (instead of passing it to the viewer):
'Load the standalone report mrptDoc.Load(IO.Path.Combine(Application.StartupPath, "rptTest.rpt")) 'Pass the dataset to the report mrptDoc.SetDataSource(mdsData) 'Set values of some objects dynamically ApplyTextObject("txtTitle", "Title of the report provided dynamically by the application") 'Set the values of the parameters mrptDoc.SetParameterValue("PopulationThreshold", 5000000) 'Export the Report Document object to a PDF file Dim strOutputPath As String = IO.Path.Combine(Application.StartupPath, "rptTest.PDF") mrptDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, strOutputPath)
//Load the standalone report _rptDoc.Load(System.IO.Path.Combine(Application.StartupPath, "rptTest.rpt")); //Pass the dataset to the report _rptDoc.SetDataSource(_dsData); //Set values of some objects dynamically ApplyTextObject("txtTitle", "Title of the report provided dynamically by the application"); //Set the values of the parameters _rptDoc.SetParameterValue("PopulationThreshold", 5000000); //Export the Report Document object to a PDF file string strOutputPath = System.IO.Path.Combine(Application.StartupPath, "rptTest.PDF"); _rptDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, strOutputPath);
By experience, I can tell you that exporting as PDF documents works very well. Other formats will greatly vary depending on the formatting of your report.
Feeding your report with a custom chart
Another question often asked in newsgroup is how to send images or charts you have on the screen to the report? I personally use this feature because I use a charting component to build a chart which I prefer over the charting features of Crystal Reports (and that users can customize and send the customization to the report).
The trick is to create an array of bytes from your image and add it to the dataset you are sending to Crystal Reports. Easier said than done!
The first thing you need to do is to add field to the Countries table (I named it Chart) of your schema (DemoSchema.xsd) of the Reports project and set the DataType property to System.Byte(). Be very careful here as you will find only the data type without the parenthesis. You will need to add them manually (to indicate it is an array instead of just a single byte).
One way of adding the field is to open the schema file (rptTest.xsd) in the designer, right-click the table, select Add and Column (see figure 17).
Figure 17: Adding a new field to the schema
Notice that since I planned this chart right at the beginning of my article without warning your, I have already added that field to the programmatically created dataset.
In order to be able to see your new field on the report, you will need to update your reports references. This is done by using Crystal Reports -> Database -> Verify Database option. After you did that, your new field (Chart is available for you to place it on your report (since I placed it in the Countries table, I have added the Chart field to group header).
We now need to provide a value to this new field in our dataset before passing the dataset to the ReportDocument object. For the sake of simplicity, I have added 2 PictureBox to my form (one containing a US flag and the other one containing a US flag because I only have these 2 countries in my demo table).
We need to transform the bitmaps that are in the PictureBox controls (or the chart you have, or any other image source) to an array of bytes. Depending on the object you are using, the method varies a bit. This is the code required for a bitmap in a PictureBox:
'Save the canadian flag into a Memory Stream object and from there to an array of byte ms = New IO.MemoryStream picCdn.Image.Save(ms, picCdn.Image.RawFormat) arrBytes = ms.GetBuffer ms.Close() dt.LoadDataRow(New Object() {1, "Canada", arrBytes}, True)
//Save the canadian flag into a Memory Stream object and from there to an array of byte ms = new System.IO.MemoryStream(); picCdn.Image.Save(ms, picCdn.Image.RawFormat); arrBytes = ms.GetBuffer(); ms.Close(); dt.LoadDataRow(new object[] {1, "Canada", arrBytes}, true);
See the CreateDataSet method for the complete implementation of this.
Now you can test your application (and don’t forget to copy your modified rptTest.prt file to the correct folder).
Crystal Reports generates a bunch of temp files
This is one really bad side of Crystal Reports. A lot of temp files are created when reports are generated and those are just never deleted. These files are created in your Temp folder (set by your environment variable).
I found a user having about 2 gigs worth of these temp files. They are easily recognizable and can be easily deleted.
All you have to do is to call the following method when your application exits:
Public Shared Sub DeleteCrystalTempFiles() Dim strPath As String = IO.Path.GetTempPath For Each f As String In IO.Directory.GetFiles(strPath, "*}.rpt") Try IO.File.Delete(f) Trace.WriteLine("Crystal Report temp file " + f + " deleted.") Catch ex As Exception Trace.WriteLine("Failed to delete Crystal Report temp file " + f + " (" + ex.ToString + ".") End Try Next For Each f As String In IO.Directory.GetFiles(strPath, "~cpe{*}.tmp") Try IO.File.Delete(f) Trace.WriteLine("Crystal Report temp file " + f + " deleted.") Catch ex As Exception Trace.WriteLine("Failed to delete Crystal Report temp file " + f + " (" + ex.ToString + ".") End Try Next End Sub
void DeleteCrystalTempFiles() { string strPath = System.IO.Path.GetTempPath(); foreach (string f in System.IO.Directory.GetFiles(strPath, "*}.rpt")) { try { System.IO.File.Delete(f); Trace.WriteLine("Crystal Report temp file " + f + " deleted."); } catch (Exception ex) { Trace.WriteLine("Failed to delete Crystal Report temp file " + f + " (" + ex + "."); } } foreach (string f in System.IO.Directory.GetFiles(strPath, "~cpe{*}.tmp")) { try { System.IO.File.Delete(f); Trace.WriteLine("Crystal Report temp file " + f + " deleted."); } catch (Exception ex) { Trace.WriteLine("Failed to delete Crystal Report temp file " + f + " (" + ex + "."); } } }
Free Walkthroughs
Of course I am not pretending that the current article provides you all the tricks you will ever need to know. Crystal Reports is a big engine reports that has many more feature than those introduced here.
Back in 2005, Business Objects (the company behind Crystal Reports at that time) created a 591 pages walkthroughs that can still be downloaded for free from here. I strongly suggest you grab this document as it contains a lot of information still valid today.
Conclusion
I really think that this revisited article gives you a brief overview about how to get started creating your first report with custom data along with simple methods of setting objects properties and parameters.