Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] sorting is not working properly with jquery in html report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] sorting is not working properly with jquery in html report
 Login/Join
 
Gold member
posted
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>,


Web FOCUS 7.7.03
PDF
 
Posts: 69 | Registered: May 24, 2013Report This Post
<Kathryn Henning>
posted
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
 
Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Why are you using jQuery to sort in a fex? Have you considered AHTML?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Platinum Member
posted Hide Post
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') || '","' ||


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Platinum Member
posted Hide Post
Small correction:

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


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Gold member
posted Hide Post
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
 
Posts: 69 | Registered: May 24, 2013Report This Post
Gold member
posted Hide Post
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
 
Posts: 69 | Registered: May 24, 2013Report This Post
Expert
posted Hide Post
Consider:
...
BY (raw data field) NOPRINT BY (formatted field)
...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<FreSte>
posted
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
 
Report This Post
Gold member
posted Hide Post
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,


Web FOCUS 7.7.03
PDF
 
Posts: 69 | Registered: May 24, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] sorting is not working properly with jquery in html report

Copyright © 1996-2020 Information Builders