1 2 Previous Next 17 Replies Latest reply: Aug 26, 2013 8:09 AM by Gus C RSS

    Show Report In Two Columns

    Gus C

      Apex 3.2

       

      I have a report and it has 1 column and 22 rows.

      At the moment, I show the first 15 rows and then you must paginate.

      The report will not grow much, may be to 30 rows.

       

      Is there a way to display the first 15 rows in 1 column and the second in another column, side by side etc

       

      Gus

        • 1. Re: Show Report In Two Columns
          Gus C

          My current report sql is

           

          select distinct gwh.shortdescr

          from udm_wh gwh, udm_lde lde

          where lde.ldeid = gwh.ldeid

          and lde.ldekey = 'MCDGBL'

          order by 1

           

          Gus

          • 2. Re: Show Report In Two Columns
            Pars

            you can go to Report Attributes>>  = 30

            and you get 30 rows on one column in report

            but you not required this? require two coumn of 15-15?

            • 3. Re: Show Report In Two Columns
              Gus C

              Yes 2 columns of 15 rows

               

              Gus

              • 4. Re: Show Report In Two Columns
                Gus C

                So far, I have this

                 

                SELECT col1, col2, col3

                FROM (SELECT gwh.shortdescr col1,

                lag(gwh.shortdescr, 1) over(ORDER BY gwh.shortdescr ASC) col2,

                lag(gwh.shortdescr, 1) over(ORDER BY gwh.shortdescr ASC) col3,

                row_number() over(ORDER BY gwh.shortdescr ASC) rn

                FROM udm_wh gwh, udm_lde lde

                where lde.ldeid = gwh.ldeid

                and lde.ldekey = 'MCDGBL')

                WHERE Mod(rn, 3) = mod((select count (1) from udm_wh gwh, udm_lde lde

                where lde.ldeid = gwh.ldeid

                and lde.ldekey = 'MCDGBL'), 3)

                ORDER BY 3

                 

                This splits the report in to 3 columns, but I need distinct data

                 

                select distinct gwh.shortdescr

                from udm_wh gwh, udm_lde lde

                where lde.ldeid = gwh.ldeid

                and lde.ldekey = 'MCDGBL'

                order by 1

                 

                 

                Gus

                • 5. Re: Show Report In Two Columns
                  christoph.hillinger

                  Hi

                   

                  If you have an Oracle 11 then you could use the pivot-operator - like for example the following query on the EMP table:

                  select *

                    from (select ename, row_num - col * 8 row_num, col

                            from (select ename, rownum row_num, floor(rownum / 9) col from emp)) pivot(max(ename) for col in(0,

                                                                                                                              1))

                  order by row_num;

                   

                  Maybe this helps (you have to change 8 to 15 and 9 to 16 for 15 rows - but the EMP table only has 14 rows).

                   

                  Alternatively you can do something similar by making views and joining them.

                   

                  Greetings

                  Chris

                  • 6. Re: Show Report In Two Columns
                    Gus C

                    Unfortunatley, this is Oracle 10.2

                    • 7. Re: Show Report In Two Columns
                      christoph.hillinger

                      Too bad, then how about the following:

                       

                      with base as

                      (select ename, row_num - col * 8 row_num, col

                          from (select ename, rownum row_num, floor(rownum / 9) col from emp))

                      select t1.ename, t2.ename

                      from (select * from base where col = 0) t1,

                           (select * from base where col = 1) t2

                      where t1.row_num = t2.row_num(+)


                      This should work in Oracle 10.2.


                      If you want, I can try to change your statement into this form, but I think you can manage.


                      Greetings

                      Chris

                      • 8. Re: Show Report In Two Columns
                        Gus C

                        Tried it but still having problems getting the disitnct data

                         

                        Gus

                        • 9. Re: Show Report In Two Columns
                          Gus C

                          Can anybody help with this.

                           

                          Gus

                          • 10. Re: Show Report In Two Columns
                            christoph.hillinger

                            Hi Gus

                             

                            Could you post the results you're getting so far, I'm having a hard time understanding the data.

                             

                            Greetings

                            Chris

                            • 11. Re: Show Report In Two Columns
                              Gus C

                              With this query

                               

                              with base as

                              (select distinct shortdescr, row_num - col * 16 row_num, col

                                  from (select gwh.shortdescr, rownum row_num, floor(rownum / 16) col from udm_wh gwh, udm_lde lde
                              where lde.ldeid = gwh.ldeid
                              and lde.ldekey = 'MCDGBL'))

                              select t1.shortdescr, t2.shortdescr

                              from (select * from base where col = 0) t1,

                                   (select * from base where col = 1) t2

                              where t1.row_num = t2.row_num(+)

                               

                              I get

                               

                              SHORTDESCRSHORTDESCR
                              GERMANYPORTUGAL
                              GERMANYROMANIA
                              SERBIAGERMANY
                              GERMANYRUSSIA
                              FINLANDPOLAND
                              RUSSIAHUNGARY
                              BELGIUMITALY
                              CZECH REPUBLICPOLAND
                              DENMARKPOLAND
                              AUSTRIAITALY
                              CROATIANORWAY
                              GERMANYROMANIA
                              BULGARIANETHERLANDS
                              AUSTRIAITALY
                              GERMANYPORTUGAL

                               

                              As you can see, this is not distinct data

                               

                              Gus

                              • 12. Re: Show Report In Two Columns
                                Mohan1312

                                Hi,

                                 

                                try this,

                                select * from

                                (select col,lead(col,15,0) over (order by rownum) col2

                                from (select distinct gwh.shortdescr as col

                                      from udm_wh gwh, udm_lde lde

                                      where lde.ldeid = gwh.ldeid and lde.ldekey = 'MCDGBL'

                                      order by 1))

                                where rownum <=  15

                                 

                                Regards,

                                Mohan.

                                • 13. Re: Show Report In Two Columns
                                  christoph.hillinger

                                  Yup, you're right there, it is pretty undistinct.

                                   

                                  The following should work better:

                                   

                                  with base as

                                  (select shortdescr, row_num - col * 16 row_num, col

                                      from (select gwh.shortdescr, rownum row_num, floor(rownum / 16) col

                                              from (select distinct gwh.shortdescr

                                                      from udm_wh gwh, udm_lde lde

                                                     where lde.ldeid = gwh.ldeid

                                                       and lde.ldekey = 'MCDGBL')))

                                  select t1.shortdescr, t2.shortdescr

                                    from (select * from base where col = 0) t1,

                                         (select * from base where col = 1) t2

                                  where t1.row_num = t2.row_num(+)

                                   

                                   

                                  Greetings

                                  Chris

                                  • 14. Re: Show Report In Two Columns
                                    fac586

                                    GusC wrote:

                                     

                                    Apex 3.2

                                     

                                    I have a report and it has 1 column and 22 rows.

                                    At the moment, I show the first 15 rows and then you must paginate.

                                    The report will not grow much, may be to 30 rows.

                                     

                                    Is there a way to display the first 15 rows in 1 column and the second in another column, side by side etc

                                    This is a presentation problem so the correct place to solve it is in the presentation layer using CSS. CSS3 introduces multiple column layouts, but this feature is only supported in recent browser versions—in particular requiring IE10+ in standards mode, which I suspect will be a problem as (a) you're not likely to be using IE10, and (b) APEX 3.2 themes aren't HTML5 and won't trigger standards mode (and aren't likely to be fully compatible even if forced to).

                                     

                                    Nonetheless, I think it's a technique that's worth demonstrating (if only for me to get some experience of using it), before considering an alternative option using interleaved row ordering and CSS floats that might be of more immediate use to you.

                                     

                                    Start with a standard report using a basic query on the [OE]HR EMPLOYEES table:

                                     

                                    select
                                        first_name
                                      , last_name
                                      , phone_number
                                    from
                                        oehr_employees
                                    order by
                                        last_name
                                      , first_name
                                    
                                    

                                     

                                    The appropriate structure for a single column report with a predefined sort order is an ordered list, so we create a custom generic row report template, as a copy of the Standard report template with the following modifications:

                                     

                                    Before Rows

                                    <table cellpadding="0" border="0" cellspacing="0" id="report_#REGION_STATIC_ID#" class="pagination-container">
                                      #TOP_PAGINATION#
                                    </table>
                                    <ol class="single-value-list" #REPORT_ATTRIBUTES#>
                                    
                                    

                                     

                                    Before Each Row

                                    <li>
                                    
                                    

                                     

                                    Column Template 1

                                    #COLUMN_VALUE#
                                    
                                    

                                     

                                    After Each Row

                                    </li>
                                    
                                    

                                     

                                    After Rows

                                    </ul>
                                    <div class="t17CVS">#EXTERNAL_LINK##CSV_LINK#</div>
                                    <table cellpadding="0" border="0" cellspacing="0" class="pagination-container">
                                      #PAGINATION#
                                    </table>
                                    
                                    

                                     

                                    This report template is generic enough to be used in both solutions, and although it appears you don't need pagination in the solution, I've retained it to provide more flexibility and because it involves an interesting problem in creating balanced columns on the last page in the interleaved row order option.

                                     

                                    As the template only displays one column per row, and to exercise the column layout break properties, all of the table columns are hidden and we create a derived column that uses an HTML Expression containing some hCard microformat markup to make the list item content span 2 lines:

                                     

                                    <div class="vcard">
                                      <span class="fn n"><span class="given-name">#FIRST_NAME# <span class="family-name">#LAST_NAME#</span></span>
                                      <div class="tel">#PHONE_NUMBER#</div>
                                    </div>
                                    
                                    

                                     

                                    We can then implement some basic list/hCard formatting using CSS:

                                     

                                    /*
                                      Basic list/hCard formatting common to both CSS3 multi-column and interleaved rows/CSS float solutions
                                    */
                                    ol {
                                      margin-left: 0;
                                      padding-left: 0;
                                      width: 44em;
                                      white-space: nowrap;
                                      list-style-type: square;
                                    }
                                      ol li {
                                        margin: 0 0 0.5em 2em;
                                      }
                                    
                                    .vcard .family-name {
                                      font-weight: 600;
                                      text-transform: uppercase;
                                    }
                                    
                                    

                                     

                                    The CSS3 multi-column rules used in the first report region are pretty straightforward, although they have to take account of the current level of browser support by using vendor-specific properties where appropriate:

                                     

                                    /*
                                      CSS3 multi-column formatting
                                    */
                                    .multi-col ol {
                                    /* Safari/Chrome/Opera */
                                      -webkit-column-count: 2;
                                      -webkit-column-gap: 0;
                                      /* Firefox */
                                      -moz-column-count: 2;
                                      -moz-column-gap: 0; 
                                      /* IE10+ */
                                      column-count: 2;
                                      column-gap: 0;
                                    }
                                      .multi-col ol li {
                                        -webkit-column-break-inside: avoid;
                                        -moz-column-break-inside: avoid;
                                        break-inside: avoid;
                                        /* Workaround for FF as break-inside doesn't work. */
                                        page-break-inside: avoid;
                                      }
                                    
                                    

                                     

                                    For the interleaved row ordering workaround used in the second report, the query has to be modified to calculate the page each row will appear on, and the position in each column on the page it will occupy:

                                     

                                    with emps_ordered_unique as (
                                        select
                                    -- If you need distinct rows then just restrict them at the start
                                            distinct
                                            first_name
                                          , last_name
                                          , phone_number
                                          , row_number()
                                              over (
                                                order by last_name, first_name) row#
                                        from
                                            oehr_employees
                                        order by
                                            last_name
                                          , first_name)
                                      , emps_interleaved as (
                                          select
                                              first_name
                                            , last_name
                                            , phone_number
                                            , row#
                                              -- 30 is overall number of rows per page
                                            , ceil(row# / 30) page#
                                              -- 30 is overall number of rows per page; 15 maximum number of rows per column
                                            , mod(row# - 1, least(ceil(count(*) over (partition by ceil(row# / 30)) / 2), 15)) col_row#
                                          from
                                              emps_ordered_unique)
                                    select
                                        first_name
                                      , last_name
                                      , phone_number
                                    from
                                        emps_interleaved
                                    order by
                                        page#
                                      , col_row#
                                      , row#
                                    
                                    

                                     

                                    The trick here is to interleave the rows, with those that have the same position in each column appearing in pairs so that they can be laid out side-by-side using element widths and CSS floats.

                                     

                                    The CSS for this report is:

                                     

                                    /*
                                      Interleaved rows/CSS float formatting
                                    */
                                    #interleaved .pagination-container {
                                      clear: both;
                                    }
                                    #interleaved ol {
                                    }
                                      #interleaved ol li {
                                        float: left;
                                        width: 20em;
                                      }
                                    
                                    

                                     

                                    The drawback with the latter option is that although the list entries are visually ordered down and across the columns, they are not semantically ordered thus in the HTML markup, so any user perceiving the list without this CSS (for example via a screenreader, or a mobile device with an alternative style sheet) gets the list in a weird order. For that reason I probably wouldn't use this approach. At present I'd implement the CSS3 multi-column approach as a progressive enhancement for users with up-to-date browsers, and allow it to gracefully degrade to a basic 1-column list in legacy versions.

                                    1 2 Previous Next