10 Replies Latest reply: Dec 16, 2013 7:20 AM by NDG123 RSS

    Custom template: table within a table

    NDG123

      Hi,

       

      I recently started working with a custom template for the first time. I'm already pretty far in getting what I want to accomplish, but I have no idea how to implement what I'm going to describe here.

      In my application I need data from 3 tables (simplified, it's actually more than that). The data in these tables have a 1 to many relationship. The data is as follows: 1 activity has many tasks, 1 task has many subtasks.

       

      I would like to show a screenshot but looks like I can't post pictures here, so simplified, what I already have is:

      Activity1

           Task1

                Subtask1

      Activity1

           Task1

                Subtask2

      Activity1

           Task2

                Subtask3

      Activity1

           Task2

                Subtask4

      Here he repeats my template for every row

       

      But what I would like to have:

      Activity1

           Task1

                Subtask1

                Subtask2

           Task2

                Subtask3

                Subtask4

      Here he does not repeat the template for every row, but adapts it according to the data. The number of tasks within an activity and the number of subtasks within a task are not fixed.

      Can anyone tell me how to accomplish this?

       

      The code of my template:

          <div class="complex"> 
          <table cellpadding="0" border="0" cellspacing="0"> 
              <tr><td>#SOD#</td></tr> 
              <tr><td>#OOD#</td></tr> 
              <tr><td>#ACT#</td></tr> 
          </table> 

       

          <table cellpadding="0" border="0" cellspacing="0"> 
              <tr><th>Taak</th><th>Werknemer Naam</th></tr>
              <tr><td>#TAAK#</br>Master afdeling: #AFD_CODE#</br>Belmo nummer: #BELMO#</br>Planning LA: #PLANNING_LA#</td><td>#WN_NAAM#</td><td>Voeg subtaak/mijlpaal toe</td></tr>
          </table>
          <h2>#SUBTAAK_OF_MIJLPAAL#</h2> 
          <table cellpadding="0" border="0" cellspacing="0"> 
              <tr><th>Subtaak/Mijlpaal</th><th>Gelinkte personeelsleden</th><th>Actie</th><th>Subsubtaak/Submijlpaal</th><th>Gelinkte personeelsleden</th></tr>
              <tr><td>#SUBTAAK_ID# #SUBTAAK_MIJLPAAL_OMSCHRIJVING#</br>Duiding: #DUIDING#</br>Beleidsvlaggen: #BELEIDSVLAGGEN#</br>Deadline: #DEADLINE#</br>Status: #STATUS#</br>Nummer: #NUMMER#</br>Regelgeving: #REGELGEVING#</td><td>Verantwoordelijke: #VERANTWOORDELIJKE#</br>Andere: #ANDERE_PERSONEELSLEDEN#</td><td></td><td>Subsubtaak of submijlpaal: #SUBSUBTAAK_SUBMIJLPAAL#</br>#SUBSUBTAAK_ID# #SUBSUBTAAK_OMSCHR#</br>Duiding: #SUB_DUIDING#</br>Deadline: #SUB_DEADLINE#</br>Status: #SUB_STATUS#</td><td>Verantwoordelijke: #NAAM_VERANTW#</br>Andere: #ANDERE_PL_SUBSUB#</td></tr>
          </table>
          </div>

       

      Tell me if you need the SQL as well and I'll post it.

       

      I am currently working with the test environment Oracle supplied us, because we are testing if we can use APEX at our company.

      The version is Application Express 4.2.4.00.07 and it's running on Oracle 11g.

       

      Kind regards,

      NDG

        • 1. Re: Custom template: table within a table
          Mike Kutz

          It sounds like you need to build a Region Plugin.

           

          Some examples:

          http://www.apex-plugin.com/oracle-apex-plugins/region-plugin/skillbuilders-calendar_94.html

          http://www.apex-plugin.com/oracle-apex-plugins/region-plugin/google-visualizations-orgchart_187.html

           

          the 'super-cool' way

          Basically, you need to develop the javascript that uses jQuery, JS, CSS to transform data that is in JSON format into the way you want it to be displayed.

          The AJAX pl/sql procedure would be called whenever the GUI needs [more] data.  (that just 'spits out' the SQL results in JSON format)

           

          the 'cheating' way

          The 'render' portion of the plugin runs through the data and hand-builds out the <table> code via PL/SQL LOOPs.

          I've created one region plugin like this.  It works, but I'm (currently) stuck with the fonts and color I hard-coded in the PL/SQL procedure.

           

          MK

          • 2. Re: Custom template: table within a table
            fac586

            NDG123 wrote:

             

            I recently started working with a custom template for the first time. I'm already pretty far in getting what I want to accomplish, but I have no idea how to implement what I'm going to describe here.

            In my application I need data from 3 tables (simplified, it's actually more than that). The data in these tables have a 1 to many relationship. The data is as follows: 1 activity has many tasks, 1 task has many subtasks.

             

            I would like to show a screenshot but looks like I can't post pictures here, so simplified, what I already have is:

            Activity1

                 Task1

                      Subtask1

            Activity1

                 Task1

                      Subtask2

            Activity1

                 Task2

                      Subtask3

            Activity1

                 Task2

                      Subtask4

            Here he repeats my template for every row

             

            But what I would like to have:

            Activity1

                 Task1

                      Subtask1

                      Subtask2

                 Task2

                      Subtask3

                      Subtask4

            Here he does not repeat the template for every row, but adapts it according to the data. The number of tasks within an activity and the number of subtasks within a task are not fixed.

            Can anyone tell me how to accomplish this?

             

            The code of my template:

                <div class="complex"> 
                <table cellpadding="0" border="0" cellspacing="0"> 
                    <tr><td>#SOD#</td></tr> 
                    <tr><td>#OOD#</td></tr> 
                    <tr><td>#ACT#</td></tr> 
                </table> 

            Be wary of overusing tables. Other HTML structures are available. The preceding data looks like a list.

                <table cellpadding="0" border="0" cellspacing="0"> 
                    <tr><th>Taak</th><th>Werknemer Naam</th></tr>
                    <tr><td>#TAAK#</br>Master afdeling: #AFD_CODE#</br>Belmo nummer: #BELMO#</br>Planning LA: #PLANNING_LA#</td><td>#WN_NAAM#</td><td>Voeg subtaak/mijlpaal toe</td></tr>
                </table>
                <h2>#SUBTAAK_OF_MIJLPAAL#</h2> 
                <table cellpadding="0" border="0" cellspacing="0"> 
                    <tr><th>Subtaak/Mijlpaal</th><th>Gelinkte personeelsleden</th><th>Actie</th><th>Subsubtaak/Submijlpaal</th><th>Gelinkte personeelsleden</th></tr>
                    <tr><td>#SUBTAAK_ID# #SUBTAAK_MIJLPAAL_OMSCHRIJVING#</br>Duiding: #DUIDING#</br>Beleidsvlaggen: #BELEIDSVLAGGEN#</br>Deadline: #DEADLINE#</br>Status: #STATUS#</br>Nummer: #NUMMER#</br>Regelgeving: #REGELGEVING#</td><td>Verantwoordelijke: #VERANTWOORDELIJKE#</br>Andere: #ANDERE_PERSONEELSLEDEN#</td><td></td><td>Subsubtaak of submijlpaal: #SUBSUBTAAK_SUBMIJLPAAL#</br>#SUBSUBTAAK_ID# #SUBSUBTAAK_OMSCHR#</br>Duiding: #SUB_DUIDING#</br>Deadline: #SUB_DEADLINE#</br>Status: #SUB_STATUS#</td><td>Verantwoordelijke: #NAAM_VERANTW#</br>Andere: #ANDERE_PL_SUBSUB#</td></tr>
                </table>
                </div>

            How is this HTML organised across the template attributes? It looks like you need to make use of conditional row templates based on changing Activity/Task/Subtask, something like:

             

            Row Template 1

             

            Activity

                 Task

                      Subtask

             

            Row Template 1 Expression

             

            Activity != Activity of preceding row

             

            Row Template 2

             

                 Task

                      Subtask

             

            Row Template 2 Expression

             

            Task != Task of preceding row

             

            Row Template 3

             

                      Subtask

             

            In a similar way to the approach used in this previous thread.

             

            Analytic functions in the report query are useful in determining the boundaries between report partitions. The restriction to only 4 conditional row templates is limiting. Assuming you are using an HTML5 theme, adopting HTML5 mark-up conventions that allow some implied closing tags (e.g. </tbody>, </tr>, </th>, </td>) to be omitted is helpful. Note that the self-closing break element is written <br/>, not </br>. (And that I still consider using it in this way to be non-conforming.)

            Tell me if you need the SQL as well and I'll post it.

            A better way to get help with problems of this nature is to create an example with sample data on an apex.oracle.com workspace and share guest developer credentials here so we can see and work with the query, report, and templates ourselves.

            I am currently working with the test environment Oracle supplied us, because we are testing if we can use APEX at our company.

            The version is Application Express 4.2.4.00.07 and it's running on Oracle 11g.

            For problems like this you need to identify the theme you are using, and the browser(s)/version(s) the application will run in so we can provide appropriate advice.

            • 3. Re: Custom template: table within a table
              NDG123

              Thank you for your swift responses.

               

              MK, I don't think your solution is suited for me as I am under some timepressure and have no experience with Region plugins, jQuery, JS and the JSON format. I'm a BI-consultant and not a webdeveloper, but I do appreciate and thank you for your contribution.

               

              fac, thank you for the tips about using a list and <br/> in stead of </br> (</br> seems to work as well though, weird).

              I have to admit I have no idea what to respond to some of your questions, like 'How is this HTML organised across the template attributes?' and which theme I'm using.

              I started the template from scratch, it's a 'Standard'-template class and it's the 'Named Column (row template)'-type. I cannot elaborate on the version of the browser, the application would be used by multiple users each with their own browser/version, but starting with IE version 9 will be our best bet.

               

              I'll try and set up a workspace but this might take a while as I'll have to adjust the data because there is some sensitive information in there which I can't just throw on the web. I'll get back to you when the environment is there ready for you to work with.

               

              Thanks again,

              Kind regards,

              NDG

              • 4. Re: Custom template: table within a table
                fac586

                NDG123 wrote:

                 

                I have to admit I have no idea what to respond to some of your questions, like 'How is this HTML organised across the template attributes?'

                The template definition consists of a number of attributes. The main HTML structure for the report is contained in the Row Template and Before/After Rows attributes. How are these used in your template?

                and which theme I'm using.

                Themes are collections of templates, CSS files and JS scripts that control the look and feel of APEX applications. Go to Home > Application Builder > Application > Shared Components > Themes to find out which theme is currently being used.

                • 6. Re: Custom template: table within a table
                  NDG123

                  Hi,

                   

                  fac, currently I'm only using the "Row Template 1"-attribute. The theme I'm using is theme 26.

                   

                  AshleeShehan, thank your for that link, it does seem to do what I want my application to do, I'll have a look at it!

                   

                  Kind regards,

                  NDG

                  • 7. Re: Custom template: table within a table
                    fac586

                    NDG123 wrote:

                     

                    fac, currently I'm only using the "Row Template 1"-attribute.

                    As described above, you'll need to use multiple conditional row templates to repeat rows at different levels.

                    The theme I'm using is theme 26.

                    That's an HTML5 theme, so the table mark-up can be simplified by removing closing </tr>, </th>, and </td> tags.

                    AshleeShehan, thank your for that link, it does seem to do what I want my application to do, I'll have a look at it!

                    Like plug-ins, it looks overcomplicated based on the information you've provided so far. Concentrate on the report template to begin with.

                    • 8. Re: Custom template: table within a table
                      NDG123

                      Hi fac,

                       

                      I've set up the workspace for you to work with:

                      Workspace: NDG

                      Username: guest

                      Password: test

                       

                      It's page 8 in application 35272.

                       

                      I've anonimised a lot of the data by using substrings, which is why it might look a bit weird, but the codes and ID's are unchanged and they are what's important. The data is in Dutch, I hope that's not a problem...

                       

                      A bit more information about the data:

                      The template has 3 parts to it, each with a 1 to many relationship to the next part

                      the first part is:

                      OOD

                      SOD
                      ACTIVITY

                       

                      The second part is:

                      Task, with some information about the task

                       

                      The third part is:

                      Subtask, with information about the subtask

                       

                       

                      Above the subtask you'll see 'Belmo mijlpaal' or 'Subtaak', these are two different kinds of subtasks (which I didn't mention in my original post). Ideally, the result would be:

                      Activity A

                           Task A

                                Subtask, the 'Subtaak' kind:

                                     Subtask A

                                     Subtask B

                                Subtask, the 'Belmo mijlpaal' kind:

                                     Subtask C

                                     Subtask D

                           Task B

                                Subtask, the 'Subtaak' kind:

                                     Subtask E

                                     Subtask F

                                Subtask, the 'Belmo mijlpaal' kind:

                                     Subtask G

                                     Subtask H

                       

                      Activity B

                      ...

                       

                      I hope it's clear to you.

                       

                      Let me know what you think!

                       

                      Kind regards,

                      Nils De Groote

                      • 9. Re: Custom template: table within a table
                        fac586

                        I've added page 9 as a copy of your original report page, and created 2 report regions on it. Both reports use your query, to which I have added 3 analytic columns and an order by clause:

                         

                        ...
                          , row_number()
                              over (
                                partition by op_activiteit.act_id
                                order by  op_taak.taak_id
                                        , op_teamplanning.subtaak_mijlpaal desc
                                        , op_teamplanning.subtaak_id)                                   activity_rn
                          , row_number()
                              over (
                                partition by op_activiteit.act_id, op_taak.taak_id
                                order by  op_teamplanning.subtaak_mijlpaal desc
                                        , op_teamplanning.subtaak_id)                                   task_rn
                          , case
                              when   row_number()
                                       over (
                                         partition by op_activiteit.act_id
                                         order by op_taak.taak_id
                                                , op_teamplanning.subtaak_mijlpaal desc
                                                , op_teamplanning.subtaak_id)
                                   = count(*)
                                       over (
                                         partition by op_activiteit.act_id)
                              then
                                '</div>'
                              else
                                null
                            end                                                                         close_activity
                        ...
                        order by
                            op_activiteit.act_id
                          , op_taak.taak_id
                          , op_teamplanning.subtaak_mijlpaal desc
                          , op_teamplanning.subtaak_id
                        
                        
                        

                         

                        The activity_rn column numbers each row within an activity, the task_rn column numbers each row within a task, and close_activity generates a closing tag for the activity container div when a row is the final subtask for an activity. The order by clause matching the ordering in the analytic columns is necessary to render the hierarchy of subtasks within tasks within activities.

                         

                        The Standard Template View uses the Standard report template to show all of the query data and display the values of the new columns to illustrate how they are used in Row Template Expressions. The Custom Template View shows the report using this template:

                         

                        Row Template 1

                        <div class="complex">
                          <table cellpadding="0" border="0" cellspacing="0">
                            <tr> <td> #SOD#
                            <tr> <td> #OOD#
                            <tr> <td> #ACT#
                          </table>
                          <table cellpadding="0" border="0" cellspacing="0">
                            <tr>
                              <th> Taak
                              <th> Werknemer Naam
                            <tr>
                              <td>
                                #TAAK# <br/>
                                Master afdeling: #AFD_CODE# <br/>
                                Belmo nummer: #BELMO# <br/>
                                Planning LA: #PLANNING_LA#
                              <td>
                                #WN_NAAM#
                              <td>
                                Voeg subtaak/mijlpaal toe
                          </table>
                          <h2>#SUBTAAK_OF_MIJLPAAL#</h2>
                          <table cellpadding="0" border="0" cellspacing="0">
                            <tr>
                              <th> Subtaak/Mijlpaal
                              <th> Gelinkte personeelsleden
                              <th> Actie
                              <th> Subsubtaak/Submijlpaal
                              <th> Gelinkte personeelsleden
                            <tr>
                              <td>
                                #SUBTAAK_ID# #SUBTAAK_MIJLPAAL_OMSCHRIJVING# <br/>
                                Duiding: #DUIDING# <br/>
                                Beleidsvlaggen: #BELEIDSVLAGGEN# <br/>
                                Deadline: #DEADLINE# <br/>
                                Status: #STATUS# <br/>
                                Nummer: #NUMMER# <br/>
                                Regelgeving: #REGELGEVING#
                              <td>
                                Verantwoordelijke: #VERANTWOORDELIJKE# <br/>
                                Andere: #ANDERE_PERSONEELSLEDEN#
                              <td>
                              <td>
                                Subsubtaak of submijlpaal: #SUBSUBTAAK_SUBMIJLPAAL# <br/>
                                #SUBSUBTAAK_ID# #SUBSUBTAAK_OMSCHR# <br/>
                                Duiding: #SUB_DUIDING# <br/>
                                Deadline: #SUB_DEADLINE# <br/>
                                Status: #SUB_STATUS#
                              <td>
                                Verantwoordelijke: #NAAM_VERANTW# <br/>
                                Andere: #ANDERE_PL_SUBSUB#
                          </table>
                        #CLOSE_ACTIVITY#
                        
                        
                        

                         

                        Row Template 1 Condition

                        Use Based on PL/SQL Expression

                         

                        Row Template 1 Expression

                        #ACTIVITY_RN# = 1

                         

                        Row Template 1 is used for the first row of an activity, displaying the activity information, its first task, and its first subtask.

                         

                        Row Template 2

                          <table cellpadding="0" border="0" cellspacing="0">
                            <tr>
                              <th> Taak
                              <th> Werknemer Naam
                            <tr>
                              <td>
                                #TAAK# <br/>
                                Master afdeling: #AFD_CODE# <br/>
                                Belmo nummer: #BELMO# <br/>
                                Planning LA: #PLANNING_LA#
                              <td>
                                #WN_NAAM#
                              <td>
                                Voeg subtaak/mijlpaal toe
                          </table>
                          <h2>#SUBTAAK_OF_MIJLPAAL#</h2>
                          <table cellpadding="0" border="0" cellspacing="0">
                            <tr>
                              <th> Subtaak/Mijlpaal
                              <th> Gelinkte personeelsleden
                              <th> Actie
                              <th> Subsubtaak/Submijlpaal
                              <th> Gelinkte personeelsleden
                            <tr>
                              <td>
                                #SUBTAAK_ID# #SUBTAAK_MIJLPAAL_OMSCHRIJVING# <br/>
                                Duiding: #DUIDING# <br/>
                                Beleidsvlaggen: #BELEIDSVLAGGEN# <br/>
                                Deadline: #DEADLINE# <br/>
                                Status: #STATUS# <br/>
                                Nummer: #NUMMER# <br/>
                                Regelgeving: #REGELGEVING#
                              <td>
                                Verantwoordelijke: #VERANTWOORDELIJKE# <br/>
                                Andere: #ANDERE_PERSONEELSLEDEN#
                              <td>
                              <td>
                                Subsubtaak of submijlpaal: #SUBSUBTAAK_SUBMIJLPAAL# <br/>
                                #SUBSUBTAAK_ID# #SUBSUBTAAK_OMSCHR# <br/>
                                Duiding: #SUB_DUIDING# <br/>
                                Deadline: #SUB_DEADLINE# <br/>
                                Status: #SUB_STATUS#
                              <td>
                                Verantwoordelijke: #NAAM_VERANTW# <br/>
                                Andere: #ANDERE_PL_SUBSUB#
                          </table>
                        #CLOSE_ACTIVITY#
                        
                        
                        

                         

                        Row Template 2 Condition

                        Use Based on PL/SQL Expression

                         

                        Row Template 2 Expression

                        #TASK_RN# = 1

                         

                        Row Template 2 is used for the first row of a task that is not the first in an activity, displaying the task and its first subtask.


                        Row Template 3

                          <h2>#SUBTAAK_OF_MIJLPAAL#</h2>
                          <table cellpadding="0" border="0" cellspacing="0">
                            <tr>
                              <th> Subtaak/Mijlpaal
                              <th> Gelinkte personeelsleden
                              <th> Actie
                              <th> Subsubtaak/Submijlpaal
                              <th> Gelinkte personeelsleden
                            <tr>
                              <td>
                                #SUBTAAK_ID# #SUBTAAK_MIJLPAAL_OMSCHRIJVING# <br/>
                                Duiding: #DUIDING# <br/>
                                Beleidsvlaggen: #BELEIDSVLAGGEN# <br/>
                                Deadline: #DEADLINE# <br/>
                                Status: #STATUS# <br/>
                                Nummer: #NUMMER# <br/>
                                Regelgeving: #REGELGEVING#
                              <td>
                                Verantwoordelijke: #VERANTWOORDELIJKE# <br/>
                                Andere: #ANDERE_PERSONEELSLEDEN#
                              <td>
                              <td>
                                Subsubtaak of submijlpaal: #SUBSUBTAAK_SUBMIJLPAAL# <br/>
                                #SUBSUBTAAK_ID# #SUBSUBTAAK_OMSCHR# <br/>
                                Duiding: #SUB_DUIDING# <br/>
                                Deadline: #SUB_DEADLINE# <br/>
                                Status: #SUB_STATUS#
                              <td>
                                Verantwoordelijke: #NAAM_VERANTW# <br/>
                                Andere: #ANDERE_PL_SUBSUB#
                          </table>
                        #CLOSE_ACTIVITY#
                        
                        
                        

                         

                        Row Template 3 is the default template, used used to display subtasks following the first row of activities and tasks.

                         

                        All templates include the #CLOSE_ACTIVITY# column to automatically close the activity container if the template is used to render the activity's last subtask.

                         

                        As noted above, I have concerns about the HTML structure you are using here, but not knowing the application or data (not to mention the language!) I've not made any changes in that respect. After seeing the query I've got concerns about that (and the data model) as well. It certainly looks like the query could be simplified.

                         

                        Ordering and pagination need to be carefully considered with a multilevel report and template of this nature.

                         

                        Users can't apply ad hoc ordering by clicking table headings, and a known order must be used to maintain the activity/task/subtask hierarchy. If you want users to be able to sort activities/tasks/subtasks, then you have to provide some kind of additional control to allow them to specify this, and use the specified order in the conditional row template mechanism as well as the overall report order.

                         

                        APEX pagination is problematic where a single physical row in the query results maps to multiple logical "rows" or levels in the report layout. The simplest approach is to use no pagination, and construct the app to apply filters to the report so that the result set does not contain an excessive number of rows.

                        • 10. Re: Custom template: table within a table
                          NDG123

                          Hi fac,

                           

                          Wow, this is exacty what I needed, thank you!

                           

                          About your concerns, this application is going to replace an existing application made with completely different software. Because of this I'm trying to create the exact same structure for displaying the data as what the users are used to from the old application.

                           

                          The data models and the queries that go with them are not in my hands, this is a model that has existed for quite some time and is used across the whole organisation. I will have to export the data from the datawarehouse into Apex and I think changing the datamodel with or for those exports is not a great idea, the data in the application should resemble the data in the datawarehouse.

                           

                          It's not necessary for the users to be able to sort the data, this was not possible in the old application and with the right filters the data should be limited which means they have no need for it.

                           

                          I am indeed going to add filters so that users can get only the data they need, but I wanted to have the structure right first to see if what I wanted was at all possible.

                           

                          Thank you again for your great help fac!

                           

                          Kind regards,

                          NDG