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     [SOLVED] Show a COMPUTE based on a SUM in a graph and a HEADING

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Show a COMPUTE based on a SUM in a graph and a HEADING
 Login/Join
 
Platinum Member
posted
This is probably fairly easy for a pro, but I can't reason out the answer for myself.

I'm trying to display either just a heading, or even better a 100% pie chart of a certain color.

Here's the setup: I have a bunch of test cases that can end up having a Red, Yellow, or Green status, and I want to show the results of the whole test based on a sum of the test results. For example, if any test cases are "Red" then there was a serious error, and the whole test should be declared to FAIL. If there were no Reds but some Yellows, I want to declare the test Passed, but note there was conditions to check out. All Green is good.
My data is a summary of test errors, e.g.
Severity......Error ID......Error_Count
Red 201 1
Red 202 15
Yellow 134 5
Green 150 1200
Green 125 1600

I have gotten as far as doing a SUM across make, and even got a compute to tell me the result, but I don't know how to have that compute be the only thing on the report (or again, be a value that I can make a pie chart from).

Here's my code for this example:
 
TABLE FILE ERROR_SUMMARY
SUM 
Error_Count
ACROSS LOWEST Severity
     COMPUTE 
          TEST_RESULT/A30 = IF C1 GT 0 THEN 'Red - Fail' ELSE IF C2 GT 0 THEN 'Pass, but yellow errors found' ELSE 'Pass, all green!';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END
  


My ideal would be to have a Pie Chart that's colored Red, Yellow, or Green based on the value of the computer, with the compute text as a label or title. Is this possible? How do you have a report with just a compute on it?

This message has been edited. Last edited by: J.Hines,



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
There's a flaw in your ACROSS, which will be alphabetical - 'green', 'red', 'yellow'...

It might be possible to achieve this using less passes through the data, but here is a quickie solution:

-* Create the master for Error Summary ---------------------------------
APP FI DATAMAST DISK error_summary.mas
-RUN

-WRITE DATAMAST FILE=ERROR_SUMMARY, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=SEVERITY   , ALIAS=SEVERITY   , USAGE=A6, ACTUAL=A6, $
-WRITE DATAMAST FIELD=ERROR_ID   , ALIAS=ERROR_ID   , USAGE=I4, ACTUAL=A4, $
-WRITE DATAMAST FIELD=ERROR_COUNT, ALIAS=ERROR_COUNT, USAGE=I5, ACTUAL=A5, $



-* Create the data file for Error Summary ------------------------------
FILEDEF ERROR_SUMMARY DISK error_summary.ftm
-RUN

-WRITE ERROR_SUMMARY Red    201    1
-WRITE ERROR_SUMMARY Red    202   15
-WRITE ERROR_SUMMARY Yellow 134    5
-WRITE ERROR_SUMMARY Green  150 1200
-WRITE ERROR_SUMMARY Green  125 1600



SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET PAGE=NOLEAD
-RUN



-* Create a hold file containing severity counts -----------------------
TABLE FILE ERROR_SUMMARY
SUM
ERROR_COUNT
ACROSS SEVERITY COLUMNS 'Red' AND 'Yellow' AND 'Green'
ON TABLE HOLD AS H_ERROR_SUMMARY
END
-RUN



-* Create a hold file containing severity test result ------------------
TABLE FILE H_ERROR_SUMMARY
PRINT
COMPUTE TEST_RESULT/A30 = IF ERRRed GT 0 THEN 'Red - Fail' ELSE IF ERRYellow GT 0 THEN 'Pass, but yellow errors found' ELSE 'Pass, all green!'; AS '';
ON TABLE HOLD AS H_CHARTTITLE
END
-RUN

-READFILE H_CHARTTITLE



-* Create a hold file containing severity test result colour -----------
TABLE FILE H_ERROR_SUMMARY
PRINT
COMPUTE DUMMY_MEASURE/I1 = 1;

COMPUTE CHART_COLOUR/A10 = IF ERRRed GT 0 THEN '255,0,0' ELSE IF ERRYellow GT 0 THEN '255,255,0' ELSE '0,255,0';
ON TABLE HOLD AS H_CHARTCOLOUR
END
-RUN

-READFILE H_CHARTCOLOUR
-CLOSE H_CHARTCOLOUR



-* Create a severity test result chart ---------------------------------
GRAPH FILE H_CHARTCOLOUR
SUM DUMMY_MEASURE AS ''
BY CHART_COLOUR NOPRINT

ON GRAPH SET HAXIS 400
ON GRAPH SET VAXIS 400
ON GRAPH SET LOOKGRAPH PIE

ON GRAPH SET GRAPHSTYLE *
setReportParsingErrors(false);
setSelectionEnableMove(false);
setLegendDisplay(false);
setTextString(getTitle(),"&TEST_RESULT");
setPlace(true);
setFillColor(getSeries(0),new Color( &CHART_COLOUR ) );
ENDSTYLE
END
-RUN


I'll let you prettify the chart using the GUI or your knowledge of chart styling...


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
Platinum Member
posted Hide Post
Hello J Hines,

Can you just HOLD from your first Table File and then print your computed field...

ON TABLE HOLD
END
GRAPH FILE HOLD
SUM TEST_RESULT
..
END

Hope that helps.


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Report This Post
Platinum Member
posted Hide Post
First of all, thank you so much!

quote:
Originally posted by Francis Mariani:
There's a flaw in your ACROSS, which will be alphabetical - 'green', 'red', 'yellow'...

Yes, sorry, result of my simplification. My data is "1 - Red", "2 - Yellow" so it will actually sort right, but thank you, because I might create a DEFINE to split this into two fields.
It might be possible to achieve this using less passes through the data, but here is a quickie solution:

In this code, how are you able to refer to "ERRRed" and "ERRYellow"? I don't see them defined in the previous HOLD file.
quote:


-* Create a hold file containing severity test result ------------------
TABLE FILE H_ERROR_SUMMARY
PRINT
COMPUTE TEST_RESULT/A30 = IF ERRRed GT 0 THEN 'Red - Fail' ELSE IF ERRYellow GT 0 THEN 'Pass, but yellow errors found' ELSE 'Pass, all green!'; AS '';
ON TABLE HOLD AS H_CHARTTITLE
END
-RUN




Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
Sorry, I should have mentioned that these ACROSS column names are generated, based on the data values. Since I have SET ASNAMES=ON, when using ACROSS for a HOLD file, the data value is used as part of the column name.


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
Platinum Member
posted Hide Post
So then where does the "ER" come from? I'm trying to translate to my code, which uses a master file, and I have ON TABLE SET ASNAMES ON for that one (the first pull with the ACROSS) and still it's saying it can't find the field in H_ERROR_SUMMARY.

Why did you have to do
ACROSS SEVERITY COLUMNS ....  


instead of just leaving it as
ACROSS SEVERITY
? If you didn't have ASNAMES turned on, how would you refer to those computed columns?



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
SET ASNAMES=ON
TABLE FILE ERROR_SUMMARY
SUM
ERROR_COUNT
ACROSS SEVERITY COLUMNS 'Red' AND 'Yellow' AND 'Green'
ON TABLE HOLD AS H_ERROR_SUMMARY
END
-RUN

?FF H_ERROR_SUMMARY
-RUN


The ERR comes from the column being SUMmed, ERROR_COUNT. I think the convention is that the first three characters are used. If you don't use ASNAMES=ON, to determine what the columns are called, run ?FF H_ERROR_SUMMARY. Then use column notation, C1, C2, etc. (I usually avoid doing this).


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
Platinum Member
posted Hide Post
I tried doing that right before my TABLE FILE, but when I try to run it, I get a tiny dialog box with a very unhelpful error:

 >SET<
SET ASNAMES=ON 


But I did ON TABLE SET ASNAMES ON and that seems to work.



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Platinum Member
posted Hide Post
Wow. I had this all done, and was about to post my resulting code, but then App Studio crashed hard, and somehow, rather than just crashing, ALSO deleted the content in the file I was working in (which I had saved previously). The file is still there, but empty. What program DOES THAT, modify and save a file while crashing!?



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
We gave up on using App Studio 8.0.08 because for some of us, it constantly deleted the contents of a file while saving. This has something to do with deleting all the code before inserting the updated code.

Guess why I use an external text editor? EditPlus has its own backup facility - something that none of the WebFOCUS tools do.


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
Platinum Member
posted Hide Post
Okay, I redid it from scratch again (well, using Francis' code plus what I remembered, mostly around graph style).

I was able to combine the second two hold files into one and it works the same:

-* Create the master for Error Summary ---------------------------------
APP FI DATAMAST DISK error_summary.mas
-RUN

-WRITE DATAMAST FILE=ERROR_SUMMARY, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=SEVERITY   , ALIAS=SEVERITY   , USAGE=A6, ACTUAL=A6, $
-WRITE DATAMAST FIELD=ERROR_ID   , ALIAS=ERROR_ID   , USAGE=I4, ACTUAL=A4, $
-WRITE DATAMAST FIELD=ERROR_COUNT, ALIAS=ERROR_COUNT, USAGE=I5, ACTUAL=A5, $



-* Create the data file for Error Summary ------------------------------
FILEDEF ERROR_SUMMARY DISK error_summary.ftm
-RUN

-WRITE ERROR_SUMMARY Red    201    1
-WRITE ERROR_SUMMARY Red    202   15
-WRITE ERROR_SUMMARY Yellow 134    5
-WRITE ERROR_SUMMARY Green  150 1200
-WRITE ERROR_SUMMARY Green  125 1600



SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET PAGE=NOLEAD
-RUN



-* Create a hold file containing severity counts -----------------------
TABLE FILE ERROR_SUMMARY
SUM
ERROR_COUNT
ACROSS SEVERITY COLUMNS 'Red' AND 'Yellow' AND 'Green'
ON TABLE HOLD AS H_ERROR_SUMMARY
END
-RUN



-* Create a hold file containing severity test result ------------------
-* By default, the field names from the compute use the first 3 characters of the field name being summed (ERROR_COUNT) --> ERR
TABLE FILE H_ERROR_SUMMARY
PRINT
COMPUTE TEST_RESULT/A30 = IF ERRRed GT 0 THEN 'Red - Fail' ELSE IF ERRYellow GT 0 THEN 'Pass, but yellow errors found' ELSE 'Pass, all green!'; AS '';
ON TABLE HOLD AS H_CHARTTITLE
END
-RUN

-READFILE H_CHARTTITLE



-* Create a hold file containing severity test result colour -----------
TABLE FILE H_ERROR_SUMMARY
PRINT
COMPUTE DUMMY_MEASURE/I1 = 1;
COMPUTE TEST_RESULT/A30 = IF ERRRed GT 0 THEN 'Red - Fail' ELSE IF ERRYellow GT 0 THEN 'Pass, but yellow errors found' ELSE 'Pass, all green!'; AS '';
COMPUTE CHART_COLOUR/A10 = IF ERRRed GT 0 THEN '249,64,17' ELSE IF ERRYellow GT 0 THEN '217,217,0' ELSE '2,172,104';
ON TABLE HOLD AS H_CHARTCOLOUR
END
-RUN

-* Necessary to be able to use computes as amper variables ------------
-READFILE H_CHARTCOLOUR
-CLOSE H_CHARTCOLOUR



-* Create a severity test result chart ---------------------------------
GRAPH FILE H_CHARTCOLOUR
SUM DUMMY_MEASURE AS ''
BY CHART_COLOUR NOPRINT

ON GRAPH SET HAXIS 400
ON GRAPH SET VAXIS 400
ON GRAPH SET LOOKGRAPH PIE

ON GRAPH SET GRAPHSTYLE *
setPieTilt(0);
setPieDepth(0);
setUseSeriesBorderDefaults(true); 		//Allows border color to be changed from black default
setSeriesDefaultTransparentBorderColor(true);	//Makes borders transparent
setReportParsingErrors(false);
setSelectionEnableMove(false);
setLegendDisplay(false);
setTextString(getTitle(),"&TEST_RESULT");
setPlace(true);
setFillColor(getSeries(0),new Color( &CHART_COLOUR ) );
ENDSTYLE
END
-RUN




Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
Expert
posted Hide Post
A thing of beauty Smiler


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
Platinum Member
posted Hide Post
Another part associated with this that I just was having tons of trouble with was doing a single COMPUTE after the ACROSS (in order to have just one column) and then including the text from that COMPUTE in the report HEADING.

The report would lock up the entire server, whereas if it was before the ACROSS, it wouldn't. But to put it before the ACROSS, I had to create a define field to sum on.

For anyone else interested in the future, here is the code that ended up working:
 DEFINE FILE MY_FILE
  HAS_RED/I4 = IF SEVERITY = 'Red' THEN 1 ELSE 0;
  HAS_YEL/I4 = IF SEVERITY = 'Yellow' THEN 1 ELSE 0;
END

TABLE FILE MY_FILE
SUM
   COUNT
   HAS_RED NOPRINT
   HAS_YEL NOPRINT
   COMPUTE TEST_RESULT/A30 = IF TOT.HAS_RED GT 0 THEN 'FAIL' ELSE IF TOT.HAS_YEL GT 0 THEN 'PASS WITH YELLOW' ELSE 'PASS' ; NOPRINT
ACROSS SEVERITY AS '' COLUMNS 'Red' AND 'Yellow' AND 'Green'
HEADING
"Test Results <FST.TEST_RESULT "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END 



Prod: 8.2.0.4 OS:Windows 10 Output:AHTML, Excel 2007+

The life of a designer is a life of fight against the ugliness.
 
Posts: 141 | Location: North Carolina | Registered: August 10, 2012Report This Post
<Emily McAllister>
posted
quote:
Originally posted by J.Hines:
Wow. I had this all done, and was about to post my resulting code, but then App Studio crashed hard, and somehow, rather than just crashing, ALSO deleted the content in the file I was working in (which I had saved previously). The file is still there, but empty. What program DOES THAT, modify and save a file while crashing!?


Hello,
I'm so sorry to hear this happened to you! This kind of crash is an issue we take seriously. We have been working diligently on crash prevention and data recovery.

You may be happy to hear that we have added a robust backup functionality to WebFOCUS 8.2. Starting with App Studio 8.2.01, opened files are automatically saved by default (every 5 minutes). Should App Studio crash, a “Document Recovery” dialog appears when App Studio restarts indicating list of files App Studio has recovered from the previous session. You can choose to restore the file with unique name or replace content in existing file. We will have more information about this feature in future announcements and 8.2 is slated for GA in Q3.

I know it's too late to restore your file now, but I'm glad you were able to get it working, and I hope this never happens to you again! If it does, you may want to contact Tech Support so that we can pinpoint the reason for your crashes.

Please let me know if there's anything I can do to help you.
Emily McAllister
Focal Point Moderator
 
Report This Post
Expert
posted Hide Post
Five minute backups might be good for people that use the GUI. Five minutes for real developers might be too long an interval, but I guess this is a start.


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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Show a COMPUTE based on a SUM in a graph and a HEADING

Copyright © 1996-2020 Information Builders