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.
Trademarks
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.
Disclaimer
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.
» 
IE 7
Firefox 2.0
Comment and Contribute
