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.