BookmarkSubscribeRSS Feed
Scott_Mitchell
Quartz | Level 8

Good Evening, 

 

I have been given the task of producing a single VA report which can be filtered (on multiple data-items) 200+ times and use AMO to present the resulting visualisations in Excel using Add-In for Microsoft Office, which will then be converted to a PDF for consumption.  

The users do not want to manually change the filters or manually convert the reports to PDF.

 

Is there a way, using only a vanilla install of VA 7.3  (i.e. no OA, no EG, etc.), to produce a result in the above manner?

 

I have read http://support.sas.com/resources/papers/proceedings16/SAS3500-2016.pdf and gather there is a way to create VBA code that interacts with the AMO, but I may be "barking up the wrong tree", given that the paper is discussing OA also.

 

Any help or ideas would be greatly appreciated.

 

Thank you in advance.

 

Scott

6 REPLIES 6
PetriRoine
Pyrite | Level 9

Hello @Scott_Mitchell,

 

I read your question and realised that it can be understood in two ways. So I wanted to make sure that everybody is on the same page.

SAS® Office Analytics (OA) is the commercial package that includes (not limited to) SAS Add-In for Microsoft Office (AMO) and SAS Enterprise Guide (EG). AMO is the component enabling SAS VA reports to be accessible from your Microsoft Office applications. 

 

Now, did you want to achieve your task without OA, meaning no AMO either? Or do you have AMO in your platform by some other means? Anyway, I think AMO is mandatory to achieve what you are after. 

 

Best regards,

Petri 

 

 

Scott_Mitchell
Quartz | Level 8

Hi Petri,

 

Thank you for giving me the opportunity to clarify. 

 

We have VA 7.3 and AMO 7.1 installed onsite, and we are hoping to use both of these to achieve the desired outcome.  We don't have E.G. or any other SAS products licensed.

 

I was lead to believe that you could license AMO without licensing OA, hence the confusion.

 

Thank you again.

 

Regards,

Scott

Jatin_Jim
Obsidian | Level 7

If you don't mind, can you please elaborate your question with sample example, so can understand better way to provide you the perfect solution..

 

Regards;

Jim

Scott_Mitchell
Quartz | Level 8
Hi Jim,

Thank you for taking your time to reply to my post.

This is more a validation of a concept. Normally I would provide an example, but I think what I am proposing requires a yes or no answer as to whether this can be achieved using a stand alone install of VA with AMO Licensed. Perhaps I can simplify the question however.

Using VBA can we dynamically interact with the control object filters applied to a visualisation presented in Excel using the AMO?

I have followed the directions specified to add the reference of SAS Add-in for Microsoft Office to the workbook, as described in the PDF attached to my previous post. I have subsequently tested the interaction between VBA and AMO using the Hello World method outlined in the AMO Help Documentation but receive a Subscript Out of Range error from the VBA debugger. Since the whitepaper is explicitly discussing how to achieve interaction with the AMO licensed as part of AO and we don't have AO licensed, I was concerned that perhaps the functionality is somewhat different.

Thanks again.

Regards
Scott
itchyeyeballs
Pyrite | Level 9

Hi Scott,

 

Have you thought about using one of the browser automation tools to get the pdf files direct from VA itself? You could cut out using Excel all together.

 

In my experience the Office tools are great but can be slow to refresh after selecting an option, even if you get something working with VBA looping through 200 options could be pretty slow.

 

Alternativley is it feasible to create the report in Base SAS? I think that would be much more geared up to producing batch pdf outputs.

Scott_Mitchell
Quartz | Level 8
Hi Itchy,

Thank you for your questions and insight.

Yes we did entertain the idea of bypassing Excel using an automation tool. Unfortunately there is still a requirement to create visualisations in Excel to supplement those from VA. I offered the Stored Processes as a potential solution, but the stakeholders declined.

I agree with your position on using BASE to get the job done. However we were told that this functionality (all though not core) was achievable during the DD process and now the Stakeholders want it achieved using VA or want categorical proof that it can't be done. I am not at a point yet that I can say definitively that it can't be done, hence me throwing it open to the community.

Thank you again for your help, it is really appreciated.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1159 views
  • 0 likes
  • 4 in conversation