Databases Reference
In-Depth Information
C H A P T E R 13
Advanced Interactive Reporting
by Sharon Kennedy
Data. It is all about the data. There are usually only a few ways to get data into a system (multi-page
wizard, single-page edit, or tabular form) but there are endless ways that your users will want to get their
data out. It used to be that we would need to code a report for every way that our users would want to
slice-and-dice the data, maintaining them forever, adding in “just one more column . . .” With the
introduction of Interactive Reports, developers can put together reports that contain every data point
relating to a topic, and then end users can customize them to their needs, mitigating the need for
developer intervention. That said, there are still good reports and not so good reports. The key is not just
including all the data points, but including them in ways that are useful and that meet your user's needs.
Creating a Report
Your basic interactive report starts with a simple SQL statement such as that in Listing 13-1.
Listing 13-1.
Basic Query
select PRODUCT ID,
PRODUCT NAME,
PRODUCT DESC,
CATEGORY ID,
AVAILABLE YN,
PRICE,
CREATED BY,
CREATED ON,
UPDATED BY,
UPDATED ON
from AB PRODUCTS
Using SQL, you can decode your foreign key values, keeping in the IDs to allow for drill downs
(discussed later). Listing 13-2 shows a nested subquery being used to retrieve foreign key values.
Listing 13-2.
Including Category Name
select PRODUCT ID,
PRODUCT NAME,
PRODUCT DESC,
CATEGORY ID,
(select CATEGORY NAME
