Proplem w/Default Value for Parameter

I have a Parameter with 2 non-queried choices, Yes and No. I set the default
to No. When the report displays, instead of the default value displaying in
the dropdown, the users are being forced to choose. any ideas what would
cause this or how to fix?
TIA
deanI have a yes and no parameter. The labels say Yes and No and the values are
Y and N. You can either put in Y or ="Y" (either syntax works).
Also, it is case sensitive. So be sure the default you put in matches the
value not the label AND you are matching the case.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dean" <deanl144@.hotmail.com.nospam> wrote in message
news:Os5F3I6$HHA.4200@.TK2MSFTNGP04.phx.gbl...
>I have a Parameter with 2 non-queried choices, Yes and No. I set the
>default to No. When the report displays, instead of the default value
>displaying in the dropdown, the users are being forced to choose. any ideas
>what would cause this or how to fix?
> TIA
> dean
>|||I fixed this myself by deleting my deployed report and redeploying.
"Dean" <deanl144@.hotmail.com.nospam> wrote in message
news:Os5F3I6$HHA.4200@.TK2MSFTNGP04.phx.gbl...
>I have a Parameter with 2 non-queried choices, Yes and No. I set the
>default to No. When the report displays, instead of the default value
>displaying in the dropdown, the users are being forced to choose. any ideas
>what would cause this or how to fix?
> TIA
> dean
>sql

proper way to set parameters programatically?

What is the proper way to set a parameter collection's values in code? (C#
or VB.NET) should you get the parameter list using getReportParameters from
the SOAP web service then setting the items in the returned collection then
passing the collection back through setreportparameters..... or can you
just pass a list of programatically created values and names though the
setReportParaemters with out having to get the initial list (say if you
already know the parameters required) thanks!See the examples using parameters here:
http://msdn2.microsoft.com/en-us/library/aa258532(sql.80).aspx
This applies to the render method, which I'm not sure if you'll use or
not, but I think this should give you an idea of what to do.
Smokey Grindle wrote:
> What is the proper way to set a parameter collection's values in code? (C#
> or VB.NET) should you get the parameter list using getReportParameters from
> the SOAP web service then setting the items in the returned collection then
> passing the collection back through setreportparameters..... or can you
> just pass a list of programatically created values and names though the
> setReportParaemters with out having to get the initial list (say if you
> already know the parameters required) thanks!

Proper use of IF statement in stored procedure?

I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:

Cannot use empty object or column names. Use a single space if necessary.

So, what's the correct way of doing the following?

IF @.filename <> ""
BEGIN
UPDATE Experimental_Images SET contentType = @.contentType, filename = @.filename WHERE (id = @.iconNo)
ENDThe problem you are having is that you are using double quotes when youshould be using single quites. The rest of your statements are fine.

What do you mean exactly by "if the parameter is empty". If youmean if it contains an empty string or a NULL, try it like this:

IF ISNULL(@.filename,'') <> ''

If you mean it contains a NULL, try this:

IF @.filename IS NOT NULL

And if you mean it contains an empty string, try it like this:

IF @.filename <> ''

prompt/parameter position in UI

RS 2000 - I have 3 parameters in my report. I would like the 3rd parameter to be positioned directly under the 2nd parameter so it makes more sense to the user than when the parameters are displayed by default left-right top-bottom and the 3rd parameter ends up under the 1st parameter. What controls the position/location of the prompts? Do I have any control over it?I am very interested in this question. Maybe in 2 months you have found an answer (even if it is "No control over it"). Please help. Thank you.|||Unfortunately, that is exactly the response I got - there is no control over it at this time. I also did not get the impression that it was fixed in 2005.|||Thank you|||

The ordering of the controls is based on the ordering in the rdl. The layout for the parameters in Report Manager is not user adjustable. However, if you want to create your own web pages you can use the new web viewer control and create your own parameter area, ordering and displaying the parameters however you please. There is a significant amount of work required to do this generically, but it would be farily simple for a small set of reports and parameters.

-Daniel

prompt/parameter position in UI

RS 2000 - I have 3 parameters in my report. I would like the 3rd parameter to be positioned directly under the 2nd parameter so it makes more sense to the user than when the parameters are displayed by default left-right top-bottom and the 3rd parameter ends up under the 1st parameter. What controls the position/location of the prompts? Do I have any control over it?I am very interested in this question. Maybe in 2 months you have found an answer (even if it is "No control over it"). Please help. Thank you.|||Unfortunately, that is exactly the response I got - there is no control over it at this time. I also did not get the impression that it was fixed in 2005.|||Thank you|||

The ordering of the controls is based on the ordering in the rdl. The layout for the parameters in Report Manager is not user adjustable. However, if you want to create your own web pages you can use the new web viewer control and create your own parameter area, ordering and displaying the parameters however you please. There is a significant amount of work required to do this generically, but it would be farily simple for a small set of reports and parameters.

-Daniel

sql

Prompt Parameter Query?

Hi there,
How to I convert Northwind Access Query look like this
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
Employees.HireDate
FROM Employees
WHERE
(((Employees.HireDate) Between [Enter Begining date]
And
[Enter ending date]))
OR
(((([Employees].[HireDate]) Like [Enter Begining date]) Is Null))
OR
(((([Employees].[HireDate]) Like [Enter ending date]) Is Null));
Into SQL Server 2005 Stored Proc. I tried
create proc usp_hdate as
declare @.Hdate datetime
select FirstName, LastName, HireDate
From Employees
Where (HireDate = @.Hdate) or HireDate Is Not Null
but no results
All I want to create prompt parameter for HireDate
When you don't type parameter It will return all records
when you type the date it will return specific record
Thanks an advanced
Oded DrorCREATE PROC usp_hdate
AS
DECLARE @.Hdate DATETIME
SELECT FirstName, LastName, HireDate
FROM Employees
WHERE HireDate = COALESCE(@.Hdate,HireDate)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Oded Dror wrote:
> Hi there,
> How to I convert Northwind Access Query look like this
> SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
> Employees.HireDate
> FROM Employees
> WHERE
> (((Employees.HireDate) Between [Enter Begining date]
> And
> [Enter ending date]))
> OR
> (((([Employees].[HireDate]) Like [Enter Begining date]) Is Null))
> OR
> (((([Employees].[HireDate]) Like [Enter ending date]) Is Null));
>
> Into SQL Server 2005 Stored Proc. I tried
> create proc usp_hdate as
> declare @.Hdate datetime
> select FirstName, LastName, HireDate
> From Employees
> Where (HireDate = @.Hdate) or HireDate Is Not Null
> but no results
> All I want to create prompt parameter for HireDate
> When you don't type parameter It will return all records
> when you type the date it will return specific record
>
> Thanks an advanced
> Oded Dror
>
>
>
>|||Thanks for your help
This will return all value if the parameter is null
but what about when I'm submitting parameter
Basically I want to submit parameter and received one record or
don't submit record and received all records
Thanks,
Ed Dror
"MGFoster" <me@.privacy.com> wrote in message
news:iiVyf.3116$Hd4.2207@.newsread1.news.pas.earthlink.net...
> CREATE PROC usp_hdate
> AS
> DECLARE @.Hdate DATETIME
> SELECT FirstName, LastName, HireDate
> FROM Employees
> WHERE HireDate = COALESCE(@.Hdate,HireDate)
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> Oded Dror wrote:

Prompt Parameter in Ad Hoc Reports

Can i do this?
In the filter window, i want to add a field and make it into a prompt. But
then when the users run the report they should have the ability to not select
anything from the prompt and hence see the report for all values of that
field...On Feb 27, 11:47 am, Tk_Neo <T...@.discussions.microsoft.com> wrote:
> Can i do this?
> In the filter window, i want to add a field and make it into a prompt. But
> then when the users run the report they should have the ability to not select
> anything from the prompt and hence see the report for all values of that
> field...
One way of doing it is to create a report parameter for the filter
options (incl. no filter as an option) and either use the report
parameter value selected to filter with -or- use the report parameter
in the query or stored procedure to filter the results. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I posted a way to do this in the msdn forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1285083&SiteID=1
hth
Helen
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1172636771.488607.218240@.t69g2000cwt.googlegroups.com...
> On Feb 27, 11:47 am, Tk_Neo <T...@.discussions.microsoft.com> wrote:
>> Can i do this?
>> In the filter window, i want to add a field and make it into a prompt.
>> But
>> then when the users run the report they should have the ability to not
>> select
>> anything from the prompt and hence see the report for all values of that
>> field...
> One way of doing it is to create a report parameter for the filter
> options (incl. no filter as an option) and either use the report
> parameter value selected to filter with -or- use the report parameter
> in the query or stored procedure to filter the results. Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>sql

prompt for parameter in a view

I would like to create a view that will prompt the user for a parameter.
Much the same way a user can provide the paramenter for a query in Access.
Seems like it should be simple, but I don't know how to do it.
Any ideas?
Thanks
Tyler
Hi,
SQL Server will not prompt for a user input. Access will allow because it
have both Front end and back end.
Thanks
Hari
SQL Server MVP
"Tyler" <Tyler@.discussions.microsoft.com> wrote in message
news:CA2CA968-4B15-4ED6-AECF-FE3C17C119AB@.microsoft.com...
>I would like to create a view that will prompt the user for a parameter.
> Much the same way a user can provide the paramenter for a query in Access.
> Seems like it should be simple, but I don't know how to do it.
> Any ideas?
> Thanks
> --
> Tyler

prompt (parameter) position

RS 2000 - I have 3 parameters in my report. I would like the 3rd parameter to
be positioned directly under the 2nd parameter so it makes more sense to the
user than when the parameters are displayed by default left-right top-bottom
and the 3rd parameter ends up under the 1st parameter. What controls the
position/location of the prompts? Do I have any control over it?Sorry, the builtin report viewers don't provide control over the parameter
positions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty B" <PattyB@.discussions.microsoft.com> wrote in message
news:20D41C77-B0C0-4B04-82E5-72DFE5FDE84D@.microsoft.com...
> RS 2000 - I have 3 parameters in my report. I would like the 3rd parameter
> to
> be positioned directly under the 2nd parameter so it makes more sense to
> the
> user than when the parameters are displayed by default left-right
> top-bottom
> and the 3rd parameter ends up under the 1st parameter. What controls the
> position/location of the prompts? Do I have any control over it?|||Will SQL Server 2005 Reporting Services allow setting the position of the
parameters?
"Robert Bruckner [MSFT]" wrote:
> Sorry, the builtin report viewers don't provide control over the parameter
> positions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Patty B" <PattyB@.discussions.microsoft.com> wrote in message
> news:20D41C77-B0C0-4B04-82E5-72DFE5FDE84D@.microsoft.com...
> > RS 2000 - I have 3 parameters in my report. I would like the 3rd parameter
> > to
> > be positioned directly under the 2nd parameter so it makes more sense to
> > the
> > user than when the parameters are displayed by default left-right
> > top-bottom
> > and the 3rd parameter ends up under the 1st parameter. What controls the
> > position/location of the prompts? Do I have any control over it?
>
>|||No, it doesn't.
You could write your own web page with the top half showing the
parameters the way you want and show the report in the bottom half
using URL access. When you submit the parameters you could dynamically
change the report so the parameters are populated accordingly.
I have done similar things in RS2000 but you need to know some ASP
Cheers
Chris
Doris wrote:
> Will SQL Server 2005 Reporting Services allow setting the position of
> the parameters?
>
> "Robert Bruckner [MSFT]" wrote:
> > Sorry, the builtin report viewers don't provide control over the
> > parameter positions.
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Patty B" <PattyB@.discussions.microsoft.com> wrote in message
> > news:20D41C77-B0C0-4B04-82E5-72DFE5FDE84D@.microsoft.com...
> > > RS 2000 - I have 3 parameters in my report. I would like the 3rd
> > > parameter to
> > > be positioned directly under the 2nd parameter so it makes more
> > > sense to the
> > > user than when the parameters are displayed by default left-right
> > > top-bottom
> > > and the 3rd parameter ends up under the 1st parameter. What
> > > controls the position/location of the prompts? Do I have any
> > > control over it?
> >
> >
> >sql

Promp for info when executing stored procedure

Hello,
How can i do to ask for input parameters when one stored procedure is
executed?
For example:
I have one sp test that has one input parameter(exec sp_test param1), if i
execute the stored procedure without the input parameter its returned the
following error:
Server: Msg 201, Level 16, State 4, Procedure dba_sp_defrag_obj, Line 0
Procedure 'sp_test' expects parameter '@.param1', which was not supplied.
All i want to do is to advertise before the execution the need of input
parameter:
ex:
Insert value for param1:
Insert value for x:
Insert value for y:
Insert value for z:
and so on.
Thanks and best regards,
Jorge Mendes
Try this to get the list of parameters:
Select * from INFORMATION_SCHEMA.Parameters
Where specific_name = '<YourprocedureName>'
order by Ordinal_position
HTH, Jens Suessmeyer.
*** Sent via Developersdex http://www.codecomments.com ***

Promp for info when executing stored procedure

Hello,
How can i do to ask for input parameters when one stored procedure is
executed?
For example:
I have one sp test that has one input parameter(exec sp_test param1), if i
execute the stored procedure without the input parameter its returned the
following error:
Server: Msg 201, Level 16, State 4, Procedure dba_sp_defrag_obj, Line 0
Procedure 'sp_test' expects parameter '@.param1', which was not supplied.
All i want to do is to advertise before the execution the need of input
parameter:
ex:
Insert value for param1:
Insert value for x:
Insert value for y:
Insert value for z:
and so on.
Thanks and best regards,
Jorge MendesTry this to get the list of parameters:
Select * from INFORMATION_SCHEMA.Parameters
Where specific_name = '<YourprocedureName>'
order by Ordinal_position
HTH, Jens Suessmeyer.
*** Sent via Developersdex http://www.codecomments.com ***

Progromatically choosing yesterday's date as a parameter.

I have a report setup against an analysis service cube. It runs fine. Users
choose a date from the parameter list and then it pulls out the data nicely.
I need to programatically have it select a parameter for yesterday's date.
Somebody suggested setting a default script for the parameter of
=DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") to choose yesterday's date.
The problem is that my mdx date is in the format [Dimension Name].[All
Time].[Quarter].[Month].[Day] and the calendar that is being used is in the
tax year. (i.e - today, where in the first quarter of 2006)
How can I convert the current date to the appropriate format?
Any help would be greatly appreciated.
Thanks,
MattI have done similar things in the past and I wrote a SQL query that gets the
current date then I use the datepart function to get the parts (i.e quarters)
If you want to reformat the way a part displays I put the datepart inside a
CASE statement.
Another approach is to turn it into a string and use the datepart to get
the parts you want. It may look something like this.....
SELECT 'Q' + CAST(DATEPART(qq, GETDATE()) AS varchar(255)) AS Quarter
There is probably a better way to do this but this is how I did it.
"Matt" wrote:
> I have a report setup against an analysis service cube. It runs fine. Users
> choose a date from the parameter list and then it pulls out the data nicely.
> I need to programatically have it select a parameter for yesterday's date.
> Somebody suggested setting a default script for the parameter of
> =DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") to choose yesterday's date.
> The problem is that my mdx date is in the format [Dimension Name].[All
> Time].[Quarter].[Month].[Day] and the calendar that is being used is in the
> tax year. (i.e - today, where in the first quarter of 2006)
> How can I convert the current date to the appropriate format?
> Any help would be greatly appreciated.
> Thanks,
> Matt

Programmability of parameter bar(layout/items)?

Hi all,

does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?

How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?

Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?

Thanks a lot for any help

Best Regards
Marc

Unfortunately, you can't do either of these things. (#1, #2, or the "hand-over" of parameter selection via Report Designer).

If the parameter selection needs to be as dynamic as you describe, the best solution is to actually build your own custom UI in which you display the appropriate parameter UI in the appropriate places to the appropriate users...Then take the values that get selected and pass them to the report via URL Access, or plug them into the ReportViewer with code, andrender the report in the viewer. www.gotreportviewer.com has samples of the latter.

Hope this helps!

|||

Hey Russell,

that's not really good news...but thanks a lot for your help anyway..i'm going to try the solution with passing parameters by code into the ReportViewer control.

Cheers
Marc

|||

Hi Marc,

I have a similar problem. Could you please explain how you were able to solve this problem.

Thanks a lot for your help.

Regards,

Simranjeev

|||You need to query the web service to find out which parameters a report has, their data types and values etc. then programatically populate dropdowns etc. and arrange them how you like in your custom UI. You can use either a web or a windows UI for this.|||

Thanks ... Can i find some article / sample code somewhere on how to build custom UI and use it as the Parameter Area in Reporting Services

Programmability of parameter bar(layout/items)?

Hi all,

does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?

How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?

Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?

Thanks a lot for any help

Best Regards
Marc

Unfortunately, you can't do either of these things. (#1, #2, or the "hand-over" of parameter selection via Report Designer).

If the parameter selection needs to be as dynamic as you describe, the best solution is to actually build your own custom UI in which you display the appropriate parameter UI in the appropriate places to the appropriate users...Then take the values that get selected and pass them to the report via URL Access, or plug them into the ReportViewer with code, andrender the report in the viewer. www.gotreportviewer.com has samples of the latter.

Hope this helps!

|||

Hey Russell,

that's not really good news...but thanks a lot for your help anyway..i'm going to try the solution with passing parameters by code into the ReportViewer control.

Cheers
Marc

|||

Hi Marc,

I have a similar problem. Could you please explain how you were able to solve this problem.

Thanks a lot for your help.

Regards,

Simranjeev

|||You need to query the web service to find out which parameters a report has, their data types and values etc. then programatically populate dropdowns etc. and arrange them how you like in your custom UI. You can use either a web or a windows UI for this.|||

Thanks ... Can i find some article / sample code somewhere on how to build custom UI and use it as the Parameter Area in Reporting Services

Programmability of parameter bar(layout/items)?

Hi all,
does anybody of you know how to code and customize the parameter bar of the
SSRS reports? I know about putting code behind expressions but how is it with
parameters?
How is it possible to...
1. define the layout of the parameter bar and the positions of the parameter
items?
2. make the parameter items user-specific?
Is it possible to define a report with the report designer and hand over
data (parameters) by code-behind or a referenced assembly?
Thanks a lot for any help
Best Regards
MarcI don't know if this is the right way but I turned off the parameter bar and
added the following code
Dim parm As New
Microsoft.Reporting.WebForms.ReportParameter("week",
DrpWeeks.SelectedValue.ToString)
Dim p() As Microsoft.Reporting.WebForms.ReportParameter = {parm}
ReportViewer1.ServerReport.SetParameters(p)
DrpWeeks is a drop down box populated just as I would have if I had
populated it in the report designer.
User specific is by grabbing user!userid
"MarcBey" wrote:
> Hi all,
> does anybody of you know how to code and customize the parameter bar of the
> SSRS reports? I know about putting code behind expressions but how is it with
> parameters?
> How is it possible to...
> 1. define the layout of the parameter bar and the positions of the parameter
> items?
> 2. make the parameter items user-specific?
> Is it possible to define a report with the report designer and hand over
> data (parameters) by code-behind or a referenced assembly?
> Thanks a lot for any help
> Best Regards
> Marc
>
>

Programatically Email a report

Does anyone have any code to run a report and email it. I want to run the
report that has one parameter and then email it. With the report as the Body
of the email and then the same report as an attachment in pdf format. I would
like to also add some text to the email in front of the report. Or could you
point me to a sample or book that would help. Which report interface would I
use?
--
Thanks,
Jon AYou could use subscriptions - see "E-Mail Delivery in Reporting Services" in
reporting Services Books On Line.
If you want more control over the email you could create the pdf file
programatically either using URL access (again in BOL) or the Web Services,
then write a bit of code to send email - that will depend on what your email
system is.
"Jon A" wrote:
> Does anyone have any code to run a report and email it. I want to run the
> report that has one parameter and then email it. With the report as the Body
> of the email and then the same report as an attachment in pdf format. I would
> like to also add some text to the email in front of the report. Or could you
> point me to a sample or book that would help. Which report interface would I
> use?
> --
> Thanks,
> Jon A|||Which is more efficient Web services or url access. I tried using
subscriptions but I can't figure out how to get the report in the body of the
message. Also my mail server needs you to logon. Where do I put the user name
and password for the mail server to get or does subsciptions use an outlook
profile?
--
Thanks,
Jon A
"Mary Bray [SQL Server MVP]" wrote:
> You could use subscriptions - see "E-Mail Delivery in Reporting Services" in
> reporting Services Books On Line.
> If you want more control over the email you could create the pdf file
> programatically either using URL access (again in BOL) or the Web Services,
> then write a bit of code to send email - that will depend on what your email
> system is.
>
> "Jon A" wrote:
> > Does anyone have any code to run a report and email it. I want to run the
> > report that has one parameter and then email it. With the report as the Body
> > of the email and then the same report as an attachment in pdf format. I would
> > like to also add some text to the email in front of the report. Or could you
> > point me to a sample or book that would help. Which report interface would I
> > use?
> > --
> > Thanks,
> > Jon A