PDA

View Full Version : Prefill views with current year



jaarden
5-Aug-2013, 09:24 AM
Hi,

For some reports I use views with free parameters.
For one report I want to start default with the current year, thus @par1@ = currentyear.

What is the best way to do this?

Regards,

Jeroen

Bob Cergol
5-Aug-2013, 05:43 PM
Since you already know about using parameters in dyn-views, then I guess you also know about setting a default value for them. Parameters in dyn-views are not typed, but starting in version 6.4 I think, you can type them as dates by specifying a default using "@DATE:getdate()" or "@DATE:now()" and then Dynamic AI knows the parameter is of date-type and will present a date-picker calendar at report runtime.

Unfortunately in the current version you cannot use more complex date functions or expressions so you cannot format the date default with datepart to show only a year. You could do something simple like: @DATE:getdate()-30 to change the date from today's date.

An admittedly klunky approach would be to label the parameter so the user understands that any date they choose in the current year will be used to specify the year, i.e. no matter what today is -- 01/01/2013 .. 12/31/2013 will give them the same result in your report -- 2013

Alternatively, if you can rely on filters in the report -- instead of using parameters in the view, then you have a huge number of date functions you can specify as the default value on the date column. If you must use a parameter, perhaps because you are doing sub-selects in the view, then I think you could also just make the default value 2013 -- but then you have to maintain the default value once a year in your view.

Maybe Bo or Eddy have more creative ideas.

Bob

jaarden
6-Aug-2013, 01:51 AM
Thx Bob, I dig a little further to find the best approach for my case.

Jeroen

Bob Cergol
6-Aug-2013, 08:32 AM
One thing that occurred to me I didn't suggest because it seemed overly complicated for your purpose was perhaps to use a partition list. While creating partitions is not your purpose -- i.e. dynamically opening variable table names -- it is also a technique for dynamic replacement of practically unlimited variables in a dyn-view. One of those variables could be the year datepart of today's date. Maybe this could work for you. In case you're unfamiliar with partitions, attached is documentation I wrote for myself since I had trouble understanding the feature as documented. Anyway if you find some creative solution please share it here.
Regards,

Bob