VBA import SSRS w/ param Into Excel

jameshigginson

New Member
Joined
Jul 9, 2015
Messages
14
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>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
***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.
 
Upvote 0
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
 
Upvote 0
***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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top