HTML Code - (Total Coffee row gives desired result of a red ...

[Ben Maxwell]

HTML Code - (Total Coffee row gives desired result of a red light in column 4, and a blank cell in column 5)

 

Excel Code - (Notice that the traffic lights are now totally missing in the Total Coffee row)

 

Is there some trick with the code to make the Excel behave the same as the HTML

This is the code that I am using :

TABLE FILE IBISAMP/GGSALES

SUM

GGSALES.SALES01.UNITS

GGSALES.SALES01.DOLLARS

COMPUTE Image/A30 = IF UNITS LE 1000000 THEN images/red_dot.png ELSE images/green_dot.png; AS Traffic,Light

COMPUTE Picture/A30 = IF UNITS LE 1000000 THEN images/red_dot.png ELSE images/green_dot.png; AS Traffic,Light

FOR

GGSALES.SALES01.CATEGORY

Coffee AS Coffee LABEL R0100 OVER

Food AS Food LABEL R0200 OVER

Gifts AS Gifts LABEL R0300 OVER

RECAP R1000(1)/P20CB=R0100+R0200+R0300;

AS Total Items OVER

RECAP R1000(3)=IF R1000(1) GT 5000000 THEN images/red_dot.png ELSE IF R1000(1) LT 5000000 THEN images/green_dot.png;

R1000(4)=IF R1000(1) GT 5000000 THEN images/red_dot.png ELSE IF R1000(1) LT 5000000 THEN images/green_dot.png;

OVER

RECAP R1100(1)/P20CB=100000*(R0100+R0200+R0300);

AS Total Coffee OVER

RECAP R1100(3)=IF R1100(1) GT 5000000 THEN images/red_dot.png ELSE IF R1000(1) LT 5000000 THEN images/green_dot.png;

-* R1100(4)=IF R1100(1) GT 5000000 THEN images/red_dot.png ELSE IF R1000(1) LT 5000000 THEN images/green_dot.png;

ON TABLE SET PAGE NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET XLSXPAGESETS ON

ON TABLE SET STYLE *

$

TYPE=REPORT,

BORDER-TOP=LIGHT,

BORDER-BOTTOM=LIGHT,

BORDER-LEFT=LIGHT,

BORDER-RIGHT=LIGHT,

$

TYPE=DATA,

COLUMN=N4,

IMAGE=(Image),

SIZE=(0.152778 0.166667),

$

TYPE=DATA,

COLUMN=N5,

IMAGE=(Picture),

SIZE=(0.152778 0.166667),

$

TYPE=TITLE,

COLOR=WHITE,

BACKCOLOR=SILVER,

$

ENDSTYLE

END

[Toby Mills]

Hi Ben

When you PCHOLD this for Excel - which format are you using

EXL2K or XLSX

Reason I ask is the you might have more luck with EXL2K because its a web archive.

Also for your RECAPs where youre using an IF THEN ELSE sort of syntax, be sure you always include the very last ELSE to make sure youre getting the right value (otherwise you just get the prior value). Not sure if this matters with your data, but its a good practice.

Also not that its going to help you in this situation, but it looks like you could shorten up your IF THEN ELSEs by doing something like this:

IF R1100(1) GT 5000000 THEN images/red_dot.png ELSE images/green_dot.png;

That way youll catch the 5000000 and youve got your ELSE in there. You can eliminate asking IF again

Let us know about the EXL2K idea. Early on in XLSXs history, we had trouble with styling with XLSX where styling worked okay in EXL2K. Worth a try anyway.

[Ben Maxwell]

Hi Toby,

I was using the XLSX. In the EXL2K version I get this :

 

As for the RECAP I was being lazy for test purposes :slight_smile:

[Toby Mills]

Thanks for the update Ben.

Looks like your example from EXL2K shows that the right value is getting put in the cell, but in EXL2K, Excel doesnt think of those things as URLs to pull up a copy of the image.

What about adding the whole URL to your image instead of just starting with images. something like:

http://yourmachine/directory/images/green_dot.png

Is that possible When Excel starts, I think it loses track of what your browser knows about relative positioning. I suppose it all depends on when (timing wise) the image gets put in the output. Is the image actually encoded and placed inside the output, or does Excel really need to retrieve it from a URL and it renders when you open it

Sorry Im short on time or Id try to answer that one. Id use Fiddler and watch my network traffic while the Excel file opens to see if calls fire off to go get the images.

If its possible to fully qualify the dot.png files, try that. In other words, if you can type in a URL right in your browser and make it show you a red_dot.png, then use that URL in your RECAPs

Just a thought. I bet somebody here knows exactly what you need to do.

[Manoj Chaurasia]

Yes with EXL2K I had to change it to be

'img src=http://servername/approot/sandbox/red_dot.png with the less that and greater than characters at the beginning and end