Using Crystal Reports for Visual Studio 2005 to Build Reports from DB2 9 - Part A
January 30, 2007
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, youll 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 worlds 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 isnt 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, its 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.