December 28, 2015 at 12:17 pm
In an SSRS 2008 report, I want the user to be able to pick a date from the parameter called @requestedreceiveddate and have the default value set as todays date. This paramter is set to have a value of date/time.
The logic of the default value is the following: select cast(getdate() as date) as [Request Received Date] in a dataset called requestdate. and this works fine.
However when the user is able to select a requested received date from a list of values the calendar icon goes away and the drop down list values display date and time. I only want the date to be displayed. I am using the following sql:
SELECT DISTINCT personID, cast(value as date) as [Request Received Date],
FROM OPS.dbo.Customcustomer
wHERE attributeID = 2911
and personID = @personID
order by [Request Received Date]
Thus can you tell me how to have only a list of dates displayed in the dropdown list of dates that the user can select from?
December 28, 2015 at 5:09 pm
I think it's an XOR thing...
Either you can select a date (no list of values)... and you can use the Calendar control
OR
You can select a date from a dropdown.
you can't mix them. (I tried it and got the same thing... once you go for the "specify values..." part, you lose the Calendar control.) About your only option might be to use a Year dropdown then Month and then prompt for a list of available days..
SELECT myDate
FROM myTable
WHERE YEAR(myDate) = @SelectedDate
AND MONTH(myDate) = @SelectedMonth
Oh, and if you're going to use a bunch of dates like that, you might want a Calendar table that has columns for Year and Month... You really wouldn't want to calculate that stuff on the fly, because you'd get a table scan instead of anything efficient.
December 28, 2015 at 6:29 pm
I would like to 'You can select a date from a dropdown'. Can you tell me how to accomplish this goal?
December 28, 2015 at 7:28 pm
Right-click your date parameter > Parameter Properties.
Available Values > Get values from Query , then choose the dataset and the value and label fields.
Same as any other dropdown in SSRS.
December 29, 2015 at 10:24 am
I did as you suggested, "Right-click your date parameter > Parameter Properties.
Available Values > Get values from Query , then choose the dataset and the value and label fields.". The dropdown list displays the default date but the calendar goes away. I want the user to be able to change the date. I have tried to use a default dataset without the if statement listed below and I have tried to use the today() or now() ssrs functions but that did not work.
I am now using the following query:
--DECLARE @studentSelection TINYINT = 1 -- 0 = All Customers, 1 = One customer
--end debug
IF @studentSelection = 0
BEGIN
SELECT distinct @personID AS personID, cast(getdate() as date) as [Request Received Date], convert(varchar,cast(StatusReqDTEValue.[Request Received Date] as date), 101) as label
END
ELSE
BEGIN
SELECT DISTINCT personID, cast(value as date) as [Request Received Date],
FROM OPS.dbo.Customcustomer
wHERE attributeID = 2911
and personID = @personID
order by [Request Received Date]
I want the user to be able to change the default date if they need to. Thus can you tell me how to accomplish this goal?
December 29, 2015 at 5:55 pm
I don't think you can change the default date... well, the user can't.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply