8 Replies Latest reply: Jan 25, 2012 12:14 PM by John McClain RSS

    Report Totals

    John McClain
      I have searched the forum for an answer to this (Google too) and have not been able to see anything that speaks to this question.

      In APEX, I have a standard SQL report region with this SQL:

      WITH DETAIL_DATA as (
      SELECT so.so_store_cd
      , sl.qty
      , sl.unit_prc
      , i.repl_cst
      FROM so so
      , so_ln sl
      , itm i
      WHERE so.del_doc_num = sl.del_doc_num
      AND sl.itm_cd = i.itm_cd
      AND so.so_wr_dt BETWEEN :P3_BEGIN_DATE and :P3_END_DATE)
      , SUMMARY_DATA as (SELECT so_store_cd
      , sum(qty) ext_qty
      , sum(qty*unit_prc) ext_prc
      , sum(qty*repl_cst) ext_cost
      , sum(qty*unit_prc)-sum(qty*repl_cst) ext_gmd
      FROM DETAIL_DATA
      GROUP BY so_store_cd)
      --
      SELECT nvl(so_store_cd,'Grand Total:') store,
      sum(ext_qty) ext_qty
      , sum(ext_prc) ext_prc
      , sum(ext_gmd) ext_gmd
      , decode(sum(ext_prc), 0, 0, round(sum(ext_gmd)/sum(ext_prc),4)) * 100||'%' ext_gmp
      FROM SUMMARY_DATA
      GROUP BY rollup((so_store_cd))

      And I get this:

      STORE           EXT_QTY      EXT_PRC     EXT_GMD      EXT_GMP
      05           2      120      -40      -33.33%
      CW           2 1458      678      46.5%
      50           10 8749.58 7192.98      82.21%
      02           56 16332.73 7287.68      44.62%
      00           93 76759.39 65299.36      85.07%
      JM           582 591991.59 483010.31      81.59%
      01           2141 799715.33 509445.92      63.7%
      C1           2912 637650.54 334570.17      52.47%
      Grand Total: 5798 2132777.16 1407444.42      65.99%

      Works great until you enable sorting. Then, the "Grand Total:" row is considered to be just another data row, and that gets sorted along with the "actual" data.

      I have also tried just enabling column sums in Report Attributes since that stays at the bottom, but I can't see a way to perform any calculations, i.e. sum(ext_gmd)/sum(ext_prc). I don't just want to sum that column (ext_gmp), I need to have the calculation result.

      So, my questions are:

      1) Is there a way to "lock" the row at the bottom of the results using the Group by/Rollup method, irrespective of sorting, or
      2) Is there any way to perform calculations of the Apex generated Total row when using the Report Attributes SUM functionality?

      Thanks
      John
        • 1. Re: Report Totals
          510477
          What you've run into is one of the programmatic items in APEX SQL for which the obvious operation doesn't work the way you think it might.

          What I would recommend is to put your detail rows in one region and the sum/total in a second region below. If using APEX 4+ you can specify a parent region that still ties the two together visually.

          No, there currently isn't a way within the APEX "summing" to do numerical computations. I wish there were because the percentages would be really nice.
          • 2. Re: Report Totals
            John McClain
            blarman74 - Thanks for that.

            The two region approach is what I am currently using. It does the job, but doubles the query time since it needs to make two passes of the same (raw data) query.

            John
            • 3. Re: Report Totals
              510477
              Yep. I run into the same problem. I would love for SUM processing to be added as an APEX feature at some point, but until then, this is the workaround.
              • 4. Re: Report Totals
                fac586
                jmcclain wrote:

                In APEX, I have a standard SQL report region with this SQL:
                Please post code and sample output wrapped in <tt>\
                ...\
                </tt> tags to preserve formatting.
                1) Is there a way to "lock" the row at the bottom of the results using the Group by/Rollup method, irrespective of sorting
                This can be done with a custom named column (row) report template that uses conditional row templates to output the total row in a <tt>tfoot</tt> element. Use <tt>grouping_id</tt> in the query to get a column (not displayed) that can be used to identify the total row.

                Reproduce your report on apex.oracle.com and post some guest developer credentials here if you want some help with this.
                • 5. Re: Report Totals
                  John McClain
                  This seems like an interesting solution. I will give it a try and let you know.
                  Thanks for the sugesstion and the links!
                  • 6. Re: Report Totals
                    John McClain
                    Hey fac586,

                    I'll take you up on your offer to help! I didn't find much un the way of useful examples.

                    Here's the login info for apex.oracle.com:

                    Workspace: BT_DEV
                    User: guest_dev
                    PWD: dev_01

                    The application is SALES_TEST, and the report name is SALES_DATA.

                    The report query now includes the GROUPING_ID. I haven't modified anything with respect to the report templates.

                    Thanks in advance.

                    John
                    • 7. Re: Report Totals
                      fac586
                      John McClain wrote:
                      Hey fac586,

                      I'll take you up on your offer to help! I didn't find much un the way of useful examples.
                      I've had a go and the result is generally satisfactory.

                      I created a copy of your report in a new region SALES 2, with static region ID <tt>sales-2</tt> for use as an ID selector in CSS. No changes to the report query as yet:
                      WITH DETAIL_DATA as (
                      SELECT sd.so_store_cd
                      , sd.qty
                      , sd.unit_prc
                      , sd.repl_cst
                      FROM SALES_DATA sd)
                      , SUMMARY_DATA as (SELECT so_store_cd
                      , sum(qty) ext_qty
                      , sum(qty*unit_prc) ext_prc
                      , sum(qty*repl_cst) ext_cost
                      , sum(qty*unit_prc)-sum(qty*repl_cst) ext_gmd
                      FROM DETAIL_DATA
                      GROUP BY so_store_cd)
                      -- 
                      SELECT nvl(so_store_cd,'Grand Total:') store, 
                      sum(ext_qty) ext_qty
                      , sum(ext_prc) ext_prc
                      , sum(ext_gmd) ext_gmd
                      , decode(sum(ext_prc), 0, 0, round(sum(ext_gmd)/sum(ext_prc),4)) * 100 ext_gmp
                      , GROUPING(so_store_cd) TOTAL_ROW
                      FROM SUMMARY_DATA
                      GROUP BY rollup((so_store_cd))
                      Then I created a custom named column report template as a "copy" of the Standard generic column report template: i.e. it uses the same basic HTML structure, class attributes etc required for support in the current theme, but it contains a fixed number of columns structured specifically for this report.

                      Before Rows
                      <table border="0" cellpadding="0" cellspacing="0" summary="" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#" class="report-holder">
                        #TOP_PAGINATION#
                        <tr>
                          <td>
                            <table border="0" cellpadding="0" cellspacing="0" class="report-standard" summary="#REGION_TITLE#">
                            <thead>
                              <th class="header">#1#</th>
                              <th class="header">#2#</th>
                              <th class="header">#3#</th>
                              <th class="header">#4#</th>
                              <th class="header">#5#</th>
                            </thead>
                      After Rows
                            </table>
                            <div class="CSV">#EXTERNAL_LINK##CSV_LINK#</div>
                          </td>
                        </tr>
                        #PAGINATION#
                      </table>
                      Row Template 1
                            <tfoot>
                              <tr #HIGHLIGHT_ROW#>
                                <td class="data">#1#</td>
                                <td class="data">#2#</td>
                                <td class="data">#3#</td>
                                <td class="data">#4#</td>
                                <td class="data">#5#</td>
                              </tr>
                            </tfoot>
                      Row Template 1 Condition
                      Use Based on PL/SQL Expression

                      Row Template 1 Expression
                      #6# = 1
                      The purpose of this conditional row template is to generate the only superaggregate row in this report (the Grand Total) as the table footer (the <tt>tfoot</tt> element). Column <tt>#6#</tt> contains the <tt>grouping(so_store_cd)</tt> values generated in the query: 0 = normal row; 1 = superaggregate row.

                      Row Template 2
                              <tr #HIGHLIGHT_ROW#>
                                <td class="data">#1#</td>
                                <td class="data">#2#</td>
                                <td class="data">#3#</td>
                                <td class="data">#4#</td>
                                <td class="data">#5#</td>
                              </tr>
                      Normal rows use this template.

                      Named column report templates don't support the same substitution strings as generic column templates. There's no <tt>#COLUMN_HEADER_NAME#</tt> substitution, so column header <tt>id</tt> and corresponding <tt>headers</tt> attributes should be specified explicitly in the template&mdash;however it's probably unnecessary to do so for a simple table structure like this. <tt>#ALIGNMENT#</tt> is also not supported, so declaratively setting column/heading alignment in the report column attributes is not possible. Again this can be done in HTML in the template, but I prefer to do it using CSS (here in the page HTML Header):
                      <style type="text/css">
                      #sales-2 .report-standard th {
                        text-align: left;
                      }
                      #sales-2 .report-standard td {
                        text-align: right;
                      }
                      #sales-2 .report-standard td:first-child {
                        text-align: left;
                      }
                      #sales-2 .report-standard tr:last-child td.data {
                        border-bottom: 1px solid #ddd;
                      }
                      #sales-2 .report-standard tfoot td {
                        border-bottom: none !important;
                        font-weight: bold;
                      }
                      </style>
                      This also overrides some theme border styling that conflicts with this technique, and renders the totals footer in bold text.

                      This is not perfect, but I think it's a decent first approximation. From a standards viewpoint I'm not happy about just wrapping the total row in <tt>tfoot</tt> wherever the sort leaves it in the row order. This doesn't conform to the usable contexts for this element described in the spec:
                      >
                      As a child of a table element, after any caption, colgroup, and thead elements and before any tbody and tr elements, but only if there are no other tfoot elements that are children of the table element.

                      As a child of a table element, after any caption, colgroup, thead, tbody, and tr elements, but only if there are no other tfoot elements that are children of the table element.
                      >
                      i.e. it should always be positioned before or after all of the other rows (and it would be better if those other rows were all contained in a <tt>tbody</tt>). Other than offending my HTML sensibilities, this also loses you alternating row styling as the odd/even sequence is broken when the total row is pulled out of its original position.

                      Due to the report values in this report, the total row will normally be positioned first or last, except when sorting by Store or Extended GM%. Re: Report formatting/sorting issue, so I'd experiment with using that approach to force the total row to come first/last when sorted by columns where the actual values wouldn't do so. Text literals like "Grand Total" can go in the <tt>tfoot</tt> row template rather than the query so as not to be considered when sorting.
                      • 8. Re: Report Totals
                        John McClain
                        Very nice! That's exactly what I was looking for. I appreciate this very much. The outline you provided was very concise and easy to follow, and is working great in my environment.