Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked Available values = "From Query" Dataset = [create a dataset that returns a table w/ a Id and Description column] Value field = [the Id field from the table] Label field=[the Description column from the table] Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default Non-queried report parameter not updated when project is deployed

Adding a value to a non-queried default report parameter value does not update on the target server after deployment.

To recreate

1. Create a report in Visual Studio and add a report parameter with the following properties:

Multi-value is checked

Available values = "From Query"

Dataset = [create a dataset that returns a table w/ a Id and Description column]

Value field = [the Id field from the table]

Label field=[the Description column from the table]

Default values = "Non-queried" (add several values the match the IDs from the table so that some of the values in the report dropdown will show up as checked when rendering the report)

2. Build and deploy the report to the reporting server. View the report and verify the specified items are checked in the report parameter.

3. Go back to Visual Studio and add a value to the Non-queried Default values.

4. Build and deploy the report again. View the report. The newly added item is not selected.

Notes

I verified that the newly added ID exists in the rdl file (as xml) on both the development box and the server where the report was deployed. However, when I view the report parameter using Management Studio (connect to the reporting server), the newly added value for the report parameter does not exist. I verified that changes are being deployed by adding new parameters and changing other properties of the parameter. I thought maybe the rdl itself was being cached somehow - I tried restarting IIS, SQL Server, and SQL Reporting services. None worked. Note that running the report on the development box by running the project through Visual Studio DOES reflect the change to the parameter.

Work-arounds

1. Create a dataset for the report that returns a table of the Ids that you want pre-selected. The query could be something like this:

SELECT '4' AS SelectedId
UNION
SELECT '5' AS SelectedId
UNION
SELECT '6' AS SelectedId

2. Delete the report in Management Studio, then redeploy.

I have issue w/ both workarounds because for 1) it is not intuitive and you have to remember to do this for every similar case, and 2) this extra step has to occur each time the report is deployed w/ changes to the report parameter.

Hi there!

I recently discovered the same problem.
The option of deleting the report and do a redeploy is nothing for us, because of already made subscribtions that will also dissapear..

Is there a release comming to fix the problem?

Option 2 (using datasources) is not the most efficient one..
Any words from the developers?

//P?r

|||

My collegue and I are also having this problem. Non-queried defaults don't seem to work.

When will this be fixed?

|||

I have not reported this, other than posting here. Can anyone tell me the best place to submit this to support?

I tried going to http://support.microsoft.com/contactussupport/?ws=support and I received the following message:

"If you think you have found a bug in a Microsoft product, contact our Microsoft Product Support Services department.
(800) MICROSOFT (642-7676)."

I then tried to call the phone number and got a voice menu. After a couple of minutes of searching for the correct option, I gave up.

|||This is by design. The best way to update parameters on server is to delete existing report before deploying a new one.|||This is "by design"? That's not a logical response. If you read the entire thread you will see that deleting the report is not an option for many of us since all subscriptions will be lost. This is a serious bug that should be fixed ASAP.|||

Actually it really is By Design. I understand that you don't agree with the design. The reason for this design choice is that some things associated with the report depend on a certain parameter state, like subscriptions. Therefore, we consider the server's parameter state to be the "master". In order to change the parameter state, you need to do so on the server, either through Report Manager, Management Studio, or SharePoint integrated UI.

The alternative would be to allow republishing to reset the parameter state. In this case, any changes made to the parameters through a server call would be wiped out, since server changes don't cascade back to Report Designer. For example, Mary publishes the RDL with a display prompt of "Enter a parameter value." Some time later, Bob changes the display prompt (using Report Manager) to "Please enter the desired value." Some time after that, Mary republishes the report, this time with a display prompt of "Here is where you enter the user's department name." Which version of the display value is used? Sure, Mary and Bob shouldn't have made conflicting changes, but it happens, and the SW has to make a choice.

It is possible, of course, to enable a comingling of the server state and the project state, but that requires additional complexity and UI to allow conflict resolution. Such a conflict resolution feature is something we haven't implemented to-date. It's possible that this could be implemented in a future release.

A different workaround for the specific case of default values is to modify the default values in Report Manager, Management Studio, or SharePoint integrated UI. That doesn't require deleting the report, and allows you to retain the subscriptions and other bound entities.

Default for an int parameter in SP

I'm using a stored procedure that receives one parameter namely @.EmployeeID INT

but when I want to give this parameter a default value, my SP fails.

I did it like with a varchar where it works.

--
@.Employee INT = '%'

--

Is this correct or did I use a wrong syntac/wildcard?

Greetings,
GeoffINT is a numeric datatype only you should use digits to assign defaults.
If not use VARCHAR to assign such special chars.|||Isn't there a numeric default that I can use?

actually i use the SP to do the next thing.

If I don't give a parameter I want all recoreds to be returned. When passing through the ID (as parameter tot the SP) I want only that record to be returned. So if I understand correctly you're saying there is no sucth thing as setting a default for an int-type?

Greetings,
Godofredo|||Only numerics are allowed as default for int.|||Can i define a range then?

like @.EmployeeID INT = [0-9]

so that all numbers can be received? or how precisely do I do this?

If no parameter is given I want al records returned.

Greetings,
Geoff|||CREATE PROCEDURE sp_myproc
@.emp_id int = 0
as

select * from my_table
where emp_id =
case @.emp_id when 0 then emp_id else @.emp_id end

when you call sp_myproc without specifying any parameters then all employees are retrieved.

Is that what you were asking?|||Yes indeed

default Datetime parameter

Hi,
I'm editing some reports at the moment, and they've been set up using start
and end date parameters both having datetime datatype.
They both have a default value as the user would like them to run
immediately, the problem is they are both set to Date.Now() so no
information is coming out as the start date is now! i would like the start
date to default to 5 yrs in the past, what can i type in. I'd like it to
use the Date.Now() or getdate() methods or something like that so it will
change automatically.
any suggestions would be much appreciated.
cheers GregSet the default value to =System.DateTime.Now.AddYears(-5).|||Set the default value to =System.DateTime.Now.AddYears(-5).|||To do the same thing via sql... create a dataset with
select dateadd(yy,-5,getdate())
and use this as the default...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Potter" wrote:
> Set the default value to =System.DateTime.Now.AddYears(-5).
>|||cheers guys,
went for potter's solution as it's running off a stored procedure and
couldn't be bothered to change it.
thanks
Greg
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D8794D05-5CCD-4F96-92DE-09EB773C8F9C@.microsoft.com...
> To do the same thing via sql... create a dataset with
> select dateadd(yy,-5,getdate())
> and use this as the default...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Potter" wrote:
>> Set the default value to =System.DateTime.Now.AddYears(-5).
>>|||Ok new problem, and in fact part fo the reason why i asked the firsdt
question.
The report has a hyperlink to drillthrough to the next report, the
parameters are passed through by the hyperlink, the problem is i keep
getting an error on the start date. They are both set as datetime
datatypes. The error is
The value provided for the report parameter 'StartDate' is not valid for its
type. (rsReportParameterTypeMismatch)
cheers
Greg
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D8794D05-5CCD-4F96-92DE-09EB773C8F9C@.microsoft.com...
> To do the same thing via sql... create a dataset with
> select dateadd(yy,-5,getdate())
> and use this as the default...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Potter" wrote:
>> Set the default value to =System.DateTime.Now.AddYears(-5).
>>|||Greg,
How are you going about this hyperlinking? Are you using 'jump to
report' or are you using the 'jump to url'?
Is the value for the date in the hyperlink actually the parameter value
(ie - =Parameters!Date.Value) or is it a value from a dataset (ie
=Fields!Date.Value)?
Potter|||Jump to report and it's literally just passing the parameter from the first
report to the parameter in the second report.
Greg
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> Greg,
> How are you going about this hyperlinking? Are you using 'jump to
> report' or are you using the 'jump to url'?
> Is the value for the date in the hyperlink actually the parameter value
> (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> =Fields!Date.Value)?
> Potter
>|||Also for the record, the links work fine in preview mode it's only a problem
when viewed through report manager
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> Greg,
> How are you going about this hyperlinking? Are you using 'jump to
> report' or are you using the 'jump to url'?
> Is the value for the date in the hyperlink actually the parameter value
> (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> =Fields!Date.Value)?
> Potter
>|||Try explicitly converting the value passed in the Jump To. ie =Convert.ToDateTime(Parameters!DateParam.Value)
Greg wrote:
> Also for the record, the links work fine in preview mode it's only a problem
> when viewed through report manager
> "Potter" <drewpotter@.gmail.com> wrote in message
> news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> > Greg,
> >
> > How are you going about this hyperlinking? Are you using 'jump to
> > report' or are you using the 'jump to url'?
> >
> > Is the value for the date in the hyperlink actually the parameter value
> > (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> > =Fields!Date.Value)?
> >
> > Potter
> >|||cheers, i haven't tried that as i have an in house written report viewer
which seems to deal with it no problem. So i'm not worried about report
manager having problems with it anymore. Thanks for your help though
Greg
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134784157.263872.184070@.g47g2000cwa.googlegroups.com...
> Try explicitly converting the value passed in the Jump To. ie => Convert.ToDateTime(Parameters!DateParam.Value)
> Greg wrote:
>> Also for the record, the links work fine in preview mode it's only a
>> problem
>> when viewed through report manager
>> "Potter" <drewpotter@.gmail.com> wrote in message
>> news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
>> > Greg,
>> >
>> > How are you going about this hyperlinking? Are you using 'jump to
>> > report' or are you using the 'jump to url'?
>> >
>> > Is the value for the date in the hyperlink actually the parameter value
>> > (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
>> > =Fields!Date.Value)?
>> >
>> > Potter
>> >
>sql

Default date using datepicker

Hi
I'm working on a report in RS2005 and are using a start and enddate
parameter. They are of the datatype DateTime. By using this type it is
possible to use the datepicker on the report. However, I would like the
report to have a default date range say 01/01/2006 as startdate and
12/31/2006 as enddate, with the option to change this using the datepicker.
How do I set this up. I've tried using the Non-queried field on the parameter
wihtout any luck.
Thank you.This is how I do it
Create a dataset against some SQL database, either the db you're querying or
the Report Server.
Query : Select getdate() as Today, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
as FirstDayOfYear, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1,
0)) as LastDayOfYear
In your report, open the parameters pane.
Set the parameter data type to DateTime
Set the default values to "From Query", select the dataset you created and
select the column you want to use from your query.
The date picker control seems to need the time as well as the date, so don't
format the values from the SQL query.
The date picker will also display this timestamp if it's not 00:00:00 so you
might have to tweak the output a bit.
More tips on calculating different sql server dates at:
http://www.databasejournal.com/features/mssql/article.php/3076421
Kaisa M. Lindahl Lervik
"Martin GC" <MartinGC@.discussions.microsoft.com> wrote in message
news:26850882-D269-4F3C-8BA7-BECD2B1382C0@.microsoft.com...
> Hi
> I'm working on a report in RS2005 and are using a start and enddate
> parameter. They are of the datatype DateTime. By using this type it is
> possible to use the datepicker on the report. However, I would like the
> report to have a default date range say 01/01/2006 as startdate and
> 12/31/2006 as enddate, with the option to change this using the
> datepicker.
> How do I set this up. I've tried using the Non-queried field on the
> parameter
> wihtout any luck.
> Thank you.

Default date problem

Hi,

Good Day!

In my sproc, I m trying to set a default value for a parameter, but it's sending me an error. It seems like having probs with brackets!

Code Snippet

@.Date DateTime = GetDate(),

Code Snippet

Msg 102, Level 15, State 1, Procedure usp_Receive_Add, Line 8
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure usp_Receive_Add, Line 40
Must declare the scalar variable "@.ProductID".

Please tell me what's the prob! I m trying to set todays date to the parameter if nothing was supplied in the @.Date parameter.

Regards

Kapalic

Use the following logic to set the current date..

Code Snippet

Create proc MyProc
(
@.Date datetime = '1900-01-01'
)
as
Begin
Select @.Date = Case When @.Date <> '1900-01-01' Then @.Date Else Getdate() End
Select @.Date
End

go

Exec MyProc '2/2/2007'
Exec MyProc

|||How about setting default value of @.date to NULL and set it to GetDate() in store procedure body? I just wandering 1900-1-1 is valid value. here is valid value of DateTime according SQL Server 2005 document.

datetime

January 1, 1753, through December 31, 9999

|||

Yes.. You can do it with NULL.

Suppose if you want to store the explicit null value on your table then this logic wont work. RITE?

So we are setting some default value which we are assuming that it never passed from our UI.

1900-01-01 is valid value only. it is with in the given range Buddy.. It is a typical sql coders starting value [Cast(0 as Datetime)]

|||

In a variable declaration, you can set a variable to a constant, e.g., a value.

However, you cannot set it to the results of a function. Getdate() is a function.

As suggested, if you wish to make the parameter optional, set a default value of '01/01/1900', and then if you wish to set it to the current date/time, after entering the procedure code (after 'AS'), set the parameter = getdate().

Default date parameter in subscription

Hi all. Does anyone know how I can provide today's date as a subscription's
default date parameter? I thought I might be able to just state =Today() or
something similar when creating the subscription but it doesn't seem to work
out.
Thanks in advance.When you publish the report initially on the report server, the default
value of the report parameter has to be =Today. Expressions as parameter
values can only be defined on the initial report publishing.
Every explicitly user-specified parameter value on the report execution or
when setting up a subscription will be interpreted as constant value and not
as expression.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Raul R" <roller8@.hotmail.com> wrote in message
news:ehkI80%231EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hi all. Does anyone know how I can provide today's date as a
subscription's
> default date parameter? I thought I might be able to just state =Today()
or
> something similar when creating the subscription but it doesn't seem to
work
> out.
> Thanks in advance.
>|||This may be a dumb question, but how do you set default values for parameters
when publishing?
I tried by going into the dataset properties and under the parameters tab
setting the value next to one of my params to '=datetime.today.adddays(-1)'
but when running the report i'm still prompted for the values...
My goal is to have this report setup w/ subscriptions that automatically run
using the previous days date, and also provide the flexibility to the user so
they can enter any date range they want...
Thanks!
"Robert Bruckner [MSFT]" wrote:
> When you publish the report initially on the report server, the default
> value of the report parameter has to be =Today. Expressions as parameter
> values can only be defined on the initial report publishing.
> Every explicitly user-specified parameter value on the report execution or
> when setting up a subscription will be interpreted as constant value and not
> as expression.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Raul R" <roller8@.hotmail.com> wrote in message
> news:ehkI80%231EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > Hi all. Does anyone know how I can provide today's date as a
> subscription's
> > default date parameter? I thought I might be able to just state =Today()
> or
> > something similar when creating the subscription but it doesn't seem to
> work
> > out.
> >
> > Thanks in advance.
> >
> >
>
>|||Once a report is published on a report server and you then publish a report
with the same name and the same report parameter names, the information from
the new report gets merged with the old report. This is very useful for
production environments where you have your data sources pointing to
production databases and probably certain constant parameter default
settings. Hende, just "updating" the report won't trash your configuration
settings.
If you want to avoid this behavior, just delete the report from the report
server before you publish it again. Then the default values specified should
take effect.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben Sullins" <BenSullins@.discussions.microsoft.com> wrote in message
news:1EDE6675-EF22-47D4-A832-8FD90481D141@.microsoft.com...
> This may be a dumb question, but how do you set default values for
parameters
> when publishing?
> I tried by going into the dataset properties and under the parameters tab
> setting the value next to one of my params to
'=datetime.today.adddays(-1)'
> but when running the report i'm still prompted for the values...
> My goal is to have this report setup w/ subscriptions that automatically
run
> using the previous days date, and also provide the flexibility to the user
so
> they can enter any date range they want...
> Thanks!
> "Robert Bruckner [MSFT]" wrote:
> > When you publish the report initially on the report server, the default
> > value of the report parameter has to be =Today. Expressions as parameter
> > values can only be defined on the initial report publishing.
> > Every explicitly user-specified parameter value on the report execution
or
> > when setting up a subscription will be interpreted as constant value and
not
> > as expression.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Raul R" <roller8@.hotmail.com> wrote in message
> > news:ehkI80%231EHA.3820@.TK2MSFTNGP11.phx.gbl...
> > > Hi all. Does anyone know how I can provide today's date as a
> > subscription's
> > > default date parameter? I thought I might be able to just state
=Today()
> > or
> > > something similar when creating the subscription but it doesn't seem
to
> > work
> > > out.
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
> >

Default date parameter

I have two date parameters, a start and end date. I want the end date to
default to the start date if the user does not fill out the field for the end
date. If they do fill out the end date, then I want them to take this.
This should be really simple but everything I've tried has not worked. How
do you get SRS to do this? I have tried setting default values in Report
Parameters screen and I've also messed with the Allow Null Value checkbox.
Do I need to check this also? Please let me know how to configure the Report
Parameters.
Thank you.Ryan,
If I understand you correctly, you should be able to set the EndDate
parameter to 'Allow Null'.
In you SQL, check the parameter values passed in and conditionally
assign the EndDate the value of the StartDate if the EndDate is null.
Andy Potter

default date in a subscription

Hello! How can i put a default date in one of my parameters in a subsciption?
i.e : each day - i want 'today' date in the parameter...
Thanks> Hello! How can i put a default date in one of my parameters in a
subsciption?
> i.e : each day - i want 'today' date in the parameter...
Use the Today(), Now() or DateString() VB functions for the default value
expression, e.g.
=DateString()
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hello!
I did try the functions you wrote - but it seems that i can not do that in a
subscription - only in the report parameter.
I want to be able to do that when i define a subscription...
Thanks Any Way and if you have other idea - i will be happy if you wrote back.
Thanks!
"Penker" wrote:
> Hello! How can i put a default date in one of my parameters in a subsciption?
> i.e : each day - i want 'today' date in the parameter...
> Thanks|||When you create a subscription, you have possibility to use the default
values of the report parameters (at the bottom of the "New Subscription"
page). So, just put the VB function in the expression for the default value
of the report parameter, and then use this default value in your
subscription.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Penker" <Penker@.discussions.microsoft.com> wrote in message
news:0A004250-7E9A-4511-8A38-E8B94F2D7B34@.microsoft.com...
> Hello!
> I did try the functions you wrote - but it seems that i can not do that in
a
> subscription - only in the report parameter.
> I want to be able to do that when i define a subscription...
> Thanks Any Way and if you have other idea - i will be happy if you wrote
back.
> Thanks!
> "Penker" wrote:
> > Hello! How can i put a default date in one of my parameters in a
subsciption?
> > i.e : each day - i want 'today' date in the parameter...
> > Thanks|||I tried this several ways to accomplish this and have been unsuccessful. From
what i've read it seems you must set the default params in reoprt designer.
Still having trouble on how to accomplish that. But once I do here is the
function I had planned on using, if you figure out how to set the default
param values in report designer please post. Thanks...
=datetime.today.adddays(-1) --ive tested this in a textbox and it works...
"Dejan Sarka" wrote:
> When you create a subscription, you have possibility to use the default
> values of the report parameters (at the bottom of the "New Subscription"
> page). So, just put the VB function in the expression for the default value
> of the report parameter, and then use this default value in your
> subscription.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Penker" <Penker@.discussions.microsoft.com> wrote in message
> news:0A004250-7E9A-4511-8A38-E8B94F2D7B34@.microsoft.com...
> > Hello!
> > I did try the functions you wrote - but it seems that i can not do that in
> a
> > subscription - only in the report parameter.
> > I want to be able to do that when i define a subscription...
> > Thanks Any Way and if you have other idea - i will be happy if you wrote
> back.
> > Thanks!
> >
> > "Penker" wrote:
> >
> > > Hello! How can i put a default date in one of my parameters in a
> subsciption?
> > > i.e : each day - i want 'today' date in the parameter...
> > > Thanks
>
>

Default a parameter to todays date

How can I set the default of a datetime parameter to be todays date?
Thanks,
AidanDId you try =Today() or =Now() ?
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im Newsbeitrag
news:OUvRiODaFHA.1088@.TK2MSFTNGP14.phx.gbl...
> How can I set the default of a datetime parameter to be todays date?
> Thanks,
> Aidan
>|||That's great cheers
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uxNaGkDaFHA.1040@.TK2MSFTNGP10.phx.gbl...
> DId you try =Today() or =Now() ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Aidan Garnish" <aidan.garnish@.waterstons.co.uk> schrieb im Newsbeitrag
> news:OUvRiODaFHA.1088@.TK2MSFTNGP14.phx.gbl...
> > How can I set the default of a datetime parameter to be todays date?
> >
> > Thanks,
> >
> > Aidan
> >
> >
>

DecryptByKeyAutoCert with cert_password bugged?

I am having trouble with the DecryptByKeyAutoCert function when I try to provide the cert_password parameter.

According to the BOL, it is the second parameter of the function:
DecryptByKeyAutoCert
( cert_ID , cert_password , { 'ciphertext' | @.ciphertext }
[ , { add_authenticator | @.add_authenticator }
[ , { authenticator | @.authenticator } ]
]
)
However, when I provide a password, I get the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of DecryptByKeyAutoCert function.

This is totally in contradiction with what the BOL description says:

cert_password

Is the password that protects the private key of the certificate. Can be NULL if the private key is protected by the database master key. varchar.

Does anyone have any experience with this? I tried Google already but didn't get too many results, unfortunately.

I do not want to use the master key because that would enable all DBAs to read the encrypted data without knowing any password to decrypt. But I do need to use the automatic function due to the design of our dated VB6 application (i.e. it is impossible to open the key prior to the select due to design of interaction with Crystal Reports).

Also, is there any way to find out what parameter type the function is actually expecting? Where are these functions stored?

PS: I tried this on win2003 SP1/SQL2005 SP1 and winXP SP2/SQL2005 SP2 - same result on both.

Thanks in advance!

Okay, I found the solution: the function apparently expects an nvarchar instead of a varchar...
What a difference one letter sometimes can make...

Declaring and Using report parameters

I need to declare a report parameter, for example BegPeriod. Once that
parameter has been declared I need to work the parameter. For example the
parameter needs to evaluated with the following statement: "if
right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the value
in EarnDed00 else 0 Jan". This returns a month, year and deduction value.
that will need to be done for all months.
Will Reporting Services do this? If so, can you direct me to the correct
method.
Thank you,
normadReport parameters are easy to declare. Go to layout tab and report menu,
report parameters. Add a parameter.
Now, you don't really say where you want to use this expression. Is
EarnDed00 a field in a dataset? Are you wanting to use this expression to
pass a value to a stored procedure? Are you wanting the expression to be
shown in a field of the table object?
Read up on expressions. And note, when you create a query parameter RS
automatically creates a report parameter but you don't have to use it, you
can map a query parameter to an expression.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NormaD" <NormaD@.discussions.microsoft.com> wrote in message
news:98012AA4-B6EA-4270-8BFB-265E8E35152B@.microsoft.com...
>I need to declare a report parameter, for example BegPeriod. Once that
> parameter has been declared I need to work the parameter. For example the
> parameter needs to evaluated with the following statement: "if
> right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the
> value
> in EarnDed00 else 0 Jan". This returns a month, year and deduction value.
> that will need to be done for all months.
> Will Reporting Services do this? If so, can you direct me to the correct
> method.
> Thank you,
> normad
>|||Bruce: Thank you. I want this expression to be shown in the report. Can
this be done?
Norma
"Bruce L-C [MVP]" wrote:
> Report parameters are easy to declare. Go to layout tab and report menu,
> report parameters. Add a parameter.
> Now, you don't really say where you want to use this expression. Is
> EarnDed00 a field in a dataset? Are you wanting to use this expression to
> pass a value to a stored procedure? Are you wanting the expression to be
> shown in a field of the table object?
> Read up on expressions. And note, when you create a query parameter RS
> automatically creates a report parameter but you don't have to use it, you
> can map a query parameter to an expression.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NormaD" <NormaD@.discussions.microsoft.com> wrote in message
> news:98012AA4-B6EA-4270-8BFB-265E8E35152B@.microsoft.com...
> >I need to declare a report parameter, for example BegPeriod. Once that
> > parameter has been declared I need to work the parameter. For example the
> > parameter needs to evaluated with the following statement: "if
> > right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the
> > value
> > in EarnDed00 else 0 Jan". This returns a month, year and deduction value.
> > that will need to be done for all months.
> >
> > Will Reporting Services do this? If so, can you direct me to the correct
> > method.
> >
> > Thank you,
> >
> > normad
> >
> >
>
>|||Easily. If you are using the table control add another column (right mouse
click on a column and add one to the left or right of an existing one). Then
do a right mouse click on the new field in the detail row and pick
expression. This brings up the expression builder. Read up in books online
how to write expressions. You can easily do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NormaD" <NormaD@.discussions.microsoft.com> wrote in message
news:FD3119FF-7E16-4B81-B7B9-DBC6AC230A5F@.microsoft.com...
> Bruce: Thank you. I want this expression to be shown in the report. Can
> this be done?
> Norma
> "Bruce L-C [MVP]" wrote:
>> Report parameters are easy to declare. Go to layout tab and report menu,
>> report parameters. Add a parameter.
>> Now, you don't really say where you want to use this expression. Is
>> EarnDed00 a field in a dataset? Are you wanting to use this expression to
>> pass a value to a stored procedure? Are you wanting the expression to be
>> shown in a field of the table object?
>> Read up on expressions. And note, when you create a query parameter RS
>> automatically creates a report parameter but you don't have to use it,
>> you
>> can map a query parameter to an expression.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "NormaD" <NormaD@.discussions.microsoft.com> wrote in message
>> news:98012AA4-B6EA-4270-8BFB-265E8E35152B@.microsoft.com...
>> >I need to declare a report parameter, for example BegPeriod. Once that
>> > parameter has been declared I need to work the parameter. For example
>> > the
>> > parameter needs to evaluated with the following statement: "if
>> > right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the
>> > value
>> > in EarnDed00 else 0 Jan". This returns a month, year and deduction
>> > value.
>> > that will need to be done for all months.
>> >
>> > Will Reporting Services do this? If so, can you direct me to the
>> > correct
>> > method.
>> >
>> > Thank you,
>> >
>> > normad
>> >
>> >
>>

Declaring a tablename in a sproc as a parameter

Hi

Can someone please shed some light as to how this can be done.

With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.

select * from @.tableName where condition = condition

exec ('select * from ' + @.tablename)

|||

Tried doing it with the statement and it doesnt work. Obviously you have to declare variable and set it, here's how I've done it below:

declare @.tablename varchar;
set @.tablename = 'tablename'
exec('select * from' + @.tablename)

I get an error saying invalid object name 't'

|||

try

declare @.tablename varchar (100)

|||

Yes, varchar == varchar(1).

Also you are missing a space after the FROM keyword: exec('select * from' + @.tablename), but, given the error message, this might just be the sample you have posted...

-LV

|||

I've got it working guys, thanks final code is:

declare @.tablename varchar(100);
set @.tablename = 'tbl_name'
exec('select * from ' + @.tablename)

Decimal Digits Lost When Using Decimal(9,2) Parameter

Hello,
I am trying to use a decimal type parameter in my stored procedure.
When I pass any decimal value to it (for example: 12.34), the decimal
part is always truncated off. I am left with only 12 as the value that
get put into my table. I have checked the obvious...the datatype is
decimal in the procedure. The data type for the column is decimal(9,2)
in my table. Any idea about what is going on here?Can you show some code so we can repro/diagnose?
<joey.powell@.topscene.com> wrote in message
news:1125598102.549921.46630@.g47g2000cwa.googlegroups.com...
> Hello,
> I am trying to use a decimal type parameter in my stored procedure.
> When I pass any decimal value to it (for example: 12.34), the decimal
> part is always truncated off. I am left with only 12 as the value that
> get put into my table. I have checked the obvious...the datatype is
> decimal in the procedure. The data type for the column is decimal(9,2)
> in my table. Any idea about what is going on here?
>|||joey.powell@.topscene.com wrote:
> Hello,
> I am trying to use a decimal type parameter in my stored procedure.
> When I pass any decimal value to it (for example: 12.34), the decimal
> part is always truncated off. I am left with only 12 as the value that
> get put into my table. I have checked the obvious...the datatype is
> decimal in the procedure. The data type for the column is decimal(9,2)
> in my table. Any idea about what is going on here?
You are proably using the value in a calculation that includes an
integer data type. Make sure all operations use decimal as the data
type.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joey,
The datatype DECIMAL is shorthand for DECIMAL(18,0). Change
the data type of the procedure parameter to DECIMAL(9,2).
Steve Kass
Drew University
joey.powell@.topscene.com wrote:

>Hello,
>I am trying to use a decimal type parameter in my stored procedure.
>When I pass any decimal value to it (for example: 12.34), the decimal
>part is always truncated off. I am left with only 12 as the value that
>get put into my table. I have checked the obvious...the datatype is
>decimal in the procedure. The data type for the column is decimal(9,2)
>in my table. Any idea about what is going on here?
>
>

Debugging SQL statement

I am working in SQL Server Reporting Services, and I have an error using a dynamic parameter. The error surfaces during runtime. I believe I could fix the parameter if I knew exactly what the resulting SQL statement looks like. So the question is: how do I view the actual SQL statement that is being executed in a report? I tried SQL Profiler, which shows how my parameter drop-down lists are being populated, but it does not show the main dataset SQL statement.

Alternatively, are there any examples of using a dynamic parameter that is a "datetime" data type?

Any help would be appreciated. Thanks.

Hello,

When are you getting the error, after setting your parameters when you click "View Report"?

You should be capturing the 'Exception' and 'SQL:BatchStarting' events in your trace. This will first whenever the report is started (regardless of error) and any error from the SQL statement.

Of course, if you have the RDL file, you can always look in there for the SQL command under the <CommandText> tag.

Hope this helps.

Jarret

|||

Thanks for responding.

Yes, I am getting the error after I click "View Report". I have the RDL file, but next to the <CommandText> tag, I see the same statement construct that I used in the Dataset (i.e. I see the IIf(parameter!... structure instead of an actual SQL statement) . I'll check out the trace again, but I'm certain that the only SQL statements that were in there pertain to setting up the parameter lists (e.g. "select salesrep from sourcetable").

Thanks again.

|||

Were you able to set up the trace and see the exception and the main dataset's SQL statement? Do you get the error when you're in BIDS using the preview tab? If so, you should be able to see the error (it may be in the output window, Ctrl+Alt+O).

If you have multiple dataset's there should be a <CommandText> tag for each one in your RDL file. Inside of each of these should be the SQL query. Are you saying you have references to Parameters!... in your dataset? Your dataset command should only contain a valid SQL statement, as this is what is sent to the DB server to be executed.

Go to the data tab in BIDS and select your main dataset from the 'Dataset:' dropdown. It should load that command (from the RDL file), then hit the red Exclamation button to run the query. Do you get an error?

Jarret