how to use parameter label as database named in dataset

i have two database on one server... i want ssrs to connect to the database selected by user at run time using parameter label info.

the problem is that the sql server login i have stored in report authentication has default database to one of the above, so every time i run the reports it pointing to the default database only.

i was thing of using one of the below

1. use parameters!a.label

select xyz from abc

2. select xyz from @a.label.dbo.abc

but nothing works

As far as I understand from your problem, you have 2 databases and you want to select data from one table wich has the same name in both databases.

Question: Can you select data from both tables when connected to the default database?

Can this be a solution for you:

IF @Parameter='A'
    BEGIN
      select * from defaultdatabase..Table
    END
ELSE
    BEGIN
      select * from otherdatabase..Table
    END

This was just to show you another possible way to solve your problem.

Back to your 2 solutions, you are in the right way. Are you trying to put that code inside the query designer in SSRS? You should use a stored procedure and concatenate the value from the variable. That should do.

Let me know if you solved your problem or need further assistance.

i am not using stored procedure..its just a simple query and i want to write it directly in query designer

another point is that the database name is stored as parameter label..not value, because i am using parameter value to store server name.

i am having similar databases in many cities, so i want to run report for a particular city based on user selection (thru parameter)..however now i face an issue where i have two of such database on one server machine...and the login used has one of the database as default...so whenever user is selecting any of the above two database in parameter, report is running for default database only.

i try below suggestion, but doesnot work in query designer

if @param.label='A' then

 select ..from table

else

 select ..from db.table

No, you will not be able to do @param.label in dataset.

You need to do something like this

You might need to create another parameter to hold the value/label you need.

I suggested you to use stored procedure in order you to send that parameter value/label into sp and build your query dynamically.

i dont want to use store procedure because i have to write it on all databases

i try ur suggestion of using IF condition but it doesnot work..

if @srvr='db4'
select  rm_code from abc.i_master  where i_type in (@itype) order by i_code

else
select  rm_code from i_master  where i_type in (@itype) order by i_code

error: must declare the scalar variable '@srvr'

incorrect syntax near the keyword else

No, you do not need to create the stored procedure in every database. You need to create the sp in a database that has access to other databases.

Imagine that you receive parameter database and you build your query based on that:

USE [Database_A]
GO
ALTER PROCEDURE [dbo].[SP_Name]
	@Database_Name AS NVARCHAR(100)
AS
DECLARE @SQL AS VARCHAR(300)
SET @SQL='SELECT * FROM '+ @Database_Name +'.dbo.[Table]'

EXEC (@SQL)

USE [Database_A]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[SP_Name]
		@Database_Name = 'Database_XPTO'

SELECT	'Return Value' = @return_value