NEW: VBA import SSRS w/ param Into Excel

J_Marley

New Member
Joined
Oct 18, 2018
Messages
2
Before I get into the issue I am experiencing, I think it should be said that my expertise in this matter is extremely lacking, so pretend that we're on /r/explainitlikeim5.

I'll also note that I have tried to do some extensive research across multiple sites and forums, but nothing that really nails down the solution. The closest I've seen was from this forum and this thread: https://www.mrexcel.com/forum/excel...srs-w-param-into-excel.html?highlight=atomsvc.

So down to the situation: I have multiple SSRS reports that need to be pulled into Excel to create a dashboard. Each of these reports have their own parameters, but some of these parameters match across the reports (date, location, etc). I want to get the value entered in to a cell or selected by a checkbox to change the parameter for multiple connections (atomsvc files).

My thought on how this would work is that the user would check a box next to the report they want to have updated or run, enter a starting and/or ending date and click a button. That's all they would see on the front end in Excel. Behind the scene the VBA would need to change the parameters in the connection string for the selected reports with the new values, save the connection file, update the report tables (on their sheets either inside the same workbook or in a different workbook).

Any help, guidance or suggestions would be greatly appreciated.

The Basics:
* Windows 10
* Excel 2016

Current connection string: <?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>Manual Overrides</atom:title><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018 %2000%3A00%3A00&EndDate=10/15/2018
%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0xDx1x0x0x0x0xDx4x1x0"><atom:title>Tablix7</atom:title></collection><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018%2000%3A00%3A00&EndDate=10/15/2018%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0xDx1x0x0x0x1xDx6x0x0"><atom:title>Tablix8</atom:title></collection><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018%2000%3A00%3A00&EndDate=10/15/2018%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0xDx1x0x0x0x2xDx4x1x0"><atom:title>Tablix9</atom:title></collection><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018%2000%3A00%3A00&EndDate=10/15/2018%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x1"><atom:title>Tablix1</atom:title></collection></workspace></service>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sorry, with the HTML on it instantly converted my connection string that I was attempting to post. Hopefully it shows this time.

The Basics:
* Windows 10
* Excel 2016

Current connection string: <?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>Manual Overrides</atom:title><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018 %2000%3A00%3A00&EndDate=10/15/2018
%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0xDx1x0x0x0x0xDx4x1x0"><atom:title>Tablix7</atom:title></collection><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018%2000%3A00%3A00&EndDate=10/15/2018%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0xDx1x0x0x0x1xDx6x0x0"><atom:title>Tablix8</atom:title></collection><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018%2000%3A00%3A00&EndDate=10/15/2018%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x0xDx1x0x0x0x2xDx4x1x0"><atom:title>Tablix9</atom:title></collection><collection href="http://workserver/ReportServer?%2FSales%2FManual%20Overrides&StartDate=6/1/2018%2000%3A00%3A00&EndDate=10/15/2018%2000%3A00%3A00&AdvancedFilter=Hide%20Valid%20Within%202%20Week%20Reason&rs%3AParameterLanguage=&rs%3ACommand=Render&rs%3AFormat=ATOM&rc%3ADataFeed=xAx0x1"><atom:title>Tablix1</atom:title></collection></workspace></service>
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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