"XML parsing failed at line xxx, column 36: illegal xml character" when importing to Power Pivot from SSRS report

Question

Excel 2016 (16.0.6965.2076)

SQL Server 2014 (12.0.4213.0)

I have an SSRS report with 10 parameters - two are dates and the other 8 are text dropdowns using a query to populate the options. If I try to fetch this report into a Power Pivot Data Model in Excel (Power Pivot -> Manage -> From Other Sources -> Report), when completing the Table Import Wizard (after successfully specifying my report and parameter values and seeing the report render correctly in the wizard) by clicking on the Finish button on the Select Tables and Views page I receive an error message:

XML parsing failed at line 1345, column 36: Illegal xml character.

The line given varies depending on what parameter values I specify for the report, but the column is always 36. I have inspected my report and these line and column numbers do not appear to relate to my report, as often they are both greater than the total number of rows/columns being returned in the report.

I have looked around online and found a few resources which deal with this issue, but I've been unable to find a solution to my issue:

This blog and this associated Microsoft Connect ticket suggest that I can get this to work by exporting my report as a Data Feed and referencing that from within the Power Pivot wizard instead, but this also doesn't work for me, I get a different error:

Unable to obtain schema for data feed '[Report feed Name]'. Please make sure this feed exists.

I have found suggestions elsewhere online that this subsequent error (or the original problem) may be as a result of the report URL being too long (including parameters/values). Unfortunately I cannot see any further way to reduce the URL length (currently over 700 characters), as I have minimised my parameter names and the report name, but still have this issue (I do not believe I can reduce the parameter values as they are fetched from an application database which we cannot change).

I have also seen suggestions to replace my "Get values from a query" parameters with "Specify values" parameters, but this is not viable as the parameter values must be fetched from the application database mentioned above and will change over time, so need to be fetched dynamically.

Can anyone suggest a solution or further troubleshooting steps I can follow for this issue?

Answer 1

To narrow down your problem, I would export the report (using your parameters) to XML format. This is effectively a manual repro of what Power Pivot is doing when it calls your report.

Then I would open that file in an XML editor (e.g. Visual Studio) and hunt for "line 1345, column 36: Illegal xml character.".

If this is triggered by a textbox name, you can override them for XML export by setting the DataElementName property.

If it is triggered by a data value, I would use an SSRS expression to try to avoid it. You can test for Globals!RenderFormat.Name = "XML" to only manipulate the data value under that scenario.