Focal Point
[SOLVED]Drill Throughs on an HTML5 Chart Extension

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7527093396

May 12, 2020, 01:28 PM
Berny
[SOLVED]Drill Throughs on an HTML5 Chart Extension
I am using one of IB's HTML5 chart extension, datatables grid. We have a drill through on the chart, but it only works for the results on the first page. Anyone have any luck with getting drill throughs to work?

 GRAPH FILE SPREAD_RANKED
SUM SPREAD_RANKED.SPREAD_R.NETSPREADAMT AS 'Spread'
SPREAD_RANKED.SPREAD_R.NETSPREADCHANGEAMT AS 'vs. Prev Wk'
SPREAD_RANKED.SPREAD_R.EMPLOYEESAMT AS 'Contr'
SPREAD_RANKED.SPREAD_R.EMPLOYEESCHANGEAMT AS 'vs. Prev Wk'
SPREAD_RANKED.SPREAD_R.ACCOUNTSAMT AS 'Acct'
SPREAD_RANKED.SPREAD_R.ACCOUNTSCHANGEAMT AS 'vs. Prev Wk'
BY SPREAD_RANKED.SPREAD_R.RANK AS 'Rank'
BY SPREAD_RANKED.SPREAD_R.INTERNALEMPLNAME AS 'Producer'
BY SPREAD_RANKED.SPREAD_R.INTERNALEMPLID AS 'Empl ID'
BY SPREAD_RANKED.SPREAD_R.CURRENTROLEID AS 'Role'
BY SPREAD_RANKED.SPREAD_R.OFFICEID_NAME AS 'Office'
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET AUTOFIT ON
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET LOOKGRAPH EXTENSION
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setPlace(true);
setCurveFitEquationDisplay(false);
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, $
TYPE=DATA, COLUMN=N1, BUCKET= >row, $
TYPE=DATA, COLUMN=N2, BUCKET= >row, $
TYPE=DATA, COLUMN=N3, BUCKET= >row, $
TYPE=DATA, COLUMN=N4, BUCKET= >row, $
TYPE=DATA, COLUMN=N5, BUCKET= >row, $
TYPE=DATA, COLUMN=N6, ALT='aer_producer_page_call',
TARGET='_blank', BUCKET= >measure,
FOCEXEC=IBFS:/WFC/Repository/aerrepor/std_reports/reportsm0jyr/producerir6x/aer_producer_page_call.fex(
EMPLID=SPREAD_RANKED.SPREAD_R.INTERNALEMPLID
WEEK_END_DATE=&MDYY_WEEKENDINGDATE.QUOTEDSTRING), $
TYPE=DATA, COLUMN=N7, BUCKET= >measure, $
TYPE=DATA, COLUMN=N8, BUCKET= >measure, $
TYPE=DATA, COLUMN=N9, BUCKET= >measure, $
TYPE=DATA, COLUMN=N10, BUCKET= >measure, $
TYPE=DATA, COLUMN=N11, BUCKET= >measure, $
*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
*GRAPH_JS_FINAL
"pieProperties": {
    "holeSize": "0%"
},
"chartType": "com.ibi.datatables",
"agnosticSettings": {
    "chartTypeFullName": "com.ibi.datatables"
}
*END
ENDSTYLE
END 


WebFocus 8202, Windows

This message has been edited. Last edited by: Berny,
May 13, 2020, 04:09 PM
Hallway
The DataTables library is an awesome library for creating tables. We use it quite often. However, we don't use the extension as it seems pretty limited, where the full DataTables library is chuck full of configuration options. Check out all their examples here: DataTables Examples

To use the library outside of the extension, all you have to do is HOLD the table request as FORMAT HTMTABLE, add a few CSS classes, and then use an HTMLFORM block to manually create an HTML page and include the libraries the the head element. There is a caveat in that IBI doesn't seem to believe in using a thead element in their output, so you need to do a little jQuery to manipulate the table a bit. However, that's not too difficult. The anchor tags pass just fine in the HTMTABLE and work without issue. The example below, I have done a basic datatables configuration and included the Bootstrap4 styling framework.
 
SET BYDISPLAY = ON
SET CENT-ZERO = ON
SET PAGE-NUM = OFF
SET HOLDMISS = ON
SET HOLDLIST = PRINTONLY
SET NODATA = ''
SET TITLES = OFF
SET ASNAMES = OFF
SET HTMLCSS = OFF
SET HOLDATTRS = ON

TABLE FILE CAR
SUM DEALER_COST/D12M
RETAIL_COST/D12M
SALES/D12M
BY COUNTRY
BY CAR
BY MODEL
BY LENGTH
BY WIDTH
BY HEIGHT
BY WEIGHT
BY WHEELBASE
BY FUEL_CAP
BY BHP
BY RPM
BY MPG
BY ACCEL
ON TABLE HOLD FORMAT HTMTABLE AS HOLD_HTMTABLE
ON TABLE SET STYLE *
TYPE=REPORT, LINES-PER-PAGE=UNLIMITED, $
TYPE=ACROSSTITLE, CLASS='tblHead acrossTitle', $
TYPE=ACROSSVALUE, CLASS='tblHead acrossValue', $
TYPE=GRANDTOTAL, CLASS='tblFoot', $
TYPE=TITLE, CLASS='tblHead', $
TYPE=DATA, CLASS='tblData', $
TYPE=DATA, COLUMN=N2, ALT='Drill Down 1', TARGET='_blank', URL=https://www.google.com/search?(q=COMP.CAR),CLASS='tblData link', $
ENDSTYLE
END
-RUN

-HTMLFORM BEGIN NOEVAL
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Datatables Example</title>
    <link rel="shortcut icon" type="image/png" href="https://datatables.net/media/images/favicon.png">
    <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" />
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.css" />

    <script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/js/bootstrap.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.js"></script>
</head>
<body>
    <main style="margin: 10px;">
        <section id="datatable_outer">
            !IBI.FIL.HOLD_HTMTABLE;
        </section>
    </main>
    <script>
        $(document).ready(function () {

            //Add an ID and bootstrap classes to the table https://getbootstrap.com/docs/4.4/content/tables/
            $('#datatable_outer').find('table')
                .attr('id', 'ibiTable')
                .addClass('table table-sm table-striped table-hover table-bordered');

            //Create a <thead> element and move any titles to the header. 
            $('#ibiTable').prepend('<thead/>');
            $('#ibiTable').find('.tblHead').closest('tr').prependTo($('#ibiTable').find('thead'));

            //Change <td> elements in <thead> to <th> and give the new th all the td attributes
            $('#ibiTable thead td.tblHead').each(function () {
                const $td = $(this);
                $td.wrapInner('<th/>');
                const $th = ($(this).find('th'));
                $th.addClass($(this).attr('class'));
                const $cl = $(this).attr('align') === 'RIGHT' ? $th.addClass('alignRight') : '';
                const $cs = $(this).attr('colspan') !== undefined ? $th.attr('colspan', $(this).attr('colspan')) : '';
                $th.unwrap();
            });

            //create Datatable
            $('#ibiTable').DataTable();
        });
    </script>
</body>
</html>
-HTMLFORM END
 

This message has been edited. Last edited by: Hallway,


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
May 14, 2020, 04:00 PM
Berny
Thanks for the info! I will definitely check this out. We did find other limitations with the html 5 chart extension, so we will definitely check out this avenue. Quick question, does this seem to work well on mobile? If not, that's okay. I will do some exploring to make sure.
May 14, 2020, 04:48 PM
Hallway
It does work well on mobile. You can either swipe on the table to view the different columns, or the have a Responsive extension that optimizes the table's layout for different screen sizes through the dynamic insertion and removal of columns from the table, and adds a button to the side that expands the record to see all the fields. See the examples here: https://datatables.net/extensi...isation/default.html

This message has been edited. Last edited by: Hallway,


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
May 15, 2020, 02:40 PM
Berny
Thanks! I will mark this as resolved.