April 10, 2011 at 1:04 pm
Hi everyone,
I'm trying to create a report with 3 parameter radio button (boolean) like this:
- monthly
- weekly
- daily
I have too, 2 parameter datetime (StatDate and EndDate).
I need that when the monthly radio buttons are true, others are false and StartDate with day-30 and so on.
Can you help me ?
April 11, 2011 at 6:52 am
You can't really do it with a radio button. However you can make a single select parameter for your daily, weekly, monthly parameter. Then based on the selection made there have a the default values for your start and end dates be based on a query that looks at the selected value in the daily/weekly/monthly parameter to determine the start date.
The query would be something like
DECLARE @Offset TINYINT
SELECT @Offset = CASE WHEN @DWM = 'Daily' THEN -1
WHEN @DWM = 'Weekly' THEN -7
WHEN @DWM = 'Monthly' THEN -30
END
SELECT StartParam = DATEADD(dd,@Offset,GetDate()), EndParam = GetDate()
@DWM is the value selected by your parameter.
Your StartDate Parameter should be fed by the Start value from the query above and your EndDate Parameter should be fed by the End value from the query above.
One additional note, in order for this to work your @DWM parameter should be at the top of the list of parameters so that it is selected first-that way the dependant query and parameters can be populated.
April 11, 2011 at 8:04 am
Daniel,
I need to also maintain the ability for the user to choose the dates.
Today my following parameters has the following expression: = CDate (DateAdd ("d ", -30, now ())).
Can I use the CASE expression?
How to format "dd / mm / yyyy"?
Thanks ...
April 11, 2011 at 8:48 am
In my first entry if you make the StartParam and EndParam the default values of your StartDate and EndDate parameters respectively, and both your StartDate and EndDate parameters are visible, the users will still have the opportunity to change the dates as they desire.
With regard to your date format question. Living and working in the US, I unfortunately do not have a lot of experience with other date formats, other than making sure the PC environment is set up correctly. I expect if you Google SSRS date formats you will probably find what you need. You can always manually manipulate the date format in code but that would not be my first choice.
April 11, 2011 at 11:03 am
I tried to put that code, but the expression give me an error.
Where I put that code, please.
April 11, 2011 at 1:31 pm
First in the DECLARE statement change @Offset to a SMALLINT, not a TINYINT
Second, I did some poking around with date formats, and I found a format option that will show the date in dd/mm/yyyy format. Try this query for the dataset to feed the default values for the StartDate and EndDate parameters
DECLARE @Offset SMALLINT
SELECT @Offset = CASE WHEN @DWM = 'Daily' THEN -1
WHEN @DWM = 'Weekly' THEN -7
WHEN @DWM = 'Monthly' THEN -30
END
SELECT StartParam = CONVERT(VARCHAR(10),DATEADD(dd,@Offset,GetDate()),103)
,EndParam = CONVERT(VARCHAR(10),GetDate(),103)
April 12, 2011 at 5:56 am
Hi Daniel,
I created a new DataSet with that code, but the Date_time is not populated.
I put that code as shown. What am I doing wrong?
Tks,
Robson
April 12, 2011 at 6:20 am
Hi Daniel,
Got it. But, I have enabled the option of letting the user choose their own date with a calendar. Converted to datetime, but failed.
how I do it ?
April 12, 2011 at 6:30 am
Hi Daniel
Got it. but just putting a default value.
how do I clear the default value of each choice?
April 12, 2011 at 6:56 am
If the default value is populated, you should be able to type over the default values with new values.
April 12, 2011 at 7:10 am
The problem is: suppose someone choose daily, and then clicks on a monthly basis, the value of the start date does not update.
April 12, 2011 at 9:01 am
The parameter where they choose Daily, Weekly, Monthly should be the first parameter in the list.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply