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.