[CLOSED] Accordion report and displaying computed field
I have a report that the business unit wants to have as an expandable accordion report that also has red/green stoplight images for if the metric is over/under a certain amount. I'm currently using a computed text field to display the image, but the text field does not display at the highest level of the accordion report as it treats it like a subtotal.
Is there another way I could get this stoplight image to display at the highest level? Here's the code, I currently have the computed image fields as A250 (I'll leave out the -SET &DATE date fields to save space):
JOIN
LEFT_OUTER
PLANTYPECODECONVERT WITH VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PLANTYPEDATECODE
IN vwloanservlossmitigationplansinitiated TO MULTIPLE
FOCPLANMIXPLANTYPEINDEX.SEG01.DATECODE IN focplanmixplantypeindex TAG J1 AS J1
END
DEFINE FILE VWLOANSERVLOSSMITIGATIONPLANSINITIATED
CURRENTMONTH/D12 MISSING ON =IF VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGYEAR EQ &CURRENTYEAR AND VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGMONTH EQ &CURRENTMONTH THEN 1 ELSE MISSING;
PREVMONTH/D12 MISSING ON =IF VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGYEAR EQ &CURRENTYEAR AND VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGMONTH EQ &PREVMONTH THEN 1 ELSE MISSING;
PREVYEAR/D12 MISSING ON =IF VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGYEAR EQ &PREVYEAR AND VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGMONTH EQ &PREVMONTH THEN 1 ELSE MISSING;
CURRENTQUARTER/D12 MISSING ON =IF VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGYEAR EQ &CURRENTYEAR AND VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGQUARTER EQ &CURRENTQUARTER THEN 1 ELSE MISSING;
PREVQUARTER/D12 MISSING ON =IF VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGYEAR EQ &PREVQUARTERYEAR AND VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGQUARTER EQ &PREVQUARTER THEN 1 ELSE MISSING;
CURRENTYTD/D12 MISSING ON =IF VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGYEAR EQ &CURRENTYEAR AND VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGMONTH LE &CURRENTMONTH THEN 1 ELSE MISSING;
PREVYTD/D12 MISSING ON =IF VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGYEAR EQ &PREVYEAR AND VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.REPORTINGMONTH LE &CURRENTMONTH THEN 1 ELSE MISSING;
PLANTYPECODECONVERT/MDYY=DATECVT(HDATE(VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PLANTYPEDATECODE, 'YYMD'),'YYMD','MDYY');
END
TABLE FILE VWLOANSERVLOSSMITIGATIONPLANSINITIATED
SUM
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVMONTH AS '&PREVMONTHTITLE'
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTMONTH AS '&CURRENTMONTHTITLE'
COMPUTE MONTHLYVARIANCE/D12.2% MISSING ON = ( ( VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTMONTH - VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVMONTH ) / VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVMONTH ) * 100; AS 'Variance'
COMPUTE MONTHLYVARIANCEIMAGE/A250 MISSING ON = IF MONTHLYVARIANCE LT 0 THEN '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_red.png[/IMG]' ELSE '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_green.png[/IMG]'; AS ''
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVYEAR AS '&PREVYEARTITLE'
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTMONTH AS '&CURRENTMONTHTITLE'
COMPUTE YEARLYVARIANCE/D12.2% MISSING ON = ( ( VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTMONTH - VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVYEAR ) / VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVYEAR ) * 100; AS 'Variance'
COMPUTE YEARLYVARIANCEIMAGE/A250 MISSING ON = IF YEARLYVARIANCE LT 0 THEN '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_red.png[/IMG]' ELSE '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_green.png[/IMG]'; AS ''
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVQUARTER AS '&PREVQUARTERTITLE'
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTQUARTER AS '&CURRENTQUARTERTITLE'
COMPUTE QUARTERLYVARIANCE/D12.2% MISSING ON = ( ( VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTQUARTER - VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVQUARTER ) / VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVQUARTER ) * 100; AS 'Variance'
COMPUTE QUARTERLYVARIANCEIMAGE/A250 MISSING ON = IF QUARTERLYVARIANCE LT 0 THEN '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_red.png[/IMG]' ELSE '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_green.png[/IMG]'; AS ''
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVYTD AS '&PREVYTDTITLE'
VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTYTD AS '&CURRENTYTDTITLE'
COMPUTE YTDVARIANCE/D12.2% MISSING ON = ( ( VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.CURRENTYTD - VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVYTD ) / VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PREVYTD ) * 100; AS 'Variance'
COMPUTE YTDVARIANCEMAGE/A250 MISSING ON = IF YTDVARIANCE LT 0 THEN '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_red.png[/IMG]' ELSE '[IMG]/ibi_apps/run.bip?BIP_REQUEST_TYPE=BIP_RUN&|BIP_folder=IBFS%253A%252FWFC%252FRepository%252FUtilities%252FImages%252F&|BIP_item=bullet_ball_green.png[/IMG]'; AS ''
BY J1.SEG01.HIGHLEVELPLANTYPE
BY VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PLANTYPE
WHERE VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.LOANTYPE EQ 2;
WHERE J1.SEG01.COMBOPART NE 2;
WHERE VWLOANSERVLOSSMITIGATIONPLANSINITIATED.VWLOANSERVLOSSMITIGATIONPLANSINITIATED.PLANTYPEDATECODE NE MISSING;
WHERE J1.SEG01.HIGHLEVELPLANTYPE NE 'HHF';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET EXPANDBYROW ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT,
CALC-LOCATION=TOP,
$
TYPE=SUBTOTAL,
BY=1,
COLOR=RGB(1 1 1),
BACKCOLOR=RGB(167 167 167),
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
JUSTIFY=LEFT,
PAGE-LOCATION=TOP,
$
ENDSTYLE
END
This message has been edited. Last edited by: capples,
WF 8.2.0.3 Windows 10 64bit HTML, AHTML, PDF, Excel
December 23, 2015, 09:06 AM
Danny-SRL
capples,
Could you post a repro based on the CAR file?
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
December 23, 2015, 11:09 AM
capples
Here's a repro based on the CAR file, I used the images that were available in the ibisamp folder.
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
COMPUTE DIFFERENCE/D12.2 = CAR.BODY.DEALER_COST - CAR.BODY.RETAIL_COST;
COMPUTE DIFFERENCEIMAGE/A250 = IF DIFFERENCE LE -1000 THEN '<img src="smplogo1.gif"></img>' ELSE '<img src="smpbkg1.jpg"></img>';
BY CAR.ORIGIN.COUNTRY
BY CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET EXPANDBYROW ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
Thanks, Clint
WF 8.2.0.3 Windows 10 64bit HTML, AHTML, PDF, Excel
December 23, 2015, 11:57 AM
MartinY
It's probably not what you are looking at, but it may be an alternative. The EXPANDBYROW is not supported with Multi verb but with this way you have image also for the upper level:
TABLE FILE CAR
SUM DEALER_COST
RETAIL_COST
COMPUTE DIF /D12.2 = DEALER_COST - RETAIL_COST;
COMPUTE DIFIMG/A250 = IF DIF LE -1000 THEN '<img src="smplogo1.gif"></img>' ELSE '<img src="smpbkg1.jpg"></img>';
BY COUNTRY
SUM DEALER_COST
RETAIL_COST
COMPUTE DIF /D12.2 = DEALER_COST - RETAIL_COST;
COMPUTE DIFIMG/A250 = IF DIF LE -1000 THEN '<img src="smplogo1.gif"></img>' ELSE '<img src="smpbkg1.jpg"></img>';
BY COUNTRY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
December 29, 2015, 01:09 PM
capples
Thanks for the suggestion, but it's not quite what I'm looking for. I don't think there is really a good way to do this, so I'll just settle on having two separate reports with a drilldown between the two.
Thanks, Clint
WF 8.2.0.3 Windows 10 64bit HTML, AHTML, PDF, Excel