March 30, 2009 at 3:10 pm
Hi,
I have a report with three parametres in the report.(2 date ranges and 1 drop downlist). Actually first parametres(DDL) has YTD, MTD, WTD and so on, when they select YTD the other two data range parametres should populate the dates accordingly. For example If it is MTD it would populate 3/1/2009 and 3/30/2009 in the two controls. Similarly this should work for Today, Yesterday, WTD(Week To Date), MTD(Month To Date), YTD(Year To Date). So the date population of the two date parametre controls are my inputs to the stored procedure which executes and get the data for the report.
This way user changes the value in drop down the date ranges should be populated or else he can change them manually. If the user select the drop down the start and end dates should be populated else user should be able to select his own data ranges and run the report also.
The approach i had right now is only working for first time and the second time user select value from drop down it values reamins same in the date input controls.
I have total 8 report parametres for my report. 3 of which are known to you according to above data given. The other 5 parametres are to hold the values of Today, Yesterday, WeekToDate,MonthToDate,YearToDate so that these are to be sent to the datetime report parametres controls.
I have used this non queried expression under DEFAULT VALUES( for my start date control:
=Switch(Parameters!TimePeriod.Value.Equals("TODAY"), Parameters!TodayDate.Value,Parameters!TimePeriod.Value.Equals("YESTERDAY"), Parameters!Yesterday.Value,Parameters!TimePeriod.Value.Equals("WEEKTODATE"), Parameters!WeekToDate.Value,Parameters!TimePeriod.Value.Equals("MONTHTODATE"), Parameters!MonthToDate.Value,Parameters!TimePeriod.Value.Equals("YEARTODATE"), Parameters!YearToDate.Value)
I have used this non queried expression under DEFAULT VALUES (Non-Queried) for my end date control:
=Switch(Parameters!TimePeriod.Value.Equals("TODAY"), Parameters!TodayDate.Value,Parameters!TimePeriod.Value.Equals("YESTERDAY"), Parameters!Yesterday.Value,Parameters!TimePeriod.Value.Equals("WEEKTODATE"), Parameters!TodayDate.Value,Parameters!TimePeriod.Value.Equals("MONTHTODATE"), Parameters!TodayDate.Value,Parameters!TimePeriod.Value.Equals("YEARTODATE"), Parameters!TodayDate.Value)
I have two datasets one for the report data and other data set for getting the default dates for different time periods like YTD, MTD, TODAY, YESTERDAY, WTD.
First data set is stored proc and second one is text "SELECT
DBO.ufn_GetDateOnly(GETDATE()) as [TodayDate],
DBO.ufn_GetYesterday(GETDATE()) as [Yesterday],
DBO.ufn_GetFirstDayOfWeek(GETDATE()) as [WeekToDate],
DBO.ufn_GetFirstDayOfMonth(GETDATE()) as [MonthToDate],
DBO.ufn_GetFirstDayOfYear(GETDATE()) as [YearToDate]"(only one rows for different dates)
the parametres used for dates have DEFAULT VALUES (Queried) set to the the column name of datasource 2.
I am not able to find why the dates are not changing when the user selected different TIME Period. It ONLY WORKS for the first time but not all times.
Please let me know how we can accomplish that. Any help would be greatly appreciated.
Thanks,
Suman
April 3, 2009 at 11:53 am
I too have found it difficult to modify date parameters that are cascaded from a single parameter like you are doing, allowing the user to modify them once they are populated.
Like you say the firs time it works, but after that the default values will not be processed because it already has a value.
I would be interested to see if you could up with any other responses or answers.
I did have a thought or two about perhaps simplifying your existing report. You populate 5 parameters that seem to do nothing but allow you to substitute their values based on the first DDL parameter. It seems like most of that could be done directly in the non queried defaults for your existing parameters or even directly in your SQL code.
Thinking more about your situation, Have you thought about adding another value to your DDL of Custom timeframe? Perhaps disable your datetimes if it's not set to custom? That way a user could just open the report select WTD and click view report. Or they click custom and get both datepicker parameters and you're off and running?
What do you think?
-Luke.
April 3, 2009 at 1:00 pm
Below given is the rdl format which will make you more clear on the issue i have. I show user with a drop down conrol with values added by me and the dates should be populating based on that. It only populates for the first time and not in the next moment. See the rdl file and execute so that things will be more clear.
Please modify the rdl accordingly and send back to me if you have any ideas.
Public Function StDateByTimePeriod(ByVal strTimePeriod As String,ByVal dtTodayDate as Date) As Date
' return the start date required for the selected time period
Select Case strTimePeriod
Case "Today": Return CDate(dtTodayDate)
Case "Yesterday": Return CDate(DateAdd("d",-1, dtTodayDate))
Case "DayBefore": Return CDate(DateAdd("d",-2, dtTodayDate))
End Select
End Function
Public Function EdDateByTimePeriod(ByVal strTimePeriod As String,ByVal dtTodayDate as Date) As Date
' return the end date required for the selected time period
Select Case strTimePeriod
Case "Today": Return CDate(dtTodayDate)
Case "Yesterday": Return CDate(DateAdd("d",-1, dtTodayDate))
Case "DayBefore": Return CDate(DateAdd("d",-2, dtTodayDate))
End Select
End Function
Any help would be greatly appreciated.
April 3, 2009 at 1:05 pm
April 3, 2009 at 1:11 pm
Thanks for the reply luke
I have attached the RDL file please see that and run it changing the extesnison of the file.
Thanks,
suman
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply