04-24-2014 09:21 AM
hi sas gurus
i am completely new to sas and excel plugin
we currently have a 5 reports using the same olap cube but with different filters. finally when all these 5 reports are run, we create a final excel sheet using data from all these 5 excel sheets.
basically the user wants to automate the process.
any ideas would be appreciated to point me in the right direction.
04-24-2014 10:20 AM
Not sure what do you mean by automation? If it is 100% automation in my book, then I will run a batch job.
We have similar scenarios that we leverage Stored Process to process Cubes, then generate reports. You will still need EG to script STPs, then Add-in can take from there.
04-24-2014 10:27 AM
thanks for your reply
what i mean is that we use the same cube and set different filters and export different views of that cube into excel.
then we finally create one excel sheet, that is final result.
now i dont know much, but i was thinking about creating vbscripts to automatically connect to sas cube and get results and format it.
is this approach even possible.
04-24-2014 10:41 AM
I knew little to nothing about MDX, so I won't attempt to use VBscripts to call for it, but if you do, I think it could be a viable approach.
This is what I would do:
Repeating all of the procedures involving the cube (filters, slices, different views, exporting to excel etc.) in EG, then use the EG generated code to make your own Stored Process. You can fire up your Stored Process using SAS Microsoft Add-in from within Excel. Mission accomplished. Fairly straightforward and painless.
I myself used to be a hardcore old school Base programmer, and now I am a slacker, it is all EG's fault. LOL.
04-24-2014 10:45 AM
If you want to export output automatically from SAS to Excel in different sheets then this may be helpful
OPTIONS ( Sheet_Name = 'A' );
PROC PRINT DATA=sashelp.shoes; RUN;
OPTIONS ( Sheet_Name = 'B' );
PROC PRINT DATA=sashelp.class; RUN;
ODS TAGSETS.EXCELXP CLOSE;
04-24-2014 10:36 AM
The SAS plugin has a refresh option, by that SP will run and new results will become visible. Somewhat similar as pressing f9 on a field to refresh
Still the question what do you mean by automation
04-24-2014 10:50 AM
@Amar, What do you have? please specify. The SAS plugin SAS/Amo? Which SAS version? Which office version? What kind of server? What type of Olap?
All is SAS but not all of SAS is equal.
04-24-2014 11:32 AM
These versions is a comnbination with probably SAS 9.2 and it will work with Excel Word and PowerPoint but not with Outlook (that is a newer version).
With AMO you can do some SAS Analytics (importing data) but also running SAS-Stored processes.
Doing it that way you do some filtering at the SAS-server side and you have clean/ready to present reports. This all being managed form the central SAS metadata repository.
You can be seduced by importing just data into Excel and doing the processing in Excel.
You can import cubes by the SAS OLE/DB (using mdx) or by those mentioned tagsets approach.
As you mentioned SAS/Amo, I am assuming you are using the SP approach. Is this correct?