[CLOSED] sorting is not working properly with jquery in html report
I need to give the sorting functionality to my report.I have searched in this forum. I got one snippet to give sorting functionality by combining the jquery and webfocus. Code is below.
SET BYDISPLAY=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
SET CENT-ZERO=ON
-RUN
TABLE FILE CAR
SUM
COMPUTE ROWCOUNT/P6 = ROWCOUNT + 1; NOPRINT
COMPUTE COMMA/A1 = IF ROWCOUNT EQ 1 THEN '' ELSE ',';
COMPUTE JS_ARRAY_VALUES/A500 =
'["' || COUNTRY || '","' ||
CAR || '","' ||
MODEL || '","' ||
TRIM('L', FPRINT(SALES,'D6','A10'), 10, ' ', 1, 'A10') || '","' ||
TRIM('L', FPRINT(WEIGHT,'D6','A10'), 10, ' ', 1, 'A10') || '"]';
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS HDATA1
END
-RUN
-HTMLFORM prefunding_online/webviewer1.htm
but in this code when i did the sorting for numeric field it is sorting like character wise sorting but not as a number. Same thing happening for date field also.
Is there any other logic to make this work?
How to make this work?This message has been edited. Last edited by: <Kathryn Henning>,
Web FOCUS 7.7.03 PDF
July 30, 2013, 02:05 PM
<Kathryn Henning>
Hi santu,
Which fields are sorting as alpha and not numeric? Are they SALES and WEIGHT? Please provide us with some specifics on this.
Regards,
Kathryn
July 30, 2013, 02:18 PM
Francis Mariani
I don't see the jQuery code you're using to add sorting capabilities. Whatever it is that your'e using, can't you define if a column contains alpha or numeric data?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
July 30, 2013, 02:53 PM
Doug
Why are you using jQuery to sort in a fex? Have you considered AHTML?
July 31, 2013, 05:28 AM
Twanette
AHTML would be ideal - but it requires a license for WebFOCUS Active Technologies.
You are trimming the left blank spaces, so the data will then sort alphabetically. Perhaps you should rather use leading zeros for the numeric data. Would this be an option? e.g. something like:
quote:
FPRINT(SALES,'P6L','A10') || '","' ||
WebFOCUS 8.2.06 mostly Windows Server
July 31, 2013, 05:29 AM
Twanette
Small correction:
quote:
FPRINT(SALES,'P6L','A6') || '","' ||
WebFOCUS 8.2.06 mostly Windows Server
July 31, 2013, 05:46 AM
santu
Doug: I had tried to use AHTML but i am facing below problem. So, i went for jquery
Not able to make the header fixed ?(means header is fixed and table should be scrollable).I have tried with hfreeze but it is not working in active html.
Kathryn Henning: SALES and WEIGHT columns are sorting as alpha because we are passing the dataset to jquery as a string(JS_ARRAY_VALUES/A500) and i am changing the column datatype. Is it possible to pass the dataset to jquery without changing the datatype of columns.
Francis Mariani: I have tried to add the numeric column to JS_ARRAY_VALUES/A500 without changing the column datatype(numeric to string) but data is not displaying on the screens. Is there any wayto pass the data without converting datatype?
Web FOCUS 7.7.03 PDF
July 31, 2013, 08:11 AM
santu
Twanette:
I have tried your solution it is sorting in number format only but i want to display the data in 12,000 format but not in 012000. Is there any way to display like that?
Web FOCUS 7.7.03 PDF
July 31, 2013, 09:23 AM
Doug
Consider:
...
BY (raw data field) NOPRINT BY (formatted field)
...
July 31, 2013, 09:49 AM
Francis Mariani
You have not specified which jQuery plugin you're using, but I will guess it's DataTables. If so, look into "mRender": jQuery > DataTables > Usage > Columns
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
August 01, 2013, 07:47 AM
<FreSte>
Below a working example. The trick is to have numeric data unformatted (no commas/periods as seperator), so I change the FPRINT from D6 to P6. Further I added a function to add the seperators after sorting
-Fred-
SET BYDISPLAY=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
SET CENT-ZERO=ON
-RUN
TABLE FILE CAR
SUM
COMPUTE ROWCOUNT/P6 = ROWCOUNT + 1; NOPRINT
COMPUTE COMMA/A1 = IF ROWCOUNT EQ 1 THEN '' ELSE ',';
COMPUTE JS_ARRAY_VALUES/A500 =
'["' || COUNTRY || '","' ||
CAR || '","' ||
MODEL || '","' ||
TRIM('L', FPRINT(SALES, 'P6','A10'), 10, ' ', 1, 'A10') || '","' ||
TRIM('L', FPRINT(WEIGHT,'P6','A10'), 10, ' ', 1, 'A10') || '"]';
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS HDATA1
END
-RUN
-HTMLFORM BEGIN
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<title>Demo jQuery / WebFOCUS</title>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js"></script>
<script src="https://datatables.net/release-datatables/media/js/jquery.dataTables.min.js"></script>
<script type=text/javascript charset=utf-8>
/* Data set - can contain whatever information you want */
var aDataSet = [
!IBI.FIL.HDATA1;
];
function addCommas( _sValue ) {
var _sRegExp = new RegExp('(-?[0-9]+)([0-9]{3})');
while(_sRegExp.test(_sValue)) {
_sValue = _sValue.replace(_sRegExp, '$1.$2');
}
return _sValue;
}
$(document).ready(function() {
$('#dynamic').html( '<table cellpadding="0" cellspacing="0" border="0" class="display" id="example"></table>' );
$('#example').dataTable({
"aaData": aDataSet,
"bPaginate": false,
"bInfo": false,
"bFilter": false,
"sScrollY": "350",
"bScrollCollapse": true,
"aoColumns": [
{ "sTitle": "Country" },
{ "sTitle": "Car" },
{ "sTitle": "Model" },
{ "sTitle": "Sales" },
{ "sTitle": "Weight" }
],
"aoColumnDefs": [
{
"aTargets": [ 3,4 ],
"mRender": function (data, type, full) {
if ( type === 'display' ) {
return addCommas(data);
}
return data;
}
}
]
});
});
</script>
<body>
<div id="dynamic"></div>
</body>
</html>
-HTMLFORM END
August 02, 2013, 07:24 AM
santu
Thank you Freste.
Its working.
Freste: one more problem i am having. Is it possible to add a subtotal row for each group in jquery table. Can we customize this?This message has been edited. Last edited by: santu,