Format date parameter to year 2

[pb100]

In CRXI Developer, I am developing reports to deliver on CR Server using an ODBC connection to SQL 2005 Express. I have a table with a date field and want to create cascading(dependent) parameters where the user can filter which data to show in a chart by a chosen time period. The desire is to have the user first choose which years to include, then based on that, the remaining parameter choices would show only values that fall in the years chosen.
1. How can I define the parameter for my date field to show values for year only instead of each date? (i.e. 9/19/06 would show as 2006 in the parameter selection window)

Thanks in advance for any help!

 

[satinsilhouette]

The best way I can think of is to use a sql command object in which you could format the date and then create a parameter for it based off that field. This will feed up into CR, but the workload would stay on the database server.

Otherwise, you would have to format the field in a formula and then base your parameter off that formula field, but that leaves all the processing on the local machine.

Thanks!

Thanks so much!
satinsilhouette

 

[pb100]

Thank you so much for the suggestion, but I have not found a way to create a dynamic parameter from anything other than fields in tables. In the Create New Parameter window, I choose Dynamic, then Insert, and the only options are table fields. Is there another way to create a parameter where I can use SQL Expressions?
TIA!

 

[synapsevampire]

As Satin suggested, use the Add Command direclty below your SQL Server connection, and paste in the appropriate SQL, as in:

select year(table.field) from table

Now you can use te SQL command hat as the source for the parameter.

-k

 

[pb100]

Thank you so much! I had no idea where I would go to create a SQL command like that! Found it hiding in the Database Expert! It works great! Yay!