About changing database using a parameter

Question

Post reply

About changing database using a parameter

paratras

SSC Enthusiast

Points: 175
More actions
December 20, 2022 at 4:06 pm

#4128016

Hi, I need to change the DataSet source database.
I've created a parameter name "database" and the I've create a DataSource like "DataSource=server;initial catalog=" & Parameters!database.Value
Then I can choose the parameter (database) from a dropdown.
Everything works if I run the report from the ssrs web page. But If I set the parametr programmatically from webform the parameter is set buy the datasource does not change with this parameter.
Any other ideas how can I programmatically from C# change the database of the report? I do not want to create several Reports one for each database

Thanks for your help
Diego
January 9, 2023 at 11:31 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27060 More actions · Answer 1

Are all of the databases on the same server?

If so, don't change the data source. Do it in a stored proc.

Pass in the database name to the proc, and either use dynamic SQL, or create the same proc in each database and call the appropriate proc based on the value of the parameter.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

frederico_fonseca SSCoach Points: 16167 More actions · Answer 2

it may have to do with how you are doing it.

passing parameters is as per manual e.g. "&Parametername=parametervalue" at end of url for report.

https://myrshost/ReportServer?/AdventureWorks 2008R2/Employee_Sales_Summary_2008R2&ReportMonth=3&ReportYear=2008

see attached for how it was one in one of my projects report_parameter_server_db_details

paratras SSC Enthusiast Points: 175 More actions · Answer 3

It was a great idea, but I cannot pass parameters like that from inside C# code

C# code is like this

ReportParameter parameter = new ReportParameter("base", "databasename"); //base is the parameter
ReportViewer1.ServerReport.ReportPath = "/FE/InvoiceConQR";
ReportViewer1.ServerReport.SetParameters(parameter);
ReportViewer1.LocalReport.Refresh();

And the parameter is receiving the value. If I print the param value in the report to check it is set

Thanks anyway

Diego

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

you are mixing serverreport with localreport and I think that is your issue.

from https://learn.microsoft.com/en-us/sql/reporting-services/application-integration/using-the-winforms-reportviewer-control?source=recommendations&view=sql-server-ver16

you can see on the local report mode that the parameter is added to the local report object, not the server report, and the refresh is then done on the local report.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 5

on a diff note - might be good to start looking at options to replace reportviewer (and SSRS altogether) as .net core does not, and according to ms, will not, support reportviewer.