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.
