list parameterized dates

Hello,

For my report I want to have the days listed as a dropdown with simple dates during the month (3/27/2007), but there may be many records for that date as it is using the time as well. Unfortunately I don't even know where to begin

Ultimately the user will be given a dropdown listbox showing the simple dates only for the current month and on selection user will view only that days report. How do I basically treat each day as a group in the parameter?

Thanks!

Create a dataset with the following sql statement:

SELECT convert(varchar,<datefield>,101),<datefield>

FROM table

WHERE <datefield> between '01/01/2007' and '04/01/2007' specify the time frame here

group by convert(varchar,<datefield>,101),<datefield>

ORDER BY <datefield> -- add this field so you can sort by date

This will display only the date without the time for the time period specified in the where clause.

Then create a parameter in your report called date and select this dataset for the available values.

|||

Hello,

Will the date picker not work for you? If you set the data type of your parameter as DateTime, the user will get a little calendar button next to the parameter where they can select a date. If this won't work for you, you'll have to create a table in your database that holds all the possible values to be selected, then you will have to create another dataset (select Dates from CalendarTable) and use it as your parameters available values.

As for the issue with the records having the time in the datetime field... You can get around that by converting the field to a date without the time on it. For example, in your SQL query, you may have ... where DateField = @.DateParam. In order to fix this, do this instead ... where convert(varchar, DateField, 101) = @.DateParam. The 101 style will remove the time from your date.

Hope this helps.

Jarret

|||

This definitely is on the right track. Though I can't get it to only group by single dates.

My query window still has all corresponding records of the dates and not singular dates.

Question, on the CONVERT statement what does the number 101 represent?

Thank you Susan22

|||

I have yet to see a datepicker appear if I simply change the datatype to datetime.

Even if I just use a known datefield I get a querylist view of all the dated records.

I see where all this will be worth learning though. Just the basic reports (drag&drop) are amazingly useful. Thanks for your help.

|||

That's true and if you remove the ORDER BY and the date field and only select distinct records you won't be able to sort by date because the date field has been converted to string.

The 101 returns the date format MM/DD/YYYY.

I would follow Jarret's suggestion and create a non-queried parameter and type in all possible dates in the available values table.

|||

Are you on RS2000 or RS2005?

I could be wrong, but I don't think the date picker was available in RS2000, so if that's what you're using, you'll need to go the other route I mentioned in my previous post.

In regards to Susan's query, I don't think you were getting the grouped dates because the actual date was being grouped on also. Try this instead:

SELECT distinct convert(varchar,<datefield>,101)

FROM table

WHERE <datefield> between '01/01/2007' and '04/01/2007' specify the time frame here

group by convert(varchar,<datefield>,101)

ORDER BY <datefield> -- add this field so you can sort by date

Jarret