Matrix report

partlycloudy

APEX 4.2.2

Say I have a resultset with 3 columns: customer name, date and number of orders. What is the quickest and most flexible way to put this data on an APEX report such that the customers go down the Y-axis and Date goes across and each cell in the report is the number of orders. Sort of like a sparse matrix, or pivot report. Also, the aggregation level of the date should be modifable at run-time i.e. each column represents a week or a month or day with the column heading changing accordingly.

e.g. for a monthly summary,

with metrics as (
SELECT
trunc(dt,'mm') dt,
nm,
count(*)
FROM data
group by nm,trunc(dt,'mm')
)
select
nm,
max(decode(....)) c1,
max(decode(...)) c2,
...
from metrics

but I am not sure how to go about doing the max(decode(...)) to show a rolling N months of data. Similarly for weekly and daily view.

Help? Thanks

fac586

Hi Vikas

Your post inspired me to try out something I had been thinking about for a while. Unfortunately after a promising start I've had difficulty finding time to sort out the loose ends and actually post a response. This is probably therefore too late to be useful. but it looks like a useful technique that (oddly) doesn't seem to have been covered here before.

This approach uses a standard report region based on a SQL query, Oracle's PIVOT clause, a simple XSL stylesheet, and—of course—a custom named column report template (this is me after all).

The query needs 3 input parameters: a start date (p6_start_date), the day/week/month aggregation level (p6_agg_level), and the number of these periods to display in the matrix (p6_periods). The first step is to use these values in a row generator to produce the date dimension for the report. This is used as a subquery in the IN clause of the PIVOT XML query that counts the number of orders by customer and period. As the number of columns in the result set is unknown until runtime, the matrix columns are returned in a single XML fragment for each customer. An XSL stylesheet is used to transform the standard pivot XML structure into an XHTML representation consisting of table cell td elements. Finally the XHTML fragment is serialized from XMLType to a CLOB that can be displayed in APEX reports.

with dates as
      sele
          case :p6_agg_level
            when 'dd' then to_date(:p6_start_date, 'DD.MM.YYYY') + (level - 1)
            when 'ww' then to_date(:p6_start_date, 'DD.MM.YYYY') + 7 * (level - 1)
            else add_months(to_date(:p6_start_date, 'DD.MM.YYYY'), level - 1)
          end dt
      from
          dual
      connect by level <= :p6_periods)
  , order_pivot as (
      select
          customer
        , order_dt_xml
      from
          (select
              cu.cust_first_name || ' ' ||  cu.cust_last_name customer
            , o.order_id
            , trunc(o.order_date, :p6_agg_level) order_dt
          from
              oehr_orders o
                join oehr_customers cu
                  on o.customer_id = cu.customer_id)
      pivot xml
          (count(order_id) n_orders for order_dt in (select dt from dates)))
select
    customer
  , xmlserialize(
      content
      xmltransform(
          order_dt_xml
        , xmltype(q'{
            <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
              <xsl:output method="html" />
                <xsl:template match="/PivotSet">
                  <xsl:for-each select="item">
                    <td>
                      <xsl:attribute name="headers">
                        <xsl:value-of select="column[@name='ORDER_DT']" />
                      </xsl:attribute>
                      <xsl:value-of select="column[@name='N_ORDERS']" />
                    </td>
                  </xsl:for-each>
                </xsl:template>
            </xsl:stylesheet>}'))) matrix
from
    order_pivot
order by
    customer

The Display As attribute for the MATRIX column in the report needs to be Standard Report Column so that the mark-up is treated as HTML and not escaped and rendered as text.

The custom report template is based on a copy of the theme 25 Standard report template. There's nothing special about this except that the row template doesn't include HTML mark-up for individual matrix cells and headers. As the second column contains both the HTML and data for all of the matrix columns, the #2# substitution string is included without a wrapping HTML element.

Before Rows

<table class="uReportContainer" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">
<tbody class="uReportPagination">
#TOP_PAGINATION#
</tbody>
<tbody class="uReportBody">
  <tr>
    <td>
      <table class="uReport uReportStandard uReportPivot">
        <thead>
          <tr> <th id="CUSTOMER">#1#</th> #2# </tr>
        </thead>
        <tbody>

After Rows

        </tbody>
      </table>
    </td>
  </tr>
</tbody>
<tbody class="uReportPagination">
#PAGINATION#
</tbody>
</table>
<div class="uReportDownloadLinks">#EXTERNAL_LINK##CSV_LINK#</div>

Row Template 1

<tr> <td headers="CUSTOMER">#CUSTOMER#</td> #MATRIX# </tr>

After exploring various options for generating the matrix column headers I decided to stick with standard APEX functionality and used the PL/SQL Headings Type with a function body returning colon delimited headings:

declare

  l_matrix_th varchar2(4000);

begin

  with dates as (
        select
            case :p6_agg_level
              when 'dd' then to_date(:p6_start_date, 'DD.MM.YYYY') + (level - 1)
              when 'ww' then to_date(:p6_start_date, 'DD.MM.YYYY') + 7 * (level - 1)
              else add_months(to_date(:p6_start_date, 'DD.MM.YYYY'), level - 1)
            end dt
        from
            dual
        connect by level <= :p6_periods)
  select
      xmlserialize(
          content
          xmlagg(
              xmlelement(
                  "th"
                , xmlattributes(to_char(dt, 'YYYY-MM-DD') "id")
                , case :p6_agg_level
                    when 'dd' then to_char(dt, 'DD.MM.YYYY')
                    when 'ww' then to_char(dt, '"W"WW.YYYY')
                    else to_char(dt, 'Mon YYYY')
                  end)
              order by dt)
          as varchar2(4000)) thead
  into
      l_matrix_th
  from
      dates;
    
  apex_debug.trace(l_matrix_th);

  return 'Customer:' || l_matrix_th;

end;

Finally apply some page level CSS to align the matrix columns:

.uReportPivot td:first-child {
  text-align: left;
}
.uReportPivot td {
  text-align: right;
}

fac586

Hi Vikas

Your post inspired me to try out something I had been thinking about for a while. Unfortunately after a promising start I've had difficulty finding time to sort out the loose ends and actually post a response. This is probably therefore too late to be useful. but it looks like a useful technique that (oddly) doesn't seem to have been covered here before.

This approach uses a standard report region based on a SQL query, Oracle's PIVOT clause, a simple XSL stylesheet, and—of course—a custom named column report template (this is me after all).

The query needs 3 input parameters: a start date (p6_start_date), the day/week/month aggregation level (p6_agg_level), and the number of these periods to display in the matrix (p6_periods). The first step is to use these values in a row generator to produce the date dimension for the report. This is used as a subquery in the IN clause of the PIVOT XML query that counts the number of orders by customer and period. As the number of columns in the result set is unknown until runtime, the matrix columns are returned in a single XML fragment for each customer. An XSL stylesheet is used to transform the standard pivot XML structure into an XHTML representation consisting of table cell td elements. Finally the XHTML fragment is serialized from XMLType to a CLOB that can be displayed in APEX reports.

with dates as
      sele
          case :p6_agg_level
            when 'dd' then to_date(:p6_start_date, 'DD.MM.YYYY') + (level - 1)
            when 'ww' then to_date(:p6_start_date, 'DD.MM.YYYY') + 7 * (level - 1)
            else add_months(to_date(:p6_start_date, 'DD.MM.YYYY'), level - 1)
          end dt
      from
          dual
      connect by level <= :p6_periods)
  , order_pivot as (
      select
          customer
        , order_dt_xml
      from
          (select
              cu.cust_first_name || ' ' ||  cu.cust_last_name customer
            , o.order_id
            , trunc(o.order_date, :p6_agg_level) order_dt
          from
              oehr_orders o
                join oehr_customers cu
                  on o.customer_id = cu.customer_id)
      pivot xml
          (count(order_id) n_orders for order_dt in (select dt from dates)))
select
    customer
  , xmlserialize(
      content
      xmltransform(
          order_dt_xml
        , xmltype(q'{
            <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
              <xsl:output method="html" />
                <xsl:template match="/PivotSet">
                  <xsl:for-each select="item">
                    <td>
                      <xsl:attribute name="headers">
                        <xsl:value-of select="column[@name='ORDER_DT']" />
                      </xsl:attribute>
                      <xsl:value-of select="column[@name='N_ORDERS']" />
                    </td>
                  </xsl:for-each>
                </xsl:template>
            </xsl:stylesheet>}'))) matrix
from
    order_pivot
order by
    customer

The Display As attribute for the MATRIX column in the report needs to be Standard Report Column so that the mark-up is treated as HTML and not escaped and rendered as text.

The custom report template is based on a copy of the theme 25 Standard report template. There's nothing special about this except that the row template doesn't include HTML mark-up for individual matrix cells and headers. As the second column contains both the HTML and data for all of the matrix columns, the #2# substitution string is included without a wrapping HTML element.

Before Rows

<table class="uReportContainer" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">
<tbody class="uReportPagination">
#TOP_PAGINATION#
</tbody>
<tbody class="uReportBody">
  <tr>
    <td>
      <table class="uReport uReportStandard uReportPivot">
        <thead>
          <tr> <th id="CUSTOMER">#1#</th> #2# </tr>
        </thead>
        <tbody>

After Rows

        </tbody>
      </table>
    </td>
  </tr>
</tbody>
<tbody class="uReportPagination">
#PAGINATION#
</tbody>
</table>
<div class="uReportDownloadLinks">#EXTERNAL_LINK##CSV_LINK#</div>

Row Template 1

<tr> <td headers="CUSTOMER">#CUSTOMER#</td> #MATRIX# </tr>

After exploring various options for generating the matrix column headers I decided to stick with standard APEX functionality and used the PL/SQL Headings Type with a function body returning colon delimited headings:

declare

  l_matrix_th varchar2(4000);

begin

  with dates as (
        select
            case :p6_agg_level
              when 'dd' then to_date(:p6_start_date, 'DD.MM.YYYY') + (level - 1)
              when 'ww' then to_date(:p6_start_date, 'DD.MM.YYYY') + 7 * (level - 1)
              else add_months(to_date(:p6_start_date, 'DD.MM.YYYY'), level - 1)
            end dt
        from
            dual
        connect by level <= :p6_periods)
  select
      xmlserialize(
          content
          xmlagg(
              xmlelement(
                  "th"
                , xmlattributes(to_char(dt, 'YYYY-MM-DD') "id")
                , case :p6_agg_level
                    when 'dd' then to_char(dt, 'DD.MM.YYYY')
                    when 'ww' then to_char(dt, '"W"WW.YYYY')
                    else to_char(dt, 'Mon YYYY')
                  end)
              order by dt)
          as varchar2(4000)) thead
  into
      l_matrix_th
  from
      dates;
    
  apex_debug.trace(l_matrix_th);

  return 'Customer:' || l_matrix_th;

end;

Finally apply some page level CSS to align the matrix columns:

.uReportPivot td:first-child {
  text-align: left;
}
.uReportPivot td {
  text-align: right;
}

Scott Wesley

That's a pretty darn clever combination of a few technologies there - well done!

Some crazy stuff with xml queries that I'd love to learn.

partlycloudy

Paul - Whew, I think you have outdone yourself this time. That's a dazzling display of various Oracle technologies wrapped up in a deceptively simple SQL query. It will take me a while to digest it. Very elegant, good job.

But I ended up doing something much simpler that works well

1. A simple rolling 12-period query using the "agg level" page item in conjunction with the trunc(dt,<level>). So this generates 12 months, 12 weeks or 12 days.

2. PL/SQL headings to generate the month,week or day to match up with the data

3. 12 days does not provide a whole lot of detail so I included another 30-day rolling detail report as a supplement to the monthly report (couldn't figure out how to drilldown from the monthly to the daily :-(

4. The daily report has a total of 31 columns but columns 29 - 31 are conditional depending on the month selected

I will post some code for review shortly.

Thanks again for sharing.