VBA import SSRS w/ param Into Excel

[jameshigginson]

Please excuse my lack of direct knowledge on this subject as this is not a area which I have much experience in. I am currently developing a dashboard in excel which runs off of multiple SSRS reports. The dashboard gives the user the ability to quickly visualize data from multiple reports simultaneously.

The current method of loading my dashboard involves manually going into report services, selecting parameters unique to each report, then exporting each report one at a time into an excel workbook; which then feeds the dashboard. Unfortunately I have to do this with roughly 10 reports every time i want to load the dashboard. Making this task very repetitive and not very efficient. Even more so I would like to be able to give this dashboard out to co-workers at some point down the road and cannot expect them to take the time required to load each report manually.

Is their a way I can use VBA to query/import each SSRS report into excel including the unique parameters required to generate it without having to manually load each report? I have asked for power pivot however I do not think it will be installed on company assets for some time leaving me to seek alternative solutions.

We do have access to atomsvc as well as a couple of other options such as CSV,MHTML,HTML,XML. Below is a example of a atomsvc for a report which I am attempting to import into my dashboard. Any incite would be greatly appreciated. Thanks


<?xml version="1.0" encoding="utf-8" standalone="yes"?><service xmlns:atom="http://www.w3.org/2005/Atom" xmlns:app="http://www.w3.org/2007/app" xmlns="http://www.w3.org/2007/app"><workspace><atom:title>Analysis - Revenue and Expense Report.rdl</atom:title><collection href="http://ssrsreportweb/ReportServer?http%3a%2f%2freportweb%2fadmin%2fRental%2fReport+Library%2fReporting%2fAnalysis+-+Revenue+and+Expense+Report.rdl&PeriodTypeParm=6&periodParm=201504&ShowOrgParm=1&InOrgParm=1&OrgSelectionParm=0999&LocationTypeParm=2&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0"><atom:title>table1</atom:title></collection></workspace></service>

 

[jameshigginson]

This is what I am working with "sometypeofreport.rdl" which is also is followed by what I am assuming are the parameters required to generate the report. If I try to load the entire string into a web query with all the parameters it says it is to long. Is there a way I can load the report and the parameters through a VBA query?

Below is a example of some of the parameters that I have found. Again this is a first for me I am not sure if these are indeed parameters or not but I assume they are.

&Dates=11506
&Previous_Day_Date%3Aisnull=True
&Org_Comparison=1

If someone could please show me what kind of context to start with Im sure I could play with it enough to get it to work the way I need it too.

Thanks.

 

[UserS1]

Hi, do you still need help with this? I have done exactly what you are asking.

Cheers.

 

[jameshigginson]

***THANK YOU/SOLVED***

I got it solved thank you for all the PMs and guidance. FYI if someone else has this issue, my resolution was to change &Var1=value1 to just &var1=value1 and it would pass the parameters through a URL STRING which could be queried.

 

[UserS1]

Hi James

What did you use to pass the URL String? (software) - are you able to give a brief description of the solution you came up with?

Thanks

 

[aceamit]

***THANK YOU/SOLVED***

I got it solved thank you for all the PMs and guidance. FYI if someone else has this issue, my resolution was to change &Var1=value1 to just &var1=value1 and it would pass the parameters through a URL STRING which could be queried.

Could you please post the complete solution? I am trying to achieve the same.

 

[masuccj]

Could you please post the complete solution? I am trying to achieve the same.

I am another person trying to do the same thing. Please post your solution if you are able. I am sure many would appreciate it.