How to connect generic database field to report?

Hello,

I want to make a report where multiple users can use the same report to connect to their databases and then print out the report with information from those databases. Both databases have the exact same tables and fields but the data that is in them is different. However, I have only been able to figure out how to connect the report to one specific database, and therefore the report always prints out information from that database instead of the user specified one. So let's say I want my report to print out the name that is in the database field Name for any database I connect to, how would I do this?

Assuming SSRS 2005, you can use an expression-based connection string as demonstrated in the ExpressionBasedConnection sample report in this download. Since I think it is a no-no to pass the connection string as a report parameter, the report gets it from the Report Server config file.|||I looked at your samples but I'm not sure I quite understand them. It looks like your ExpressionBasedConnection report just connected to the database named AdventureWorks. But my reports are going to be connecting to databases that have the same underlying structure but with variable names that I won't know when creating the report. I will only know the name of the database when the user runs our software to connect to whatever database they use. But I still want to create a report that will use, for example, the Name, Address, State fields from the Customer table, but I don't want to hook it up to one specific database. How do I do this?|||

OK, but somehow the end-user has to specify the database name at some point, correct? Let's assume that the user will pass the database name as a report parameter. Then, you can use an expression-based connection string in the report data source (it must be private), to establish connection to that database.

Did I miss anything?

|||Yes at some point the user will specify what database they want to connect to but I don't think I fully understand how to use that database in the report. So let's say I want to add a dataset to my report by the name of Company. So in the Dataset tab of the report I select new dataset and I get to the Dataset dialog box. So I name the dataset Company. I go into the "Datasource connection" dialog box by clicking the ... button. I've created a parameter for the dataset call ConnectionStr, so therefore in the Connection String edit box, I type " = Parameters!Connection.Value". Then I click OK that dialog and return to the Dataset dialog box. For the query string I type "SELECT * FROM Company" and then click OK. So in the datasets toolbar, I can see a dataset named Company but it doesn't have any of the company fields in it like Name, Address, etc. How to I get these fields in the dataset so I can use them in my report? Since the report doesn't know what the ConnectionStr parameter is yet it doesn't seem like it will be able to do this.|||Start with a normal connection string. Click the Refresh Fields button on the Data tab in the Report Designer. Then change to an expression-based connection string.|||Alright, so I did what you suggested, but when I drag a field from the dataset I want it says First(Fields!Name.Value, "Company"), but I don't want a report that just prints the information of the first company in the dataset, I want a report printed for every company that is in the dataset after my query has been applied.|||This usually happens when the data region is bound to a different dataset than the one you are dragging the fields from. Take a look at the Dataset property of the data region and clear it (or set to Company). Then, you can re-drag your fields or remove the First function so the field reference is =Fields!Name.Value.|||So I've accomplished this, and now I am having a similar problem of connecting a generic data source to the reportviewer. When I'm in the Data Sources window and I select Add New Data Source, in order to add a new one, I have to select a specific database. But I don't want the reportviewer to connect to one specific data source, I just want it to print out the report that I have made following your tips, how do I do this?