Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


steve hodos

Recommended Posts

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

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

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

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...