3 Replies Latest reply on Dec 16, 2013 1:15 AM by partlycloudy

    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

        • 1. Re: Matrix report
          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;
          }
          
          • 2. Re: Matrix report
            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.

            • 3. Re: Matrix report
              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.