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.

javascript code in html report.

 <SCRIPT type=text/javascript charset=utf-8>
/* Data set - can contain whatever information you want */
var aDataSet = [
 
!IBI.FIL.HDATA1;
 
];
 
$(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" }
        ]
    } );
} );
</SCRIPT>
 

In fex file:

 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>,

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

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?

Why are you using jQuery to sort in a fex? Have you considered AHTML?

AHTML would be ideal - but it requires a license for WebFOCUS Active Technologies.

Santu, in terms of the code below:

quote:

TRIM('L', FPRINT(SALES,'D6','A10'), 10, ' ', 1, 'A10') || '","' ||

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') || '","' ||

Small correction:

quote:

FPRINT(SALES,'P6L','A6') || '","' ||

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?

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?

Consider:

...
BY (raw data field) NOPRINT BY (formatted field)
...

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

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

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,