Report Parameters not work with Dynamic Connection String in SSRS

0
votes

we currently work on an project to distribute reports generated from SSRS, the report is target to different database with different survey list(each database have different list of survey), so we use Expression-based Connection Strings(dynamic connection) in the report design.

expression for connection:

="data source=ServerName;initial catalog=" & Parameters!DatabaseName.Value

the parameter are databaseName and surveyId.

databaseName: name of the target database, which is used in the data source connection.

surveyId: survey Identity, its available values are update based on the databaseName parameter.

we use the report buider to design the report, and it works fine in the report builder, survey list updated after change databaseName.

BUT after upload the RDL file to SSRS service, the report cannot change the databaseName parameter from web portal, always stick on the default value.

please let me know how to make the parameter works in the SSRS web portal, is there something wrong with the datasource connectionstring?

BTW: we use report builder 2016 and SQL server 2016, the databases are Azure SQL database.

Thanks in advance.

1
sql-serverreporting-servicesreportbuilderrdl
Hi Denis, I had same issue if you do not mind, can you share your parameters what looks like? – MertG
hi, here is connection string expression in a dynamic data source: ="Data Source=servername,1433;Initial Catalog=dev-" & Parameters!TenantName.Value & "-db" it generate connection string as ="Data Source=servername,1433;Initial Catalog=dev-tenant1-db – Denis D

1 Answers

0
votes

I think your problem is due to PERMISSIONS.

When you run the report from Report Builder, it uses your account (windows user) since there are no credentials specified in the data source.

When the report is deployed to the report server, it's trying to allow the server to access the server but it doesn't have permissions.

You would need to either add a user and password for the SSRS report server and add the credentials to your data source expression OR give permission to the databases or server for the SSRS server account.