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] Hiding Legend within Graph on Filtering
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Hiding Legend within Graph on Filtering
 Login/Join
 
Silver Member
posted
We have a table which includes 12 months of data for each Company and within that table is actual, budget and prior year information. Each line has this classification as well as a Code to identify the type of data.
The Ledger information is applicable across multiple table/graphs and it is the Code that changes for each graph (say utilities, freight or whatever).
The Code is however what is used within the graph to tell it what series to display.
Within the graph they have a filter for what ledger to display (whether to display Actual, Budget, Prior Year etc. )
This aspect works fine and only that data is displayed.
In the legend however, (I believe because the 3 codes are the series and these aren't what is actually being filtered), it will always still display all 3 of the legends, even if only one ledger is selected.

Below is a sample of what the table looks like (I just output it to Excel for simplicity).
COMPANY FY QTR PERIOD FYPERIODCODE LEDGER COUNTRY COMPNAME AMOUNT
00001 2017 Q1 01 2017-01 BFRT Budget United States 00001 - TG MISSOURI 92,495.00
00001 2017 Q1 01 2017-01 FRT Actual United States 00001 - TG MISSOURI 21,705.82
00001 2017 Q1 01 2017-01 PYFRT Prior Fiscal Year United States 00001 - TG MISSOURI 68,473.69
00001 2017 Q1 02 2017-02 BFRT Budget United States 00001 - TG MISSOURI 27,235.00
00001 2017 Q1 02 2017-02 FRT Actual United States 00001 - TG MISSOURI 21,860.09
00001 2017 Q1 02 2017-02 PYFRT Prior Fiscal Year United States 00001 - TG MISSOURI 14,352.76
00001 2017 Q1 03 2017-03 BFRT Budget United States 00001 - TG MISSOURI 29,235.00
00001 2017 Q1 03 2017-03 FRT Actual United States 00001 - TG MISSOURI 35,468.96
00001 2017 Q1 03 2017-03 PYFRT Prior Fiscal Year United States 00001 - TG MISSOURI 27,740.60
00001 2017 Q2 04 2017-04 BFRT Budget United States 00001 - TG MISSOURI 80,645.00
00001 2017 Q2 04 2017-04 FRT Actual United States 00001 - TG MISSOURI 25,952.90
00001 2017 Q2 04 2017-04 PYFRT Prior Fiscal Year United States 00001 - TG MISSOURI 21,204.48
00001 2017 Q2 05 2017-05 BFRT Budget United States 00001 - TG MISSOURI 29,235.00
00001 2017 Q2 05 2017-05 FRT Actual United States 00001 - TG MISSOURI 63,692.68
00001 2017 Q2 05 2017-05 PYFRT Prior Fiscal Year United States 00001 - TG MISSOURI 25,162.86


 
JOIN
KPIFRT.SEG01.COMPANY IN KPIFRT TO MULTIPLE USERS.SEG01.USERCO
IN USERS TAG J4 AS J4
END
DEFINE FILE KPIFRT
USERS/A25='&IBIMR_user';
FYPERIOD/A10=KPIFRT.SEG01.FY || '-' || KPIFRT.SEG01.PERIOD;
FRT/D12.2 MISSING ON=IF CODE EQ 'FRT' THEN AMOUNT ELSE MISSING;
BFRT/D12.2 MISSING ON=IF CODE EQ 'BFRT' THEN AMOUNT ELSE MISSING;
PYFRT/D12.2 MISSING ON=IF CODE EQ 'PYFRT' THEN AMOUNT ELSE MISSING;
END
ENGINE INT CACHE SET ON
GRAPH FILE KPIFRT
-* Created by Info Assist for Graph
SUM KPIFRT.SEG01.FRT AS 'Actual'
KPIFRT.SEG01.BFRT AS 'Budget'
KPIFRT.SEG01.PYFRT AS 'Prior Year Actual'
BY KPIFRT.SEG01.COMPNAME
ACROSS KPIFRT.SEG01.PERIOD
WHERE COMPNAME EQ &COMPNAME.(OR(FIND COMPNAME IN KPICOMPNAME |FORMAT=A40)).COMPNAME:.;
WHERE COUNTRY EQ &COUNTRY.(OR(FIND COUNTRY IN KPICNTRY |FORMAT=A20)).COUNTRY:.;
WHERE QTR EQ &QTR.(OR(FIND QTR IN KPIQTR |FORMAT=A2)).QTR:.;
WHERE LEDGER EQ &LEDGER.(OR(FIND LEDGER IN KPILEDG |FORMAT=A20)).LEDGER:.;
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET LOOKGRAPH VLINE
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
setUseSeriesShapes(true);
setMarkerSizeDefault(50);
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, $
*GRAPH_SCRIPT
setMarkerSizeDefault(30);
setReportParsingErrors(false);
setSelectionEnableMove(false);
setCurveFitType(0,0);
setTextString(getY1Title(),"Dollars");
setDisplay(getY1Title(),true);
setTextString(getO1Title(),"Fiscal Year - Period");
setDisplay(getO1Title(),true);
setScaleMinAuto(getY1Axis(),true);
setScaleMaxAuto(getY1Axis(),true);
setGridStepAuto(getY1MajorGrid(),true);
setFontSizeAbsolute(getLegendText(),true);
setAutofit(getLegendText(),false);
setPlaceResize(getLegendText(),0);
setFontSizeAbsolute(getO1Label(), true);
setFontSizeInPoints(getO1Label(), 7);
setPlaceResize(getO1Label(), 0);
setFontSizeAbsolute(getY1Label(), true);
setFontSizeInPoints(getY1Label(), 8);
setPlaceResize(getY1Label(), 0);
setFillMissingData(0);
setLegendPosition(1);
-*setLineBasicStrokeType(getSeries(1),16);
-*setLineBasicStrokeType(getSeries(2),13);
setFontSizeInPoints(getLegendText(),7);
setMarkerShape(getAllSeries(),2);
setTransparentBorderColor(getSeries(0), true);
*END
ENDSTYLE
END
-RUN

-*IA_GRAPH_FINISH
 



Not sure how to post an image otherwise I'd include the graph output.

Any help would be appreciated on how to hide the additional legend.

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


WF 81.5, Windows7
AS/400 Database.
All Outputs

 
Posts: 46 | Registered: November 26, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Does the valid select value from &LEDGER are the same as for CODE (FRT, BFRT, PYFRT) ? What are they ?

If I understand properly what you're trying to accomplish, you can do something such as this (but may not open anymore in AS and InfoAssist for Graph)
...
GRAPH FILE KPIFRT
SUM
-IF &LEDGER NE 'FRT'  THEN GOTO BFRT;
    KPIFRT.SEG01.FRT   AS 'Actual'
-BFRT
-IF &LEDGER NE 'BFRT' THEN GOTO PYFRT;
    KPIFRT.SEG01.BFRT  AS 'Budget'
-PYFRT
-IF &LEDGER NE 'BFRT' THEN GOTO ENDSUM;
    KPIFRT.SEG01.PYFRT AS 'Prior Year Actual'
-ENDSUM
BY KPIFRT.SEG01.COMPNAME
...


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2192 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Silver Member
posted Hide Post
Martin,

The &LEDGER names are different than the CODE (they are Actual, Budget and Prior Fiscal Year).
It didn't like your suggestion and was erroring on the ENDSUM, however you sparked an inspiration.
I had been toying with the idea of NOPRINTing the field and using the code below, I managed to get it to Hide/Show the columns based on the selection made.


We used a series of SET's at the beginning of the Graph to code the Ledger.
 
-SET &LEDGER1 = &LEDGER1.(<All,All>,<Actual,Actual>,<Budget,Budget>,<Prior Fiscal Year,PriorFiscalYear>).;
-SET &HIDE1 = IF &LEDGER1 EQ 'Actual' OR 'All' THEN '' ELSE 'NOPRINT';
-SET &HIDE2 = IF &LEDGER1 EQ 'Budget' OR 'All' THEN '' ELSE 'NOPRINT';
-SET &HIDE3 = IF &LEDGER1 EQ 'PriorFiscalYear' OR 'All' THEN '' ELSE 'NOPRINT';
-SET &LEDGER2 = IF &LEDGER1 EQ 'All' THEN _FOC_NULL ELSE &LEDGER1;
 


And then is the Where Statement
 
SUM KPIFRT.SEG01.FRT AS 'Actual' &HIDE1
KPIFRT.SEG01.BFRT AS 'Budget' &HIDE2
KPIFRT.SEG01.PYFRT AS 'Prior Year Actual' &HIDE3
BY KPIFRT.SEG01.COMPNAME
ACROSS KPIFRT.SEG01.PERIOD
WHERE COMPNAME EQ &COMPNAME.(OR(FIND COMPNAME IN KPICOMPNAME |FORMAT=A40)).COMPNAME:.;
WHERE COUNTRY EQ &COUNTRY.(OR(FIND COUNTRY IN KPICNTRY |FORMAT=A20)).COUNTRY:.;
WHERE QTR EQ &QTR.(OR(FIND QTR IN KPIQTR |FORMAT=A2)).QTR:.;
WHERE LEDGER EQ '&LEDGER2';
 


Thanks for all your help.


WF 81.5, Windows7
AS/400 Database.
All Outputs

 
Posts: 46 | Registered: November 26, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
SUM KPIFRT.SEG01.FRT AS 'Actual' &HIDE1
KPIFRT.SEG01.BFRT AS 'Budget' &HIDE2
KPIFRT.SEG01.PYFRT AS 'Prior Year Actual' &HIDE3
BY KPIFRT.SEG01.COMPNAME
ACROSS KPIFRT.SEG01.PERIOD
WHERE COMPNAME EQ &COMPNAME.(OR(FIND COMPNAME IN KPICOMPNAME |FORMAT=A40)).COMPNAME:.;
WHERE COUNTRY EQ &COUNTRY.(OR(FIND COUNTRY IN KPICNTRY |FORMAT=A20)).COUNTRY:.;
WHERE QTR EQ &QTR.(OR(FIND QTR IN KPIQTR |FORMAT=A2)).QTR:.;
WHERE LEDGER EQ '&LEDGER2';


This is also a good option but you need to do it that way
SUM
 KPIFRT.SEG01.FRT AS 'Actual' &HIDE1
 KPIFRT.SEG01.BFRT AS 'Budget' &HIDE2
 KPIFRT.SEG01.PYFRT AS 'Prior Year Actual' &HIDE3
BY KPIFRT.SEG01.COMPNAME
ACROSS KPIFRT.SEG01.PERIOD
WHERE COMPNAME EQ &COMPNAME.(OR(FIND COMPNAME IN KPICOMPNAME |FORMAT=A40)).COMPNAME:.;
WHERE COUNTRY EQ &COUNTRY.(OR(FIND COUNTRY IN KPICNTRY |FORMAT=A20)).COUNTRY:.;
WHERE QTR EQ &QTR.(OR(FIND QTR IN KPIQTR |FORMAT=A2)).QTR:.;
WHERE LEDGER EQ '&LEDGER2';

Otherwise you will have an error : MISSING VERB.
When FRT will have to be hidden it will also hide the verb SUM and the other field will miss the SUM


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2192 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Hiding Legend within Graph on Filtering

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