stored process AMO issue

[gyambqt]

Hello Experts,

I know when you open a STP file with AMO, the AMO can only accept three type of results , html, csv and SASreport xml. I want to know why when you add the following program at the top of your ods tagset.excel statement, it can trick excel or AMO to open a new excel session?

data _null_;
rc = stpsrv_header('Content-type', 'application/vnd.ms-excel');
run;

[ballardw]

Is the semicolon missing on the ODS Tagsets.excelxp statement an effect of pasting into the forum or not actually in the code submitted?

[gyambqt]

Hi Ballardw,

I have modified my original post, the ; has been added.

[Cynthia_sas]

Hi, STPSRV_HEADER is the way you tell "streaming" results (such as the Info Delivery Portal or the Stored Process Web App) to have the browser (the browser) handle the streaming results from the SP. The browser if it is compliant, should use the helper application specified in the Content-type header to open the content in the stream.

  AMO does not accept streaming results. There is no guarantee that AMO will respect your Content-type header. Generally, if you want to use TAGSETS.EXCELXP, you write a stored process that will execute via URL as a streaming stored process. The URL is executed using the Portal or the Stored Process Web Application. There have  been other postings in this forum about using TAGSETS.EXCELXP. When you use a URL to execute a stored process that creates TAGSETS.EXCELXP XML, it is the BROWSER that launches EXCEL. It is not AMO that is accepting the stored process results. It is the BROWSER that is receiving the TAGSETS.EXCELXP XML and then the BROWSER that is launching EXCEL based on the content-type header. If Excel has the AMO plug-in then that's wonderful, but the Stored Process results could be sent to a machine that had Excel but did NOT have AMO.

  So usually, if you have a Stored Process that creates TAGSETS.EXCELXP results, you do NOT invoke that stored process from within AMO. You usually invoke that stored process from a URL.

cynthia

[gyambqt]

Hi Cynthia,

Thanks for your response.

• Do you mean when I open a STP report using the AMO plug-in  in the Excel, it will execute the STP via URL that will launch a new excel session if I defined the content type=excel, like ('Content-type', 'application/vnd.ms-excel'); in STPSRV_HEADER?
• In your above statement, you mentioned AMO (AMO plug in in excel) does not accept streaming results but in your other post, you have mentioned AMO can accept xml, csv and SASreport xml (I believe SASreport xml is different to xml), but what if the xml,csv and SASreport.xml has been set as a stream result in STP? Im bit confused here.

Thanks

[Cynthia_sas]

Hi:

Let me clarify. It is my understanding that AMO for Excel can accept CSV, HTML and SASReport XML *only* from a stored process -- if you want the content to be "refreshable". In earlier versions of SAS and the Platform, you could not generate TAGSETS.EXCELXP output inside AMO at all.. Just like AMO-Word can ONLY accept RTF, HTML and SASReport XML.

  When you start up the Stored Process Web Application, you are NOT inside Excel. You are inside a browser and are invoking your stored process via URL. If you then invoke a stored process *INSIDE THE BROWSER* and the stored process has an STPSRV_HEADER override, that instructs the browser that the Content-type header is for Excel, then the browser will launch EXCEL. Please see these screen shots. There is a stored process called "try_xp" that invokes TAGSETS.EXCELXP. If I invoke that SP from a the Stored Process Web App, following this process:

1) make simple SP

2) invoke from SPWA

3) browser asks if I want to open

4) I save as myfile.xml

5) open myfile.xml

  Then, if I try to invoke the SP from Excel, with AMO loaded, I get this message that says the SP will run, but the content will NOT be refreshable.

When the file is opened, it has been named "main.xml":

  I believe this message is new in SAS 9.4. So whether you can do what you want will depend on what version of SAS you have on your servers and whether it matters to you if the content is refreshable.

cynthia

[gyambqt]

hi Cynthia,

thanks for for your clarification.

i am interested to know the logic behind for your last two screenshots that to open a Stp with Amo in the excel.

so basically do you mean when you open a stp with Amo loaded in the excel, it is excel itself that receive the result or the xml file from tagset.excelxp rather than Amo when you make the content type = excel in the Stp header statement,that's why you can refresh the content with the Amo.

[Cynthia_sas]

Hi:

  Here's my simplified explanation: First this is a special SP -- Your SP has to invoke TAGSETS.EXCELXP in order to generate that window. What happens is:

1) Your SP creates TAGSETS.EXCELXP output -- in other words, you have designed the SP specifically for TAGSETS.EXCELXP

2) if you run that SP from within EXCEL, then the SAS & AMO controllers detect that it is NOT CSV, HTML or SASReport output that they know how to handle

3) So the AMO controller shows you that message about the fact that the content is not refreshable, and then,

4) if you click "Open Workbook", the AMO controllers pass the ExcelXP XML directly to a Workbook -- so it is more like your SP is creating a static, non-refreshable output in a new Workbook.

  I took that screen shot using a SAS 9.4, EG 6.1 BI Platform image.

cynthia

[gyambqt]

HI Cynthia,

i have replaced the content type with txt/xml rather than application/vnd.ms-excel for stpsrv_header in the excel and it produced an error when I open the stp in the excel With Amo loaded. I thought amo should open a new excel session to interpret the txt/xml output just like if you specify content type =application/vnd.ms-excel.

[Cynthia_sas]

Hi:

  Why did you do that? I showed using a Content-type header for Application/vnd.ms-excel that worked. Text/xml is not the right content-type header if you are using TAGSETS.EXCELXP. "Regular" Text/XML does not imply Excel. It could be Math Markup Language XML or Chem Markup Language XML or any other form of XML that AMO does not know what to do with. You really will need to work with Tech Support on this. They will need to look at ALL of your code and ALL of your SP metadata and give you advice about how to proceed based on the version of the Platform that you are running.

cynthia

[gyambqt]

Thanks so much Cynthia!