SQL Reporting Services 2005 - Non-queried Report Parameters

Question

I'm working on a report in Reporting Services and I can't figure out why I'm having trouble with Non-queried report parameters.

I'm trying to get the current year and have tried:

=YEAR(TODAY())    
=DATEPART("yyyy",TODAY())    

I have also tried TODAY instead of TODAY()

All of these seem to break the Year dropdown on my report. I thought if something was wrong it would just not get the correct default... but nope, it breaks the whole field.

Any thoughts? articles?

UPDATE:
Wait, wait, wait... the weirdest thing. The Year parameter is the second parameter of this report. And its grayed out (with no value) UNTIL I select the first parameter (im my case "category"). Am I somehow telling the Year param to do this? or does SSRS 2005 process the params in order? I don't think I ever noticed this before.

UPDATE 2:
Please see all comments

Answer 1

=DateTime.Today.Year

should work as well.

Edit: Bruno - i have the same behavior as you are seeing. I created a sample report with a string first parameter with no default value, and a string 2nd parameter with a default of =DateTime.Today.Year. When I have the one with the default as the 2nd parameter in the order, it shows up empty and disabled.

I was able to fix this problem 2 ways: first by adding a default of =String.Empty to my first parameter, and the second way was to just change the order of the parameters.

Not sure if this behavior is by design or a bug - but like you said, I hadn't noticed it either until today when you pointed it out in your question.

Answer 2

Try:

=Year(Now)

I'm not sure if this is what you need, but it worked for me. I used it to form a date string so I used =Year(Now).ToString().

Answer 3

SSRS does process the report parameters in order - order can be important if you have dependencies between your parameters.

From MSDN: "Parameter order is important when you have cascading parameters, or when you want to show users the default value for one parameter before they choose values for other parameters."

So, it will always wait until you have a default value for your first parameter or you enter the first parameter's value, before it processes the next one, and so on.

http://msdn.microsoft.com/en-us/library/cc281392.aspx

Answer 4

I found the by making sure that ALL of my parameters had at least some default value, then you will not experience they greyed out datetime picker. So, every parameter before your datetime paremeter in order needs to have a (default) value or it will not work.

Answer 5

Def due to order of evaluation of parameters.

Simple workaround ; in the parameters tab you can change the order of the parameters - this will move the date pickers to the top of the list and are then enabled straight away, on my server at least.

Someone raised the issue of basing a date filter on a queried default value - if you do this, you will notice a very annoying knock-on effect of getting screen refresh any time the dates get changed, before you have a chance to requery the reports.