steve hodos Posted December 9, 2021 Share Posted December 9, 2021 In excel, you can highlight / format cells using Conditional Formatting / Data Bars / Gradient Fill (see screenshot): My questions are: is this possible to do inside a WebFOCUS stylesheet if not, is is possible to get WebFOCUS to tell Excel to do this for specified columns Thanks, Steve Link to comment Share on other sites More sharing options...
Warren Hinchliffe Posted December 9, 2021 Share Posted December 9, 2021 I suspect not. May need to ask TIBCO support. Would be a great new feature. One option would be an Excel template. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted December 10, 2021 Share Posted December 10, 2021 Probably can only be performed with an Excel macro within the template since the Excel Conditional Formatting needs to read the data to be applied. Link to comment Share on other sites More sharing options...
John Gelona Posted December 10, 2021 Share Posted December 10, 2021 I have found 2 ways to do things in Excel that WebFOCUS cant do. One is to use CSS, the other is Excel Macros. So far, I havent found anything I need to do in Excel that WebFOCUS cant do or easily do that cant be done in a macro. From simple thing like Freezing Panes and turning on filters to Filtered Subtotals, Group Borders, Insert cell comments, rotate cell contents, Vertical centering cells, etc. Link to comment Share on other sites More sharing options...
David Briars Posted December 10, 2021 Share Posted December 10, 2021 Is your requirement to add a data bar within a HTML grid report, using WF StyleSheet syntax If so have you looked at the GRAPHTYPE StyleSheet commands DEFINE FILE GGSALES DIFFERENCE/D7M=BUDDOLLARS-DOLLARS; END TABLE FILE GGSALES BY CITY SUM BUDDOLLARS/D7M DOLLARS/D7M DIFFERENCE AS 'Difference' ON TABLE SET PAGE-NUM OFF ON TABLE SET STYLESHEET * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ GRAPHTYPE=DATA, COLUMN=DIFFERENCE, GRAPHLENGTH=1, GRAPHCOLOR=GREEN, $ GRAPHTYPE=DATA, COLUMN=DIFFERENCE, GRAPHLENGTH=1, GRAPHCOLOR=RED, WHEN=DIFFERENCE LT 0, $ ENDSTYLE END Link to comment Share on other sites More sharing options...
steve hodos Posted December 12, 2021 Author Share Posted December 12, 2021 David, nice to hear from you. I knew about GRAPHTYPE, but the customer wants the familiar looking EXCEL formatting, which actaullu is a little nicer, becase it acts as a BACKCOLOR Link to comment Share on other sites More sharing options...
Martin Yergeau Posted December 13, 2021 Share Posted December 13, 2021 You can do the same with BACKCOLOR and can be much more complex if you use MACROs instead of only including a WHEN condition DEFINE FILE GGSALES DIFFERENCE/D7M=BUDDOLLARS-DOLLARS; END TABLE FILE GGSALES BY CITY SUM BUDDOLLARS/D7M DOLLARS/D7M DIFFERENCE AS 'Difference' ON TABLE SET PAGE-NUM OFF ON TABLE SET STYLESHEET * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=GREY, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=GREEN, WHEN=DIFFERENCE GT 50000, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=RED, WHEN=DIFFERENCE LT 0, $ ENDSTYLE END Using MACROs DEFINE FILE GGSALES DIFFERENCE/D7M=BUDDOLLARS-DOLLARS; END TABLE FILE GGSALES BY CITY SUM BUDDOLLARS/D7M DOLLARS/D7M DIFFERENCE AS 'Difference' COMPUTE DIFF /I1 = IF DIFFERENCE GT 130000 THEN 7 ELSE IF DIFFERENCE GT 50000 AND DIFFERENCE LE 130000 AND CITY EQ 'Boston' THEN 6 ELSE IF DIFFERENCE GT 50000 AND DIFFERENCE LE 130000 THEN 5 ELSE IF DIFFERENCE GT 0 AND DIFFERENCE LE 50000 THEN 4 ELSE IF DIFFERENCE LT 0 AND DIFFERENCE GT -50000 THEN 3 ELSE IF DIFFERENCE LE -50000 AND DIFFERENCE GT -110000 THEN 2 ELSE IF DIFFERENCE LE -110000 THEN 1; NOPRINT ON TABLE SET PAGE-NUM OFF ON TABLE SET STYLESHEET * INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$ DEFMACRO=VAL_7, MACTYPE=RULE, WHEN=DIFF EQ 7, $ DEFMACRO=VAL_6, MACTYPE=RULE, WHEN=DIFF EQ 6, $ DEFMACRO=VAL_5, MACTYPE=RULE, WHEN=DIFF EQ 5, $ DEFMACRO=VAL_4, MACTYPE=RULE, WHEN=DIFF EQ 4, $ DEFMACRO=VAL_3, MACTYPE=RULE, WHEN=DIFF EQ 3, $ DEFMACRO=VAL_2, MACTYPE=RULE, WHEN=DIFF EQ 2, $ DEFMACRO=VAL_1, MACTYPE=RULE, WHEN=DIFF EQ 1, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=GREEN, MACRO=VAL_7, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=BLUE, COLOR=WHITE, MACRO=VAL_6, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=RGB(160 0 110), MACRO=VAL_5, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=GREY, MACRO=VAL_4, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=RGB(200 200 0), MACRO=VAL_3, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=ORANGE, MACRO=VAL_2, $ TYPE=DATA, COLUMN=DIFFERENCE, BACKCOLOR=RED, MACRO=VAL_1, $ ENDSTYLE END Link to comment Share on other sites More sharing options...
David Briars Posted December 13, 2021 Share Posted December 13, 2021 Thought you wanted to emulate Excel Color Bars. If you want to emulate Excel Color Scales, then yes, I would look at the BACKCOLOR StyleSheet, as Martin shows. Maybe offer a JSCHART Heatmap to your users as well. Lots of opportunities to meet your users needs with WebFOCUS. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted December 13, 2021 Share Posted December 13, 2021 DavidBriars: Thought you wanted to emulate Excel Color Bars. Also thought that at first but GRAPHTYPE does not work with Excel Link to comment Share on other sites More sharing options...
David Briars Posted December 13, 2021 Share Posted December 13, 2021 Thought OP wanted to emulate Excel Color Bars in an HTML grid report. Link to comment Share on other sites More sharing options...
steve hodos Posted December 13, 2021 Author Share Posted December 13, 2021 Guys, this is great, but sadly, the customer is pretty strict about using the Excel Data bars, both for use as a gradient, and GRAPHTYPE functionality as a BACKCOLOR. Thanks for the suggestions! Link to comment Share on other sites More sharing options...
Martin Yergeau Posted December 13, 2021 Share Posted December 13, 2021 steve.hodos: the customer is pretty strict about using the Excel Data bars Solution : Excel Template with macros Link to comment Share on other sites More sharing options...
Toby Mills Posted December 13, 2021 Share Posted December 13, 2021 As Martin suggests - this is a good job for an excel template. Just pop your data up in a worksheet behind the graph. I had a customer like that in Dallas. The guy was a wizard with Excel - it turned out to be way faster for me to just populate a data worksheet for him and he put the layers on the top like he wanted. Less billable hours for me though Link to comment Share on other sites More sharing options...
steve hodos Posted December 14, 2021 Author Share Posted December 14, 2021 okay, so- one last question before I go start nagging my in-house excel expert: will using an excel template allow me to conditionally call an excel macro - say, for columns a/d/f, and not b/c/e Link to comment Share on other sites More sharing options...
Martin Yergeau Posted December 14, 2021 Share Posted December 14, 2021 You create your Excel Template with a macro recorded then you use this template to populate your WF report. If you are not a wizard with Excel, populate your data in Excel with WF then apply the changes that you want in Excel by applying a recording macro. You may just then apply some little twick but you can find all procedure on how to record a macro and launch it auomatically by searching on the web Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now