In excel, you can highlight / format cells using Conditiona...

[steve hodos]

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

[Warren Hinchliffe]

I suspect not. May need to ask TIBCO support. Would be a great new feature.

One option would be an Excel template.

[Martin Yergeau]

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.

[John Gelona]

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.

[David Briars]

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

[steve hodos]

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

[Martin Yergeau]

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

[David Briars]

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.

[Martin Yergeau]

DavidBriars:

 

Thought you wanted to emulate Excel Color Bars.

 

 

Also thought that at first but GRAPHTYPE does not work with Excel

[David Briars]

Thought OP wanted to emulate Excel Color Bars in an HTML grid report.

[steve hodos]

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!

[Martin Yergeau]

steve.hodos:

 

the customer is pretty strict about using the Excel Data bars

 

 

Solution : Excel Template with macros

[Toby Mills]

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

[steve hodos]

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

[Martin Yergeau]

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