Forum Stats

  • 3,854,216 Users
  • 2,264,340 Discussions
  • 7,905,609 Comments

Discussions

Matrix report

partlycloudy
partlycloudy Member Posts: 8,168 Silver Trophy
edited Dec 15, 2013 8:15PM in APEX Discussions

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

jwellsnhHari_639Pavel_ptimmy3780

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,219 Red Diamond
    Answer ✓

    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;
    }
    
    jwellsnhHari_639Pavel_p

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,219 Red Diamond
    Answer ✓

    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;
    }
    
    jwellsnhHari_639Pavel_p
  • Scott Wesley
    Scott Wesley Member Posts: 6,260 Gold Crown

    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
    partlycloudy Member Posts: 8,168 Silver Trophy

    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.

This discussion has been closed.