Using Crystal Reports for Visual Studio 2005 to Build Reports from DB2 9 - Part A

Tuesday Jan 30th 2007 by Paul Zikopoulos

Paul Zikopoulos shows you how you can create a very slick cross tab report using Crystal Reports for Visual Studio 2005 to generate a report from an IBM DB2 9 data server.

In this article, I want to show you how you can create a very slick cross tab report using Crystal Reports for Visual Studio 2005 to generate a report from an IBM DB2 9 data server.

Note: To perform the steps in this article, you need the DB2 9 SAMPLE database and the pureXML feature installed on your DB2 9 data server. You can create the DB2 9 SAMPLE database by entering the DB2SAMPL –XML command on any command prompt. To meet these requirements on a test machine, I recommend that you download the free DB2 Express-C product, which includes the pureXML feature, so you can follow the steps outlined in this article. You can download your own free no-database-size limit copy of DB2 9 (which also includes 64-bit support) at: http://www.ibm.com/db2/express. In addition, you’ll need to IBM add-ins for Visual Studio .NET which are part of this installation (and any DB2 data server installation for that matter).

Building a report from DB2 9 data using Crystal Reports for Visual Studio 2005

The Business Objects Crystal Reports product is one of the world’s best reporting tools. A special edition of Crystal Reports has long been embedded within the Visual Studio product. Crystal Reports for Visual Studio 2005 provides .NET developers with the ability to generate a rich set of reports for deployment. The Visual Studio integrated development environment (IDE) gives you all sorts of controls to include parameterized reports, design controls, and much more.

To create a report based on data from a DB2 9 data server with Crystal Reports for Visual Studio 2005, perform the following steps:

1. In Visual Studio 2005 create a new Visual Basic project by clicking File->New->Project->Windows Application:

2. Name the project by changing the Name field to STAFFReport:

3. Click OK. Visual Studio is now set up with a Visual Basic-based Windows Application project:

4. Right-click on STAFFReport project in the Solution Explorer window, select Add->New Item->Crystal Report, enter STAFFReportDB29 in the Name field and click Add:

5. Since we want to build a cross tab report using the built-in Crystal Reports wizard, select Using the Report Wizard and Cross-Tab then click OK:

6. On the Data page of the Cross-Tap Report Creation Wizard, expand Create New Connection and click OLE DB (ADO). The Available Data Sources list lets you select from a number of different data sources that you can connect to:

7. From the list of OLE DB (ADO) providers, select IBM OLE DB Provider for DB2 and click Next:

8. Type SAMPLE in the Data Source field and the appropriate user ID and password in their respective fields for your data server and click Finish:

Note: By default, any user account that belongs to the Windows Administrators group has system administrative (SYSADM) authority on a DB2 9 data server and can be used to perform the steps in this article; however, this level of authority isn’t required for this article (just makes things easier if you have it).

When you get a successful connection to the database, the OLE DB (ADO) data source on the Data page of the Cross-Tab Report Creation Wizard expands to show the schemas contained in your database. For example:

9. Expand the schema that you used to create the SAMPLE database (by default, it’s the user account that you used to log on to the system when you ran the db2sampl –xml command as a perquisite to this article):

You can see in the previous figure that you can select Tables, Views, and Stored Procedures to provide the source data for your report.

If you were working with SAP, you might be reluctant to click the Tables icon because the underlying database is typically composed of tens of thousands of tables. To work around this issue, you can apply filtering to these objects as follows:

a.  Right-click on the object you want to filter and select Options:

b. Set the filter according to your filtering requirements and press OK. For example, the following image shows a filter that filters the Tables folder such that only tables that begin with S are shown in the Server Explorer tree (note the % wildcard character):

Note: You might need to refresh the view by clicking F5 for the new filter to be applied to the Server Explorer.

You can see the effects of this filter when the Tables folder is expanded:

10.  Select the STAFF table, move it to the Selected Tables box by clicking > followed by Next (You can also just drag-and-drop the table to this box by holding down the left mouse button):

11.  Drag-and-drop the columns so that your Cross-Tab designer looks like the following image and click Next:

You can use the Browse Data and Find Field buttons to help you select the appropriate columns for your cross tab report.

It’s outside the scope of this article to go into the details and benefits of a cross tab report; needless to say, it’s a very powerful report type and one of the most popular used by analysts.

This particular cross tab report will generate a report that looks at the type of job in a company (the rows) and categorizes their salaries (the summary) by department (the column).

12.  Select Pie Chart and click Next:

You are free to change the other options on this page, but for this article, the defaults are fine.

13.  On the Record Selection page, you can filter the data that will be included in the report. For this article, ensure this window looks like this:

The previous settings will include all the employees in the STAFF table who work in departments IDs that are less than or equal to 20.

14.  Select a style template to apply to your report. I chose Silver Sage 2, but choose whatever style you want and click Finish:

You report is now ready for use in your application and the Visual Studio IDE should look similar to the following image:


It’s outside of the scope of this article to teach you how to use Crystal Reports; however, you can use the Field Explorer to add new fields to the report, as well as other handy items like page numbers, dates (located in the Special Fields tree) and so on.

Wrapping it Up...

The Main Report Preview view (located at the bottom left of the report designer – see the previous figure) can be used to see how your application will render the report.

You can switch between this view and the designer view (called Main Report – the default view) to resize and format (among other things) your report.

You can see that my cross tab report tells me some valuable information about the salary distribution for departments 10, 15, and 20. Using the associated pie chart, I can quickly see that department 10 is responsible for 40.2% of the salary across these departments. As I look for more details, I can refer to the cross tab report for specific department summaries, the break down for each department’s aggregate salary by job, and more.

Now you’ve got a report just sitting there in Visual Studio. Are you finished? Not yet! In Part B of this article, I’ll show you how to format the report so it looks a little ‘cleaner’, hook the report up to an application, and add a data grid that displays all of the information for even more analysis.

» See All Articles by Columnist Paul C. Zikopoulos

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 and has written over sixty magazine articles and several books about it. Paul has co-authored the books: Information on Demand: Introduction to DB2 9 New Features, IBM DB2 9: New Features, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: mailto:paulz_ibm@msn.com.


IBM, DB2, and pureXML are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Windows is a trademark of Microsoft Corporation in the United States, other countries, or both.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2007. All rights reserved.


The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

Mobile Site | Full Site