holemania
asked on
Reporting Services - Dynamic database parameter
Hello Experts,
I have 12 databases across the country that I would like to be able to connect to via one report. I am able to do this by creating a datasource and setup a parameter to connect to the other database as linked server. This works fine.
=(Parameters!DB.Value + ".dbo.SP_Employee)
From the above, I just define my databases and then via using linked server, I can connect to each database.
However, I have a need to have the ability to select multiple databases at once. With the above method, I can only select 1 database at a time. I can't select all database or multiple database. Does reporting services have the capability to allow multiple database selection?
I have 12 databases across the country that I would like to be able to connect to via one report. I am able to do this by creating a datasource and setup a parameter to connect to the other database as linked server. This works fine.
=(Parameters!DB.Value + ".dbo.SP_Employee)
From the above, I just define my databases and then via using linked server, I can connect to each database.
However, I have a need to have the ability to select multiple databases at once. With the above method, I can only select 1 database at a time. I can't select all database or multiple database. Does reporting services have the capability to allow multiple database selection?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Harish Varghese
Another good approach is to create a stored proc in your default database which accesses tables in all your linked servers and returns the result.
ASKER
harish_varghese,
Thanks for your input in this. So here's what I did. I create a different dataset for each database. Create a parameter for each dataset. So I have 12 parameters to select from, one for each database.
This seems to work if I select a database for each parameter, but I can't seem to get it so that user does not have to pick all 12 databases. I want it where the user could pick 3 of the database and only run it for 3. I had set the parameter to allow blank and NULL, but still no luck.
Am I missing something?
Thanks for your input in this. So here's what I did. I create a different dataset for each database. Create a parameter for each dataset. So I have 12 parameters to select from, one for each database.
This seems to work if I select a database for each parameter, but I can't seem to get it so that user does not have to pick all 12 databases. I want it where the user could pick 3 of the database and only run it for 3. I had set the parameter to allow blank and NULL, but still no luck.
Am I missing something?
ASKER
I noticed that I can create 1 parameter with multiple database and use it as a multiple value. However, my query doesn't seem to work. Here's what I did.
In the Data tab, I create this:
="SELECT ID, NAME, DEPT FROM EMPLOYEE " + Parameters!DB.Value + ".DBO.EMPLOYEE"
In my parameter, I set my DBs as followed:
DB1 = [CASVR1].DB1
DB2 = [NYSVR2].DB2
DB3 = [WISVR1].DB3
I can set it where I can pick the multiple parameters by checkbox in my dropdown. However, it's not working. Do I need to do something special?
In the Data tab, I create this:
="SELECT ID, NAME, DEPT FROM EMPLOYEE " + Parameters!DB.Value + ".DBO.EMPLOYEE"
In my parameter, I set my DBs as followed:
DB1 = [CASVR1].DB1
DB2 = [NYSVR2].DB2
DB3 = [WISVR1].DB3
I can set it where I can pick the multiple parameters by checkbox in my dropdown. However, it's not working. Do I need to do something special?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to get it to work with my solution. However, giving points to person that took the time and effort to respond to my question.