I have a column I added as a boolean, which then needed to be added as a string. I deleted the column, regenerated, readded the column as a string, then regenerated.
The table and data model both show in sync and the column is the proper type.
However, when an SSRS report is ran that uses this column, it is erroring out stating…
could not be set to a 'System.String' value. You must set this property to a non-null value of type 'System.Boolean'
Perhaps the system generated it onto the reports when it first was created, but didn’t remove it when it was deleted. Then when it was readded, the system saw it existed already on the report and didn’t bother to readd/replace it with new data types. That is my theory at least.
I don’t know how many reports it is affecting, but it is likely a lot as it’s a WhseBin_UD table column.
There has to be some ‘proper’ way of handling this, isn’t there? In the past we’ve tried this and just ended up re-adding the old fields and defining new fields.
I don’t know what you could do if you’re on Cloud, but if you’re On-Prem and have access to your SQL server, you might be able to query the SSRS database, e.g.:
select * from ReportServer.dbo.Catalog where Content like '%ShipToPO_c%'
That’ll at least get you a list of RDL files that include that field in their SQL query. From there you’d have to figure out where the field is actually being used and convert things to the appropriate type.
select CONVERT(nvarchar(max), CONVERT(xml, Content)), * from ReportServer.dbo.Catalog where CONVERT(nvarchar(max), CONVERT(xml, Content)) like ‘%_c%’
I was able to use this to convert it to readable text. However, the only ‘_c’ in the xml for the report in question were Lbl_Calc Lbl_Company Lbl_Contract fields.
Doing the where clause with the custom field in question ‘%MassAllocate_c%’ netted 0 results against the entire table.
