Formats Unknown to system running a stored process in Excel thru SAS Add-In

[SAS_Doctor]

I'm running a stored process that uses ods tagsets.excelxp from Excel through SAS Add-In and I am getting the following error:

System.TypeInitializationException: The type initializer for 'SAS.Shared.Formats' threw an exception. ---> System.InvalidOperationException: SAS Shared Files location not found. Formats are not installed ---> System.IO.FileNotFoundException: Main Formats DLL not found
File name: 'C:\Program Files\SAS\SharedFiles\Formats\jazxfbrg.dll'
at SAS.Shared.Formats..cctor()
--- End of inner exception stack trace ---
at SAS.Shared.Formats..cctor()
--- End of inner exception stack trace ---
at SAS.Shared.Formats.FormatDouble(Double toFormat, String formatName, Int32 width, Int32 decimals)
at SAS.Report.Data.DataUtils.FormatNumericValue(Value val, ValueInfo valueInfo)
at SAS.Report.Data.DataSource.FormatValues(EmbeddedData data)
at SAS.Report.Data.DataSource.LoadEmbeddedData()
at SAS.Report.Viewer.Table.LayoutTable(IReportWriter reportWriter, IReportLayout reportLayout)
at SAS.AMO.Reports.Table.LayoutTable()
at SAS.OfficeAddin.ExcelReports.Table.LayoutTable()
at SAS.AMO.Reports.Body.GetMaxTableColumns(Object document, Object renderInfo)
at SAS.AMO.Reports.Section.GetMaxTableColumns(Object document, Object renderInfo)
at SAS.AMO.Reports.Section.GetMaxTableColumns(Object document, Object renderInfo)
at SAS.AMO.Reports.Section.GetMaxTableColumns(Object document, Object renderInfo)
at SAS.AMO.Reports.View.GetMaxTableColumns(Object document, Object renderInfo)
at SAS.OfficeAddin.ExcelAddin.OpenSASReport(JobContext context)

[Cynthia_sas]

Hi:
A few comments:
1) you might want to search the Stored Process forum for TAGSETS.EXCELXP related postings, because,

2) The Add-in for Microsoft Office, specifically Excel, can ONLY receive these types of results from stored processes:
--HTML
--CSV
--SASReport XML
...therefore, I would expect that your stored process will just not work at all when you are in AMO and try to run a Stored Process that uses TAGSETS.EXCELXP. I think that it is likely that your message has nothing to do with the user-defined formats. (In contrast, the PowerPoint Add-in for Microsoft Office can ONLY receive SASReport XML results; and the Word Add-in for Microsoft Office can ONLY receive HTML, RTF and SASReport XML results. SASReport XML is -not- the same type of XML as that created by TAGSETS.EXCELXP.)

3) If you want to use TAGSETS.EXCELXP for stored process results, your stored process has to run either in the Information Delivery Portal or through the Stored Process Web App. If you are going to use either of these methods of submitting the stored process, then you do need to use the STPSRV_HEADER function to control the helper application that should open for Excel XML file that gets created by the stored process. (I know that there are postings about this in the Stored Process forum).

cynthia

[CaseySmith]

Cynthia is correct that the SAS Add-in does not currently support ExcelXP tagset output (it is being considered for a future release). However, the SAS Add-in should at least handle this scenario gracefully, and from the callstack I think there is a problem with the formats dll not being found.

[Cynthia_sas]

In the past, when I submitted a TAGSETS.EXCELXP program in a stored process in Excel 2003 using the SAS Add-in, I got a popup window with the message "The SAS Report results for "SP_name" could not be processed." So, you're right, the error message was much more graceful. But I do not have Office 2007 or Office 2010 to test with, so I don't know whether that popup window still exists in 2007 or 2010 AMO. My test image only has Office 2003 available.

The format library (for user-defined formats) should be in a special location, as described here -- http://support.sas.com/kb/40/103.html ... but the formats DLL not being found -- that seems like a possible Tech Support issue.

But still, even with the formats DLL available, and the format library in the correct location, that won't change the fact that TAGSETS.EXCELXP doesn't work with the SAS Add-in for Microsoft Office for Stored Process results.

cynthia

[SAS_Doctor]

Cynthia and Casey,
Thanks for your help. I am new to EG and SAS Server technology. So this a learning experience for me.

The original version of this code produced all of the numbers for the spreadsheet in a dataset that was output using PROC REPORT and it did not use the ExcellXP tagset. That version successfuly populated an excel 2007 spreadsheet. I would open a new spreadsheet and run the stored process from the reports button. But then the users wanted formulas so they could see the "What if" scenarios. So I modified the original version to use the excelxp tagset style element to create formulas. That's when it quit working.

So the main premise is that I want to create a spreadsheet that uses SAS data and embeded formulas in an excel spreadsheet. Can I do it thru the SAS Add-In? Do I have to use the ExcelXP tagset? Or do I need to run it out of SAS Information Delivery Portal or SAS Web Report Studio?

[Cynthia_sas]

Hi:
As long as the stored process returns HTML results, then Excel will open the HTML file gracefully using the SAS Add-in for Microsoft Office. As far as I know, there is no way to send a formula to Excel using HTML.

TAGSETS.EXCELXP will allow you to send a formula using the TAGATTR style attribute, however, as you discovered, TAGSETS.EXCELXP cannot have its form of XML received by the SAS Add-in for Microsoft Office.

TAGSETS.EXCELXP "flavor" of XML will NOT work with the SAS Add-in for Microsoft Office and Excel. It will NOT work with Word or PowerPoint. It will NOT work with Web Report Studio. It -might- work with Enterprise Guide, however, Enterprise Guide will not open the TAGSETS.EXCELXP file and it will NOT launch Excel for you (although EG can create a TAGSETS.EXCELXP output file).

The only way I have ever successfully received TAGSETS.EXCELXP output from a stored process was to run the stored process using the Information Delivery Portal or using the Stored Process Web Application. There have been previous forum postings about coding your stored process for this scenario.

cynthia