 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point Conditional BAR and Free Text in FML
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Conditional BAR and Free Text in FML Read-Only TopicGo | Search | Notify | Tools |
| Conditional BAR and Free Text in FML | Login/Join |
| Platinum Member |
I'm using an FML hierarchy to create a report with 3 sections: Revenue, Salaries, and Expenses. I want to use the same report for multiple departments. Some departments only have revenue and expenses and no salaries. My question is how can I conditionally include/exclude the BAR underlines and Free Text (for headings) so the reports with no Salary section have no Salary heading or the bars around the totals if there is no salary information ? Thanks, Bethany Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server | ||
|
| Virtuoso |
That's a tough one that I have not found a way around yet. I've been back doing a lot of FML lately and have been annoyed by the same problem. You can use WHEN EXISTS to remove the data, but the BAR still shows up as would any freeform text. I've thought of possibly adding a conditional sylte to change the font color to white, but didn't like the idea much so never did anything more with that. Any other ideas, anyone? Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Platinum Member |
Darin, I've created a new column in my final output called SALDAT that is a "Y" if there is salary data for the department and null if there isn't any. I'm trying to use an IF statement in the output to skip the code for the salary section if SALDAT is null. Eg. (The green is the IF statements. Disregard the '|' after the < the SET FORMULTIPLE = ON TABLE FILE VU_ACCT_HIER SUM BUDGET AS 'Revised Annual,Budget' ACTUAL AS 'Actuals' COMPUTE CYREMAIN/D12.2 = BUDGET - ACTUAL; AS 'Remaining' COMPUTE CYUSED/D12.2 = (ACTUAL/BUDGET) * 100; AS 'CY,% Used' LYEDACT AS 'Last Year End,Actuals' LYTDACT AS 'LYTD,Actuals' COMPUTE PYREMAIN/D12.2 = LYEDACT - LYTDACT; AS 'LY Used' COMPUTE PYUSED/D12.2 = (LYTDACT/LYEDACT) * 100; AS 'LY,% Used' -IF &LVLS NE 'ALL' GOTO SUMRY; BY DESCR NOPRINT PAGE-BREAK -GOTO NEXTPRT -SUMRY BY ALL NOPRINT -NEXTPRT FOR SUM_ID 25 AS CAPTION OVER 25 WITH CHILDREN 1 WHEN EXISTS AS CAPTION OVER BAR OVER 25 ADD WHEN EXISTS AS 'OPERATING REVENUE TOTAL' LABEL REV OVER BAR OVER 36 AS CAPTION OVER -IF <|SALDAT EQ 'Y' GOTO INCLDSAL ELSE GOTO SKIPSAL; -INCLDSAL 37 AS CAPTION OVER 37 WITH CHILDREN ALL WHEN EXISTS AS CAPTION OVER BAR OVER 37 ADD AS 'OPERATING SALARY TOTAL' LABEL SALTOT OVER BAR OVER -SKIPSAL " " OVER 46 AS 'NON-SALARY DIRECT EXPENSE' OVER 46 WITH CHILDREN 1 WHEN EXISTS AS CAPTION OVER BAR OVER 46 ADD AS 'NON-SALARY DIRECT EXPENSE TOTAL' LABEL EXP OVER BAR OVER -IF <|SALDAT EQ 'Y' GOTO INCSAL ELSE GOTO SKIPSAL2; -INCSAL RECAP EXPTOT = SALTOT + EXP; AS 'OPERATING EXPENSE TOTAL' OVER RECAP EXPTOT(4) = (EXPTOT(2)/EXPTOT(1)) * 100; OVER RECAP EXPTOT(8) = (EXPTOT(6)/EXPTOT(5)) * 100; OVER BAR OVER RECAP ALLTOTAL/P12.2CB = REV - SALTOT - EXP; AS 'TOTAL' OVER RECAP ALLTOTAL(4)/P5.2% = (ALLTOTAL(2)/ALLTOTAL(1) * 100); OVER RECAP ALLTOTAL(8)/P5.2% = (ALLTOTAL(6)/ALLTOTAL(5) * 100); OVER -GOTO FINRPT2 -SKIPSAL2 RECAP EXPTOT = EXP; AS 'OPERATING EXPENSE TOTAL' OVER RECAP EXPTOT(4) = (EXPTOT(2)/EXPTOT(1)) * 100; OVER RECAP EXPTOT(8) = (EXPTOT(6)/EXPTOT(5)) * 100; OVER BAR OVER RECAP ALLTOTAL/P12.2CB = REV - EXP; AS 'TOTAL' OVER RECAP ALLTOTAL(4)/P5.2% = (ALLTOTAL(2)/ALLTOTAL(1) * 100); OVER RECAP ALLTOTAL(8)/P5.2% = (ALLTOTAL(6)/ALLTOTAL(5) * 100); OVER -FINRPT2 BAR AS '=' WHERE BUDGET NE 0 OR ACTUAL NE 0 OR LYEDACT NE 0 OR LYTDACT NE 0; HEADING "Loyola University Chicago" "Report of Operating Expenses Excluding Scholarships" "<DESCR" "&ENDDATE" "" FOOTING "<SALDAT " ON TABLE SET PAGE-NUM OFF ON TABLE PCHOLD FORMAT &WFFMT END Can you refer to a value in the table in an IF statement using the "<" before the column name like " BethanyThis message has been edited. Last edited by: Bethany, Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server | |||
|
| Expert |
Bethany, unfortunately you can't refer to data values when mixing Dialogue Manager commands and WebFOCUS code, -IF is Dialogue Manager and relies on variables not data. I haven't looked at your code in detail, but here's a possibility: Create a compound report, one part would be for Departments with Revenue, Expenses and Salaries, the other part would be for Departments with Revenue, Expenses and no Salaries. SInce this would be two different TABLE commands, you could leave out the Salary related code in the secomnd part of the compound report. This, of course, means that the Departments would not be in alphanumeric order in the compound report but split into two different sections which could be highlighted in the report heading. Just an idea... 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 | |||
|
| Platinum Member |
Great idea Francis. The compound report works. Bethany Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server | |||
|
| Virtuoso |
Francis already gave the answer. There isn't a way to reference fields in Dialogue Manager except by holding them and then reading them in as &variables. Francis solution would possible work (except for the sorting), but in my case, I have other "sub" sections with BARs before totals so i would have to create separate reports for each section. Then that is defeating the whole purpose of using hierarchies, because everything is hard-coded. argh! What we need is a conditional BAR that checks to see if the previous line(s) exists BAR AS '-' WHEN EXISTS LABEL1, LABEL2, LABEL3 OR functionality that provides an automatic subtotal for a rollup section that includes a BAR. Currently you must explicitly specify the subtotal line 'value' ADD ALL. You can get them automatically by 'value' WITH CHILDREN ALL ADD but it appears at the top of the section instead of as a bottom like NORMAL subtotals. Guess it's time for a new feature request. Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Conditional BAR and Free Text in FML
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Conditional BAR and Free Text in FMLCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |