Focal Point
Report layout help

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8501018972

April 09, 2008, 03:59 PM
JennyM
Report layout help
I need to create a report that looks like a grid with 10 different information blocks across and a variable number of rows down the page. There will never be more than 10 blocks across. I want these blocks to have behaviour characteristics of certain colors to mean certain things depending on an outcome. (sort of like the stoplight graphics but with more info than a 1 character light graphics). Does anyone know how to approach doing this? I have a layout but cannot figure out how to attach it here. I can e-mail it to whomever feels like slaying this dragon! I am really green with respect to htm and would really appreciate the help. Thanks!


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
April 09, 2008, 04:06 PM
Dan Pinault
JennyM,

There is a chart type in Advanced Graph Assistant called a Spectral Map (under the 'Special' chart types tab). The description reads, "Row or column matrix of markers that are colored according to data values". I've not used it myself but it looks like something that may meet your needs.

Regards,

Dan


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
April 09, 2008, 04:15 PM
JennyM
Thanks Dan, I will look at that and see what I can figure out!


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
April 09, 2008, 04:42 PM
JennyM
Ok, that information is clear as mud, anyone have any idea how to USE that graph?


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
April 09, 2008, 05:35 PM
Francis Mariani
Not sure if you're looking for a graph, but here's an HTML report:

TABLE FILE CAR
SUM
LENGTH WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL

COMPUTE WIDTHFLAG/A1 =
  IF WIDTH LT 258 THEN '1' ELSE
  IF WIDTH FROM 259 TO 399 THEN '2' ELSE '3'; NOPRINT

COMPUTE WBFLAG/A1 =
  IF WIDTH FROM 0 TO 200 THEN '1' ELSE
  IF WIDTH FROM 201 TO 300 THEN '2' ELSE
  IF WIDTH FROM 301 TO 400 THEN '3' ELSE
  IF WIDTH FROM 501 TO 500 THEN '3' ELSE '4'; NOPRINT

BY COUNTRY

ON TABLE SET PAGE NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLESHEET *
BORDER=1, BORDER-COLOR=SILVER, FONT='ARIAL', SIZE=8, $

TYPE=TITLE, STYLE=BOLD, $

TYPE=DATA, COLOR=WHITE, $
TYPE=DATA, COLUMN=COUNTRY, COLOR=BLACK, $

TYPE=DATA, COLUMN=LENGTH, BACKCOLOR=RED, WHEN=LENGTH LT 600, $
TYPE=DATA, COLUMN=LENGTH, BACKCOLOR=GREEN, WHEN=LENGTH GE 600, $

TYPE=DATA, COLUMN=WIDTH, BACKCOLOR=LIME, WHEN=WIDTHFLAG EQ '1', $
TYPE=DATA, COLUMN=WIDTH, BACKCOLOR=CYAN , WHEN=WIDTHFLAG EQ '2', $
TYPE=DATA, COLUMN=WIDTH, BACKCOLOR=GREEN, WHEN=WIDTHFLAG EQ '3', $

TYPE=DATA, COLUMN=HEIGHT, BACKCOLOR='MIDNIGHT BLUE', WHEN=HEIGHT LT 70, $
TYPE=DATA, COLUMN=HEIGHT, BACKCOLOR=GREEN, WHEN=HEIGHT GE 70, $

TYPE=DATA, COLUMN=WEIGHT, BACKCOLOR=PINK, WHEN=WEIGHT LT 5000, $
TYPE=DATA, COLUMN=WEIGHT, BACKCOLOR=GREEN, WHEN=WEIGHT GE 5000, $

TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR='CADET BLUE', WHEN=WBFLAG EQ '1', $
TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR=GOLDENROD, WHEN=WBFLAG EQ '2', $
TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR=TURQUOISE, WHEN=WBFLAG EQ '3', $
TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR=MAGENTA, WHEN=WBFLAG EQ '4', $

TYPE=DATA, COLUMN=FUEL_CAP, BACKCOLOR='MEDIUM SLATE BLUE', WHEN=FUEL_CAP LT 30, $
TYPE=DATA, COLUMN=FUEL_CAP, BACKCOLOR=ORANGE, WHEN=FUEL_CAP GE 31, $

TYPE=DATA, COLUMN=BHP, BACKCOLOR=BLUE, WHEN=BHP LT 300, $
TYPE=DATA, COLUMN=BHP, BACKCOLOR=GREEN, WHEN=BHP GE 301, $

TYPE=DATA, COLUMN=RPM, BACKCOLOR=KHAKI, WHEN=RPM LE 5000, $
TYPE=DATA, COLUMN=RPM, BACKCOLOR=MAROON, WHEN=RPM GT 5000, $

TYPE=DATA, COLUMN=MPG, BACKCOLOR=RED, WHEN=MPG LT 60, $
TYPE=DATA, COLUMN=MPG, BACKCOLOR=GOLD, WHEN=MPG GE 60, $

TYPE=DATA, COLUMN=ACCEL, BACKCOLOR=PURPLE, WHEN=ACCEL LT 20, $
TYPE=DATA, COLUMN=ACCEL, BACKCOLOR=GREEN, WHEN=ACCEL GE 20, $
ENDSTYLE
END


Looks like:




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
April 09, 2008, 05:47 PM
Prarie
Nice!


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
Francis, that is a very nice example, do you make house calls as the WebFOCUS doctor? Good One


Leah
Francis, the Looks like: did not show anything, can you send me what it actually looks like when it is on the screen, please?


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
Never mind Francis, I copied this into our hints and suggestions procedure area and ran it and I see the results. That is similar to what i want but i need the ability to have Length, width, heigth, and weight for England in ONE box then Length, width, heigth, and weight for France to the right of that box and another country in a box directly underneath and so on. I can send you a layout of what we are trying to accomplish to your e-mail address.


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
If you change the BY COUNTRY To ACROSS COUNTRY you will get that effect. Then change COLUMN TO ACROSSCOLUMN in the stylesheet.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
quote:
BY COUNTRY To ACROSS COUNTRY you will get that effect. Then change COLUMN TO ACROSSCOLUMN in the stylesheet.

Getting closer! Now I need only 2 countries per line any ideas?


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
I'm not sure what you are asking?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
Jenny,

If I understand you correctly, you can create a dummy sort field whose value is 1 for every other country and 2 for the others. Then you can do ACROSS DUMMYSORT followed by ACROSS COUNTRY as Prarie said.

Hope that helps.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
I will try that and see how it goes. Thanks all!


Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000
I'm not sure if the double ACROSS technique will work, the values of COUNTRY are unique, so I don't think they'll be split two per row.

If your output is PDF, using Labels styling might do the job.

Here's an example:

TABLE FILE CAR
SUM
LENGTH WIDTH HEIGHT
-*WEIGHT WHEELBASE FUEL_CAP
-*BHP RPM MPG ACCEL

COMPUTE WIDTHFLAG/A1 =
  IF WIDTH LT 258 THEN '1' ELSE
  IF WIDTH FROM 259 TO 399 THEN '2' ELSE '3'; NOPRINT

COMPUTE WBFLAG/A1 =
  IF WIDTH FROM 0 TO 200 THEN '1' ELSE
  IF WIDTH FROM 201 TO 300 THEN '2' ELSE
  IF WIDTH FROM 301 TO 400 THEN '3' ELSE
  IF WIDTH FROM 501 TO 500 THEN '3' ELSE '4'; NOPRINT

BY COUNTRY PAGE-BREAK

ON TABLE PCHOLD FORMAT PDF

ON TABLE SET PAGE NOLEAD

ON TABLE SET STYLESHEET *

UNITS=IN, PAGESIZE=LEGAL, ORIENTATION=LANDSCAPE,
LEFTMARGIN=0.25, TOPMARGIN=0.25,
PAGEMATRIX=(2 5), ELEMENT=(4 2.1), GUTTER=(0.5 0),
MATRIXORDER=HORIZONTAL, LABELPROMPT=OFF, $

BORDER=1, BORDER-COLOR=SILVER, FONT='ARIAL', SIZE=8, $

TYPE=TITLE, STYLE=BOLD, $

TYPE=DATA, COLOR=WHITE, $
TYPE=DATA, COLUMN=COUNTRY, COLOR=BLACK, $

TYPE=DATA, COLUMN=LENGTH, BACKCOLOR=RED, WHEN=LENGTH LT 600, $
TYPE=DATA, COLUMN=LENGTH, BACKCOLOR=GREEN, WHEN=LENGTH GE 600, $

TYPE=DATA, COLUMN=WIDTH, BACKCOLOR=LIME, WHEN=WIDTHFLAG EQ '1', $
TYPE=DATA, COLUMN=WIDTH, BACKCOLOR=CYAN , WHEN=WIDTHFLAG EQ '2', $
TYPE=DATA, COLUMN=WIDTH, BACKCOLOR=GREEN, WHEN=WIDTHFLAG EQ '3', $

TYPE=DATA, COLUMN=HEIGHT, BACKCOLOR='MIDNIGHT BLUE', WHEN=HEIGHT LT 70, $
TYPE=DATA, COLUMN=HEIGHT, BACKCOLOR=GREEN, WHEN=HEIGHT GE 70, $

TYPE=DATA, COLUMN=WEIGHT, BACKCOLOR=PINK, WHEN=WEIGHT LT 5000, $
TYPE=DATA, COLUMN=WEIGHT, BACKCOLOR=GREEN, WHEN=WEIGHT GE 5000, $

TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR='CADET BLUE', WHEN=WBFLAG EQ '1', $
TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR=GOLDENROD, WHEN=WBFLAG EQ '2', $
TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR=TURQUOISE, WHEN=WBFLAG EQ '3', $
TYPE=DATA, COLUMN=WHEELBASE, BACKCOLOR=MAGENTA, WHEN=WBFLAG EQ '4', $

TYPE=DATA, COLUMN=FUEL_CAP, BACKCOLOR='MEDIUM SLATE BLUE', WHEN=FUEL_CAP LT 30, $
TYPE=DATA, COLUMN=FUEL_CAP, BACKCOLOR=ORANGE, WHEN=FUEL_CAP GE 31, $

TYPE=DATA, COLUMN=BHP, BACKCOLOR=BLUE, WHEN=BHP LT 300, $
TYPE=DATA, COLUMN=BHP, BACKCOLOR=GREEN, WHEN=BHP GE 301, $

TYPE=DATA, COLUMN=RPM, BACKCOLOR=KHAKI, WHEN=RPM LE 5000, $
TYPE=DATA, COLUMN=RPM, BACKCOLOR=MAROON, WHEN=RPM GT 5000, $

TYPE=DATA, COLUMN=MPG, BACKCOLOR=RED, WHEN=MPG LT 60, $
TYPE=DATA, COLUMN=MPG, BACKCOLOR=GOLD, WHEN=MPG GE 60, $

TYPE=DATA, COLUMN=ACCEL, BACKCOLOR=PURPLE, WHEN=ACCEL LT 20, $
TYPE=DATA, COLUMN=ACCEL, BACKCOLOR=GREEN, WHEN=ACCEL GE 20, $
ENDSTYLE
END


On my lowly WF 5.3.2 server I get this message:

(FOC3228) STYLED REPORTS CURRENTLY LIMITED TO 128 COLUMNS

so I can't specify all the fields in the row...


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
Francis, the double across technique did not work. I had to do some rigging around. but my problem at this point is that the very first column will not do the conditioning styling. Anybody have any bright ideas? Our go live day was today and well we are not ready for this report.
DEFINE FILE ALLPAGES
ACROSS_KEY/I5 = LIST;
 END
TABLE FILE ALLPAGES
PRINT
     PAGECOLOR AS '' OVER
     PRFSTATUS AS '' OVER
     PARTREC AS ''
             PGSREC AS ''
             PRFOUT AS ''
             PRFBACK AS ''
BY FORM_SIDE AS ''
BY ACROSS_KEY AS ''
BY PGIMP# AS ''
ON TABLE HOLD AS ALLPAGE FORMAT ALPHA
END
-RUN
?FF ALLPAGE
-*
TABLE FILE ALLPAGE
SUM
     PAGECOLOR AS '' OVER
     PRFSTATUS AS '' OVER
     PARTREC AS ''
     PGSREC NOPRINT
     PRFOUT NOPRINT
     PRFBACK NOPRINT
BY FORM_SIDE AS ''
ACROSS ACROSS_KEY NOPRINT
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
     DEFMACRO=COND0001,
     MACTYPE=RULE,
     WHEN=N5 EQ 'Y',
$
     DEFMACRO=COND0002,
     MACTYPE=RULE,
     WHEN=N6 EQ 'Y',
$
     DEFMACRO=COND0003,
     MACTYPE=RULE,
     WHEN=N7 EQ 'Y',
$
TYPE=REPORT,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-TOP-COLOR='BLUE',
     BORDER-LEFT-COLOR='BLUE',
     BORDER-RIGHT-COLOR='BLUE',
$ 
TYPE=DATA,
     ACROSSCOLUMN=N1,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-TOP-COLOR='BLUE',
     BORDER-LEFT-COLOR='BLUE',
     BORDER-RIGHT-COLOR='BLUE',
     BACKCOLOR=RGB(255 255 153),
     MACRO=COND0001,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-TOP-COLOR='BLUE',
     BORDER-LEFT-COLOR='BLUE',
     BORDER-RIGHT-COLOR='BLUE',
     BACKCOLOR=RGB(204 255 204),
     MACRO=COND0002,
$
TYPE=DATA,
     ACROSSCOLUMN=N1,
     BORDER-TOP=MEDIUM,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-TOP-COLOR='BLUE',
     BORDER-LEFT-COLOR='BLUE',
     BORDER-RIGHT-COLOR='BLUE',
     BACKCOLOR=RGB(255 217 236),
     MACRO=COND0003,
$
TYPE=DATA,
     ACROSSCOLUMN=N2,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
$
TYPE=DATA,
     ACROSSCOLUMN=N2,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
     BACKCOLOR=RGB(255 255 153),
     MACRO=COND0001,
$
TYPE=DATA,
     ACROSSCOLUMN=N2,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
     BACKCOLOR=RGB(204 255 204),
     MACRO=COND0002,
$
TYPE=DATA,
     ACROSSCOLUMN=N2,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
     BACKCOLOR=RGB(255 217 236),
     MACRO=COND0003,
$
TYPE=DATA,
     ACROSSCOLUMN=N3,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
$
TYPE=DATA,
     ACROSSCOLUMN=N3,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
     BACKCOLOR=RGB(255 255 153),
     MACRO=COND0001,
$
TYPE=DATA,
     ACROSSCOLUMN=N3,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
     BACKCOLOR=RGB(204 255 204),
     MACRO=COND0002,
$
TYPE=DATA,
     ACROSSCOLUMN=N3,
     BORDER-TOP=OFF,
     BORDER-BOTTOM=OFF,
     BORDER-LEFT=MEDIUM,
     BORDER-RIGHT=MEDIUM,
     BORDER-COLOR='BLUE',
     BACKCOLOR=RGB(255 217 236),
     MACRO=COND0003,
$
TYPE=REPORT,
     COLUMN=N6,
     WRAP=8.000000,
$
ENDSTYLE
END
  



Jen
WF7.1.4
Win/nt/win2003
HTML/PDF/Excel2000