1 2 Previous Next 16 Replies Latest reply on Feb 15, 2015 11:58 AM by fac586

    Dynamic number of report regions

    partlycloudy

      APEX 4.2.6

       

      create table m(i int);
      insert into m select level from dual connect by level<=10;
      
      create table n(j varchar2(1));
      insert into n select chr(65+level-1)  from dual connect by level<=20;
      
      create table mn as
      select
      m.i,
      n.j,
      dbms_random.string('x',5) val
      from m,n,(select level from dual connect by level<=4) x;
      
      

       

      10 distinct values of M.I. 20 distinct values of N.J. For each value of M.I (select list on the page), I would like to show the 20 report regions (each with 4 rows) for each value of  N.J from table MN.

       

      Creating 200 report regions on the page and conditionally showing/hiding seems like a overkill/brute-foce approach. Also, if new value(s) are added to the tables, new regions would have to be created.

       

      [Yes, I know the whole thing can be stuffed into one Interactive Report with column header filters but the users were inquiring about this kind of layout]

       

      Is there a more elegant way to do this?

       

      Thanks

        • 1. Re: Dynamic number of report regions
          fac586

          I wondered what I was going to do this afternoon with no plane to catch!

           

          Any particular theme in mind?

          • 2. Re: Dynamic number of report regions
            partlycloudy

            Howdy Paul - Well, Theme 25 (Blue Responsive) would work nicely so the report regions would automatically change the grid layout based on the viewport.

            • 3. Re: Dynamic number of report regions
              fac586

              Pseudo-dynamic report regions first cut implemented with the usual custom named column report template and some CSS.


              "Pseudo" because we know there's no supported way to generate page regions dynamically. The next level depends on what you actually want these things to look like—titles, borders, grid layout, spacing etc—and how much standard region appearance/behaviour you're trying to incorporate.

              • 4. Re: Dynamic number of report regions
                partlycloudy

                Wow, that's amazing, how did you go about doing that?! Mind sharing the app export?

                 

                Well, the regions should behave like standard regions as much as possible. I don't believe I need report column links but I am going to need a Edit button in each region to go to a form page to edit the data in that report.

                • 5. Re: Dynamic number of report regions
                  partlycloudy

                  Wait, do you have 200 regions on the page? Also, how did you get the region to appear as a "table cell" in a larger container region?

                   

                  This is looking promising, just need to make sure I understand what's going on under the covers. Thanks.

                  • 6. Re: Re: Dynamic number of report regions
                    fac586

                    VANJ wrote:

                     

                    Wait, do you have 200 regions on the page?

                    Of course not.

                    Also, how did you get the region to appear as a "table cell" in a larger container region?

                    Just following the standard pattern for a report template. The APEX engine will only emit the pagination controls as a table row, so there has to be a table somewhere to act as a container for that. As pagination is not required in this case, I was actually planning on getting rid of the table container as things evolve...

                    This is looking promising, just need to make sure I understand what's going on under the covers. Thanks.

                    I'm going to continue experimenting with this, but here are the details of the first iteration to satisfy your curiosity.

                     

                    Report Definition

                     

                    The report query just returns the MN table rows that match the select list filter as specified above. The analytic rn and n column values are used to conditionally partition the rows in the report template.

                     

                    select
                        i
                      , j
                      , val
                      , row_number() over (partition by i, j order by j) rn
                      , count(*) over (partition by i, j) n
                    from
                        mn
                    where
                        i = :p44_m_i
                    order by
                        i
                      , j
                    
                    
                    
                    

                     

                    P44_M_I is included in Page Items to Submit to support partial page refresh via a dynamic action.

                     

                    Pagination is switched off and Number of Rows and Maximum Row Count both set to 80 to maximise row processing efficiency.

                     

                    Report Template

                     

                    The Dynamic multi-table report report template is created from scratch as a Named Column (row template). The Before Rows and After Rows definitions are copied from the theme 25 Standard report template so the custom report picks up the theme styling and has the necessary hooks for PPR.

                     

                    Before Rows

                     

                    <table class="uReportContainer" #REPORT_ATTRIBUTES# id="report_#REGION_STATIC_ID#">
                    <tbody class="uReportPagination">
                    #TOP_PAGINATION#
                    </tbody>
                    <tbody class="uReportBody">
                    <tr><td>
                    
                    
                    
                    


                    After Rows

                     

                    </td>
                    </tr>
                    </tbody>
                    <tbody class="uReportPagination">
                    #PAGINATION#
                    </tbody>
                    </table>
                    <div class="uReportDownloadLinks">#EXTERNAL_LINK##CSV_LINK#</div>
                    
                    
                    
                    

                     

                    The "magic" here is what we can achieve by combining analytics and logic from the report query with conditional row templates and HTML. Using the analytic rn and n column values from the report to detect a row's position within each partition, on the first row in each partition we open a new HTML table, output the table header row and first row of the partition. On the last row in each partition we output that row and close the table. A standard body row is output for the other rows.

                     

                    Row Template 1

                     

                    The Row Template 1 table attributes are also copied from the theme 25 Standard report template so the sub-reports reflect the theme styling.

                     

                    <table class="uReport uReportStandard">
                    <thead> <tr> <th id="#I##J#-I">I <th id="#I##J#-J">J <th id="#I##J#-VAL">VAL
                    <tbody>
                      <tr> <td headers="#I##J#-I">#I# <td headers="#I##J#-J">#J# <td headers="#I##J#-VAL">#VAL#
                    
                    
                    
                    

                     

                    Row Template 1 Condition

                     

                    #RN# = 1
                    
                    
                    
                    

                     

                    Row Template 2

                     

                      <tr> <td headers="#I##J#-I">#I# <td headers="#I##J#-J">#J# <td headers="#I##J#-VAL">#VAL#
                    </table>
                    
                    
                    
                    

                     

                    Row Template 2 Condition

                     

                    #RN# = #N#
                    
                    
                    
                    

                     

                    Row Template 3

                     

                      <tr> <td headers="#I##J#-I">#I# <td headers="#I##J#-J">#J# <td headers="#I##J#-VAL">#VAL#
                    
                    
                    
                    

                     

                    Finally we need a style sheet in the page Inline CSS property to control the layout of the sub-reports:

                     

                    .uReport.uReportStandard {
                      float: left;
                      margin: 2em 2em 0 0;
                      width: 10em;
                    }
                    
                    
                    
                    

                     

                     

                    Edit: Modified table cell id and header attributes so IDs are unique as required by HTML specification.

                    1 person found this helpful
                    • 7. Re: Re: Re: Dynamic number of report regions
                      partlycloudy

                      The "magic" here is what we can achieve by combining analytics and logic from the report query with conditional row templates and HTML.

                       

                      As always, that is very clever! So it is really one big report region, masquerading as many little report regions. That being the case, how can I stick a button in each "pseudo region" like I mentioned earlier? Or are you going to have another iteration out already before I finish posting this reply? ;-)

                      • 8. Re: Re: Re: Re: Dynamic number of report regions
                        fac586

                        VANJ wrote:

                         

                        The "magic" here is what we can achieve by combining analytics and logic from the report query with conditional row templates and HTML.

                         

                        As always, that is very clever! So it is really one big report region, masquerading as many little report regions. That being the case, how can I stick a button in each "pseudo region" like I mentioned earlier?

                        Done. I know you like to do things declaratively, so we can do this by adding a Column Link to the report from the Tasks list on the report page and modifying the report template slightly.

                         

                        In the Column Link, I've set the Link Text to <span><i></i></span> and Link Attributes to class="uButton iconOnly iconButton edit" title="Edit #I##J# data" in order to get the appearance of an icon button (thanks to Shakeeb for only using class selectors in the theme CSS!) You can set the target properties as required.

                         

                        In the report template I've added a table footer row as a container for the button:

                         

                        <table class="uReport uReportStandard">
                        <thead> <tr> <th id="#I##J#-I">I <th id="#I##J#-J">J <th id="#I##J#-VAL">VAL
                        <tfoot> <tr> <td colspan="3">#LINK$01#
                        <tbody>
                          <tr> <td headers="#I##J#-I">#I# <td headers="#I##J#-J">#J# <td headers="#I##J#-VAL">#VAL#
                        
                        

                         

                        I settled on the table footer as the appropriate structure for this after initially trying to put it into the VAL header cell. The semantics of that are highly questionable: the edit link applies to the entire pseudo-region, not just the VAL header or column. Next idea was to put it in an initial header row, but there's an accessibility problem with that as the edit button would appear before the data in a serialized representation. Using the footer keeps the button structurally within the table and associated with the related data. Adding the title to the link provides some context if it somehow randomly receives focus. My remaining concern is that visually it introduces rather a lot of unproductive space...

                         

                        A modified copy of the table header rule is added to the CSS to get the footer style to match:

                         

                        .uReportStandard tfoot>tr>td {
                          border-top: 1px solid #aaa;
                          padding: 4px;
                          background-color: #eee;
                          background-image: url('data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4gPHN2ZyB2ZXJzaW9uPSIxLjEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+PGRlZnM+PGxpbmVhckdyYWRpZW50IGlkPSJncmFkIiBncmFkaWVudFVuaXRzPSJ1c2VyU3BhY2VPblVzZSIgeDE9IjUwJSIgeTE9IjAlIiB4Mj0iNTAlIiB5Mj0iMTAwJSI+PHN0b3Agb2Zmc2V0PSIwJSIgc3RvcC1jb2xvcj0iI2Y4ZjhmOCIvPjxzdG9wIG9mZnNldD0iMTAwJSIgc3RvcC1jb2xvcj0iI2VlZWVlZSIvPjwvbGluZWFyR3JhZGllbnQ+PC9kZWZzPjxyZWN0IHg9IjAiIHk9IjAiIHdpZHRoPSIxMDAlIiBoZWlnaHQ9IjEwMCUiIGZpbGw9InVybCgjZ3JhZCkiIC8+PC9zdmc+IA==');
                          background-size: 100%;
                          background-image: -webkit-gradient(linear, 50% 0%, 50% 100%, color-stop(0%, #f8f8f8), color-stop(100%, #eeeeee));
                          background-image: -webkit-linear-gradient(#f8f8f8, #eeeeee);
                          background-image: -moz-linear-gradient(#f8f8f8, #eeeeee);
                          background-image: linear-gradient(#f8f8f8, #eeeeee);
                          text-shadow: 0 1px 0 rgba(255, 255, 255, 0.75);
                          text-align: right;
                        }
                        
                        

                         

                        Thinking about how to include the edit link/button raises the question of what your data actually is and whether this is really the best way to represent it? A wise man once said "reports aren't always tables", and—at least based on the sample data provided—that looks like it could be the case here. If there is really only one datum associated with each key combination then I wouldn't use a two dimensional table representation. The I value is repeated in every table, and the J value in every row within each table, which is a waste of space and a mind-numbing experience for anyone accessing the data serially using assistive technology. Usually I'd implement such a relationship hierarchically using lists, pulling the key values out as headings which would make the data much more accessible.

                        • 9. Re: Re: Re: Re: Re: Dynamic number of report regions
                          partlycloudy

                          Brilliant! You, sir, are a wizard with HTML and CSS.

                          what your data actually is and whether this is really the best way to represent it?

                          pulling the key values out as headings which would make the data much more accessible.

                           

                          You know what? You are right. I can map my data using weekly average temperatures. So m.i would be the year (2015, 2014, etc). N.J would be the month (January, February, etc). So each pseudo region would have the month (N.J) as the region header and have 4 rows (Week 1, Week 2, Week 3 and Week 4) and a handful of data points (Max/Min/Avg temperature). The intention behind this "report" then is to display 12 months/regions (but my real data has a dynamic number of "months")  for a selected year, each with a fixed set of rows/columns (weeks/temperatures in this example).

                           

                          Does that make your solution easier to implement? Mind changing it up to show how?

                           

                          Thanks a lot!

                          • 10. Re: Re: Dynamic number of report regions
                            fac586

                            VANJ wrote:

                             

                            The "magic" here is what we can achieve by combining analytics and logic from the report query with conditional row templates and HTML.

                             

                            As always, that is very clever! So it is really one big report region, masquerading as many little report regions. That being the case, how can I stick a button in each "pseudo region" like I mentioned earlier? Or are you going to have another iteration out already before I finish posting this reply? ;-)

                            I did another version with a report query that dynamically pulled region template HTML from APEX_APPLICATION_TEMP_REGION, split this around the #BODY# substitution string, and injected the resulting fragments into the report output. Basically it works but I couldn't get the links into the #EDIT# button position in the template using a declarative link (placeholder substitution in report templates isn't recursive), and the report query was getting hairy with string manipulation and regular expressions. Rather than trying to be that dynamic, if you really want the report results to look like regions then copy the region template HTML into the report template.

                             

                            As it also didn't look as good as the first iteration and your current requirements are better served by custom HTML I'm not going to pursue this line any further at present.

                            • 11. Re: Re: Re: Dynamic number of report regions
                              partlycloudy

                              As it also didn't look as good as the first iteration and your current requirements are better served by custom HTML I'm not going to pursue this line any further at present.

                               

                              Makes sense.

                               

                              Any thoughts on my last post on re-characterizing the data as weekly temperature stats for a given year? I am guessing all that is needed is to use the appropriate region template HTML to show a region heading with the Month. That would also address your accessibility concerns, right?

                               

                              Thanks

                              • 12. Re: Dynamic number of report regions
                                fac586

                                VANJ wrote:

                                 

                                As it also didn't look as good as the first iteration and your current requirements are better served by custom HTML I'm not going to pursue this line any further at present.

                                 

                                Makes sense.

                                 

                                Any thoughts on my last post on re-characterizing the data as weekly temperature stats for a given year? I am guessing all that is needed is to use the appropriate region template HTML to show a region heading with the Month. That would also address your accessibility concerns, right?

                                Yes, nearly there. I was just considering doing some final tidying up at the Before/After Rows level in the template seeing that pagination etc is not required. That needs to be done carefully to preserve PPR.

                                 

                                Also note that the headings look a whole lot better on my MacBook Pro than on my work PC. I'm sure you'll be up to a bit of final typographical tweaking...

                                • 13. Re: Dynamic number of report regions
                                  partlycloudy

                                  Wow, the YYYY Temperatures and month headings (with the edit link next to them) look gorgeous! Adding the Celsius/Fahrenheit selector is a nice touch, I can see a use for that in my real data  as well.

                                   

                                  Please post all the template details in the final iteration or if that's too much work just export the app/page components.

                                   

                                  Correct, pagination is not required but I just thought of something...I would need to provide the standard "Export to CSV" link somewhere. Since it is really one big report region, I suppose the out-of-the-box report export link would work (just need to tweak the columns that are visible on the screen or on the export using column conditions, right? Or are there some gotchas since the report uses a custom row-template? Or I guess I can always stick a button/link somewhere on the page that goes to a new page that just exports the raw data? Thoughts?

                                  • 14. Re: Dynamic number of report regions
                                    fac586

                                    VANJ wrote:

                                     

                                    Wow, the YYYY Temperatures and month headings (with the edit link next to them) look gorgeous!

                                    What are you viewing it on?

                                    Adding the Celsius/Fahrenheit selector is a nice touch, I can see a use for that in my real data  as well.

                                    I'd appreciate your thoughts on the best way(s) to implement that, given you have more domain knowledge than I do.

                                    Please post all the template details in the final iteration or if that's too much work just export the app/page components.

                                    Will do.

                                    Correct, pagination is not required but I just thought of something...I would need to provide the standard "Export to CSV" link somewhere. Since it is really one big report region, I suppose the out-of-the-box report export link would work (just need to tweak the columns that are visible on the screen or on the export using column conditions, right? Or are there some gotchas since the report uses a custom row-template? Or I guess I can always stick a button/link somewhere on the page that goes to a new page that just exports the raw data? Thoughts?

                                    OK. It's just a question of where to put the link. There won't be a problem with the data as custom templates don't affect report exports (people are usually concerned with the opposite problem of getting custom formatting into exports, particularly on interactive reports).

                                    1 2 Previous Next