DB2 9 and Microsoft Access 2007 - Part 3: DB2 Reports

Monday Aug 4th 2008 by Paul Zikopoulos

Earlier installments of this series discussed how to use Microsoft Access 2007 as a graphical front end to a back-end IBM DB2 data server and how to leverage Access 2007 and DB2 as an integration layer. This installment examines how to build reports that run against DB2 data servers.

In the first part of this series, I showed you how to use Microsoft Access 2007 as a graphical front end to a back-end IBM DB2 data server. Specifically, I showed you how to implement linked tables and create an abstraction layer over those linked tables such that information workers can work with business artifacts directly without worrying about different naming conventions; all the while, the data resides in a DB2 data server and there is virtually no hit to productivity. In Part 2, I took the concept of linked tables a step further and showed you how to leverage Access 2007 and DB2 as an integration layer; in addition, I demonstrated some of the business rules that Access 2007 maintains when presenting DB2 data to information workers. In this article, I’m going to show you how to build reports that run against DB2 data servers, further demonstrating how transparent a back-end DB2 data server is to an Access 2007 developer.

Before you start

Make sure that you have linked the DatabaseJournalAccess2DB2 Access 2007 database that we created in Part 1 to the ORG, EMPLOYEE, STAFF, DEPT, and INVENTORY tables in the DB2 SAMPLE database. In addition, I assume you created an abstraction layer over those tables such that the All Tables view looks like this:

If you need help getting to this point, check out “DB2 9 and Microsoft Access 2007 Part 1: Getting the Data…”. In this article, we’ll be working with the STAFF table, so ensure that you can open this table and view its data. You can do this by double-clicking the Staff object that you created and worked with in the previous parts in this series:

Creating a DB2-based report using the Report wizard

To create a report on the STAFF table in your DB2 database, perform the following steps:

1.  Select the Staff table in the All Tables view.

2.  Select the Create tab and click Report Wizard in the Reports section of this ribbon. (Notice that the ribbon dynamically changes and presents you with a list of objects you can create when each tab is selected):

The Report wizard opens:

I had you select the STAFF table before invoking the Report wizard because Access 2007 smartly launches the Report wizard within the context of the selected table (see the Tables/Queries drop-down list).

3.  Select the columns that you want to include in your report, and click Next. For this simple example, select all the columns for this report such that the Report wizard looks like this:

You can move a column from the Available Fields box into the Selected Fields box by selecting it and clicking the Move Column () button; to move all of the available columns in the selected table, select the Move All Columns button ().

4.  Select a method by which to group your report by using the same control to move any grouping columns. For this example, select DEPT as the grouping column, as shown below:

Once you specify a grouping column, you can work with a set of options that govern the column by clicking Grouping Options. As you can see, you have control over options that relate to the grouping column you selected:

For this example, leave the default as Normal, but after you have finished reading this article you can experiment with this option and see how it shapes your report.

5.  Select any columns by which you want to sort your report, and specify the nature of the sorting as well as any summary you want your report to generate. For this example, select Salary from the drop-down list and select Descending order.

You can change the sort order for a selected column simply by clicking its sort order button. For example, to change the sort order for the SALARY column from ascending to descending, click the Ascending button, which changes its label to Descending. To change it back to an ascending sort, click the button again and its label changes back to Ascending. In other words, the button label always shows the sort order selected for that column.

You can add multiple sorting levels. For example, if you wanted to group your report by department (as in Step 4), and sort starting with those employees with the largest salaries who have worked for your company for the shortest time, your version of the Report wizard would look like this:

6.  On this same page, you can use Access 2007’s powerful capabilities to summarize numeric columns in your table. For this example, click Summary Options, select the same options as shown below, and click OK:

7.  Click Next.

8.  Select a Block for the layout, Portrait for the orientation, ensure that the Adjust the field width so all fields fit on a page check box is selected, and then click Next.

You can use the layout options to change the look of your report. The following figure shows the various options available and their effects on your final report:

The Orientation radio buttons allow you to choose between Landscape and Portrait printing options.

The Adjust the field width so all fields fit on a page check box adjusts the fields such that your report doesn’t spill; this option is more important for a portrait orientation since there isn’t as much left-to-right print space as in the landscape orientation.

9.  Select the Trek report style and click Next.

Of course, you can select any style you want for your report. In addition, you can create your own styles and attach them as templates – for example, you may want your company’s logo on a report.

10.   Name this report SalaryYearsOfServiceSTAFFReport, ensure that the Preview the report radio button is selected, and click Finish.

Selecting the Preview the report option allows you to see the report after clicking Finish. The Modify the report’s design option opens the report you just created in the designer mode, where you can customize your report further with more powerful report layout controls and objects.

Access 2007 now displays your report:

Of course, you can create a report from any linked table you created in Part 1: for example, a table that is linked to an IBM Informix or DB2 for z/OS data server.

Getting more control over your reports – the Report Designer

If you want more control over your report’s layout, you can use the Report Designer to create a report from scratch or work from a report you created using the wizard. It’s outside of the scope of this article to delve into all of the details of Access 2007; rather I’m 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:

Click for larger image

The Report Designer opens:

Click for larger image

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 you’ve 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 isn’t represented on the report).

Now you can add any additional columns. (You won’t 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 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