DB2 9 and Microsoft Access 2007 - Part 3: DB2 Reports - Page 2
August 4, 2008
Getting more control over your reports the Report Designer
If you want more control over your reports layout, you can use the Report Designer to create a report from scratch or work from a report you created using the wizard. Its outside of the scope of this article to delve into all of the details of Access 2007; rather Im trying to focus on showing you how your DB2 data fits naturally into an Access development environment. With that said, this section will give you a general overview of building a DB2 report from scratch.
To design a report using the report designer, select the Create tab and click Report Design in the Reports section of this ribbon:
The Report Designer opens:
Simply drag columns (or entire tables) from the Field List window to the designer palette. Notice that the Report Designer has Page Header, Detail, and Page Footer sections where you can place data and other control objects for a more customized approach to report building.
For example, drag the DEPTNUMB and LOCATION columns from the Organization artifact (which is a link to the ORG table in the SAMPLE database) and drop it in the Detail section such that the Report Designer looks like this:
You will see that the Field List changes to show the data artifact youve added to your report. Other data artifacts are moved to the Fields available in other tables box; from here you can add other tables or columns to the report.
Now add the NAME, JOB, and YEARS columns from the Staff data artifact to the fields below the ones you just added. When you add the NAME column from the Staff data artifact to your report, the Specify Relationship window opens:
You use this window to specify relationships between or among various fields on your report. For example, the STAFF table contains several employees in each department; it can relate to the ORG table using the DEPT column in a many-to-one relationship because the ORG table is used to define the different departments in the fictitious company that the SAMPLE database represents.
To define this relationship, ensure that the Specify Relationship window looks like the one that follows and click OK:
You can see that the column you added to the report is now added to the Report Designer (even though the relationship you defined isnt represented on the report).
Now you can add any additional columns. (You wont see the Specify Relationships window again unless you add a column from a new table.) When you have finished, the Report Designer palette should look like this:
Note: You can align and move the field name and field value boxes using the control node () for each field ().
As you can see, the Design tab in the Report Design Tools ribbon gives you lots of widgets to place on your report, as well as programmatic actions (such as a button that triggers an action), and more.
When you have finished designing your report, save it, and then run it by double-clicking it in the All Tables view. Your report will be different, but I customized my report such that it takes two parameters as input and returns the information I dragged onto the Report Designer:
I encourage you to experiment with all the other controls and widgets available for report design; the best way to learn them is to use them!
Other ways to get to your DB2 data
Access 2007 has a number of other options that let you quickly report on your DB2 data. For example, the Report option (shown below) enables you to create a basic report and design instantly.
This is the best option if you just need to dump your DB2 data into a simple report. For example, if you select a table, and then select the Create tab and the Report option, Access 2007 creates a report on your DB2 data in an instant:
The Report Designer opens with a pre-built report:
You can see that Access 2007 has applied a number of controls and formatting to your report. At this point, you can leverage the context ribbon to change the formatting for your report in seconds:
For example, in seconds I made the report shown above look like this:
You can see that I added some traffic light conditional formatting such that those employees whose salaries are below the suggested range for their years of service show up in red.
Wrapping it up
In this article, I showed you how to create reports in Microsoft Access 2007 from IBM DB2 data. I showed you how your reports can have fields from different tables within them, as well as briefly showcasing the different report generation options available in Access 2007. In my next article, I will show you how to create Access 2007 forms that allow you to see and work with your DB2 data.
» See All Articles by Columnist Paul C. Zikopoulos
IBM, DB2, Informix and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft 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, or service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2008.
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.