Error: Hexadecimal value 0x1D, is an invalid character

When I run the SSRS report of mine I found this error. SSRS report just simply display this error while running. When you looking at this error we must have realize this is a data issue. This data issue came from because of one of the dimension attribute has hexadecimal value.

Exact error:

“An error occurred during local report processing. An error has occurred during report processing. Cannot read the next data row for the dataset MainDataSet. The server send an unrecognizable response. “Hexadecimal value 0x1D, is an invalid character. Line 22, position 347777558.”

We can fix this error in many ways. Of course we are not going to change this in report. It is a data issue so we mainly focus to our databases. First need to identify what data generate this error. Using following query you can identify the data that affect to this error.

Select NUMBER AS Error Data

From TableName

Where NUMBER like '%' + Char (ASCII (0x1D)) +'%'

Using following query we can temporary fix the issue in database.

Select REPLACE (NUMBER, Char(Ascii(0x1D)), char(0)) AS cleance_Data

From TableName

Where NUMBER like '%' + Char (ASCII (0x1D)) +'%'

I am not going to fix the issue in database level. As a fix I did it inside the SSAS cube level. It may be not the best solution. But in my scenario cube level fix is sufficient for me.

Go to the SSAS cube dimension and double click an open the dimension. Inside the dimension attribute properties. There is Key column and Name column. Expand both key column and Name column then you can see the property call InvalidXMLCharacters. There are three options here preserve, remove and replace. Use remove option. 

Then you can run report without any hexadecimal value error.