topic Traffic lighting problems in Excel Add-In with tagsets.sasreport12 in Microsoft Integration with SAS

Traffic lighting problems in Excel Add-In with tagsets.sasreport12

Quentin — Wed, 18 Jul 2012 14:15:49 GMT

Hi,

I'm using Excel add-in to run a stored process that runs PROC REPORT to make a table with some traffic lighting. I'm using default settings, and %stpbegin %stpend, so the ODS destination is tagsets.sasreport12.

When the report is viewed in Excel I don't see the traffic lighting.

If I view the report in EG I do see the traffic lighting.

And of course I can see the traffic lighting in the xml:

<CSV>

"Slipper", " $68,641";

"Sport Shoe", " $1,690"<Value style:color="#FF0000" />;

"Women's Casual", " $51,541";

"Women's Dress", " $108,942";

</CSV>

Does this mean it is a problem with how Excel interprets the xml? Any useful alternatives for doing traffic-lighting in a way that will work for the add-in?

I've got Office Add-in 4.3, Linux server running 9.3, and Excel 2003.

I can replicate the problem with just the sample code below.

ods tagsets.sasreport12
    ENCODING="UTF8" 
    STYLE=EGDefault 
    STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASAddinforMicrosoftOffice/4.3/Styles/AMODefault.css") 
    body="~/Junk/TrafficLight.srx"
    CHARSET=' '
    ;
  proc report data=sashelp.shoes (obs=10) nowd;
    col product sales;
    compute sales;
     if product="Sport Shoe" then call define
        (_COL_ ,'style','style=[foreground=red]');
    endcomp;
  run;
ods tagsets.sasreport12 close;

Thanks,

--Q.

Re: Traffic lighting problems in Excel Add-In with tagsets.sasreport12

Cynthia_sas — Thu, 19 Jul 2012 03:23:59 GMT

Hi:

Do you explicitly override the destination or style in your stored process? Or, do you just take the default result type from inside Excel? If you are allowing client-side choices to control the results, then go to your Options window inside AMO (see screemshot) and do this:

1) try SASReport, and click the Apply Style check box and rerun the stored process

2) try HTML as the result type and click the Apply Style check box and rerun the stored process

If neither of those works (or if the HTML works and the SASReport still doesn't work), then your best bet for help is to open a track with Tech Support on this. Your stored process may have some other settings or overrides that are preventing the traffic lighting from being used.

cynthia

Re: Traffic lighting problems in Excel Add-In with tagsets.sasreport12

Quentin — Thu, 19 Jul 2012 12:47:26 GMT

Thanks Cynthia,

I will open a track with tech support.

I allow client-side choices to specify the destination. When I use SASreport I do not see the tradffic lighting in Excel. When I change to html I do see the traffic lighting. I can replicate outside of stored processing, i.e. just running the code in my first post in base SAS, to write a .srx file. I have attached the result.

When I open this .srx file in Enterprise Guide, I see the traffic lighting.

When I open the .srx file in a text editor, I see the tag for the traffic lighting.

When I open the .srx file in the Excel add-In, I don't see the traffic lighting.

So my guess is it's a problem that Excel (at least the old 2003 version of Excel) is having a hard time parsing/honoring the <Value style:color="#FF0000" /> tag in the xml.

Curious as to whether someone with a more current version of Excel might be able to open the .srx file using the Excel add-on and see if the coloring is honored?

Thanks,

--Q.

Re: Traffic lighting problems in Excel Add-In with tagsets.sasreport12

Quentin — Tue, 24 Jul 2012 13:56:18 GMT

Hi,

Tech support responded that this is a bug resulting in the Excel Add-On not honoring the traffic lighting. I assume it is a bug in how the xml is generated, or the schema used to read the xml (?).

They think it may be related to http://support.sas.com/kb/20/798.html , (which references a problem reported as fixed in 9.2). They are going to re-open that issue (or create a new one).

Their suggestion in the mean time was to use html to send traffic-lighted reports back to the Office add-on. But then, of course, you lose all the neat benefits of the SASREPORT format (http://blogs.sas.com/content/sasdummy/2012/02/22/about-sas-report-format/).

Thanks,

--Q.