I am developing a couple of SSRS reports which need to translate UTC dates into timezone-based date values based on a timezone parameter (tzid).
After I found this answer on SO I added System.Core 3.5 to the reports’ References, and added custom Code:
Public Function FromUTC(ByVal d As Date, ByVal tz As String) As Date Return (TimeZoneInfo.ConvertTimeBySystemTimeZoneId(d, tz)) End Function
and changed the Expression of each datetime field to
= Code.FromUTC(Fields!MyDateTimeField.Value, Parameters!tzid.Value)
Then a production system was upgraded to SSRS 2012 (and thus VS SSDT 2010), and report deployment did not work anymore, as VS displayed the error messages when hitting Deploy
The definition of the report ‘/myreport’ is invalid
and opening the report
[rsCompilerErrorInCode] There in an error on line 1 of custom code: [BC30560] “TimeZoneInfo” is ambiguous in the namespace “System”
or, in German
“TimeZoneInfo” ist im Namespace “System” nicht eindeutig.
So there’s no deployment for you, sorryyyyyyy.
A bit of google-fu brought up this question on Social and this issue on Connect, which means that MS is sitting on this bug for half a year now, and the “workaround” seems to be to create your own SSRS assembly, and/or to use Reflection to access the TimeZoneInfo methods, as sketched here.
I did not want to give in so easily, so I tried and verified that the procedure described in Deploying SSRS Reports with PowerShell still works for SSRS 2012.
And a colleague found that you can still deploy to SSRS 2012 using BIDS 2008 if you set the project’s TargetServerVersion to “SQL Server 2008 R2 or later”.