Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Show Trend line in the rows of the Report(along with data)
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Show Trend line in the rows of the Report(along with data)
 Login/Join
 
Silver Member
posted
Hi

Report comprises 10 columns, out of which one 9 of them pertain to the current month data, while 1 column needs to show a trend line for the last 6 months.

Is it possible to show a trend line in every row of the report.

Thank you,
Ganesh.H

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


7.7, Windows, HTML
 
Posts: 38 | Registered: December 30, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.


7.7, Windows, HTML
 
Posts: 38 | Registered: December 30, 2011Reply With QuoteReport This Post
<JG>
posted
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.
 
Reply With QuoteReport This Post
Silver Member
posted Hide Post
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...!!


7.7, Windows, HTML
 
Posts: 38 | Registered: December 30, 2011Reply With QuoteReport This Post
Silver Member
posted Hide Post
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


7.7, Windows, HTML
 
Posts: 38 | Registered: December 30, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
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:



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, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Very nice! Way easier than the way I use to do this.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1100 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
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


7.7, Windows, HTML
 
Posts: 38 | Registered: December 30, 2011Reply With QuoteReport This Post
Master
posted Hide Post
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,
 
Posts: 763 | Registered: April 23, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
-* 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


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 58 | Registered: November 19, 2014Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 763 | Registered: April 23, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
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


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 58 | Registered: November 19, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
For those of you using IE10 (forced or otherwise Wink) 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">

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5602 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Master
posted Hide Post
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,
 
Posts: 763 | Registered: April 23, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks Man. You saved many days efforts for me.
Smiler


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 58 | Registered: November 19, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Show Trend line in the rows of the Report(along with data)

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.