Passing parameter from a webpage to a report

I am developing a website which need to allow user to pass through several webpages for criteria selection and generate a report base on the selected criteria finally.

Anyone know how to pass the selected value from a webpage to the SQL statement or stored procedure which used to generate the report? Or there are other methods to do so?look at the rendering methods of RS - there's one by URL where you can pass the parameters via URL and request the report.|||I believe you simply build a querystring and append the parameters and values to the end of the querystring.|||Thanks for your kindly reply.

I have another related question see whether anyone can help. Base on my current knowledge, I know that we can pass one value for each parameter. E.g. countryID=20. If I want to pass multiple values for this parameter, e.g. countryID=20, 21, 25, ... How can I do so? Also, how should I set it in report designer for this purpose?|||Can you explain little more in detail (perhaps with some sampoe) ? Is your data in the format 20,21,25 for each record ?|||You could pass in a string like you describe. You would need to have logic in your report or stored procedure to handle such a request, though...|||In my report, there is a chart to compare sales performance between several products which are selected by user. The number of selected products is not fixed. I need to pass multiple productID into the report.

Also, I face a problem that ...seem I can't pass parameters into SQL functions to build dataset. Is it a rule? The error msg is "Syntax error or access violation.". My statement is as follows:

select * from getTable(@.productID)

Notes: getTable is a user-defined function.|||so what u can do

Select * from table where productid in (@.productid)|||In the following statement, getTable is a function with many calculation.

select * from getTable(@.productID)

If I use the one below, I can't get what I want. Also, seem can't be a string e.g. "12, 13, 14"
Select * from table where productid in (@.productid)|||so just pass the value in paenthesis

like '12,13,14'. it will definitely work.

If it is a function. then it should return the value same as u mentioned like "12,13,14"
if u can make it " '12,13,14' " . Then you can pass on values.

Passing in own Variable to print

I want to display some of the information not in the datasource selection but
through application passing in information. For example: personName,
reportName (dynamical defined depending on which button user click) and etc.
These variable only use in header or footer.
How can I pass these information to the report viwer? Any help is greatly
appreciated.I've done similar things using report parameters.
"Locus" <Locus@.discussions.microsoft.com> wrote in message
news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
>I want to display some of the information not in the datasource selection
>but
> through application passing in information. For example: personName,
> reportName (dynamical defined depending on which button user click) and
> etc.
> These variable only use in header or footer.
> How can I pass these information to the report viwer? Any help is greatly
> appreciated.|||Hi Bill, how you do it?
I tried adding parameters, it seems to automatically pop up to ask for input
value, not allowing me to passing in value programmatically.
-- Locus
"Bill Miller" wrote:
> I've done similar things using report parameters.
> "Locus" <Locus@.discussions.microsoft.com> wrote in message
> news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> >I want to display some of the information not in the datasource selection
> >but
> > through application passing in information. For example: personName,
> > reportName (dynamical defined depending on which button user click) and
> > etc.
> > These variable only use in header or footer.
> >
> > How can I pass these information to the report viwer? Any help is greatly
> > appreciated.
>
>|||Try setting the Parameters Hidden parameter to checked.
"Locus" wrote:
> Hi Bill, how you do it?
> I tried adding parameters, it seems to automatically pop up to ask for input
> value, not allowing me to passing in value programmatically.
> -- Locus
> "Bill Miller" wrote:
> > I've done similar things using report parameters.
> >
> > "Locus" <Locus@.discussions.microsoft.com> wrote in message
> > news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> > >I want to display some of the information not in the datasource selection
> > >but
> > > through application passing in information. For example: personName,
> > > reportName (dynamical defined depending on which button user click) and
> > > etc.
> > > These variable only use in header or footer.
> > >
> > > How can I pass these information to the report viwer? Any help is greatly
> > > appreciated.
> >
> >
> >

Pass DatabaseName as a Parameter - dynamic DB selection

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.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.|||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.
"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
"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.
"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.
"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.
> > >
> > > Thanks in advance.

Pass a Parameter into a textbox on a report?

I have a report that is unsing a multi-value parameter selection list. Based on the parameter I select, I would like to display the selection in a textbox directly on the report. Would someone know what statement I place into the textbox to capture the parameter:

Some details: 'read_perflvl' is the data field in the original database. 'perflvl' is the name of my report parameter.

I dropped the 'read_perflvl' field into the text box on my report and it defaulted to

"=First(Fields!read_perflvl.Value)" , and this actually worked fine if I selected only one parameter. But if I selected two or more from the multi-valued parameter list, it only shows the first one (clearly due to the FIRST function it is referencing) in the textbox.

Is there a different prefix to the statement that I can use so that it will display all of the parameter values I select?

Thanks for any suggestions.

Dear esloat,

try the following expression in the text box.

-right click on the text box and select expression from the drop down menu.

-then write the following expression:

=parameters!perflvl.value

I think this should display the value of the selected parameter in the text box.

Sincerely,

Amde

|||

Amde, Wow, great minds think alike. Just as I received your message I had pasted that exact expression into the textbox - I lifted it from the code block that was refering to the parameter in the report.

Unfortunately when I run the report I get a non-discript '#ERROR' message returned. The rest of the report runs fine, but the value of the textbox displays as an error.

Might this be due to a conflict between a string and numeric value? The originating database field is a string. Does the parameter expression store the result as a numeric value?

Anyway, I'll keep trying and post if I resolve the error message. Thanks.

|||hi , u havnt tld me frm where to pass frm asp.net to reports . so u can do as report parameter id=new report parameter()
id.name="ur id name";
id.values.add(textbox1.text);
reportviewer.serverreport(id)

..in reports ,, use same field(=fileds!database.value)

deploy report,, and use in asp.net as
reportviewer.reportpath="report path" as "/ report project1/reports"