Creating APEX report with rolling set of columns #JoelKallmanDay

The last several years I’ve participated in the Joel Kallman Day tributes with articles about SQL and PL/SQL. While there are no requirements for article topics, this year I wanted to contribute something within the framework Joel was best known for… APEX.

Many reports aggregate data over time periods and will usually return different dates and times as distinct rows. In some reports though, it is more useful to show a rolling window of time. For instance, comparing yesterday and today, or current month and prior month.

Using PIVOT or grouping on CASE statements allows you to turn rows into columns but if you want your report columns to automatically change name as needed it gets a little trickier because the SQL column names will be static even if the data within them is dynamic.

In this sample application I generate a random set of fictional orders spanning a year’s worth of data. My report though shows only the most recent data: current month, prior month, and the month before that. As of today, when I publish this article, the data will show the months of August, September, and October (the current month.) Next month though or other dates in the future, the Count and Amount columns will update and change names based on the current set of 3 months as of the time the report is run.

The report itself uses a PIVOT query on the most recent 3 months of data.

SELECT *
  FROM (SELECT region, MONTHS_BETWEEN(TRUNC(SYSDATE, 'mm'), TRUNC(order_date, 'mm')) order_month, amount
          FROM random_orders
         WHERE order_date >= ADD_MONTHS(TRUNC(SYSDATE), -2))
           PIVOT (COUNT(*) AS cnt, SUM(amount) AS amount
                 FOR order_month
                 IN (2 month_minus_2, 1 month_minus_1, 0 curr_month ));

I use 3 hidden page items to generate the month names with TO_CHAR over a sysdate calculation: P1_CURRENT_MONTH, P1_MONTH_MINUS_1, and P1_MONTH_MINUS_2.

TO_CHAR(sysdate,'Mon')
TO_CHAR(add_months(sysdate,-1),'Mon')
TO_CHAR(add_months(sysdate,-2),'Mon')

Then, in the report columns, substitution variables in the Heading of each column. For example, the headings for Count and Amount columns for 2 months ago use these:

&P1_MONTH_MINUS_2. Count
&P1_MONTH_MINUS_2. Amount

This method can be extended to display more months, or to use weeks, days, years, or smaller units like hours or minutes. The pattern is always the same. Create one hidden item for each distinct value and then use those items as substitutions within each corresponding column heading.

Scripts to create the sample application above can be found here.

I’ve used this technique for a variety of before/after comparisons and trending reports. I hope you find it useful as well. Questions and comments, as always, are welcome.