Reviewed for accuracy on January 23, 2023
In Part-1, SSRS migration steps have been discussed. Many congratulations for SSRS migration but migration journey is not yet over. SSRS migration opens door for new issue and challenges due to feature update but also it welcomes new feature that comes with newer version of SSRS.
Below mentioned all problem statements are related to migration of SSRS-2008R2 to SSRS2017 and Oracle12c have been used as report datasource that might not be relevant to other version of SSRS and other report datasources like (SQL Server, MySql etc…)
Let’s go through with these all step by step.
Date/Time datatype in Report Parameter
This one is the most impacted datatype after SSRS migration that has started throwing below
error in many reports. You can observe this error in SSRS log file.
“Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘<Report dataset name>’. —> Oracle.ManagedDataAccess.Client.OracleException: ORA-01830: date format picture ends before converting entire input string”
Solution
SSRS 2008 used the old System.Data.OracleClient. In SSRS 2016 and later you must install the Oracle ODP.NET provider, built and supported by Oracle. So probably just a difference in how the NLS_DATE_FORMAT session parameter is set by the two drivers.
Let’s discuss above error and solution:
1) If you have used Oracle method: To_Date in your dataset query without date format, then you are going to experience above error in SSRS log file because newer version of SSRS datetype parameter always hold date with default time.
T0_Date(:<Your Query Parameter Name>) : this will throw above error, if your report query parameter is directly bind with the report DateType parameter.
T0_Date(:<Your Query Parameter Name>,<Date Format>) : this will work without any error, if used <Date Format> has been specified correctly as per input value in <Your Query Parameter Name>
There are two options to fix this error.
Method#1 : Use Oracle method TRUNC in your dataset query like T0_Date(TRUNC(:<Your Query Parameter Name>)) that truncates time value from dateTime value
Method#2 : Use expression like =CDATE(<Report Parameter Name>.Value).ToString(“dd-MMM-yyyy”) in your dataset Parameters Property to bind your Query date type parameter with above expression instead of binding datetime report parameter directly.
2) After fixing above error, run your report successfully and then run following query on ReportServer database
select top 1 Parameters,* from ExecutionLog3 Order by TimeStart Desc
You will notice that ExecutionLog3.Parameters field is holding datetype parameter value like p_StartDate=1%2F13%2F2020%2012%3A00%3A00%20AM that is very much like HTML URL Encoding
To make this value in human readable format, browse below URL and use only datatime parameter value 1%2F13%2F2020%2012%3A00%3A00%20AM there and click on decode
https://www.freeformatter.com/url-encoder.html#ad-output
it will show this result 1/13/2020 12:00:00 AM
3) Many of the reports started showing performance issue after migration that had datetype parameter.
Using Oracle method TRUNC in dataset query like T0_Date(TRUNC(:<Your Query Parameter Name>)) that truncates time value from dateTime value has fixed the performance issue for us.
BOOLEAN datatype in Report Parameter
If you are using BOOLEAN datatype report parameter and you have used below kind of expression, you must be very careful as below expressions are always going to return False as output
=IIF(<Boolean Parameter Name>.Value=1,"TRUE","FALSE")
OR
=IIF(CINT(<Boolean Parameter Name>.Value)=1,"TRUE","FALSE")
Solution
Since Boolean datatype report parameter returns -1 for TRUE and 0 for False, you need to modify above expression as below to get the correct result in report output
=IIF(<Boolean Parameter Name>.Value= -1,"TRUE","FALSE")
OR
=IIF(CINT(<Boolean Parameter Name>.Value)= -1,"TRUE","FALSE")
Oracle LONG datatype is not supported
If your dataset query is holding an oracle Long datatype filed, report is not going to populate any value for this field, nor it will throw any error.
Solution
Either you need to change LONG datatype to CLOB in your oracle table definition or you need to add a new field of CLOB datatype and populate values of LONG datatype to CLOB datatype and further create a trigger on this table that will sync newly added CLOB datatype filed with existing LONG datatype field value. At the end use this CLOB field in your report’s dataset query that will populate values as expected in report output.
Table/Matrix header text display disturbed in IE Browser and excel export
Few report’s Table/Matrix header text started showing inappropriate way in internet browser and Excel export.
Solution
Right click on Table/Matrix header cell -> choose Textbox Properties – >Sizing options
Uncheck “Allow height to increase” option and checked “Allow height to decrease” option.
You need to perform this action on every header row cell of Table/Matrix header.
Bind variable does not exists error
Some reports started failing with below error.
“An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘owner_ds’. (rsErrorExecutingCommand)
ORA-01006: bind variable does not exist”
Solution
If there is any commented text (line next to where clause) in your dataset query having name of report parameter, you will experience this error.
Select
—
—
from table1 o, table2 r
where r.resource_id = o.resource_id
– – and o.effective_date <= nvl(:p_asofdate, trunc(sysdate))
To resolve this error, you need to remove the commented line from your dataset query and then above query will look like below
Select
—
—
from table1 o, table2 r
where r.resource_id = o.resource_id
Navigation and export icons are not showing correctly in Report Manager
Few users have reported that appropriate icons are not appearing in report browser and they are getting it as in yellow highlighted in below screenshot
Solution
Solution is simple. Just clear the browser cache and this issue will get fixed. Cheers 😊
EXCEL Subscriptions started failing
All the existing EXCEL subscriptions started failing and logged below error message in SSRS log file.
“ERROR: Error occurred processing subscription e7efaa03-6bfb-4b55-83bb-55c524458c8b: The value ‘EXCEL’ is not valid for setting ‘Render Format’”
Solution
In newer version of SSRS, Excel export is producing “.XLSX” file extension instead of traditional “.XLS” and behind the scene “EXECL” export option is not available directly (why so will see later here) and “EXCELOPENXML” option have been introduced that is causing above error.
If you open rsreportserver.config, you will notice below extensions for EXCEL inside <Render> tag.
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer, Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer, Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
“EXCELOPENXML” is new render format for EXCEL export hence if you export/Subscribe any report for EXCEL export, you will notice EXCELOPENXML format used in ReportServer.dbo.executionlog3 under Format column in below query output.
select top 1 [Format],* from ExecutionLog3 Order by TimeStart Desc
In above rsreportserver.config section, “EXCEL” is still there as an render option but this option is not visible (Visible=”false” in extension tag) for use and hence can’t be used directly but If there is embedded Report URL used in any of your application that forms URL like below it is still going to work and report output will get exported to traditional “.XLS” format.
https://<Report Server URL>/ReportServer?/myreport&rs:Format=EXCEL
for more see: https://docs.microsoft.com/en-us/sql/reporting-services/export-a-report-using-url-access?view=sql-server-ver15
Now in order to make existing EXCEL Subscription working, we need to modify EXCEL rendering to use EXCELOPENXML as render format.
Use below query on ReportServer database that will update existing standard EXCEL Subscriptions to use EXCELOPENXML as render format.
Use ReportServer
GO
Declare @OldRenderFormat varchar(100)='EXCEL',@NewRenderFormat varchar(100)='EXCELOPENXML';
Declare @SubscriptionID nvarchar(1000),@ExtensionSettings Nvarchar(MAX),@XMLDATA XML;
declare
cur_FIXED_Subscriptions CURSOR for Select SubscriptionID,[ExtensionSettings]
from dbo.Subscriptions
where
[ExtensionSettings] Like '%<ParameterValue><Name>RenderFormat</Name><Value>'+@OldRenderFormat+'</Value></ParameterValue>%';
Open cur_FIXED_Subscriptions
Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@ExtensionSettings
While @@FETCH_STATUS=0
Begin
Set @XMLDATA=CAST(@ExtensionSettings AS XML)
—Replace OLD Render format to New Render Format
SET @XMLDATA.modify('replace value of (/ParameterValues/ParameterValue/Value[../Name="RenderFormat"]/text())[1] with (sql:variable("@NewRenderFormat"))')
—Update OLD Render format to NEW Render format
Update Subscriptions
Set [ExtensionSettings]=cast(@XMLDATA as nvarchar(max))
where
Convert(nvarchar(1000),SubscriptionID)=@SubscriptionID
Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@ExtensionSettings
End
Close cur_FIXED_Subscriptions
DeAllocate cur_FIXED_Subscriptions
Use below query on ReportServer database that will update existing data-driven EXCEL Subscriptions to use EXCELOPENXML as render format.
—///Script to change report render format from 'EXCEL' to 'EXCELOPENXML' in Subscription Command Text
Use ReportServer
GO
Declare @OldRenderFormat varchar(100)='EXCEL',@NewRenderFormat varchar(100)='EXCELOPENXML'
Declare @SubscriptionID nvarchar(1000),@DataSettings Nvarchar(MAX),@XMLDATA XML,@NewCommandTextQuery Varchar(MAX);
—-
declare
cur_FIXED_Subscriptions CURSOR for
Select SubscriptionID,DataSettings from Subscriptions
where Cast(DataSettings As XML).value('(/DataSet/Query/CommandText/text())[1]','Varchar(MAX)') like '%'''+@OldRenderFormat+'''%';
Open cur_FIXED_Subscriptions
Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@DataSettings
While @@FETCH_STATUS=0
Begin
Set @XMLDATA=CAST(@DataSettings AS XML)
—
–Get the Subscription Command text Query and replace 'EXCEL' with 'EXCELOPENXML'
Select @NewCommandTextQuery=Replace(@XMLDATA.value('(/DataSet/Query/CommandText/text())[1]','Varchar(MAX)'), ''''+@OldRenderFormat+'''',''''+@NewRenderFormat+'''')
SET @XMLDATA.modify('replace value of (/DataSet/Query/CommandText/text())[1] with (sql:variable("@NewCommandTextQuery"))')
—Update New DataSettings
Update Subscriptions
Set [DataSettings]=cast(@XMLDATA as nvarchar(max))
where
Convert(nvarchar(1000),SubscriptionID)=@SubscriptionID
Fetch Next from cur_FIXED_Subscriptions into @SubscriptionID,@DataSettings
End
—
Close cur_FIXED_Subscriptions
DeAllocate cur_FIXED_Subscriptions
HTML4.0 Rendering is not supported
Since HTML4.0 rendering option is not supported, you need to update existing subscriptions with appropriate available rendering option like MHTML and you can use above “EXECL” to “EXCELOPENXML” queries with minimum modification for this as well.
New Feature – Common SYS account for all file share subscriptions
In newer version of SSRS, Report Server Admin can setup a centralized file share account that can be consumed by all file share subscriptions.
Visit this like to update existing file share subscriptions to use centralized file share account
for more details about this feature, please visit to https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/subscription-settings-and-a-file-share-account-configuration-manager?view=sql-server-ver15
This is a new feature whereas old way to setup file share credential to access the file in every file share subscription is still valid can be used in old way.
New Feature – Report Comments with/without attachment
Report comments as a new feature have been introduced where user can add comment with/without attachment in every report. Run any report and you will see this option in top right side (see this screenshot). If you don’t have appropriate access permission, you will not able to see this option.
If you don’t like this option and want to hide this option from Report, use below steps that will hide this feature.
- Launch Sql Server Management Studio and Connect to “Reporting Services” using required authentication. See below screenshot
- Expand Security – > Roles folder and double click on “Browse” role. Uncheck both the options (Comment on Reports and Manage Comments) related to comment Task. See below screenshot
- Do the same for other roles (Content Manager, My Reports, Publisher and Report Builder)
After this, re-start service of reporting services
You are all done with migration. Cheers 😊
