topic Re: Stored Process to create, view and download report in Excel in Visual Analytics in SAS Visual Analytics

Stored Process to create, view and download report in Excel in Visual Analytics

PK_DataScience — Fri, 14 Jun 2019 05:28:52 GMT

Hello Experts,

I have a stored process which when executed from Information Delivery Portal, generates a report in simple tabular form, which can be downloaded in Excel. I am looking for a solution to migrate this stored process to Visual Analytics from where the report should be first displayed in report viewer and then it should allow to download / export in MS Excel format, just like a list table report can be downloaded in Excel.

Thanks.

Re: Stored Process to create, view and download report in Excel in Visual Analytics

FredrikE — Fri, 14 Jun 2019 07:07:56 GMT

Hi!

If you really need to create data dynamically instead of having it in a data mart you can use "Job execution" to create a table "on-the-fly" and then use that table in your VA-report.

This link may help you to get a start:

https://documentation.sas.com/api/docsets/jobexecug/2.0/content/jobexecug.pdf?locale=en

//Fredrik

Re: Stored Process to create, view and download report in Excel in Visual Analytics

PK_DataScience — Fri, 14 Jun 2019 08:13:58 GMT

Hi Fredrik,

Thanks for your reply. The report will have more than 100,000 rows. If this report is designed and generated in VA, the export will result in partial download. Therefore I am looking for a solution to export stored process report in VA. Looking at the sample stored process in VA, I observed that reports are only displayed and there is no facility to download / export these reports.

Thanks much.

Re: Stored Process to create, view and download report in Excel in Visual Analytics

FredrikE — Fri, 14 Jun 2019 13:54:55 GMT

Hi!

Ok, wouldn't it be better if you ran the stored process directly from Excel via the SAS add-in?

//Fredrik

Re: Stored Process to create, view and download report in Excel in Visual Analytics

KeithM — Fri, 14 Jun 2019 14:16:19 GMT

I think this can be done. It is easiest to create 3 stored processes. One to generate the form that is initially displayed. Another to run the report to send results back to the viewer. Another to run a stp that exports it to ODS.

STP 1 would look similar to the following:

/**************************************************************

DATA _NULL_;

file _webout;

put "<html>";

put "<style>";

put "body {font-family: 'Calibri';}";

put "h3 {text-align: center;}";

put "div {font-size: small; font-weight: bold;}";

put "</style>";

put "<body>";

/* first form to run stored process to "return to start" */

put "<FORM ACTION='&_URL'>";

put '<input type="hidden" name="_program" value="/Projects/TLM/StoredProcess/TLM_DnM_FC_get_input">';

put '<div><input type="submit" value="RETURN TO START"></div></form>';

/* end of first form definition */

put '<br> </br>';

/* second form to run stored process that will export to excel */

put "<FORM ACTION='&_URL'>";

put '<input type="hidden" name="_program" value="/Projects/TLM/StoredProcess/TLM_DnM_export2Excel">';

put '<div><input type="submit" value="EXPORT TO EXCEL"></div></form>';

/* end of second form definition */

put "</body>";

put "</html>";

RUN;

/* ********************************************************************************************************** */

STP 2 would be the stp that you have already created making sure that it is wrapped with %stpbeing and %stpend

STP 3 would be your same code in STP 2, but you would use tags to Export to Excel like the following example:

The next step that you must have in place is to define the stored process TLM_DnM_export2Excel, and here is a short example of code that exports a PROC PRINT output to Excel. This stored process must be registered to execute on the "Stored Process Server only" and should produce Stream results.

/* Example: Create a HTML file for downloading to Excel. */

/* Using %STPBEGIN and %STPEND */

*ProcessBody;

data _null_;

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

rc = stpsrv_header('Content-disposition','attachment; filename=report.xls');

run;

%stpbegin;

proc print data=sashelp.class;

run;

%stpend;

Re: Stored Process to create, view and download report in Excel in Visual Analytics

PK_DataScience — Sat, 15 Jun 2019 06:37:10 GMT

Hi,

Thanks for your reply. I will certainly check this. My stored processes have one or more prompts. Do stored process prompts work in VA? I tried with sample stored process. In report designer, it allows to edit prompt values. But in report viewer, prompts are not shown and the stored process report gets executed with edited prompt values in designer. Prompts are very much important functionality in my sp reports.

Thanks much.

Re: Stored Process to create, view and download report in Excel in Visual Analytics

PK_DataScience — Sat, 15 Jun 2019 06:55:21 GMT

There would be around 500+ reports and thousands of users accessing the reports. Therefore a single URL is convenient here.

Thanks much.

Re: Stored Process to create, view and download report in Excel in Visual Analytics

PK_DataScience — Sun, 16 Jun 2019 09:47:59 GMT

Hi Keith and Fredrik,

All that I wanted was to migrate stored processes with prompts to VA. Just found that SAS VA 7.3 onward do not support entering prompt values and those values can be saved in designer, which is of no use. It is explained on this SAS support page:

http://support.sas.com/kb/57/009.html

But then this tip to run a stored process with full prompt functionality from a SAS Visual Analytics report is misleading:

http://support.sas.com/kb/60/738.html

Providing a link to stored process from VA report using text object is not a solution to run a stored process with full prompt functionality from a SAS Visual Analytics report.

The outcome is VA 7.3 onward do not support full functionality of stored process and further the report generated by stored process object can not be exported like list table object.

SO I think the subject is closed now.

Thank you very much.

Re: Stored Process to create, view and download report in Excel in Visual Analytics

hulksmash — Thu, 03 Oct 2019 15:39:34 GMT

This seems very close to the answer i need to my problem. The only missing piece is, what if the table you need to output is a work table? Wouldn't the STP3 not see that table?

Re: Stored Process to create, view and download report in Excel in Visual Analytics

FredrikE — Fri, 04 Oct 2019 08:12:04 GMT

You can solve that using sessions.

You can save a table to the session and pick it upp from any other program using the session ID 🙂

https://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/sessions.html

//Fredrik

Re: Stored Process to create, view and download report in Excel in Visual Analytics

hulksmash — Fri, 04 Oct 2019 13:10:58 GMT

This sounds like it's exactly what I need! Thank you!