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

So I recently added traffic light type indicators to a repor...


robert fuschetto

Recommended Posts

So I recently added traffic light type indicators to a report. The report has Subtotals and a Grand total. The traffic light indictors do not appear on the Subtotals or Grand total Any clue what I need to do to get them there. I use Wingdings.

Here is a sample using garbage data but you get the idea:

 

image.png862436 16.3 KB

Link to comment
Share on other sites

TABLE FILE GGSALES

"Gotham Grinds Sales Report"

SUM UNITS/I9C

COMPUTE CIRCLE/A2 = CHAR(108); AS ''

-* Major Sort - Region

BY REGION

ON REGION SUBTOTAL UNITS FST. CIRCLE AS 'Total '

-* Minor Sort - State

BY ST

-* Grandtotal

ON TABLE SUBTOTAL UNITS FST. CIRCLE AS 'Grand Total'

-*

ON TABLE SET HTMLCSS ON

-*

ON TABLE SET STYLE *

INCLUDE=ENBlack_dark.sty, $

-* Body of report.

TYPE=DATA, COLUMN=CIRCLE, SIZE=10, JUSTIFY=CENTER, FONT=WINGDINGS, $

TYPE=DATA, COLUMN=CIRCLE, WHEN=UNITS GE 300000, COLOR=GREEN, $

TYPE=DATA, COLUMN=CIRCLE, WHEN=UNITS LT 300000, COLOR=RED, $

-* Region Subtotal.

TYPE=SUBTOTAL, COLUMN=CIRCLE, SIZE=10, JUSTIFY=CENTER, FONT=WINGDINGS, $

TYPE=SUBTOTAL, COLUMN=CIRCLE, WHEN=UNITS GE 910000, COLOR=GREEN, $

TYPE=SUBTOTAL, COLUMN=CIRCLE, WHEN=UNITS LT 910000, COLOR=RED, $

-* Report Total.

TYPE=GRANDTOTAL, COLUMN=CIRCLE, SIZE=10, JUSTIFY=CENTER, FONT=WINGDINGS, $

TYPE=GRANDTOTAL, COLUMN=CIRCLE, WHEN=UNITS GE 3000000, COLOR=GREEN, $

TYPE=GRANDTOTAL, COLUMN=CIRCLE, WHEN=UNITS LT 3000000, COLOR=RED, $

ENDSTYLE

END

Link to comment
Share on other sites

I had just created a CARS sample:

 

image.png363566 8.37 KB

 

TABLE FILE CAR

SUM

CAR.BODY.SALES

COMPUTE WrvuImage/A2 = IF CAR.BODY.SALES GT 5000 THEN CHAR(233) ELSE IF CAR.BODY.SALES LE -5000 THEN CHAR(234) ELSE CHAR(108); AS

BY CAR.ORIGIN.COUNTRY

BY CAR.CARREC.MODEL

ON CAR.ORIGIN.COUNTRY SUBTOTAL AS *TOTAL

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE SUMMARIZE

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

TYPE=DATA,

COLUMN=N1,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N2,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=DATA,

COLUMN=N4,

COLOR=GREEN,

WHEN=N3 GE 5000,

$

TYPE=DATA,

COLUMN=N4,

COLOR=RED,

WHEN=N3 LE -5000,

$

TYPE=TITLE,

COLUMN=N1,

BACKCOLOR=GREEN,

$

TYPE=TITLE,

COLUMN=N2,

BACKCOLOR=GREEN,

$

ENDSTYLE

END

Now I see Davids postlet me absorb itlooks like more work in TEXT view. I wish they would GUI-ize the interface!

THANKS!

Link to comment
Share on other sites

like this

TABLE FILE CAR

SUM

CAR.BODY.SALES

COMPUTE WrvuImage/A2 = IF CAR.BODY.SALES GT 5000 THEN CHAR(233) ELSE IF CAR.BODY.SALES LE -5000 THEN CHAR(234) ELSE CHAR(108); AS

BY CAR.ORIGIN.COUNTRY

BY CAR.CARREC.MODEL

ON CAR.ORIGIN.COUNTRY SUBTOTAL AS *TOTAL

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE SUMMARIZE

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

TYPE=DATA,

COLUMN=N1,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N2,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=DATA,

COLUMN=N4,

COLOR=GREEN,

WHEN=N3 GE 5000,

$

TYPE=DATA,

COLUMN=N4,

COLOR=RED,

WHEN=N3 LE -5000,

$

TYPE=TITLE,

COLUMN=N1,

BACKCOLOR=GREEN,

$

TYPE=TITLE,

COLUMN=N2,

BACKCOLOR=GREEN,

$

TYPE=GRANDTOTAL, COLUMN=N4, SIZE=10, JUSTIFY=CENTER, FONT=WINGDINGS, COLOR=RGB(245 220 40), $

TYPE=GRANDTOTAL, COLUMN=N4, WHEN=N3 GT 5000, COLOR=GREEN, $

TYPE=GRANDTOTAL, COLUMN=N4, WHEN=N3 LT -5000, COLOR=RED, $

ENDSTYLE

END

not quite there yetI get this:

 

image.png485623 10.2 KB

Link to comment
Share on other sites

No, I think the issue is its loosing the WingDing at the subtotal and Grand Total level. It seems to have the right color but is not showing the wingding up / down arrowtake a look:

 

image.png409584 8.69 KB

 

TABLE FILE CAR

-*ON CAR.ORIGIN.COUNTRY SUBTOTAL AS *TOTAL

SUM

CAR.BODY.SALES

COMPUTE WrvuImage/A2 = IF CAR.BODY.SALES GT 5000 THEN CHAR(233) ELSE IF CAR.BODY.SALES LE -5000 THEN CHAR(234) ELSE CHAR(108); AS

BY CAR.ORIGIN.COUNTRY

BY CAR.CARREC.MODEL

ON CAR.ORIGIN.COUNTRY SUBTOTAL

CAR.BODY.SALES

WrvuImage AS *TOTAL

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE SUMMARIZE

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

TYPE=DATA,

COLUMN=N1,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N2,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=DATA,

COLUMN=N4,

COLOR=GREEN,

WHEN=N3 GE 5000,

$

TYPE=DATA,

COLUMN=N4,

COLOR=RED,

WHEN=N3 LE -5000,

$

TYPE=TITLE,

COLUMN=N1,

BACKCOLOR=GREEN,

$

TYPE=TITLE,

COLUMN=N2,

BACKCOLOR=GREEN,

$

TYPE=SUBTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=SUBTOTAL,

COLUMN=N4,

COLOR=GREEN,

WHEN=N3 GT 5000,

$

TYPE=SUBTOTAL,

COLUMN=N4,

COLOR=RED,

WHEN=N3 LT -5000,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

COLOR=GREEN,

WHEN=N3 GT 5000,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

COLOR=RED,

WHEN=N3 LT -5000,

$

ENDSTYLE

END

Link to comment
Share on other sites

Well, we have a problemwhy is it when I remove the subtotal (see comments below) the GRAND TOTAL no longer works

TABLE FILE CAR

SUM

CAR.BODY.SALES

COMPUTE WrvuImage/A2 = IF CAR.BODY.SALES GT 5000 THEN CHAR(233) ELSE IF CAR.BODY.SALES LE -5000 THEN CHAR(234) ELSE CHAR(108); AS

BY CAR.ORIGIN.COUNTRY

BY CAR.CARREC.MODEL

-ON CAR.ORIGIN.COUNTRY SUBTOTAL

- CAR.BODY.SALES

-* WrvuImage AS *TOTAL

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE SUMMARIZE

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

TYPE=DATA,

COLUMN=N1,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N2,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=GREEN,

WHEN=N3 GE 5000,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=RED,

WHEN=N3 LE -5000,

$

TYPE=TITLE,

COLUMN=N1,

BACKCOLOR=GREEN,

$

TYPE=TITLE,

COLUMN=N2,

BACKCOLOR=GREEN,

$

TYPE=SUBTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=SUBTOTAL,

COLUMN=N4,

COLOR=GREEN,

FONT=WINGDINGS,

WHEN=N3 GT 5000,

$

TYPE=SUBTOTAL,

COLUMN=N4,

COLOR=RED,

FONT=WINGDINGS,

WHEN=N3 LT -5000,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=GREEN,

WHEN=N3 GT 5000,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=RED,

WHEN=N3 LT -5000,

$

ENDSTYLE

END

 

image.png409503 6.99 KB

Link to comment
Share on other sites

alsois it true that if you are going to do WINDINGSyou must explicitly specify every column in a subtotal. In text mode

Ex: My real report without the WINGDINGS simply generated this code using the GUI:

ON HOLDMTDYTDUNITSWRVUS.SEG01.IDXSPECIALTY SUBTOTAL AS *TOTAL

When run it simply puts a subtotal underneath each column.

However I noticed to get the WINGDINGS to work I had to manually change it to this:

ON HOLDMTDYTDUNITSWRVUS.SEG01.IDXSPECIALTY SUBTOTAL

HOLDMTDYTDUNITSWRVUS.SEG01.MTD

HOLDMTDYTDUNITSWRVUS.SEG01.MTDBUDGET

HOLDMTDYTDUNITSWRVUS.SEG01.VARMTD

WrvuImage

HOLDMTDYTDUNITSWRVUS.SEG01.MTDPRIOR

HOLDMTDYTDUNITSWRVUS.SEG01.YTD

HOLDMTDYTDUNITSWRVUS.SEG01.YTDBUDG

HOLDMTDYTDUNITSWRVUS.SEG01.VARYTD

WrvuImage2

HOLDMTDYTDUNITSWRVUS.SEG01.YTDPRIOR

Link to comment
Share on other sites

robertf:

 

why is it when I remove the subtotal (see comments below) the GRAND TOTAL no longer works

 

 

As indicated in David Briars example, you need a prefix operator (FST.) for the summarize or subtotaling of WrvuImage. This means in your example you would have you summarize line like this:

ON TABLE SUMMARIZE CAR.BODY.SALES FST. WrvuImage

 

Sometimes it does seem to work without this, but I dont think it will work reliably without it.

For the summarize line of code:

ON CAR.ORIGIN.COUNTRY SUBTOTAL CAR.BODY.SALES FST. WrvuImage

 

If you code like this then you may not need the extra FONT='WINGDINGS' previously suggested.

Link to comment
Share on other sites

  • 2 weeks later...

Thanks for the reply Dave. I am back on this after a couple of weeks of other chaos!

I have never used FST. anywhere. I also noticed my code is not 100% working, so perhaps I should get that done first.

Note below the subtotal for ITALY shows a green dotshould be a green arrow:

 

here is the code, what would you suggest changing What is wrong Once this works, Id like to try to remove the SUBTOTAL and see what happens.

TABLE FILE CAR

SUM

CAR.BODY.SALES

-*COMPUTE WrvuImage/A2 = IF CAR.BODY.SALES GT 5000 THEN CHAR(233) ELSE IF CAR.BODY.SALES LE -5000 THEN CHAR(234) ELSE CHAR(108); AS

COMPUTE WrvuImage/A2 = IF CAR.BODY.SALES GT 5000 THEN CHAR(233) ELSE CHAR(108); AS

BY CAR.ORIGIN.COUNTRY

BY CAR.CARREC.MODEL

ON CAR.ORIGIN.COUNTRY SUBTOTAL

CAR.BODY.SALES

WrvuImage AS *TOTAL

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE SUMMARIZE

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

TYPE=DATA,

COLUMN=N1,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N2,

COLOR=NAVY,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=GREEN,

WHEN=N3 GE 5000,

$

TYPE=DATA,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=RED,

WHEN=N3 LE -5000,

$

TYPE=TITLE,

COLUMN=N1,

BACKCOLOR=GREEN,

$

TYPE=TITLE,

COLUMN=N2,

BACKCOLOR=GREEN,

$

TYPE=SUBTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=SUBTOTAL,

COLUMN=N4,

COLOR=GREEN,

WHEN=N3 GT 5000,

$

TYPE=SUBTOTAL,

COLUMN=N4,

COLOR=RED,

WHEN=N3 LT -5000,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

SIZE=10,

COLOR=RGB(245 220 40),

JUSTIFY=CENTER,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=GREEN,

WHEN=N3 GT 5000,

$

TYPE=GRANDTOTAL,

COLUMN=N4,

FONT=WINGDINGS,

COLOR=RED,

WHEN=N3 LT -5000,

$

ENDSTYLE

END

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...