Your SAS programs, embedded in web apps and elsewhere

stored process AMO issue

Reply
Regular Contributor
Posts: 152

stored process AMO issue

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;

Super User
Posts: 11,343

Re: stored process http header issue

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

Regular Contributor
Posts: 152

Re: stored process http header issue

Hi Ballardw,

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

SAS Super FREQ
Posts: 8,868

Re: stored process AMO issue

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

Regular Contributor
Posts: 152

Re: stored process AMO issue

Posted in reply to Cynthia_sas


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

SAS Super FREQ
Posts: 8,868

Re: stored process AMO issue

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

simple_sp_uses_xp.png

2) invoke from SPWA

use_spwa_to_invoke.png

3) browser asks if I want to open

instead_of_open_pick_save.png

4) I save as myfile.xml

save_as_myfile_xml.png

5) open myfile.xml

final_spwa.png

  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.

new_message_xp.png

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

final_main_xml.png

  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

Regular Contributor
Posts: 152

Re: stored process AMO issue

Posted in reply to Cynthia_sas

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.

SAS Super FREQ
Posts: 8,868

Re: stored process AMO issue

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

Regular Contributor
Posts: 152

Re: stored process AMO issue

Posted in reply to Cynthia_sas

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.

SAS Super FREQ
Posts: 8,868

Re: stored process AMO issue

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

Regular Contributor
Posts: 152

Re: stored process AMO issue

Posted in reply to Cynthia_sas

Thanks so much Cynthia!

Ask a Question
Discussion stats
  • 10 replies
  • 2797 views
  • 0 likes
  • 3 in conversation