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

Tuesday Feb 13th 2007 by Paul Zikopoulos

Paul Zikopoulos discusses how to format default columns from the last installment, add the report to an application, and additionally add a data grid that’s bound to a DB2 data server to show all the data.

In Part A of this series I showed you how to use Crystal Reports for Visual Studio 2005 with data that resides on an IBM DB2 9 data server. That article concluded with a reporting object in a designer (which also needed formatting). In this article, I’ll show you how to format those default columns that aren’t sized properly for the data which they hold, add the report to an application, and an additional add data grid that’s bound to a DB2 data server to show all the data.

Note: To perform the steps in this article, you need to have completed the steps in Part A.

Cleaning up the Default Report

Part A of this series left you with a report that was complete and contained valuable information, yet the default columns sizes used to display the data weren’t quite right. You can use the Main Report Preview view (located at the bottom left of the report designer) to see how your application will render any report you create in Visual Studio. 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 when I switch to the Main Report Preview I’ve got some display issues – the default display controls for the data aren’t sized correctly:


Use the Main Report view to resize some of these columns. For example, I can see based on the previous figure that each row label in my cross tab report needs to be adjusted vertically, while some of the salary values could benefit from a horizontal adjustment. In addition to this, the default size for the default date label is truncated (for example, the year 2006 displays as 20 in the preview).

You can change the size of any field by clicking on that field’s control and resizing it (note the blue binding box):

Note: Different parts of a Crystal Report can reside on different layers which can provide you a rich set of design time options when creating reports. However, this topic is outside the scope of this article. For this article, take note of the location of the Print Date control (shown below) which is used to insert the date the report is generated. The default sizing for this control isn’t correct either. You adjust the size of the Print Date control in Section2 (Page Header) – a different layer:


When you are finished adjusting the display properties of your cross tab report, it should look like this:


Adding Your DB2 9-based Crystal Report to your Application

The Visual Studio IDE should now look like:

Click for larger image

To embed your report within your application, perform the following steps:

1.  Expand the Crystal Reports section in the Toolbox and drag-and-drop CrystalReportViewer from the Toolbox to the designer palette as shown below:

This action places a CrystalReportViewer control within your application which your application uses to render your report.

2.  Click the CrystalReportViewer Tasks ( ) design controller:

3.  Clear the Display Group Tree and Display Status Bar check boxes. After completing this step, the Crystal Reports design controller should look like the far right WinForm:

Note: The designer controller ( ) becomes available only when you select the CrystalReportsViewer control on the design palette.

4.  Click Unlock in parent container in the design controller. Your application form should now look like:

5.  Resize the WinForm and the CrystalReportsViewer control so that they span the size of the design palette, leaving some room for another control that we’ll add later in this article. Your WinForm should now look similar to this:

6.  Bind the STAFFReportDB29 report that you built in Part A by clicking on the design controller, selecting Choose a Crystal Report, then select the STAFFReportDB29.rpt report from the Specify a Crystal Report for the control list. Click OK:

Your application is now really beginning to take shape and should look like this:

7.  Click Data>Add New Data Source, select Database, then click Next.

8.  Select the connection string that corresponds to your SAMPLE database from the Which data connection should your application use to connect to the database? list and select Yes, include sensitive data in the connection string (heh...this is just an article, so this option is OK). Click Next:

Note: If you don’t have a connection to the SAMPLE database, click New Connection to add it. If you don’t know how, refer to the “Addendum: Adding a DB2 Data Source to the Visual Studio 2005 Server Explorer” section in a previous article I’ve written which covers this process in detail.

9.  In the next page of the wizard, select the defaults and click Next.

10.  Expand Tables and select STAFF, then click Finish:

11.  You should now see the STAFF table in the Solution Explorer. Drag the STAFF object and drop it on your WinForm and resize the generated data grid so that it looks like this:

12.  Click the designer controller ( ) and ensure that Choose Data Source is bound to the STAFFBindingSource object that was automatically generated for you when you dropped the STAFF object onto the WinForm:

13.  Press F5 to build your application. It should look like this:

You can see that your report is rendered in the application along with a data grid that shows all the data in the STAFF table. Perhaps a manager wanted to navigate all the employees in an enterprise, despite the fact that the report was for the subset of the employees within the company the she manages. She could also use the controls for navigating the data set that are automatically placed on the application by Visual Studio 2005:

Notice that you also get controls for interacting (like zoom, print, refresh, and so on) with the report as well:


Wrapping it Up...

Now you have an application that generates a cross tab report from data that’s persisted in a DB2 9 data server. This application also presents a list of raw data that you can navigate – all from DB2 9.

You could have created all sorts of different reports or made a more complex application. For example, I’d likely add parameterization to this report such that I could choose to add different departments for comparative purposes and automatically generate a base set of statistics like standard deviation of the company’s average sales and the mean salary across departments with similar job families. Notice that you followed a very natural and native Visual Studio .NET development experience and were agnostic to the data server providing the data...and that’s the whole point.

» 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 and DB2 are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

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, 2006. 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