Hi all,

I like the visualize feature that comes with WebFOCUS but the way it's setup out of the box seems to be inefficient. Is there a way to display the report so that the data the visualize bars are tied to sits underneath (in the same cell) the data (ie: data and bar would be in same column instead of split out into two)?

Thanks for any and all advice,
B

This message has been edited. Last edited by: Kerry,

The only way I've seen data visualisation do this is with an ACROSS.

TABLE FILE CAR
SUM 
     SALES
BY COUNTRY
ACROSS SEATS
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,SQUEEZE=ON,ORIENTATION=PORTRAIT,$
     GRAPHTYPE=DATA,ACROSSCOLUMN=N1,GRAPHCOLOR='BLACK',$
TYPE=REPORT,GRID=OFF,FONT='ARIAL',SIZE=9,$
TYPE=TITLE,STYLE=BOLD,$
TYPE=ACROSSVALUE,SIZE=9,$
TYPE=ACROSSTITLE,STYLE=BOLD,$
END

This is a manual method (I only have it working for HTML at the moment):

-* File vi2s.fex - Simulate Visualization (Adapted from CatiFrance3.fex (from FocalPoint) - HTML only

-*-- Set scale - used to calculate width
-SET &SCALE = 600;

SET HOLDLIST = PRINTONLY
SET PAGE     = NOPAGE

TABLE FILE CAR
-*-- Calculate Total Retail Cost - used to calculate width (column notation C1)
SUM
RETAIL_COST NOPRINT

-*-- Calculate Retail Cost By Country By Seats
SUM
RETAIL_COST NOPRINT
COMPUTE ARETAIL_COST1/A14 = FTOA(RETAIL_COST, '(D10)', 'A14'); NOPRINT
COMPUTE BAR_WIDTH/I4  = RETAIL_COST / C1 * &SCALE; NOPRINT
COMPUTE BAR_COLOUR/A11=
  IF RETAIL_COST LT 10000 THEN 'red' ELSE
  IF RETAIL_COST GT 40000 THEN 'blue' ELSE 'black'; NOPRINT
COMPUTE BAR/A200 = '<span style="background-color: ' | BAR_COLOUR | '; width: ' | EDIT(BAR_WIDTH) | 'px;">' | ARETAIL_COST1 | '</span>';
                    AS 'Retail Cost'
BY COUNTRY          AS 'Country'
BY SEATS            AS 'Seats'

HEADING
"Simulated Visualization Demo"

ON TABLE NOTOTAL

ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, GRID=OFF, FONT='Arial', SIZE=9, $
TYPE=HEADING, STYLE=BOLD, $
-*-- Style for text within Visualization Bar
TYPE=DATA, COLUMN=BAR, COLOR=WHITE, $
END

Very nice effect of the HTML visualization bars you achieved there Francis!

B, did you try Waz example? As he mentioned, ACROSS seems to be the only out-of-the-box feature that allows you to have data right below the visualization graph. If you only need one "visualization" column in your report, you can achieve the same effect by using a dummy field (borrowing Waz's example):

DEFINE FILE CAR
DUMMY/A5 WITH CAR = ' ';
END
TABLE FILE CAR
SUM 
     COUNTRY
     SALES
BY COUNTRY NOPRINT
ACROSS DUMMY AS ' '
HEADING
"Visualize in one column using ACROSS"
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT,  UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $
TYPE=REPORT,  GRAPHTYPE=DATA, COLUMN=N3, GRAPHCOLOR='BLACK',$
TYPE=REPORT,  GRID=OFF, FONT='ARIAL',SIZE=9, $
TYPE=TITLE,   STYLE=BOLD, $
TYPE=HEADING, STYLE=BOLD, $
TYPE=DATA,    BORDER=LIGHT, $
ENDSTYLE
END

This works in both HTML and PDF.

This only works in HTML. The first TABLE produces the bar chart with totals; the second TABLE generates the data matrix. The maximum height and width of the bars can be controlled in the stylesheet. It takes a fair amount of trial-and-error with the non-compressible spaces (nbsp) to get the bars and data columns to line up - however, if you put the totals in the data matrix (ON TABLE COLUMN-TOTAL) as well as the bar chart, very little effort is required. Interestingly, some stylesheet attributes (e.g., ACROSSVALUE) are applied to both outputs, despite being specified in only one query (even when a -RUN separates the two queries).

DEFINE FILE GGSALES
 SPACE1/A6    = '&' | 'nbsp;';
-* SPACE2/A12   = '&' | 'nbsp;' | '&' | 'nbsp;';
-* SPACE3/A18   = '&' | 'nbsp;' | '&' | 'nbsp;' | '&' | 'nbsp;';
 SPACE4/A24   = '&' | 'nbsp;' | '&' | 'nbsp;' | '&' | 'nbsp;' | '&' | 'nbsp;';
 DUMMY/A75    = '<BR><BR><BR><BR>Totals' | SPACE4 | SPACE1 ;
 YRMTH/MYY    = DATE ;
 YRMTHX/A6MYY = YRMTH ;
 YRMONTH/A50  = SPACE4 | EDIT(YRMTHX,'99/9999') | SPACE1 ;
END
-*
TABLE FILE GGSALES
 HEADING
  "Region Sales by Month"
 SUM DOLLARS/D8M AS ''
 BY DUMMY AS ''
 ACROSS YRMTH NOPRINT
 WHERE DATE GE '19970101'
 ON DUMMY UNDER-LINE
-*ON TABLE SET ACROSSLINE OFF
 ON TABLE NOTOTAL
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
  UNITS=IN, SQUEEZE=ON, ORIENTATION=LANDSCAPE, GRID=OFF, WRAP=OFF, $
  GRAPHTYPE=DATA, ACROSSCOLUMN=DOLLARS, GRAPHCOLOR=BLUE, GRAPHLENGTH=2, GRAPHWIDTH=0.5, $
  TYPE=HEADING, SIZE=20, STYLE=BOLD, JUSTIFY=CENTER, $
  TYPE=REPORT, COLUMN=DUMMY, STYLE=-UNDERLINE, $
  TYPE=DATA, STYLE=BOLD, JUSTIFY=CENTER, $
 ENDSTYLE
END
-*
TABLE FILE GGSALES
 SUM DOLLARS/D8M AS ''
 BY REGION  AS ''
 ACROSS YRMONTH AS ''
 WHERE DATE GE '19970101'
 ON TABLE SET ACROSSLINE SKIP
 ON TABLE NOTOTAL
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
  UNITS=IN, SQUEEZE=ON, ORIENTATION=LANDSCAPE, GRID=OFF, WRAP=OFF, $
  TYPE=ACROSSVALUE, STYLE=BOLD +UNDERLINE, JUSTIFY=CENTER, $
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

There is one other option.

If you had a set of images from _ to    you could calc which image to use.

Then use OVER to place the image under the value or visa-versa.

Thanks for all of the reply's and sorry for the late response - I was out of the office yesterday.

Francis, thanks for the code. That is exactly what I'm looking for. I appreciate the help from everyone!

I have a requirment to see if this can be done with bars under values in the output. Has anyone explored this further with WF 8/AppStudio? Please let me know.

quote:

Originally posted by Francis Mariani:
This is a manual method (I only have it working for HTML at the moment):

-* File vi2s.fex - Simulate Visualization (Adapted from CatiFrance3.fex (from FocalPoint) - HTML only

-*-- Set scale - used to calculate width
-SET &SCALE = 600;

SET HOLDLIST = PRINTONLY
SET PAGE     = NOPAGE

TABLE FILE CAR
-*-- Calculate Total Retail Cost - used to calculate width (column notation C1)
SUM
RETAIL_COST NOPRINT

-*-- Calculate Retail Cost By Country By Seats
SUM
RETAIL_COST NOPRINT
COMPUTE ARETAIL_COST1/A14 = FTOA(RETAIL_COST, '(D10)', 'A14'); NOPRINT
COMPUTE BAR_WIDTH/I4  = RETAIL_COST / C1 * &SCALE; NOPRINT
COMPUTE BAR_COLOUR/A11=
  IF RETAIL_COST LT 10000 THEN 'red' ELSE
  IF RETAIL_COST GT 40000 THEN 'blue' ELSE 'black'; NOPRINT
COMPUTE BAR/A200 = '<span style="background-color: ' | BAR_COLOUR | '; width: ' | EDIT(BAR_WIDTH) | 'px;">' | ARETAIL_COST1 | '</span>';
                    AS 'Retail Cost'
BY COUNTRY          AS 'Country'
BY SEATS            AS 'Seats'

HEADING
"Simulated Visualization Demo"

ON TABLE NOTOTAL

ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, GRID=OFF, FONT='Arial', SIZE=9, $
TYPE=HEADING, STYLE=BOLD, $
-*-- Style for text within Visualization Bar
TYPE=DATA, COLUMN=BAR, COLOR=WHITE, $
END

I tried this, but in my output, the highlighting is only as wide as the number that displays - ie all of the 5-digit values are the same width, regardless of the actual value of the number.

I'm trying to use this because I want to show the visualization but I don't want to display the actual data. I have a column that displays elapsed time in hh:mm:ss, then I display the same value in seconds so I can visualize that. I'd like to NOT display the time in seconds, just the visualization.

I posted the suggestion in 2010, when we were using ancient web browsers. I guess the technique may not work on modern browsers. I'll take a look later today - the code (of which I remember nothing) may require a minor tweak.

Mike,

Change the SPAN into a DIV.

T

quote:

Originally posted by Mike in DeLand:
I tried this, but in my output, the highlighting is only as wide as the number that displays - ie all of the 5-digit values are the same width, regardless of the actual value of the number.

I'm trying to use this because I want to show the visualization but I don't want to display the actual data. I have a column that displays elapsed time in hh:mm:ss, then I display the same value in seconds so I can visualize that. I'd like to NOT display the time in seconds, just the visualization.

Here is a lazy hack of what Francis posted:

-* File vi2s.fex - Simulate Visualization (Adapted from CatiFrance3.fex (from FocalPoint) - HTML only

-*-- Set scale - used to calculate width
-SET &SCALE = 600;

SET HOLDLIST = PRINTONLY
SET PAGE     = NOPAGE

TABLE FILE CAR
-*-- Calculate Total Retail Cost - used to calculate width (column notation C1)
SUM
RETAIL_COST NOPRINT

-*-- Calculate Retail Cost By Country By Seats
SUM
RETAIL_COST AS 'Retail Cost'
COMPUTE ARETAIL_COST1/A14 = FTOA(RETAIL_COST, '(D10)', 'A14'); NOPRINT
COMPUTE BAR_WIDTH/I4  = RETAIL_COST / C1 * &SCALE; NOPRINT
COMPUTE BAR_COLOUR/A11=
  IF RETAIL_COST LT 10000 THEN 'red' ELSE
  IF RETAIL_COST GT 40000 THEN 'blue' ELSE 'black'; NOPRINT
COMPUTE BAR/A200 = '<span style="background-color: ' | BAR_COLOUR | '; width: ' | EDIT(BAR_WIDTH) | 'px;">&|nbsp;</span>';
                    AS ''
BY COUNTRY          AS 'Country'
BY SEATS            AS 'Seats'

HEADING
"Simulated Visualization Demo"

ON TABLE NOTOTAL

ON TABLE SET STYLE *
UNITS=IN, SQUEEZE=ON, GRID=OFF, FONT='Arial', SIZE=9, $
TYPE=HEADING, STYLE=BOLD, $
-*-- Style for text within Visualization Bar
TYPE=DATA, COLUMN=BAR, COLOR=WHITE, $
END

Also check out Susannah's article from circa 1999 - which is still relevant! Susannah's Article

T

I just found out that the code I posted earlier only works in IBI's internal browser or Internet Exploder. I don't know why that is the case.

Changed SPAN to DIV and it works!

Thanks Tony. You're ggrrreeeeeaaaaaaattt!