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

Cabman

Is this related to your issue: connect.microsoft.com/SQLServer/feedback/details/2855443/…. Here's a possible solution: leonardmurphy.com/blog/powerpivot_ssrs_xml_parsing_failed – Convery

Yes, I linked to that blog in my question and have also seen that Connect ticket, but it suggests working around the issue by using static options for the parameters, which is not feasible for me as explained in my question. It also doesn't provide complete repro steps, as some SSRS reports with multi-value query parameters work fine, just some don't. – Cabman

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.

Pram

Answer 2

The first investigation I suggest is trying to rule out the URL issue as a potential cause. For test purposes only, is it possible to copy the report and modify it so that most of the parameters are hard-coded with valid examples? You could then try fetching the report with a much smaller URL, using only one or two parameters.

If the copied report works, confirming that the URL length is the issue, then you have no option but to investigate further possibilities to reduce URL length.

If the URL length is the issue, you could incrementally add parameters to determine a target URL length that operates correctly.

Is it possible to reduce the length of input parameters? If the parameters need to match values of data or code that can't be easily modified, is it possible to modify the report to incorporate a lookup table, so that passed in short variables are mapped to their full versions?

Alternatively, do you have several text parameters with only 2 or 3 options? Or, are there are few parameters which are only valid in specific combinations? If so, it gives you another option, although it's extremely bad practice and will result in a lot of effort to maintain the reports if anything needs to change... You could create several copies of the report, hardcoding the parameters which have a limited number of valid combinations. Then, based on input parameters stored in Excel, you call the relevant report that matches those parameters, and pass through the remaining parameters in the URL.

However, if the URL length is not the issue, you can then put more effort into investigating the cause and hopefully cleaner solutions!

Atrophy

Answer 3

This is what I did.

Used =TRIM(CLEAN( CELLS )) on the entire data, replaced and uploaded.

It accepted.

Palomino