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>
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>
