[SOLVED]Sparkline in the report output

October 05, 2013, 03:36 PM

Hi,

I'm trying to show a sparkline in my report.

Code, i took it from this post Show Trend line in the rows of the Report(along with data) and modified using GGSALES.

SET ASNAMES=ON
SET PAGE=NOLEAD
-RUN
DEFINE  FILE GGSALES
LP1998/I8=DOLLARS+1000;
LP1999/I8=DOLLARS+3000;
LP2000/I8=DOLLARS+5000;
LP2001/I8=DOLLARS+7000;
LPA1998/A8  = FPRINT(LP1998,'I8','A8');
LPA1999/A8  = FPRINT(LP1999,'I8','A8');
LPA2000/A8  = FPRINT(LP2000,'I8','A8');
LPA2001/A8  = FPRINT(LP2001,'I8','A8');
END
TABLE FILE GGSALES
PRINT
DATE 
LPA1998 AS 'A1998'
LPA1999 AS 'A1999'
LPA2000 AS 'A2000'
LPA2001 AS 'A2001'
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
TABLE FILE H001
PRINT 
A1998
A1999
A2000
A2001
COMPUTE SPARKLINE/A200 =
'<span class="cls_sparkline">' || A1998|| ',' || A1999|| ',' || A2000|| ',' || A2001 || '</span>'; 
ON TABLE HOLD AS H002 FORMAT HTMTABLE
END
-RUN
-HTMLFORM BEGIN
<head>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" src="http://omnipotent.net/jquery.sparkline/2.0/jquery.sparkline.js"></script>
<script type="text/javascript">
$(function () {
    $('.cls_sparkline').sparkline('html', { enableTagOptions: true, type: 'line', width: '100px', height: '10px', fillColor: false, lineColor: '#000000', maxSpotColor: false, minSpotColor: false, spotColor: false });
});
</script>
</head>
!IBI.FIL.H002;
-HTMLFORM END

Anyone, please help me on this.

Thanks,
Rifaz

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

October 07, 2013, 02:44 AM

Dave

Rifaz,

as you can see in the code, this uses 3th party tools ( javascript ) to generate sparklines.

It's not standard WebFocus functionality.

October 07, 2013, 04:36 AM

Rifaz

Yes Dave, To generate th sparkline based on the datas, Francis provided a simple solution to plugin with jquery script in his older post, which i've mentioned in previous post.

It generates the values as expected in SPARKLINE column. I'm not sure whether those values are getting passed to jquery function or not.

If you have any other workaround to show up the sparklines based on the values, please let me know.

Thanks,
Rifaz

October 07, 2013, 10:05 AM

Francis Mariani

Rifaz, it works if you limit the number of rows. It is possible this solution will not work for a report with many rows, 4,317 in your example. Try adding a RECORDLIMIT filter, just to see it work. Perhaps the JavaScript generated by the API for 4,317 rows does eventually return, but it appears this is unusable for large reports - I would only use it for dashboards with few rows.

October 07, 2013, 12:53 PM

Rifaz

Thanks much Francis to have a look into this, still it doesn't work out even after setting the RECORDLIMIT EQ 100.

That's what my requirement as well to show up only for few number of records.

Displays the values instead of Sparkline.

However, Waz provided some solution in his older post. I'll have to check it out.

Spark Lines

Thanks,
Rifaz

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

October 07, 2013, 01:15 PM

Kamesh

Try this, it might help you. I found this in of our post in the past.

-**bullet chart example

DEFINE FILE CAR
DEF_Target/A4 = '5000';
-*FTOA(CAR.BODY.DEALER_COST,'(F11)',DEF_Target);
DEF_Current/A1000V = FTOA(CAR.BODY.RETAIL_COST,'(F11)',DEF_Current);
VAL1/A10V='350000';
VAL2/A10V='300000';
VAL3/A10V='400000';
VAL4/A10V='325000';
VAL5/A10V='375000';
DEF_SparklineHTML/A4000V = '<span class="sparklines">'|VAL1|','|VAL2|','|VAL3|','|VAL4|','|VAL5|'</span>';
END

-* I added the definitions of the HTML markup into the report.
-* I also adjusted their column widths so that it doesn't span A4000V wide.
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
DEF_SparklineHTML AS ' '
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=DATA,
COLUMN=N5,
SIZE=4,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.055556,
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=0.500000,
$
ENDSTYLE
END
-* Finally, you have to make sure it outputs using the scorecard_template.htm file.
-RUN

-HTMLFORM BEGIN
<HTML>
<HEAD>
<SCRIPT for=window type=text/javascript eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>
<SCRIPT style="Z-INDEX: 0" id=ITEM1 type=text/javascript src="/wfimages/jquery-1.7.2.min.js"></SCRIPT>
<SCRIPT style="Z-INDEX: 0" id=ITEM2 type=text/javascript src="/wfimages/jquery.sparkline.min.js"></SCRIPT>

<SCRIPT id=ITEM3 type=text/javascript>
$(function () {
$('.bullet').sparkline('html', { enableTagOptions: true, type: 'bullet', targetWidth: '1', performanceColor: '#000000', rangeColors: ['#EEE', '#CCC', '#AAA'] });
$('.stateindicator').sparkline('html', { enableTagOptions: true, type: 'pie', disableInteraction: true, width: '10px', height: '10px', sliceColors: ['#65E37F', '#E5EE77', '#FEA3A2'] });
$('.sparklines').sparkline('html', { enableTagOptions: true, type: 'line', width: '60px', height: '20px', fillColor: false, lineColor: '#000000', maxSpotColor: false, minSpotColor: false, spotColor: false });
});
</SCRIPT>
</HEAD>
</HTML>
-HTMLFORM END

-EXIT

October 07, 2013, 01:30 PM

Francis Mariani

I ran your code and it displayed data and sparkline graphs.

October 07, 2013, 02:08 PM

Rifaz

Thanks Kamesh & Francis,

It weird, in my machine, getting only the sequence of values instead of sparkline graph, even when I ran the code posted by Kamesh.

Thanks,
Rifaz

October 07, 2013, 02:12 PM

Francis Mariani

October 07, 2013, 02:14 PM

Francis Mariani

Try pasting the href to the JavaScript files in your browser

http://omnipotent.net/jquery.s.../jquery.sparkline.js

If the text of the js file does not appear, you do not have permission to navigate to it.

I would download jQuery and the Sparkline plugin and install it in your web server.

October 18, 2013, 11:38 PM

Rifaz

Thanks all for your comments,

I used this code for generating & displaying the sparkline in the report output.

APP HOLD BASEAPP
GRAPH FILE CAR
SUM DC
BY CAR
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET HAXIS 200
ON GRAPH SET VAXIS 25
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID OFF
ON GRAPH HOLD AS T001 FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(false);
setConnectLineMarkers(true);
setConnectScatterMarkers(true);
setO1LabelDisplay(false);
setO1AxisSide(0);
setO1MajorGridDisplay(false);
setO1MajorGridStyle(0);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setSeriesType(0,2);
setY1LabelDisplay(false);
setY1AxisSide(0);
setY1MajorGridDisplay(false);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(1);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),1);
setRiserBorderMode(0);
setSeriesDefaultTransparentBorderColor(false);
setUseSeriesBorderDefaults(false);
setLegendDisplay(false);
setFontSizeAbsolute(getY1Title(),true);
setFontSizeAbsolute(getY1Label(),true);
setFontSizeAbsolute(getY2Title(),true);
setFontSizeAbsolute(getY2Label(),true);
setFontSizeAbsolute(getO1Title(),true);
setPlace(true);
setFrameDisplay(false);
setY1AxisLineDisplay(false);
setO1AxisLineDisplay(false);
ENDSTYLE
END
-RUN
DEFINE FILE CAR
POS_ICON/A50 = '<img src=/approot/baseapp/t001.png>';
END

TABLE FILE CAR
PRINT CAR
COMPUTE TEST/A80 = IF SEATS GT 2 THEN POS_ICON ELSE '';
END

Thanks,
Rifaz

October 19, 2013, 03:11 PM

Danny-SRL

Maybe not very elegant, but it does the trick:

  
-SET &ECHO=ALL;
-* File Sparkline.fex
APP HOLD BASEAPP
TABLE FILE CAR
BY COUNTRY
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-SET &C=&LINES;
-REPEAT #GIFS FOR &I FROM 1 TO &C;
-READ SAVE,&COUNTRY.&I
GRAPH FILE CAR
SUM RETAIL_COST AS '' 
ACROSS MPG AS '' 
WHERE COUNTRY EQ '&COUNTRY.&I';
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET HAXIS 150
ON GRAPH SET VAXIS 25
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET BARNUMB OFF
ON GRAPH SET 3D OFF
ON GRAPH SET VZERO ON
ON GRAPH SET GRID OFF
ON GRAPH HOLD AS SPARK&I FORMAT PNG
ON GRAPH SET GRAPHSTYLE *
setMarkerDisplay(false);
setConnectLineMarkers(true);
setConnectScatterMarkers(true);
setO1LabelDisplay(false);
setO1AxisSide(0);
setO1MajorGridDisplay(false);
setO1MajorGridStyle(0);
setO1MinorGridDisplay(false);
setAxisAssignment(0,0);
setSeriesType(0,2);
setY1LabelDisplay(false);
setY1AxisSide(0);
setY1MajorGridDisplay(false);
setY1MajorGridStyle(0);
setY1MinorGridDisplay(false);
setTextFormatPreset(getY1Label(),-1);
setTextFormatPattern(getY1Label(),"#.##");
setPieFeelerTextDisplay(1);
setPieLabelDisplay(0);
setTextFormatPreset(getPieSliceLabel(),1);
setRiserBorderMode(0);
setSeriesDefaultTransparentBorderColor(false);
setUseSeriesBorderDefaults(false);
setLegendDisplay(false);
setFontSizeAbsolute(getY1Title(),true);
setFontSizeAbsolute(getY1Label(),true);
setFontSizeAbsolute(getY2Title(),true);
setFontSizeAbsolute(getY2Label(),true);
setFontSizeAbsolute(getO1Title(),true);
setPlace(true);
ENDSTYLE
ON GRAPH SET STYLE *
$
ENDSTYLE
END
? FILEDEF
-#GIFS
DEFINE FILE CAR
SPARK/A50=
-REPEAT #SPLINES FOR &I FROM 1 TO &C;
IF COUNTRY EQ '&COUNTRY.&I' THEN '<img src=/approot/baseapp/spark&I...png>' ELSE 
-#SPLINES
' ';
END

TABLE FILE CAR
SUM RETAIL_COST SPARK
BY COUNTRY
END