Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 30, 2007

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

By Paul Zikopoulos

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.

DB2 Archives