[SOLVED] Show Trend line in the rows of the Report(along with data)

January 14, 2012, 12:27 AM

Dan Satchell

Using the StyleSheet, WebFOCUS has some basic, built-in bar chart capabilities that might meet your needs. Here is a modified example from the manual. These "data visualization" bar graphs can only be applied to numeric columns.

DEFINE FILE GGSALES
 Difference/D7M=DOLLARS-BUDDOLLARS;
END
-*
TABLE FILE GGSALES
 SUM BUDDOLLARS/D7M DOLLARS/D7M Difference
 BY CITY
 ON TABLE SET PAGE-NUM OFF
 ON TABLE SET STYLE *
  GRID=OFF, $
  GRAPHTYPE=DATA, COLUMN=Difference, GRAPHWIDTH=0.2, $
  TYPE=DATA, COLUMN=Difference, GRAPHCOLOR=GREEN, WHEN=Difference GT 0, $
  TYPE=DATA, COLUMN=Difference, GRAPHCOLOR=RED, WHEN=Difference LT 0, $
  TYPE=DATA, COLUMN=Difference, GRAPHCOLOR=BLACK, WHEN=Difference EQ 0, $
 ENDSTYLE
END

January 14, 2012, 02:32 AM

Gun

Thank you for your response, I will need line graph capabilities and again with bar graph, I will need to show it in one cell(6 vertical bars relating to 6 months). Please let me know if there are any such options available.

There is an option thats says, we could save the line/bar graph as gif image and use it in the report, I would like to know, if it can be used in every row with varied plots. Again the rows are in 10,000s. Thank you again for your help.

January 16, 2012, 03:54 AM

<JG>

Gun you really do not want to think about the embeded chart option when you say you have 10,000's rows

Each chart has to be pre-generated and given a specific name that means if you have 10,000 rows you have to run 10,000 graph requests.

I suspect that you do not want to wait for 5 or 6 hours to get a result for the request.

January 16, 2012, 11:10 AM

Gun

How about generating these graphs through report caster on monthly basis and then populate it in rows ? Its around 50,000 rows of data. Hence scheduling to create images and thereafter populating should be one of the ideal approaches. Please suggest...!!

May 29, 2012, 03:14 PM

Gun

We achieved the functionality of having a trend line in every line by embedding a frame and passing the values through define variables. But Since, there are 30 K Records. Will FOCACHE or Limiting the records to 100 work ?

Or should i create a HTML Report and have a button on top that says "Next 100 Records" and thereby achieve the limiting records.

Please let me know your thoughts. Thank you

May 29, 2012, 04:37 PM

Francis Mariani

There is a very interesting jQuery plugin that is easily embedded in a WebFOCUS HTML report. Take a look at jQuery Sparklines. Here is a working example, simply run this in WebFOCUS (the fex code points to online versions of the required files).

SET ASNAMES=ON
SET PAGE=NOLEAD
-RUN

TABLE FILE CENTORD
SUM
LINEPRICE AS 'LP'
COMPUTE LPA/A15 = PTOA(LINEPRICE, '(P12)', 'A15');
BY STORE_CODE
ACROSS YEAR

WHERE STORE_CODE CONTAINS 'CA'

ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

TABLE FILE H001
PRINT
LP1998 AS '1998'
LP1999 AS '1999'
LP2000 AS '2000'
LP2001 AS '2001'

COMPUTE SPARKLINE/A200 =
'<span class="cls_sparkline">' || LPA1998|| ',' || LPA1999|| ',' || LPA2000|| ',' || LPA2001 || '</span>'; AS ''

BY STORE_CODE AS 'STORE'

ON TABLE HOLD AS H002 FORMAT HTMTABLE

ON TABLE SET STYLE *
TYPE=REPORT, FONT='ARIAL', SIZE=9, SQUEEZE=ON, GRID=OFF, $
TYPE=TITLE, STYLE=BOLD, $
TYPE=DATA, BACKCOLOR=(silver white ),
ENDSTYLE
END
-RUN

-HTMLFORM BEGIN
<head>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script><!-- jQuery -->
<script type="text/javascript" src="http://omnipotent.net/jquery.sparkline/2.0/jquery.sparkline.js"></script><!-- jQuery -->
<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

This is the result:

May 30, 2012, 08:15 AM

dhagen

Very nice! Way easier than the way I use to do this.

June 05, 2012, 08:47 AM

Gun

Thank you Francis. We had to speed up the report and the statements below did the job.

ON TABLE SET WEBVIEWER ON
ON TABLE SET CACHELINES 50

But will remember to use it in the future.

Thank you

July 25, 2013, 09:59 PM

David Briars

Variation on Francis' 29-May-12 entry in this thread..

  
-* File spkline01.fex
-* Summarize Dollars by State
TABLE FILE GGSALES
 SUM DOLLARS/P21C
 BY  ST
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD
END
-RUN
-* Create Bullet jQuery Sparkline Inline HTML for each State, and save in HOLD file.   
TABLE FILE HOLD
 SUM     AVE.DOLLARS NOPRINT
 SUM     DOLLARS     AS 'Average Sales'
 COMPUTE SPARK2FLAME/A600 =
          '<span class="bulletgraph">' ||
          PTOA(C1, '(P21)', 'A50') || ',' || PTOA(C2, '(P21)', 'A50') || ',' ||
		  '8000000, 4000000, 2000000 </span>';
		             AS 'Average Sales per State Compared to Target/Average and Performance Ranges'
 BY      ST          AS 'State'
 ON TABLE HOLD AS HLDDATA FORMAT HTMTABLE
 ON TABLE SET HTMLCSS ON
 ON TABLE SET PAGE NOLEAD
 ON TABLE SET STYLE *
  TYPE=REPORT, FONT='TREBUCHET MS', SIZE=10,SQUEEZE=ON, GRID=OFF,$
  TYPE=TITLE,  JUSTIFY=CENTER,$
  TYPE=TABFOOTING, BORDER=LIGHT, BORDER-COLOR='SILVER',$
 ENDSTYLE
 ON TABLE SUBFOOT
  "Target/Average: <AVE.DOLLARS"
  "Performance Ranges:"
  " &|nbsp; &|lt;= 2,000,000 (Poor)"
  " &|nbsp; &|lt;= 4,000,000 (OK)"
  " &|nbsp; &|lt;= 8,000,000 (Good)"
END
-RUN
-* Present the report page to the user.  
-HTMLFORM BEGIN
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
    "http://www.w3.org/TR/html4/strict.dtd">
<html>
-*
<head>
 <title>GG Sales Status Reporting</title>
 <style>
  .block_wrapper{
    width: 820px;
  }
  .innerblock {
    float: left;
    padding: 10px;
    border: 1px solid gray;
  }
 </style>
 <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>
  $(function() {
   $.fn.sparkline.defaults.common.width  = '500px';
   $.fn.sparkline.defaults.common.height = '020px';
   $('.bulletgraph').sparkline('html', { type:'bullet'});
  });
 </script>
</head>
-*
<body>
  <div class="block_wrapper">
   <div class="innerblock">
       !IBI.FIL.HLDDATA;
   </div>
  </div>
</body>
</html>
-HTMLFORM END

jQuery Sparkline URL: http://omnipotent.net/jquery.sparkline/#s-about

This message has been edited. Last edited by: David Briars,

July 26, 2013, 09:18 AM

DavSmith

David Briars, NICE!

I saw the JQuery sparklines has been updated since Francis original post and was playing with it myself, but nice job. Simple and looks great.

January 06, 2015, 01:21 AM

Gaurav

-* File spkline01.fex
-* Summarize Dollars by State
TABLE FILE GGSALES
SUM DOLLARS/P21C
BY PRODUCT
BY ST
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD
END
-RUN
-* Create Bullet jQuery Sparkline Inline HTML for each State, and save in HOLD file.
TABLE FILE HOLD
SUM AVE.DOLLARS NOPRINT
SUM DOLLARS AS 'Average Sales'
COMPUTE SPARK2FLAME/A600 =
'' ||
PTOA(C1, '(P21)', 'A50') || ',' || PTOA(C2, '(P21)', 'A50') || ',' ||
'8000000, 4000000, 2000000 ';
AS 'Average Sales per State Compared to Target/Average and Performance Ranges'
COMPUTE SPARK2LINE/A600 =
'' ||
PRODUCT || ',' || PTOA(C2, '(P21)', 'A50') ||
' ';
AS 'Average Sales per State Compared to Target/Average and Performance Ranges'
BY ST AS 'State'
ON TABLE HOLD AS HLDDATA FORMAT HTMTABLE
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, FONT='TREBUCHET MS', SIZE=10,SQUEEZE=ON, GRID=OFF,$
TYPE=TITLE, JUSTIFY=CENTER,$
TYPE=TABFOOTING, BORDER=LIGHT, BORDER-COLOR='SILVER',$
ENDSTYLE
ON TABLE SUBFOOT
"Target/Average: "Performance Ranges:"
" &|nbsp; &|lt;= 2,000,000 (Poor)"
" &|nbsp; &|lt;= 4,000,000 (OK)"
" &|nbsp; &|lt;= 8,000,000 (Good)"
END
-RUN
-* Present the report page to the user.
-HTMLFORM BEGIN
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">

-*

GG Sales Status Reporting

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js">
<script type="text/javascript" src="http://omnipotent.net/jquery.sparkline/2.0/jquery.sparkline.js">
<script>
$(function() {
$.fn.sparkline.defaults.common.width = '500px';
$.fn.sparkline.defaults.common.height = '020px';
$('.bulletgraph').sparkline('html', { type:'bullet'});
});

<script>
$(function () {
//to learn more about this, go here: http://bit.ly/K0O0WT
$('.sparkline').sparkline('html', { enableTagOptions: true, type: 'line', width: '100px', height: '20px', lineWidth: 1, spotColor: true, minSpotColor: '#001C4A', maxSpotColor: '#001C4A', lineColor: '#001C4A', fillColor: false });
});

-*

!IBI.FIL.HLDDATA;

-HTMLFORM END

Hello David,

Above code is yours just I have added one more computed row for sparkline where I want a product-wise view for each state. So in one row I can view data points for each Product.

Please Help.

Thanks & Regards
Gaurav Tyagi

January 06, 2015, 10:08 PM

David Briars

Hi Gaurav,

Thank you for your follow up to my post.

If I understand your request correctly...create a report from GGSALES that:
1. Summarizes sales within product and state.
2. Each report row (product/state) should show a jQuery Sparkline line chart, showing sales across time.

Here is something to take a look at...

-* File sparkline_line.fex
-* 
-* Summarize Sales Dollars by Product, State and Date.
-*
TABLE FILE GGSALES
SUM   DOLLARS
BY    PRODUCT
BY    ST
BY    DATE
IF ST EQ 'CA'
ON TABLE HOLD AS HLDSALES
END
-RUN
-*
-* Create Sales Report with Sparkline Trend.   
-*
DEFINE FILE HLDSALES
 DOLLARS_D/D12c = DOLLARS;
 DOLLARS_A/A15  = FTOA(DOLLARS_D,'(D12c)','A15'); 
 SAMEROW_YN/A1  = IF ((LAST PRODUCT EQ PRODUCT) AND (LAST ST EQ ST)) THEN 'Y' ELSE 'N';
 DOLL_OUT/A4096V = IF SAMEROW_YN EQ 'N' THEN DOLLARS_A ELSE DOLL_OUT || ',' || DOLLARS_A;
END
-*
TABLE FILE HLDSALES
 SUM DOLLARS/D12 AS 'Sales'
 COMPUTE SPARK2FLAME/A4096V = '<span class="inlinesparkline">' ||
                              DOLL_OUT ||
		                      '</span>'; AS 'jQuery Sparkline - Sales Trend Across Time' 
 BY  PRODUCT AS 'Product'
 BY  ST      AS 'State'
 IF ST EQ 'CA'
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HLDREPT FORMAT HTMTABLE
 ON TABLE SET HTMLCSS ON
 ON TABLE SET PAGE NOLEAD
 ON TABLE SET STYLE *
 INCLUDE = ENInformationBuilders_Light1, $
 TYPE=HEADING,COLOR='WHITE',BACKCOLOR=RGB(#4F81BD),$
 TYPE=TITLE, JUSTIFY=CENTER,$
ENDSTYLE
END
-RUN
-*
-* Present the report page to the user.  
-*
-HTMLFORM BEGIN
<!DOCTYPE html>
<html>
-*
<head>
 <title>GG Sales Status Reporting</title>
 <style>
  .block_wrapper{
    width: 820px;
  }
  .innerblock {
    float: left;
    padding: 10px;
    border: 1px solid gray;
  }
 </style>
 <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>
  $(function() {
   $.fn.sparkline.defaults.common.width  = '500px';
   $.fn.sparkline.defaults.common.height = '020px';
   $('.inlinesparkline').sparkline();   });
 </script>
</head>
-*
<body>
  <div class="block_wrapper">
   <div class="innerblock">
       !IBI.FIL.HLDREPT;
   </div>
  </div>
</body>
</html>
-HTMLFORM END

Dave

January 07, 2015, 02:19 AM

Gaurav

Hi Dave,

Thanks Man this is exactly what I want you have saved the day for me.

Just few more things.

What I understood from this is it will take that dimension on the X-axis which we have not used in the second table. You didn't do a BY for Dates therefore is has taken that on X-axis.

I am rookie in webfocus. Just 3-4 weeks old.
Can yu give a litle explanation around these two computation fields so that I can use your logic in my dashboard.

SAMEROW_YN/A1 = IF ((LAST PRODUCT EQ PRODUCT) AND (LAST ST EQ ST)) THEN 'Y' ELSE 'N';
DOLL_OUT/A4096V = IF SAMEROW_YN EQ 'N' THEN DOLLARS_A ELSE DOLL_OUT || ',' || DOLLARS_A;

Again thanks man, thanks a lot you have saved weeks of work for me.

Thanks & Regards,
Gaurav Tyagi

January 07, 2015, 04:14 AM

Tony A

For those of you using IE10 (forced or otherwise

) and therefore not able to see the sparkline and thinking "why doesn't it work for me?", you might want to add the following meta tag to force IE9 standards mode.

<meta http-equiv="X-UA-Compatible" content="IE=9">

January 07, 2015, 10:00 AM

David Briars

quote:
I am rookie in webfocus. Just 3-4 weeks old.
Can yu give a litle explanation around these two computation fields so that I can use your logic in my dashboard.

SAMEROW_YN/A1 = IF ((LAST PRODUCT EQ PRODUCT) AND (LAST ST EQ ST)) THEN 'Y' ELSE 'N';
DOLL_OUT/A4096V = IF SAMEROW_YN EQ 'N' THEN DOLLARS_A ELSE DOLL_OUT || ',' || DOLLARS_A;

Welcome to the WebFOCUS developer community!

The first TABLE FILE summarizes and sorts the data, required by the second TABLE FILE command.

The second TABLE FILE builds the HTML report which includes a SPAN tag. The SPAN tag will be a holder for the data fed to the sparkline. In this case you want one SPAN tag for each report row.

The computations you site create the content for the span tag.

For the first date in a group (Product/State) the content will be the first dollar amount. For the second date in a group, the content will be the first dollar amount pasted to the second dollar amount...and so on...

@Tony - Thank you for the META tag reminder.

This message has been edited. Last edited by: David Briars,

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10