8 Replies Latest reply on Aug 10, 2017 7:39 PM by Denis Savenko

    Best practices to implement a dynamic matrix report

    Denis Savenko

      Hello, everybody!

       

      I am fresh to APEX development, and have a really nice question to the masters. I've sorted out standard report types in apex (thanks to the splendid curriculum), but still don't know what's the best way to solve our business issues.

       

      So, let's imagine we have such a query:

      select 
           col1,
           col2,
           val1,
           val2,
           val3,
           val4,
           val5,
           val6,
           val7,
           val8,
           val9,
           val10,
           val11
      from table(mega_function(city => ?, format => ?, percent => ?, days => ?));
      

       

      And this query returns something like this:

      col1col2val1val2val3
      val4val5val6val7val8val9val10val11
      S1C12870012015:35:011501201500180027006028900120
      S1C22700024014:44:230150024012002550060nullnull
      S2C13200012015:38:281450120150012003100012032600300
      ...

       

      So, as we can see, nothing difficult so far - there is a query from a pipelined funtion and there's a result set. So, the tricky part is that we need to show it in a matrix way with applying different styling techniques.

      Something like this (clickable):https://drive.google.com/file/d/0B5nJc4UwogRSWDBtb2NBaGtDQms/view?usp=sharing

      Example Report.png

       

      Number of columns and rows are dynamic as you can see (could be one column and could be 100, so to use a PIVOT query is not a way due to intransparency and low performance. So I'd prefer just to show (manipulate the data on the 'view' level) the data in a desired way.

       

      What I understood so far, is that I'm able to complete my task using "Dynamic PL/SQL Content" Region with custom HTML markup using HTP and APEX API. But it doesn't seem the nicest and easiest way (a lot of HTML code, and I have no clue how to implement sortable rows using jQuery UI components).

       

      So, maybe my task only seems not typical and difficult? Are there any better ways to achieve desired result? Classic report with a custom template? Plugin? Anything else?

        • 2. Re: Best practises to implement a dynamic matrix report
          Denis Savenko

          Named column row template https://www.eberapp.com/ords/f?p=BLOG:READ:0::::ARTICLE:5976400346831048

          Will have a look into this, but from the first sight seems to be a bit complex solution.

           

           

          IR Pivot https://docs.oracle.com/cd/E71588_01/AEEUG/managing-pivot-reports.htm#AEEUG29137

          Already thought about this. The question is - is there a way to customize the report in a way we want? What should I do for this? Develop a new IR Pivot template?

          And the second question - what's inside of this all? Is it a good solution in terms of performance? We're going to dynamically refresh the report every, let's say, 3-5 seconds (using a dynamic action, I guess), is it suitable for such a task?

           

          Plugin: https://apex.world/ords/f?p=100:710:6917516576778::NO:RP,710:P710_PLG_ID:NL.SMART4APEX.PIVOT

          I've seen this plugin, and it looks nice, but it's paid and quite expensive for us

          • 3. Re: Best practises to implement a dynamic matrix report
            Scott Wesley

            Sounds like you're trying to do a complex thing, especially attempting to pivot n columns. That almost requires some XML?

             

            Pivoting in IR is only really good for numeric data. You're going to have a bad time with strings.

            • 4. Re: Best practises to implement a dynamic matrix report
              Denis Savenko

              I've made a demo application where I tried to show what I want to achieve (at least in terms of PIVOTing the report, without many value columns and without different styles) - https://apex.oracle.com/pls/apex/f?p=132832:2

               

              Things to be explained:

              - There's a table named 'TEST', and there's an editable interactive grid on it (the top region on the page).

              - There's a classic report on a PL/SQL function returning a query (the below region). Source of this PL/SQL function is following:

              create or replace function go_pivot return varchar2
                is
                    l_query long := 'select col2';
                begin
                    for x in (select distinct col1 from test order by col1)
                    loop
                        l_query := l_query ||
                           replace( q'|, sum(decode(col1,'$X$',v)) $X$|',
                                    '$X$',
                                   x.col1);
                   end loop;
              
                   l_query := l_query || ' from test group by col2';
              
              return l_query;
               end;
              

              As we see, the function generates a dynamic SQL query which implements PIVOT logic using SUM aggregate function (actually it doesn't matter what aggregate function is here). Notice the fact that values of col1 are ordered.

              - There's another PL/SQL function get_headings() which returns string for heading in the format 'heading1:heading2:heading3:..' as values of col1:

              create or replace function get_headings return varchar2 is
                l_headings varchar2(4000) := 'row';
              begin
                for c in (select distinct col1 from test order by 1)
              loop
              l_headings := l_headings||':'||to_char(c.col1);
              end loop;
                return l_headings;
              end;
              

              - In the properties of the classic report under the section "Source" for "Use generic column names" set to "Yes" in order to disable parsing the query by APEX engine (it allows me to add new columns to the dynamic query generated by the go_pivot() function).

              - In the attributes of the classic report under the section "Heading" for "Type" set to "PL/SQL Function body", and for "PL/SQL Function body" set "return get_headings()".

               

              After doing all these, we can see correct headings as values of col2 and dynamic rebuilding of the report works (you can try it out by adding some new rows using interactive grid and then by pressing the button 'commit').

               

              The problem is that I think it's kind of an ad-hoc solution - I always try to avoid using dynamic SQL, especially on a regular basis (and this report is going to be dynamically updated every several seconds). Performance of this stuff is unpredictable, headings are not concerted with the data and so on.. What I'd really want to know - if there a way just to manipulate the data on the 'view' level, without re-writing the query every time when I refresh the report.

              • 5. Re: Best practises to implement a dynamic matrix report
                Denis Savenko

                Just left it here to have a look:

                 

                Matrix report using PIVOT XML - Matrix report

                • 7. Re: Best practises to implement a dynamic matrix report
                  Client_321

                  Maybe you should use jasperreport tool

                  • 8. Re: Best practices to implement a dynamic matrix report
                    Denis Savenko

                    I guess it's high time I shared the results of what I have so far. Many thanks to swesley_perth, who suggested several options to look into, but unfortunately, none of them meets all my requirements.

                     

                    Main problems are hidden in the conditions in which my report will live:

                    1. Data should be dynamically updated every, let's say, 5 seconds. State of the report should persist over data updates.
                    2. Number of columns of a report is variable (definition of columns is provided with data), number of rows is variable as well of course.
                    3. Report should have sorting, pagination and scrolling (by X and Y) options. All the stuff (sorting, etc.) should be done on client-side.
                    4. Styles and custom cell rendering should be applied to cells of the table.
                    5. Cells should be clickable (click should generate an event, which is interceptable).

                     

                    My research implied following approaches:

                    • Interactive Report Pivot functionality (https://docs.oracle.com/cd/E71588_01/AEEUG/managing-pivot-reports.htm#AEEUG29137) - lacks customization, works badly with many values, especially when they are not numbers, updates are slow, all the interactivity stuff is done in the APEX engine (on backend), have no clue how to make cells clickable.
                    • Classic report based on Function - I have implemented PL/SQL function which generates dynamic PIVOT SQL query (varchar2 string), in the properties of the report Use Generic Column Names is set to Yes (in order to parse the query only in runtime, otherwise when the number of coulmns changes, the report stops working), for headings of the report I used another PL/SQL function, which generates a string in the format heading1:headning2:...:headingN.The solution works, but refreshing the data just sucks in terms of performance (dynamic SQL is always bad and not managable way if we talk about execution plans). Also this solution doesn't fit, because headings are not concerted with the data (actually I used order by col1 in the queries in both PL/SQL functions to make headings be in their places).
                    • PL/SQL Dynamic Content Region - it's possible to do anything here by using HTP package and APEX API. The tricky thing is the fact that this solution is quite complex. If I chose this way, I'd need to implement all the logic of the report 'from scratch', all the logic of HTML, CSS and JavaScript generation would be concentrated on the backend. Data changes would trigger re-generations of all this, which is not a task for RDBMS (let's be sincere with ourselves).
                    • Interactive Grid - this thing doesn't give us PIVOT functionality yet, it can't be based on a PL/SQL function, it weakly documented, JS API is not obvious, so I gave up with it.

                     

                    I realized that for such a task it's better to manipulate DOM on-the-fly on client-side instead of using some out-of-the-box APEX solutions like classic reports, interactive reports or grids.

                     

                    With some help of StackOverflow I decided to have a look on DataTables.js jQuery plugin. After a week of estimating the technology and learning some basic JavaScript (which is not my primary skill as we know), I have the following.

                     

                    - I implemented an Ajax Callback process, it runs PL/SQL code, which returns JSON-object to SYS.HTP output (using APEX_JSON or HTP packages).

                     

                    - Then I created a Static Content region on the page, the source of which is following:

                    <div id="datatable_test_container"></div>
                    

                    - I uploaded CSS and JS files of DataTables.js to application static files and included them in the page properties.

                     

                    - In the JavaScript section for page for Function and Global Variable Declaration I added this javascript code:

                    var $ = apex.jQuery;
                    var table;
                    var columns;
                    var rows;
                    
                    //table initialization function
                    function table_init (json_data) {
                       return $('#datatable_test').DataTable({
                            //column defaults options
                            columnDefs: [
                                {
                                  "data": null,
                                  "defaultContent": "-",
                                  "targets": "_all"
                                }
                            ],
                            columns: json_data.columns,
                            data: json_data.data,
                            stateSave: true
                        });
                    }
                    //function to asynchronously get data from APEX AJAX CALLBACK process and then to draw a table based on this data
                    function worker() {
                        //run the process called TEST_JSON
                        apex.server.process(
                            "TEST_JSON", {}, {
                                success: function( pData ) {
                                    //on first run we need to initialize the table
                                    if (typeof table == 'undefined') {
                                        //save current data for future use
                                        columns = $.extend(true, [], pData.columns);
                                        rows = $.extend(true, [], pData.data);
                                        //generate empty html-table in the container
                                        $('#datatable_test_container').append('<table id="datatable_test" class="display" cellspacing="0" width="100%"></table>');
                                        //init the table
                                        table = table_init(pData);
                                    //when columns of the table changes we need to reinitialize the table (DataTables require it due to architecture)
                                    } else if (JSON.stringify(columns) != JSON.stringify(pData.columns)) {
                                        //save current data for future use
                                        columns = $.extend(true, [], pData.columns);
                                        rows = $.extend(true, [], pData.data);
                                        //delete the table from DOM
                                        table.destroy(true);
                                        //generate empty html-table in the container
                                        $('#datatable_test_container').append('<table id="datatable_test" class="display" cellspacing="0" width="100%"></table>');
                                        //reinit the table
                                        table = table_init(pData);
                                    }
                                    //if data changes, clear and re-draw the table
                                    else if (JSON.stringify(rows) != JSON.stringify(pData.data)) {
                                        //save current data for future use
                                        //we don't need to save the columns, they didn't change
                                        rows = $.extend(true, [], pData.data);
                                        //clear table, add rows from recieved JSON-object, re-draw the table with new data
                                        table.clear().rows.add(pData.data).draw(false);
                                    }
                                    //if nothing changes, we do nothing
                                }  
                            }
                        );
                        //repeat the procedure in a second
                        setTimeout(worker, 1000);
                    };
                    

                     

                    - For Execute when Page Loads I added:

                    $(document).ready(function() {
                        worker();
                    });
                    

                     

                    What all this does:

                    1. Static <div> on the page recieves an empty table where DataTables constructor then is applied.
                    2. The script starts its work by triggering the Ajax Callback server process, and on success uses the result this process returned.
                    3. DataTables constructor supports different types of data source, for example it can parse an html-table or make an ajax-call, but I preferred to use an APEX process and then base the table on the JSON-object, which this process returns.
                    4. Then the script watches changes. If set of columns changes, the table is deleted from document and re-initialized using new data, if only rows changes, then the table is just refreshed with this data. If nothing changes in data then script does nothing.
                    5. This process is repeated every second.

                     

                    As a result, I have totally interactive, being dynamically refreshed, report, with such options as sorting, paging, searching, event handling and so on. And all these is done on client-side without extra queries to server.

                    Live demo is here: https://apex.oracle.com/pls/apex/f?p=132832:3 (the top region is DataTables report, below it there's an editable interactive grid on the source table, to see the changes, you can change data using the interactive grid).

                     

                    I don't know if this is the best approach, but it meets my requirements. Hope this solution could help somebody else.

                     

                    Cheers,

                    Denis