set default value of date paramter as getdate()-1 in SSRS report

Question

Post reply

set default value of date paramter as getdate()-1 in SSRS report

sql_ques

SSCommitted

Points: 1546
More actions
April 12, 2012 at 4:54 am

#392001

Hi,
What is the expression to set the default date of date parameter to getdate()-1. I want the SSRS report to choose the Startdate as previous date and end date as current date.
I set the default value of parameter as =Today(), which displays current date, how to set it to previous date? Any ideas what should be the expression?
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 1

Try this:

=DateAdd("d",-1,Today())

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sql_ques SSCommitted Points: 1546 More actions · Answer 2

sql_ques

SSCommitted

Points: 1546

April 12, 2012 at 7:22 am

#1472559

Thanks. It worked.

Heinrich-206378 Old Hand Points: 390 More actions · Answer 3

Heinrich-206378

Old Hand

Points: 390

March 27, 2015 at 4:20 am

#1786792

Many thanks

A.F. SSC Veteran Points: 200 More actions · Answer 4

A.F.

SSC Veteran

Points: 200

July 17, 2015 at 4:58 am

#1813219

Very helpful! Thanks!

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 5

For some reason the VB functions like "=Today()" would not work for the default value for a date parameter (it gave me the red angry line but would not say why it didn't like it).

I ended up making a dataset as follows and it worked fine.

select convert(date,convert(char(10),getdate(),101)) as Today

SpaveNation SSCommitted Points: 1563 More actions · Answer 6

so in SSRS what if I wanted the date to always be tomorrows date?

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 7

rcooper 78099 (3/10/2016)
so in SSRS what if I wanted the date to always be tomorrows date?

You should be able to come up with that yourself.

This is the calculation to find "yesterday":

=DateAdd("d",-1,Today())

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SpaveNation SSCommitted Points: 1563 More actions · Answer 8

=DateAdd("d",-1,Today())

I am assuming it would be

=DateAdd("d",+1,Today())

or

=DateAdd("d",1,Today())

which have both not worked.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 9

rcooper 78099 (3/10/2016)
=DateAdd("d",-1,Today())
I am assuming it would be
=DateAdd("d",+1,Today())
or
=DateAdd("d",1,Today())
which have both not worked.

In what sense?

Is an error returned?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

rcooper 78099 (3/10/2016)
=DateAdd("d",-1,Today())
I am assuming it would be
=DateAdd("d",+1,Today())
or
=DateAdd("d",1,Today())
which have both not worked.

What do you mean by have not worked? How are you assigning the default value?

I just tested both approaches and they work.

Can you share more details?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SpaveNation SSCommitted Points: 1563 More actions · Answer 11

I am told that both of those are incorrect

with a basic error of Value not recognized.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

Did you follow these steps?

- Open the Parameter properties.

- Set Data type to Date/Time.

- Go to Default Values tab.

- Click on the formula (fx) button.

- Write or paste the expression.

- Click OK to confirm the expression, then again to confirm the changes on the parameter properties.

- Test your report.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SpaveNation SSCommitted Points: 1563 More actions · Answer 13

The value provided for the report parameter 'ChkPrintDate' is not valid for its type.

This is the error produced on my front end.

SpaveNation SSCommitted Points: 1563 More actions · Answer 14

unfortunately I do not have those options see screen shot attached.

Luis Cazares (3/10/2016)
rcooper 78099 (3/10/2016)
=DateAdd("d",-1,Today())
I am assuming it would be
=DateAdd("d",+1,Today())
or
=DateAdd("d",1,Today())
which have both not worked.
What do you mean by have not worked? How are you assigning the default value?
I just tested both approaches and they work.
Can you share more details?

set default value of date paramter as getdate()-1 in SSRS report

Cookies on SQLServerCentral