This discussion is archived
6 Replies Latest reply: Aug 22, 2011 8:39 AM by fac586 RSS

Custom Report Template Issue

25631 Newbie
Currently Being Moderated
Hi,

I have a Custom Report Template, it is a Named Column(Row) Report that I have created. It seems I can get the look and feel I want on a per row basis. But when I try and convert it to be able to loop through for a specific type, like a break on the first column, it gets all messed up. I was wondering if someone might be able to shed some light for me on this I have tried everything

Here is the row template
<table width="100%"  border="0" cellspacing="1" cellpadding="0" bgcolor="#000000">

   <tr  class="Tabledetail">
      <td class="SectionHeading" width="100%" bgcolor="#336699" valign="middle">
         <img src="spacer.gif" width="1" height="1">  <b>#1#</b> 
      </td>
   </tr>

   <tr class="Tabledetail">
      <td>
         <table width="100%"  border="0" cellspacing="1" cellpadding="1" bgcolor=white>
            <tr class="Tabledetail">
               <td>
                  <img src="spacer.gif" width="5" height="1">
               </td>
               <td class=formlabel>
                  #2# 
               </td>
               <td>
                  <img src="spacer.gif" width="5" height="1">
               </td>
               <td colspan=4 class="SectionHeading" bgcolor="#336699" align=middle valign="bottom">
                  <b>Evaluation Trips</b> 
               </td>
               <td>
                  <img src="spacer.gif" width="10" height="1">
               </td>
               <td colspan=4 class="SectionHeading" bgcolor="#336699" align=middle valign="bottom">
                  <b>All Other Trips</b> 
               </td>
            </tr>
            <tr class="Tabledetail">
               <td>
                  <img src="spacer.gif" width="5" height="1">
               </td>
               <td  class=formlabel>
                  #3# 
               </td>
               <td  align=right class=formlabel>
                  <img src="spacer.gif" width="5" height="1">
               </td>
               <td align=right class=formlabel>
                  #4# 
               </td>
               <td align=right class=formlabel>
                  #5# 
               </td>
               <td align=right class=formlabel>
                  #6# 
               </td>
               <td align=right class=formlabel>
                  #7# 
               </td>
               <td>
                  <img src="spacer.gif" width="10" height="1">
               </td>
               <td align=right class=formlabel>
                  #4# 
               </td>
               <td align=right class=formlabel>
                  #5# 
               </td>
               <td align=right class=formlabel>
                  #6# 
               </td>
               <td align=right class=formlabel>
                  #7# 
               </td>
            </tr>



            <tr class="Tabledetail" width=50%>
               <td>
                  <img src="spacer.gif" width="5" height="1">
               </td>
               <td  class=formlabel>
                  #8#
               </td>
               <td class=formlabel>
                  <img src="spacer.gif" width="5" height="1">
               </td>

               <td align=right>
                  #9#
               </td>
               <td  align=right>
                  #10#
               </td>
               <td align=right >
                  #11#
               </td>
               <td align=right >
                  #12#
               </td>
               <td>
                  <img src="spacer.gif" width="10" height="1">
               </td>
               <td align=right >
                  #13#
               </td>
               <td align=right >
                  #14#
               </td>
               <td align=right >
                  #15#
               </td>
               <td align=right >
                  #16#
               </td>
            </tr>



         </table>
      </td>
   </tr>

</table>
</td>
</tr>

<tr>
<td><img src="spacer.gif" width="1" height="10"> </td>
</tr>

<tr>
<td>
Here is the before rows
 <table cellpadding="0" border="0" cellspacing="0" summary="" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">
  #TOP_PAGINATION#
  <tr>
    <td>
      <table cellpadding="0" border="0" cellspacing="0" summary="" class="report-standard">
Here is the after rows
        </table>
    </td>
  </tr>
  #PAGINATION#
</table>
But when I try and pull the upper level html tables out of the row template the format goes to heck. Anyone have any ideas?

Thanks in advance!
  • 1. Re: Custom Report Template Issue
    fac586 Guru
    Currently Being Moderated
    goochable wrote:

    I have a Custom Report Template, it is a Named Column(Row) Report that I have created. It seems I can get the look and feel I want on a per row basis. But when I try and convert it to be able to loop through for a specific type, like a break on the first column, it gets all messed up. I was wondering if someone might be able to shed some light for me on this I have tried everything
    Think you really need to reproduce this with a working example on apex.oracle.com. It's too complex to figure out by hand and brain, and we don't have the associated query, tables and data to enable us to reproduce.

    Not clear on what is meant by "I try and convert it to be able to loop through for a specific type, like a break on the first column" and how it "gets all messed up"?
    But when I try and pull the upper level html tables out of the row template the format goes to heck.
    Don't get that either. Working example(s) and some more detailed description would be helpful.

    On the mark-up provided, there are too many nested <tt>table</tt>s and deprecated attributes for my taste. If you can show us what's going on and what that required layout(s) are&mdash;either produce examples by hand using HTML regions on apex.oracle.com or post some illustration images there or on some hosting site&mdash;then we might be able to suggest some alternative HTML.
  • 2. Re: Custom Report Template Issue
    25631 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply. Yeah I started to create this on apex.oracle.com last night it is just going to take me a while :)
    So I thought I would last night just ask to see if anyone saw any glaring issues before I finish building it tonight.
    I inherited this design and I am just trying to duplicate it.

    Thanks
  • 3. Re: Custom Report Template Issue
    25631 Newbie
    Currently Being Moderated
    Hi,

    I have uploaded my custom template to apex.oracle.com

    Work Space: TEST_BR
    Username: guest
    Password: test123

    Application: 27202 CUSTOM_TEMPLATE

    and then just run it. No matter what I try I cannot get the rows to break under the first column with this template.
    In other words all of the OTC Summarys should be in one html table, etc.

    Thanks so much for any insight!
  • 4. Re: Custom Report Template Issue
    fac586 Guru
    Currently Being Moderated
    goochable wrote:
    and then just run it. No matter what I try I cannot get the rows to break under the first column with this template.
    No, it's impossible to get this report to do anything other than what it's doing. The point of custom named column templates is to structure data in a specific way, so you build a report template that for that structure&mdash;and only that structure.

    In this case the query also appears to be specifically constructed for this report's structure, so it's not really possible to switch to a standard report template and utilise the built-in break functions etc. Is the query actually based on an APEX collection created specifically for this report? (Please tell me it is based on a collection and not on some...[shudder]...generic data model?)
    I inherited this design and I am just trying to duplicate it.
    Inherited it? From 1997 I hope? If whoever created this template is still trying to produce this kind of HTML they need to be re-educated with extreme prejudice.
    In other words all of the OTC Summarys should be in one html table, etc.
    Can't get there from here. The data/query is too specifically bound to that layout.

    If the assumption about the collection is correct, back up a little and reproduce the underlying tables and data used to create the collection. Then we can try to produce something using a standard report template and features.

    Edited by: fac586 on 19-Aug-2011 09:33

    Just noticed from looking at the data in SQL Workshop that there is more data than first visible on the report (initially configured to display only 15 rows with no pagination). Puts a slightly different slant on things as more of the "generic" nature of the report structure&mdash;and hence need for a custom template (and possibly reporting from a collection)&mdash;is apparent. (Doesn't alter the fact the table semantics and accessibility are terrible.)

    We need a sort order to achieve any kind of report breaking?
  • 5. Re: Custom Report Template Issue
    25631 Newbie
    Currently Being Moderated
    Thanks for the input! Yeah it is based on a query from a collection as all this data is summations that i am pre-populating.
    Yes this html is probably from 1998 or 1999 I think they told me actually lol
    So there is no way to accomplish what I am trying to do then?
    There is no way I could use a break on first column and modify the header info to get the same sort of look and feel?

    Thanks!
  • 6. Re: Custom Report Template Issue
    fac586 Guru
    Currently Being Moderated
    goochable wrote:
    Thanks for the input! Yeah it is based on a query from a collection as all this data is summations that i am pre-populating.
    Yes this html is probably from 1998 or 1999 I think they told me actually lol
    So there is no way to accomplish what I am trying to do then?
    There is no way I could use a break on first column and modify the header info to get the same sort of look and feel?
    Still not really clear what you are trying to accomplish, and in my view there are so many problems with the "look and feel" that it's not worth perpetuating.

    Making a lot of assumptions, I've come up with the kind of HTML structure I'd use when marking up this kind of data. I added a page 2 to your example on apex.oracle.com, showing a basic presentation of this structure alongside the original for comparison, and another styled using the default theme L&F.

    <li>Given the requirement to use multi-level headers (and because I prefer to have total control over the HTML), I stayed with a custom report template rather than trying to utilise column breaking with a generic column report template. This also permits use of more advanced table structures than can be supported by standard templates, such as s<tt>colgroup</tt>s to organize the table columns as well as the rows:

    Before Rows
      <table cellpadding="0" border="0" cellspacing="0" summary="" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">
      #TOP_PAGINATION#
      <tr>
        <td>
          <table class="fish">
            <caption>Some fishy summaries</caption>
            <colgroup span="1"></colgroup>
            <colgroup span="4" class="evaluation-trips" align="right"></colgroup>
            <colgroup span="4" class="other-trips" align="right"></colgroup>
    After Rows
          </table>
        </td>
      </tr>
      #PAGINATION#
    </table>
    <li>Rather than separate tables, the report is contained in one HTML table, utilizing the <tt>tbody</tt> element to subdivide this into separate row groups to meet the "break on first column" requirement. This is achieved using conditional row templates, with PL/SQL Expressions based on the values of metadata columns added to the query:

    Row Template 1

    Header rows and first data row for each row group. <tt>scope</tt> attributes are added to multi-column headers for improved accessibility:
      <tbody>
        <tr>
          <th colspan="9" scope="rowgroup">#C1#</th>
        </tr>
        <tr>
          <th></th>
          <th colspan="4" scope="colgroup">Evaluation Trips</th>
          <th colspan="4" scope="colgroup">All Other Trips</th>
        </tr>
        <tr>
          <th>#C2#</th>
          <th>#C4#</th>
          <th>#C5#</th>
          <th>#C6#</th>
          <th>#C7#</th>
          <th>#C4#</th>
          <th>#C5#</th>
          <th>#C6#</th>
          <th>#C7#</th>
        </tr>
        <tr class="#ALT#">
          <td class="desc">#C8#</td>
          <td>#C9#</td>
          <td>#C10#</td>
          <td>#C11#</td>
          <td>#C12#</td>
          <td>#C13#</td>
          <td>#C14#</td>
          <td>#C15#</td>
          <td>#C16#</td>
        </tr>
      #CLOSE_ROW_GROUP#
    Row Template 1 Expression

    This template is used when the row metadata shows that the current row is in a different row group from the previous row:
    #ROW_GROUP# != #PREVIOUS_ROW_GROUP#
    Row Template 2

    This is the "default" template, used for any subsequent data rows in the row group:
        <tr class="#ALT#">
          <td class="desc">#C8#</td>
          <td>#C9#</td>
          <td>#C10#</td>
          <td>#C11#</td>
          <td>#C12#</td>
          <td>#C13#</td>
          <td>#C14#</td>
          <td>#C15#</td>
          <td>#C16#</td>
        </tr>
      #CLOSE_ROW_GROUP#
    Both templates make use of a <tt>#CLOSE_ROW_GROUP#</tt> column value conditionally generated in the query that returns a <tt>&lt;/tbody&gt;</tt> tag if the current row is the last data row in the row group. (Mixing logic and structure in this way is not good practice, but APEX only allows up to 4 conditional row templates, which is completely insufficient for any moderately complex structure.)

    <li>Several metadata columns (incorporating heavy use of analytic functions) are added to the report query for use in the report template or CSS presentation:
    with fish as (
          select
                    c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16
                    /*
                      Generate a fixed order for separate report sections/row groups.
                      
                      (This is a guess as the actual requirement is not specified.)
                    */  
                  , case c1
                      when 'OTC Summary' then 1
                      when 'Retained Catch Summary' then 2
                      when 'Discarded Catch Summary' then 3
                      when 'Discarded Species Composition Summary' then 4
                      when 'Retained Species Composition Summary' then 5
                      when 'Priority Species Biospecimen Summary - Discarded Catch' then 6
                      when 'Other Species Biospecimen Summary - Discarded Catch' then 7
                      when 'Dissection Summary - Discarded Catch' then 8
                    end row_group
                    /*
                      Calculate row number within row group.
                      
                      Copes with row order in some row groups being determined
                      numerically, while others used standard character semantics.
                    */
                  , row_number()
                      over (
                        partition by  c1
                        order by      to_number(regexp_replace(c8, '[^[:digit:]]')) nulls last
                                    , c8) group_rn
                    /*
                      Calculate number of rows in row group.
                    */
                  , count(*)
                      over (
                        partition by c1) group_rows
          from
                  test)
    select
              c1
              /*
                Not clear on meaning of "Weight"/"Method" values: assumed this is
                column heading equivalent to "Species".
                
                Combine both source DB columns into one for HTML heading, dealing
                with various null/space/blank issues...
              */
            , nullif(c2 || ' ', '  ') || c3 c2
            , ' ' c3
            , c4
            , c5
            , c6
            , c7
            , c8
            , c9
            , c10
            , c11
            , c12
            , c13
            , c14
            , c15
            , c16
            , row_group
              /*
                Get the rowgroup for the previous row
              */
            , lag(row_group, 1, 0)
                over (
                  order by row_group) previous_row_group
            , group_rn
            , group_rows
              /*
                Determine odd/even row number: used for standard or alternate style. 
              */
            , mod(group_rn, 2) alt
              /*
                Generate a closing element if the row is the last row in the
                row group.
              */
            , case
                when group_rn = group_rows
                then
                  '</tbody>'
                else
                  ' '
              end close_row_group
    from
              fish
    order by
               row_group
             , group_rn
    This makes major assumptions about the sort order(s) and break(s) required in the report.

    <li>Finally, the visual presentation is applied using CSS rather than (mainly deprecated) HTML attributes, via an embedded style sheet in the page HTML Header:
    <style type="text/css">
    .fish {
      empty-cells: show;
      border-collapse: collapse;
    }
    .fish tbody tr:first-child th {
      border-top: 1px solid #fff;
      font-weight: bold;
    }
    .fish th,
    .fish td {
      padding: 3px 6px;
    }
    .fish th {
      border-bottom: 1px solid #fff;
      border-left: 1px solid #fff;
      background-color: #275096;
      color: #fff;
      font-weight: 300;
      text-align: left;
    }
    .fish td {
      text-align: right;
    }
      .fish tr.\30  td {
        background-color: #dde;
      }
      .fish td:first-child {
        text-align: left;
      }
    </style>
    The default theme L&F report adds vertical borders to separate columns and column groups (latter may not be fully effective on IE: I'm not wasting my time on quirks mode fixes for that).

    The resulting report uses 60% less vertical space, and 87% less HTML code[1] than the original. Usability and accessibility are improved by eliminating nested tables and useless table cells and shim images, increasing the contrast between text and background colours, and using alternating row backgrounds for better visual tracking.

    [1] Including whitespace, but neither template is compressed in any way: both are in fully readale format including normal whitespace indentation.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points