Automating Reports to Change Filters Based on A List of Values

[Scott_Mitchell]

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

[PetriRoine]

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]

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]

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]

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]

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]

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.