Get All Possible Values of a Report Parameter

Hi,
I need an application that displays reports, and prompts the user
for the parameter values. I have used the GetReportParameters() to retrieve
the list of parameters. However, i could not find a way to get the available
values when the parameter is of a list type. For example a countries
parameter would include a number of countries to choose from ( retrieved
from a dataset ). How could i retrieve the list of all the countries
dynamically? Can someone help me out.
Thanks,
Loui MerciecaHi Loui:
It sounds as if you are looking for the ValidValues property of a
parameter. See my article here for some tips:
Using GetReportParameters
http://odetocode.com/Articles/123.aspx
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 29 Sep 2004 12:20:06 +0200, "Loui Mercieca" <loui@.gfi.com>
wrote:
>Hi,
> I need an application that displays reports, and prompts the user
>for the parameter values. I have used the GetReportParameters() to retrieve
>the list of parameters. However, i could not find a way to get the available
>values when the parameter is of a list type. For example a countries
>parameter would include a number of countries to choose from ( retrieved
>from a dataset ). How could i retrieve the list of all the countries
>dynamically? Can someone help me out.
>Thanks,
>Loui Mercieca
>|||Make sure to pass in true for the forRendering parameter. Check here for
more information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_6x4n.asp
Here is the relevant text from that page:
If ForRendering has a value of false, the parameter meta data returned
represents the parameter data that is currently associated with the
specified report. If any parameters values are based on a query and you are
interested in returning the query-based parameters valid values list, you
need to set ForRendering to true, In addition, for query based parameters,
you need to ensure that you have passed in all of the credential information
required to return the query parameters.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Loui Mercieca" <loui@.gfi.com> wrote in message
news:eeseq3gpEHA.1300@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need an application that displays reports, and prompts the user
> for the parameter values. I have used the GetReportParameters() to
retrieve
> the list of parameters. However, i could not find a way to get the
available
> values when the parameter is of a list type. For example a countries
> parameter would include a number of countries to choose from ( retrieved
> from a dataset ). How could i retrieve the list of all the countries
> dynamically? Can someone help me out.
> Thanks,
> Loui Mercieca
>|||THANK YOU! That ForRendering = true worked great for me. I was having
a similar problem. I wish they explained better what all of these
setting were. But thanks to you, I bypassed this problem.

Generic Stored procedure

Hi All!

i want to retrieve reults from table "tblCategory" by passing

search string as parameter and column name as parameter as well.

CREATE Procedure uspSearchCategory

(

@.Search nvarchar(255),

@.column varchar(100)

)

AS

SELECT

*

FROM

tblCategory

WHERE

@.column LIKE '%' + @.Search+'%'

This doesn't work as @.column in last line is incorrect. Can anybody tell me how can i achieve that.

If i write

name LIKE '%' + @.Search+'%' or

ID LIKE '%' + @.Search+'%'

it works.But can it works as general for ant column name i pass as @.column.

thanx

You need to turn your query into a string, then run the string using code like this:

exec sp_executesql @.query

where @.query is the nvarchar that contains the SQL to run

Generating parameters from a list

I have a number of fiscal reports that I am writing. Currently, the
parameter for year is hard-coded. Ideally, I would like to be able to
generate the parameter list so that it offers years in descending from the
current year back 5 years or some defined period. In this manner, the
reports would not need to be updated each new year to modify the parameter
list. Is this possible, in v. 2000?One solution could be to create a dataset that pulls the year of a date field
in your report (group on the year) and use that as your query for the
parameter.
"Sarah" wrote:
> I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?|||This query will do it... Setup your parameter and set this query to be the
default value
select datename(yyyy,getdate())
+',' + datename(yyyy,dateadd(yy,-1,getdate()))
+',' + datename(yyyy,dateadd(yy,-2,getdate()))
+',' + datename(yyyy,dateadd(yy,-3,getdate()))
+',' + datename(yyyy,dateadd(yy,-4,getdate()))
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sarah" <Sarah@.discussions.microsoft.com> wrote in message
news:4F379DE9-9B9D-454A-9ABA-41CFB39C41C9@.microsoft.com...
>I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?

Generate report automaically and email them : Please help

Please help me if possible.
My report takes a parameter say "name".
I want reports to be generated automatically for various values of
"name" and each of the generated report should be sent as email to the
person every month or so.
How do I go about achieving this? I am very new to reporting services
and I am stuck with very little documentation.
A detailed explanation or even a hint in the right direction would be
greatly appreciated.Use a data driven subscription. This will require setting up an additional
table to hold the values, but then there is a wizard to walk through in the
Report Manager to set this up. Take a look at the SQL-RS Books On Line to
get the details as it takes several steps to get it going.
Brian
"Aravind" <r.aravind@.gmail.com> wrote in message
news:1121867840.826151.118570@.o13g2000cwo.googlegroups.com...
> Please help me if possible.
> My report takes a parameter say "name".
> I want reports to be generated automatically for various values of
> "name" and each of the generated report should be sent as email to the
> person every month or so.
> How do I go about achieving this? I am very new to reporting services
> and I am stuck with very little documentation.
> A detailed explanation or even a hint in the right direction would be
> greatly appreciated.
>|||Yea, just make sure you have the enterprise edition of SQL server before you
start as it won't work in standard. The EE version is 10x the cost of
standard.
"goodman93" wrote:
> Use a data driven subscription. This will require setting up an additional
> table to hold the values, but then there is a wizard to walk through in the
> Report Manager to set this up. Take a look at the SQL-RS Books On Line to
> get the details as it takes several steps to get it going.
> Brian
> "Aravind" <r.aravind@.gmail.com> wrote in message
> news:1121867840.826151.118570@.o13g2000cwo.googlegroups.com...
> > Please help me if possible.
> >
> > My report takes a parameter say "name".
> > I want reports to be generated automatically for various values of
> > "name" and each of the generated report should be sent as email to the
> > person every month or so.
> >
> > How do I go about achieving this? I am very new to reporting services
> > and I am stuck with very little documentation.
> >
> > A detailed explanation or even a hint in the right direction would be
> > greatly appreciated.
> >
>
>