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

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.

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, 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 Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Latest Articles