atomsvc in excel from ssrs: "We couldn't get data from the data model"

Question

I have exported an SSRS report to an .ATOMSVC file by running the report and saving as Data Feed. I then try to import the data feed into Excel (Data From Other Sources > From Odata Data Feed) by selecting the .ATOMSVC file. I go through all the steps to select the tablix, save the connection file, and import data to Table. It is at that step where I get an error message.

We couldn't get data from the data model. Here's the error message we got:

The remote server return an error: (500) Internal Server Error.

I would expect the data to load to an excel sheet but instead I get this error. What could cause this error?

REFERENCE:

I have done this before with successfull data dump to Excel. Below I have included some articles those show these same steps.

• https://prologika.com/reports-as-data-feeds/
• https://youtu.be/42DRuqwXSJw?t=390

These articles how to dump the data to Excel Powerpivot; not exactly the same destination I am trying

• https://www.red-gate.com/simple-talk/sql/reporting-services/ssrs-reports-as-a-data-source-in-excel-2013/
• https://learn.microsoft.com/en-us/sql/reporting-services/report-builder/generate-data-feeds-from-a-report-report-builder-and-ssrs?view=sql-server-2017
• https://support.office.com/en-us/article/import-data-from-a-reporting-services-report-6c196bd9-05ee-4759-99c9-ac10d57260a1

ATOMSVC:

Here is the contents of the exported from SSRS.

<?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>Campylobacter and Salmonella Lab Results</atom:title><collection href="http://gcop046/ReportServer?%2FFSQA%2FCampylobacter%20and%20Salmonella%20Lab%20Results&amp;paramFacilities=14&amp;paramStartDate=06%2F01%2F2019%2000%3A00%3A00&amp;paramTestSampleType=37&amp;paramTestSampleType=25&amp;paramTestSampleType=1&amp;paramTestSampleType=33&amp;paramTestSampleType=20&amp;paramTestSampleType=44&amp;paramTestSampleType=45&amp;paramTestSampleType=32&amp;paramTestSampleType=41&amp;paramTestSampleType=21&amp;paramTestSampleType=38&amp;paramTestSampleType=47&amp;paramTestSampleType=46&amp;paramTestSampleType=2&amp;paramTestSampleType=3&amp;paramTestSampleType=24&amp;paramTestSampleType=40&amp;paramTestSampleType=34&amp;paramTestSampleType=16&amp;paramTestSampleType=4&amp;paramTestSampleType=5&amp;paramTestSampleType=39&amp;paramTestSampleType=35&amp;paramEndDate=06%2F30%2F2019%2000%3A00%3A00&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=Parts%20Rinse&amp;distinctlistTestSampleType=USDA%20Parts%20Companion&amp;distinctlistTestSampleType=USDA%20Parts%20Companion&amp;rs%3AParameterLanguage=&amp;rs%3ACommand=Render&amp;rs%3AFormat=ATOM&amp;rc%3AItemPath=tablix_dsMyData"><atom:title>tablix_dsMyData</atom:title></collection></workspace></service>

STEPS:

Answer 1

I've searched around for what could be the cause of the error message "The remote server return an error: (500) Internal Server Error.". There are many posts out there concerning this error, none are related to my problem. One of the solutions online was: "The 500 error literally says 'Something broke but i have no idea what'. It's up to you to use your skills as a developer to find out what it is.".

I inspected the contents of file that was exported and I found that it had included the same parameter repeatedly, distinctlistTestSampleType=Parts%20Rinse&. I deleted all except one occurrence of the parameter string and that fixed the problem.