Database as a Parameter?!

I have 10 Databases with same query to extract same info from all databases.
One thing I can do is to create 10 different datasets to get my results or
join 10 queries in one big query.
But what I really would like to do is to create one dataset and pass a
database-name parameter so users can select the particular database from pull
down menu and view the results from that database. So basically it will be
running query for one database at a time. I tried to do it the same way you
create all parameters but of no use. I will appreciate any kind of help.
Example:
Select manufacturer, sales
From @.DatabaseName.dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
(DATEPART([Month], FDP.OrderDate) = @.ReportYear)
I can define and use @.ReportMonth and @.ReportYear but database name
parameter @.DatabaseName is not working.
Thanks in advance.Hi,
I'm using a similar method for maintaining many report servers on different
locations and each connecting to their own databases.
I use the below text as dataset source
= "Exec " & Parameters!LSQLServerName.Value & "." &
Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
Parameters!ParameterOne.Value
LSQLServerName parameter is the Linked SQL Server. So even on the same
location you can change your sql server machine.
Note that you will not need this parameter if your reporting database is on
the same server with your datasource. Otherwise you have to declare a linked
server on the server running reporting server databases.
DatabaseName parameter is the catalog name in the database server.
Then you pass the query (or sp with parameters)
Actually you send at the end a similar query:
" Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
I hope this helps.
Eralper Yilmaz
http://www.eralper.com
http://www.kodyaz.com/default.aspx
"GJ" wrote:
> I have 10 Databases with same query to extract same info from all databases.
> One thing I can do is to create 10 different datasets to get my results or
> join 10 queries in one big query.
> But what I really would like to do is to create one dataset and pass a
> database-name parameter so users can select the particular database from pull
> down menu and view the results from that database. So basically it will be
> running query for one database at a time. I tried to do it the same way you
> create all parameters but of no use. I will appreciate any kind of help.
> Example:
> Select manufacturer, sales
> From @.DatabaseName.dbo.product FDP
> Where
> (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> I can define and use @.ReportMonth and @.ReportYear but database name
> parameter @.DatabaseName is not working.
> Thanks in advance.|||Hi Eralper,
Thank you for the quick reply. But I think I am confused now as how or where
to write those statements.
The way I have been using reporting server is to create a dataset, create a
datasource in the dataset (or use a shared datasource), either write text
query or exec SP there. Then write the parameters used in the query in the
'Report Parameters' tab in VS.NET.
I am not sure if I followed it correctly, but if you are saying to create a
new DATASOURCE.. and write the source as:
= "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
In my situation, that is giving me errors.
On the other hand, running this " Exec
[SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
solve my problem as here we are giving DBName (Pubs) and that is static.
What I am trying is ... create a pull-don menu in the report and give the
user an option to select the particular database. Then the query is run and
results are returned for the query for the particular database selected.
I may not have understood your explanation but am still confused.
Thank you for your time and patience.
GJ
"eralper" wrote:
> Hi,
> I'm using a similar method for maintaining many report servers on different
> locations and each connecting to their own databases.
> I use the below text as dataset source
> = "Exec " & Parameters!LSQLServerName.Value & "." &
> Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> Parameters!ParameterOne.Value
> LSQLServerName parameter is the Linked SQL Server. So even on the same
> location you can change your sql server machine.
> Note that you will not need this parameter if your reporting database is on
> the same server with your datasource. Otherwise you have to declare a linked
> server on the server running reporting server databases.
> DatabaseName parameter is the catalog name in the database server.
> Then you pass the query (or sp with parameters)
> Actually you send at the end a similar query:
> " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> I hope this helps.
> Eralper Yilmaz
> http://www.eralper.com
> http://www.kodyaz.com/default.aspx
>
> "GJ" wrote:
> > I have 10 Databases with same query to extract same info from all databases.
> > One thing I can do is to create 10 different datasets to get my results or
> > join 10 queries in one big query.
> >
> > But what I really would like to do is to create one dataset and pass a
> > database-name parameter so users can select the particular database from pull
> > down menu and view the results from that database. So basically it will be
> > running query for one database at a time. I tried to do it the same way you
> > create all parameters but of no use. I will appreciate any kind of help.
> > Example:
> >
> > Select manufacturer, sales
> > From @.DatabaseName.dbo.product FDP
> > Where
> > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> >
> > I can define and use @.ReportMonth and @.ReportYear but database name
> > parameter @.DatabaseName is not working.
> >
> > Thanks in advance.|||He is using the technique of having an expression as the query source (not
the data source). If you use the generic query designer (not the graphical
one) you can put an expression in, just as he shows you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:0FC916A0-A499-4131-8F49-CC26CDEA2911@.microsoft.com...
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or
where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create
a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create
a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET
doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run
and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on
different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is
on
> > the same server with your datasource. Otherwise you have to declare a
linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all
databases.
> > > One thing I can do is to create 10 different datasets to get my
results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database
from pull
> > > down menu and view the results from that database. So basically it
will be
> > > running query for one database at a time. I tried to do it the same
way you
> > > create all parameters but of no use. I will appreciate any kind of
help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.|||Hi,
Your desired sp is below.
Select manufacturer, sales
From @.DatabaseName.dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
(DATEPART([Month], FDP.OrderDate) = @.ReportYear)
First of all, create the @.DatabaseName parameter in the report parameters
screen
Then go to Data tab, and write the following statement
Select manufacturer, sales From OneOfYourDatabases.dbo.product FDP
Replace OneOfYourDatabases with a database name. And execute the query.
Running this query will populate the fields list for this dataset so you can
use these fields in the Layout tab. (You can also add field names later on
the Field Tab if necessary)
The third step will be rearrainging the query. Replace it with :
= "Select manufacturer, sales
From " & Parameters!DatabaseName.value & ".dbo.product FDP
Where
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportMonth.value & "')
and
(DATEPART([Month], FDP.OrderDate) = '" & Parameters!ReportYear.value & "')"
Lastly, click "Refresh" button (somehow this solved problems a few times),
then save.
If I'm not wrong, these four steps should work as you want.
> Hi Eralper,
> Thank you for the quick reply. But I think I am confused now as how or where
> to write those statements.
> The way I have been using reporting server is to create a dataset, create a
> datasource in the dataset (or use a shared datasource), either write text
> query or exec SP there. Then write the parameters used in the query in the
> 'Report Parameters' tab in VS.NET.
> I am not sure if I followed it correctly, but if you are saying to create a
> new DATASOURCE.. and write the source as:
> = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName
> In my situation, that is giving me errors.
> On the other hand, running this " Exec
> [SqlServer1].Pubs.dbo.MyStoredProcedure " in the data tab in VS.NET doesn't
> solve my problem as here we are giving DBName (Pubs) and that is static.
> What I am trying is ... create a pull-don menu in the report and give the
> user an option to select the particular database. Then the query is run and
> results are returned for the query for the particular database selected.
> I may not have understood your explanation but am still confused.
> Thank you for your time and patience.
> GJ
> "eralper" wrote:
> > Hi,
> >
> > I'm using a similar method for maintaining many report servers on different
> > locations and each connecting to their own databases.
> >
> > I use the below text as dataset source
> >
> > = "Exec " & Parameters!LSQLServerName.Value & "." &
> > Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " &
> > Parameters!ParameterOne.Value
> >
> > LSQLServerName parameter is the Linked SQL Server. So even on the same
> > location you can change your sql server machine.
> > Note that you will not need this parameter if your reporting database is on
> > the same server with your datasource. Otherwise you have to declare a linked
> > server on the server running reporting server databases.
> >
> > DatabaseName parameter is the catalog name in the database server.
> >
> > Then you pass the query (or sp with parameters)
> >
> > Actually you send at the end a similar query:
> >
> > " Exec [SqlServer1].Pubs.dbo.MyStoredProcedure "
> >
> > I hope this helps.
> >
> > Eralper Yilmaz
> > http://www.eralper.com
> > http://www.kodyaz.com/default.aspx
> >
> >
> > "GJ" wrote:
> >
> > > I have 10 Databases with same query to extract same info from all databases.
> > > One thing I can do is to create 10 different datasets to get my results or
> > > join 10 queries in one big query.
> > >
> > > But what I really would like to do is to create one dataset and pass a
> > > database-name parameter so users can select the particular database from pull
> > > down menu and view the results from that database. So basically it will be
> > > running query for one database at a time. I tried to do it the same way you
> > > create all parameters but of no use. I will appreciate any kind of help.
> > > Example:
> > >
> > > Select manufacturer, sales
> > > From @.DatabaseName.dbo.product FDP
> > > Where
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportMonth) and
> > > (DATEPART([Month], FDP.OrderDate) = @.ReportYear)
> > >
> > > I can define and use @.ReportMonth and @.ReportYear but database name
> > > parameter @.DatabaseName is not working.
> > >
> > > Thanks in advance.