Paul Zikopoulos discusses how to format default columns from the last installment, add the report to an application, and additionally add a data grid thats 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,
Ill show you how to format those default columns that arent sized properly
for the data which they hold, add the report to an application, and an additional
add data grid thats 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 werent 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 Ive
got some display issues the default display controls for the data arent
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
fields 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 isnt correct either. You adjust the size
of the Print Date control in Section2 (Page Header) a different
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:
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 well 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 dont have a connection to the SAMPLE database, click New Connection to add it. If you dont know how, refer to the Addendum: Adding a DB2 Data Source to the Visual Studio 2005 Server Explorer section in a previous article Ive 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 thats 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, Id 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 companys 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 thats 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:email@example.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 authors 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 authors knowledge at the time of writing.