|
|
You're in the
sql server reporting services
group:Pass DatabaseName as a Parameter - dynamic DB selection
sql server reporting services:
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 [quoted text, click to view] "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. > >
It seems like you are missing a closing quote at the end of the expression
which would result in a compilation error during report publishing. = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName" -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "GJ" <GJ@discussions.microsoft.com> wrote in message news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@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.
I had that problem a while back, but with some help, I was able to get it
working. My situation is like yours where we have 5 databases with same tables, schema, and fields. Instead of creating 5 different reports, I wanted to creat just one report and have parameters for each database. Here's the steps to how I got it to work. Hope this helps. 1) In the Dataset section type the following conditional expression: =iif(Parameters!Database.Value = "DatabaseName1", "DatabaseName1.dbo.SP_1", iif(Parameters!Database.Value = "DatabaseName2", "DatabaseName2.dbo.SP_1", iif(Parameters!Database.Value = "DatabaseName3", "DatabaseName3.dbo.SP_1", "DatabaseName4.dbo.SP_1") What this conditional expression will do is that if it's DatabaseName1 then run the stored procedure from that database, else run it from DatabaseName2, etc.. 2) Now go to the layout section of your reporting services. Then click in the ReportParameters where the Properties is. Remember the databases? Add a Parameter and use "Database" from (Parameters!Database.Value). The Database is the Database Name and Prompt, just put whatever you want. Then for Label and Value you will want to put your database names. Ex: DatabaseName1, DatabaseName1 DatabaseName2, DatabaseName2 DatabaseName3, DatabaseName3 DatabaseName4, DatabaseName4 Make sure that "Non-Queried" is select. That's all you need to do. Also make sure that the stored procedure is in each database. If you have any questions, post here. [quoted text, click to view] "Robert Bruckner [MSFT]" wrote:
> It seems like you are missing a closing quote at the end of the expression > which would result in a compilation error during report publishing. > = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName" > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > > "GJ" <GJ@discussions.microsoft.com> wrote in message > news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@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. > >
We have that problem in the past, but got it to work now. You can use
conditional expressions to get it to work. In the Data section, in the Generic Query Designer type this down. =iif(Parameters!Database.Value = "ACCT", "ACCT.dbo.SP_1", iif(Parameters!Database.Value = "HR", "HR.dbo.SP_1", iif(Parameters!Database.Value = "FIN", "FIN.dbo.SP_1", "ENG.dbo.SP_1"))) This just says that if the parameter "Database" is "ACCT" then run SP_1 in that database, if parameter is "HR" then run SP_1 in HR database, etc. Now go to the Layout tab and click on the ReportParameters. Add a parameter and name it "Database". For Prompt you can use whatever. Make sure that "Non-queried" is selected and then type in the Label and Value. Example: Accounting, ACCT Human Resource, HR Finance, FIN Engineering, ENG That should get you going. Post if you have any more questions. Regards, Chang [quoted text, click to view] "Robert Bruckner [MSFT]" wrote:
> It seems like you are missing a closing quote at the end of the expression > which would result in a compilation error during report publishing. > = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName" > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > > "GJ" <GJ@discussions.microsoft.com> wrote in message > news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@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. > >
Sorry for the repeat. I thought it didn't sent through, so I reply again.
THey're both the same. I've spent quite a while trying to figure this out, but I think this will help you. [quoted text, click to view] "chang" wrote:
> We have that problem in the past, but got it to work now. You can use > conditional expressions to get it to work. > > In the Data section, in the Generic Query Designer type this down. > > =iif(Parameters!Database.Value = "ACCT", "ACCT.dbo.SP_1", > iif(Parameters!Database.Value = "HR", "HR.dbo.SP_1", > iif(Parameters!Database.Value = "FIN", "FIN.dbo.SP_1", "ENG.dbo.SP_1"))) > > This just says that if the parameter "Database" is "ACCT" then run SP_1 in > that database, if parameter is "HR" then run SP_1 in HR database, etc. > > Now go to the Layout tab and click on the ReportParameters. Add a parameter > and name it "Database". For Prompt you can use whatever. Make sure that > "Non-queried" is selected and then type in the Label and Value. > > Example: > Accounting, ACCT > Human Resource, HR > Finance, FIN > Engineering, ENG > > That should get you going. Post if you have any more questions. > > Regards, > > Chang > > > "Robert Bruckner [MSFT]" wrote: > > > It seems like you are missing a closing quote at the end of the expression > > which would result in a compilation error during report publishing. > > = "Exec " & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName" > > > > -- > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > > "GJ" <GJ@discussions.microsoft.com> wrote in message > > news:FED73ED0-F9F1-4434-8D36-F2EA5044BD58@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,
I sent a reply to your answer but it was on the other thread. I'm adding the content below. --------------------------- 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. ----------------------------------- [quoted text, click to view] "GJ" wrote:
> 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. > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |